473,748 Members | 2,265 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 23902
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
5996
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
6592
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
1566
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
2647
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
5904
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
6964
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
12899
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
1983
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
8980
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
9356
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...
0
9231
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
8235
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
4591
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...
0
4862
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
3297
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
2773
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
2204
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.