Re: Benefits of cursors?

Gary L. Burnore (gburnore@netcom.com) wrote: : : 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 : 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. 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. DCT -- @ David Tomlinson @ @ dtomlnsn@netcom.com @ @ "By day fantastic birds flew through the petrified forest, and @ @ jeweled crocodiles glittered like heraldic salamanders on the @ @ banks of the crystalline river. By night the illuminated man @ @ raced among the trees, his arms like golden cartwheels, his @ @ head like a spectral crown...." @