Re: Benefits of cursors?

David Tomlinson (dtomlnsn@netcom.com) wrote: : Gary L. Burnore (gburnore@netcom.com) wrote: David, I (Gary) did not write this: : : : Maybe I should restate my original post. I know what the syntax of the : : : cursor is, allowing you to select multiple rows from a table. : : : It has been stated the using prepared/declared cursors for select : : : statements, : : : __even__ those that would only return one row, are more efficient if the : : : statement is to be executed more than once. This is what I would like : : : an explanation of. Is it because the optimizer only has to choose a path : : : through the indexes once? : : : Sorry about any confusion I ( gary wrote this) : : No problem. Because you declare the cursor first, this means that the : : indexes only need to be read once. This would make subsequent fetches : : (aka selects) faster. I don't know if/why it would be faster for one : : instance. I'd venture to say that the difference in performance for one : : row wouldn't be noticible. If I know for _sure_ that I'll only get one : : row, I just use a select. If someone has a good reason to always use the : : declared cursor I'd like to know. You (David) wrote this. : No. SQL needs to be pre-processed before the query is ever executed. : I don't have all of the documentation in front of me, but the main things : that happen are the statement is processed from SQL to a form : that may be directly used by the engine and the optimizer takes a look : at the statement and it's stored statistics about the tables involved : and will choose the optimal path to the data (it sometimes optimizes : the actual statement.) : This pre-processing takes time and resources. When you declare a cursor, : this is only done once. Subsequent Open and Fetch cycles then no : longer have the overhead that is associated with this processing. : This, of course, makes subsequent cycles more efficient. : If you *know* there will only be one tuple returned and you will : execute the SQL statement only once in the life of the program, : it is not necessary or desirable to declare a cursor. I'snt what you wrote and what I wrote the same thing? (declared cursors only pre-proceessed once) -- gburnore@netcom.com ------------------------------------------------------------------------------- How you look depends on where you go. -------------------------------------------------------------------------------