Server Side Cursor vs client side cursor

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

Server Side Cursor vs client side cursor

Author

Topic

vandelft
Starting Member

14 Posts

Posted - 2005-10-14 : 07:28:28

Hi all,

I have the following trace result generated by SQL server Profiler.

I see in the trace a lot thing like the following:


declare @P1 int
set @P1=180153616
declare @P2 int
set @P2=1
declare @P3 int
set @P3=4
declare @P4 int
set @P4=1
exec sp_cursoropen @P1 output, 
N'SELECT * FROM X400tbl 
WHERE adr_address=''ADDRESS123''', 
@P2 output, 
@P3 output, 
@P4 output

select @P1, @P2, @P3, @P4
Go
exec sp_cursorfetch 180153616, 1, 0, 100
Go
exec sp_cursorfetch 180153616, 1, 0, 100
Go
exec sp_cursorfetch 180153616, 8, 1, 100
Go
exec sp_cursorfetch 180153616, 8, 1, 100
Go
exec sp_cursorfetch 180153616, 1, 0, 100
Go
exec sp_cursorfetch 180153616, 1, 0, 100
Go
exec sp_cursorclose 180153616
Go
exec sp_cursorclose 180153616
Go

do this means that to execute the select statement that there is 9 rounttrips to the server?
Can any one exlpain to me what the result of the trace means?

graz
Chief SQLTeam Crack Dealer

4149 Posts

Posted - 2005-10-14 : 07:41:52

Typically you see this when a client opens two resultsets on the same connection. Basically it has to do a round trip for each row returned plus one to intiate the cursor and one to close the cursor. And these are SLOW. Make sure you only have one active recordset on a connection and you should be ok.

===============================================
Creating tomorrow's legacy systems today.
One crisis at a time.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources