473,897 Members | 3,371 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

SQL Server 2005 and Cursors

Hi Everyone!

We are using a cursor for paging results in SQL server, mainly due to
the performance gains achieved when working with large results sets.
We have found this to be of great benefit when working with SQL Server
2000, but have run into major problems when using it on SQL Server
2005.

The query goes like this:

DECLARE @PageSize int
SET @PageSize = 10

DECLARE @PK int
DECLARE @tblPK TABLE (
PK int NOT NULL PRIMARY KEY
)
DECLARE PagingCursor CURSOR DYNAMIC READ_ONLY FOR
SELECT KeyID FROM JobTable WHERE KeyID IN(SELECT KeyID FROM JobTable
WHERE Criteria = TRUE) ORDER BY JobTable.KeyID
OPEN PagingCursor
FETCH RELATIVE 1 FROM PagingCursor INTO @PK
SET NOCOUNT ON
WHILE @PageSize > 0 AND @@FETCH_STATUS = 0
BEGIN
INSERT @tblPK (PK) VALUES (@PK)
FETCH NEXT FROM PagingCursor INTO @PK
SET @PageSize = @PageSize - 1
END
CLOSE PagingCursor
DEALLOCATE PagingCursor

SELECT ResultsFields FROM JobTable INNER JOIN @tblPK tblPK ON
JobTable.KeyID = tblPK.PK WHERE Criteria = TRUE ORDER BY
JobTable.KeyID

I know this doesn't look as optimised as it should but there is a lot
happening under the hood to get it to this point. This aside, there
must be a reason why performance suffers so much with SQL 2005?
Inserting a print statement into the cursor loop outputting the date
stamp showed that each iteration was taking approx 4.5 seconds. This
is a problem we never experienced in SQL Server 2000.

Any help would be greatly appreciated!

Thanks in advance,
Steve

Jun 22 '06 #1
1 23912
mrcraze (st*********@ho tmail.com) writes:
We are using a cursor for paging results in SQL server, mainly due to
the performance gains achieved when working with large results sets.
We have found this to be of great benefit when working with SQL Server
2000, but have run into major problems when using it on SQL Server
2005.
...
DECLARE PagingCursor CURSOR DYNAMIC READ_ONLY FOR
DYNAMIC? Is there any particular reason you are using a dynamic cursor?
Dynamic cursors makes me very nervous. I always run my cursors
INSENSITIVE. Dynamic is not what you use for good performance.

It also seems like a bad idea for paging. When paging, you want a consistent
result. That is, if I perform a search, and my search matches say 59
objects. What I really want is to view all 59 objects at once. However,
there are many web authors out there, who think that I cannot digest more
than 10 at a time. Anyway, I first get to see hits 1-10. Then I expect
to see hits 11-20 next time. But if you rerun the query each time,
I might get to see hits 12-21, because a new item is now #5. If you use a
dynamic cursor, things gets even more complicated.
I know this doesn't look as optimised as it should but there is a lot
happening under the hood to get it to this point. This aside, there
must be a reason why performance suffers so much with SQL 2005?
Inserting a print statement into the cursor loop outputting the date
stamp showed that each iteration was taking approx 4.5 seconds. This
is a problem we never experienced in SQL Server 2000.


As I said, dynamic cursors always make me nervous, and I have more
than once run into issues where dynamic cursors have lead to query
plans from hell.

I tried your example in a translated version on in an exteneded version
of the Northwind database, but I did notice any difference between
SQL 2000 and SQL 20005. But without knowing your table or what
"criteria = TRUE" stands for it's difficult to try to recreate the
problem without further information.

A good source on different ways to do paging is
http://www.aspfaq.com/show.asp?id=2120.

--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Jun 23 '06 #2

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

5
5999
by: christopher.secord | last post by:
Here's a quick and dirty example of a legitimate bug in SQL Server. I've seen other examples, but they were all very complex, some even involving cursors and such. This one will produce the error with just 13 lines. A police department has a database that contains, among other things, information about parking tickets given out by the officers. Here is the database and some sample data: create table ParkingTickets (
4
6604
by: Prince Kumar | last post by:
I joined a company recently and they have a java program which hangs (does nothing) after a while. This is no way consistent. It could succeed quite a few times and can fail a few other times. There is no consistency when it fails. Could anyone here shed some light on how to debug/resolve the issue. I guess IBM looked at the issue and were not able to pinpoint where the issue is. When the program hangs and when force the DB2...
1
1569
by: joshsackett | last post by:
Here is an obscure question that I am sure can be dealt with from a 10,000 ft view: I have installed Fuego (an enterprise level BPM). Another application was built in-house to connect to Fuego (Fuego has two SQL 2000 databases to handle the business process flow and instance flow). This in-house application then uses Tomcat & JDBC to connect to the above mentioned Fuego databases and a third SQL 2000 database that handles the actual...
17
2655
by: IanIpp | last post by:
We have a 3 month old quad processor/dual core server running SQL Server 2005 and already it is getting close to hitting the CPU wall. An 8 way CPU box is prohibitively expensive and out of the question. I am looking desperately for a way to TRULY scale out SQL server...in the same way that IIS can be scaled out via App Center. The "in the box" solution for SQL Server 2005 scaling out is the DMV. Unfortunately this solution makes...
14
5911
by: dba_222 | last post by:
Dear experts, Again, sorry to bother you again with such a seemingly dumb question, but I'm having some really mysterious results here. ie. Create procedure the_test As
2
6991
by: Jobs | last post by:
Download the JAVA , .NET and SQL Server interview with answers Download the JAVA , .NET and SQL Server interview sheet and rate yourself. This will help you judge yourself are you really worth of attending interviews. If you own a company best way to judge if the candidate is worth of it. http://www.questpond.com/InterviewRatingSheet.zip
0
12910
Coldfire
by: Coldfire | last post by:
Since i cannot show the differences in a two-column like table. I am first putting MS SQL Server 2005 and then MySQL 5.x. MS SQL Server 2005 Brief Overview - SQL Server is a full-fledged database system developed specifically for large enterprise databases. All advanced features of a relational database are fully implemented. - Once you purchase the product, you are only limited to the Sybase-derived engine.
8
1992
by: Tony Toews [MVP] | last post by:
Thanks to a posting by fellow MVP Steve Foster On a computer that is running Windows Vista, Windows Server 2008, or Windows XP, an incorrect value is returned when an application queries the identity column value of a newly inserted row in various versions of SQL Server 2005 and of SQL Server 2000 http://support.microsoft.com/kb/951937 Now I don't *KNOW* that this affects Access but the KB article states "Applications that use the...
0
9837
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
11250
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
10850
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
10941
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
10478
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
9656
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
0
5873
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
2
4293
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
3300
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.