July 2, 2015 at 1:38 pm
Hello, so async cursor population is supposed to create the cursor and return the cursor id quickly, while the server works on async populating the results. For a keyset-driven cursor, SQL Server stores the key sets in tempdb, which it then uses to fetch data for cursor results. Anyway, this works fine for smaller tables, but I'm finding for large result sets, the async cursor population is very slow and indeed seems to approximate synchronous time. The wait stat I get while it is running (supposedly asynchronously) is TRANSACTION_MUTEX.
Example:
--enable async cursor
exec dbo.sp_configure 'cursor threshold', 0; reconfigure;
declare @cursor int, @stmt nvarchar(max), @scrollopt int, @ccopt int, @rowcount int;
--example of giant result set
set @stmt = 'select * from sys.all_objects o1, sys.all_objects o1';
set @scrollopt = 1 | 32768 | 65536 | 524288; --keyset, check types, key acceptable, static acceptable
set @ccopt = 1 | 8192; --readonly, allow direct
begin transaction;
--create cursor
exec sp_cursoropen @cursor = @cursor output, @stmt = @stmt,
@scrollopt = @scrollopt output, @ccopt = @ccopt output, @rowcount = @rowcount output;
--select session_id, cursor_id, properties, is_async_population FROM sys.dm_exec_cursors(0) where cursor_id = @cursor
commit transaction;
select @cursor cursor_id; --select cursor id
--close cursor
exec sp_cursorclose @cursor = @cursor
Note that using the SQL "select * from sys.all_objects o1" is much faster than "select * from sys.all_objects o1, sys.all_objects o2". However, if cursor population is async, I'd expect the time to return a cursor id to be similar between the two.
Thanks!
July 2, 2015 at 2:16 pm
Cursors generally perform horribly. If you give us more details about what you're trying to accomplish, we might be able to help you rewrite it without cursors.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
July 2, 2015 at 2:21 pm
Yeah, I have to agree with Drew, and for all the same reasons. I do have to ask though... why does it matter if there's a difference or not? For small datasets, whatever difference there might be is probably irrelevant anyway, but for large datasets, why bring a cursor into the mix? RBAR (aka row by agonizing row) is not a good way to handle large data volumes.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
July 2, 2015 at 4:38 pm
I'm mostly curious why SQL Server's async cursor population is not working as I would expect. I have no exact problem to be solved. In general, an async cursor population can be used to support server-side pagination. Although it is not the best solution, I still wanted to explore it but found that async was pretty slow for large result sets.
Imagine a database contains millions of records and a user searches for some sub-set that happens to still contains thousands, or even hundreds of thousands of them. Returning them all to the user in one set will likely crash whatever browser / front-end they use, but also will take lots of time while the user waits for a response. One method is to immediately return the top(X) records to the user where X is a manageable number, and then async shoot off a process that will build the next X records, and so on. The async bit is achievable in SQL Server through Service Broker. Of course we would not want to build all pages because that would likely be a waste. We could store the key set for the request though and only build the individual pages on demand. This supports page jumping, next, prev while still giving the user a perception of very fast performance. Anyway, this is all possible to setup and in my experience works quite well, but I was curious if async cursor population could be a viable shortcut. In other words, use a keyset driven cursor that is async populated for the paginated request. Now you have a cursor id and you can use the cursor fetch procedure to generate any page from the temporary keyset in tempdb (until the cursor is closed).
Thanks!
July 4, 2015 at 10:24 am
blueazul (7/2/2015)
I'm mostly curious why SQL Server's async cursor population is not working as I would expect. I have no exact problem to be solved. In general, an async cursor population can be used to support server-side pagination. Although it is not the best solution, I still wanted to explore it but found that async was pretty slow for large result sets.Imagine a database contains millions of records and a user searches for some sub-set that happens to still contains thousands, or even hundreds of thousands of them. Returning them all to the user in one set will likely crash whatever browser / front-end they use, but also will take lots of time while the user waits for a response. One method is to immediately return the top(X) records to the user where X is a manageable number, and then async shoot off a process that will build the next X records, and so on. The async bit is achievable in SQL Server through Service Broker. Of course we would not want to build all pages because that would likely be a waste. We could store the key set for the request though and only build the individual pages on demand. This supports page jumping, next, prev while still giving the user a perception of very fast performance. Anyway, this is all possible to setup and in my experience works quite well, but I was curious if async cursor population could be a viable shortcut. In other words, use a keyset driven cursor that is async populated for the paginated request. Now you have a cursor id and you can use the cursor fetch procedure to generate any page from the temporary keyset in tempdb (until the cursor is closed).
Thanks!
As you appear to have discovered, just having "async" in play doesn't, by itself, provide good performance. Delivering large quantities of records into ANY resultset is always going to have potential for trouble, and the number of potential causes expands quickly as the total number of records or total data size increases. One question I wonder about is whether users might be happier to be given a choice about situations returning a large number of records. Might one want to ask the user whether they'd be willing to wait 15 to 30 seconds for a complete result due to the large quantity of data.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
July 4, 2015 at 12:04 pm
Sorry, I cannot ask users to wait 15-30 seconds. In any case, even 30s might not be enough depending on the data size. Yet, regardless of data size, the first X rows can be returned quickly (assuming proper indexing). I'm certainly open to any new ideas, but users waiting for an entire result set is not going to work. In any case, it sounds like SQL Server async cursor population is not a viable shortcut due to it taking quite awhile to return the cursor id for large result sets. Implementing my own key set table and async population through service broker does however provide users with fast performance perception on large result sets.
July 7, 2015 at 10:06 am
Still would help to know what you are trying to accomplish. We can't see what you see and don't understand the problem you are trying to solve. From one of your posts it seems you are trying to accomplish some sort of server side pagination.
Care to provide a more detailed explanation of what you are trying to accomplish?
July 7, 2015 at 12:24 pm
I don't think SQL is using async processing for that specific cursor, based on this information from Books Online:
"
SQL Server does not support generating keyset-driven or static Transact-SQL cursors asynchronously. Transact-SQL cursor operations such as OPEN or FETCH are batched, so there is no need for the asynchronous generation of Transact-SQL cursors. SQL Server continues to support asynchronous keyset-driven or static application programming interface (API) server cursors where low latency OPEN is a concern, due to client round trips for each cursor operation.
"
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply