|
Using the pyodbc library with a python front end and a SQL Server 2005, if I execute a select statement with a cursor how often will it interact with the sql server? Will it only send the command with the cursor and return the results one time? Or will it send a request back and take up server time with every use of fetchone command or equivalent? If it has to interact with the server for every use of fetchone or equivalent, does that mean it is more effecient to use fetchall? |
|
Use a trace against your server. if the odbc library is using sp_cursor% functions then it will be getting the data on a row by row basis. if it is just a 'normal' select then it should be caching the entire result set on the client. |
|
I have never used python so I can't say for sure, but I never use a cursor to interact with the front end! :) In SQL server it is almost always best to think SET based and not ROW based (like cursors). I always try to release the connection to the SQL server as fast as I can to free up locks, etc, and a cursor keeps the connection until all records are retrieved or you break the connection for some reason. "Do not think of what you want to do with a row, think of what you want to do with a column" (quoute from someone, I can't remember who) 1
There is a difference between client side and server side cursor. In a declarative language like T-SQL cursors should be used only when absolutely necessary, and that is rare indeed. In a more imperative language such as Python, cursors make much more sense, and if using pyodbc in particular you cannot extract data from a SQL table without a cursor.
(Nov 18 '09 at 00:29)
TimothyAWiseman
+1 Thank you Timothy for information. It is nice to know if I ever come across Python.
(Nov 18 '09 at 07:54)
Håkan Winther
|
|
As suggested I finally got some time and did several iterations of using select statements with pyodbc with a trace running. This testing is far from exhaustive, but here are the preliminary results:
In other words, if at least in my limited testing you can execute a select statement in SSMS and the same select statement through the pyodbc library in Python. They will perform exactly the same number of reads, use almost exactly the same amount of CPU resources. But the select statement to SSMS will return its rows as fast as possible and then be gone. The pyodbc version on the other hand will stay open, holding any locks it needed, until either every row is explicitly fetched or that query is explicitly closed in the program. The practical effect of this is that in many instances, it is better to use fetchall() and move all the rows into a list as soon as the query is executed. This will release any locks that had been established for that connection and be better for concurrency. As with most things in programming, I can think of exceptions. But that seems to be the general rule. |
