Physical Implementation Of Cursors???
-
Yes, yes, I know...avoid cursors...and, I do. In fact, that is why I now have a question. I am reviewing a third party tool that does incremental data retrieval and I'm assuming it must use cursors to do the page refreshes.
I assume this because lazy where clause criteria can lead to hundreds of thousands of records being returned, yet this application's forms quickly return the first ten rows. If you hit page down, it very quickly returns another 10 rows and so on and so forth.
So, as an experiment I created a cursor against a table with 2,000,000 million rows and open it. Wow - it did that in milliseconds. Next, I use a while loop to fetch 10 records at a time. Each 10 records again takes milliseconds.
Ok, so now I see how the vendor is probably doing it. You may think this is a funny think to have to experiment with, but I always work with sets of data - I've never considered retrieving 10 records at a time.
Now I'm curious about the physical implementation of the cursor. What the heck would the execution plan look like? It obviously doesn't put all 2 million records into a temp object when I declare the cursor or it would take longer than a couple of milliseconds. I can't imagine that the cursor sits around with a lock on all joined tables and keeps track of some ordinal position or something... What's the deal?
-
I think it pulls some rows and then starts to return them. As you access more, it pulls more. Curious, is there an ORDER BY clause in the select?
The other thing to be sure is that it's pulling a read only, firehose cursor, to be sure that you won't block anything else.
-
It could still be done in set based logic. If the calling app sends in the page number it wants and the page size, the following query logic can be used.
-- Count the total rows selected
SELECT @RowCount = COUNT(*)
FROM #Page
-- Calculate the max number of pages that can be displayed
SELECT @MaxPages = CEILING((@RowCount/CONVERT(decimal(10,2),@PageSize)))
-- Reset passed Page to the highest available page if it is greater than the
-- Max pages.
SELECT @Page = CASE WHEN @MaxPages < @Page
THEN @MaxPages
ELSE @Page
END
-- Return Total Rows result set
SELECT TotalRows = @RowCount
-- Return Case List result set
SELECT <whatever>
FROM #Page [CASE]
WHERE RowID >= (@Page * @PageSize) - @PageSize + 1
AND RowID <= (@Page * @PageSize)
ORDER BY RowID
Cheers
Mark
-
Mark, yes, I know this can be solved using a set. Although, based on the characterstics of the forms and the speed of the initial load, etc., the only way I have been able to create similar functionality/performance was with a cursor. ...which is why I was so curious about the nature of the physical implimentation of the cursor.
I built a few different scenerios using a table with 2 million rows. The only way I was able to get an instantaneous 10 rows ,and 10 rows only, (upon a user driven event) was using the cursor.
-
How you define where to start cursoring if you switch from 91-100 range to 101-110 range?
Do you do cursoring through first 100 rows?
_____________
Code for TallyGenerator -
About the physical implementations of cursors.. Well it apparently depends.. However, I do think that one shouldn't be too surprised if there would be more locks taken and more tempspace used under the hood, than what one would be led to believe.
Here's a brief overview snippet from BOL:
Cursor Types
ODBC, ADO, and DB-Library define four cursor types supported by Microsoft® SQL Server™2000. The DECLARE CURSOR statement has been extended; thus you can specify the four cursor types for Transact-SQL cursors. These cursors vary in their ability to detect changes to the result set and in the resources, such as memory and space in tempdb, they consume. A cursor can detect changes to rows only when it attempts to fetch those rows a second time. There is no way for the data source to notify the cursor of changes to the currently fetched rows. The ability of a cursor to detect changes is also influenced by the transaction isolation level.
The four API server cursor types supported by SQL Server are:
- Static cursors
- Dynamic cursors
- Forward-only cursors
- Keyset-driven cursors
Static cursors detect few or no changes but consume relatively few resources while scrolling, although they store the entire cursor in tempdb. Dynamic cursors detect all changes but consume more resources while scrolling, although they make the lightest use of tempdb. Keyset-driven cursors lie in between, detecting most changes but at less expense than dynamic cursors.
Although the database API cursor models consider a forward-only cursor to be a distinct type of cursor, SQL Server does not. SQL Server considers both forward-only and scroll as options that can be applied to static, keyset-driven, and dynamic cursors.
-- end snip ---
Cursors is a tricky subject, to say the least.
/Kenneth
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply