My 3rd party softwares uses tons of cursors.

Question

My 3rd party softwares uses tons of cursors.

Viewing 9 posts - 31 through 38 (of 38 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004290 More actions · Answer 1

andrew gothard (8/13/2008)
But, there's even a way around that (and many similar problems not listed) without an explicit cursor or While loop OR UDF using a single set-based update to solve a procedureal problem...
http://www.sqlservercentral.com/articles/Advanced+Querying/61716/
Oh - I'd use a UDF now, but they didn't have them in v7, hence the cursor for that one occasion for that particular problem

Heh... cool! You write your UDF code to do a running total on the "SomeMoney" column in the following test table, and let's have a race. 😉

--===== Create and populate a 1,000,000 row test table.

-- Column "RowNum" has a range of 1 to 1,000,000 unique numbers

-- Column "SomeInt" has a range of 1 to 50,000 non-unique numbers

-- Column "SomeLetters2" has a range of "AA" to "ZZ" non-unique 2 character strings

-- Column "SomeMoney has a range of 0.0000 to 99.9999 non-unique numbers

-- Column "SomeDate" has a range of >=01/01/2000 and <01/01/2010 non-unique date/times

-- Column "SomeCSV" contains 'Part01,Part02,Part03,Part04,Part05,Part06,Part07,Part08,Part09,Part10'

-- for all rows.

-- Column "SomeHex12" contains 12 random hex characters (ie, 0-9,A-F)

-- Jeff Moden

SELECT TOP 1000000

RowNum = IDENTITY(INT,1,1),

SomeInt = ABS(CHECKSUM(NEWID()))%50000+1,

SomeLetters2 = CHAR(ABS(CHECKSUM(NEWID()))%26+65)

+ CHAR(ABS(CHECKSUM(NEWID()))%26+65),

SomeCSV = CAST('Part01,Part02,Part03,Part04,Part05,Part06,Part07,Part08,Part09,Part10' AS VARCHAR(80)),

SomeMoney = CAST(ABS(CHECKSUM(NEWID()))%10000 /100.0 AS MONEY),

SomeDate = CAST(RAND(CHECKSUM(NEWID()))*3653.0+36524.0 AS DATETIME),

SomeHex12 = RIGHT(NEWID(),12)

INTO dbo.JBMTest

FROM Master.dbo.SysColumns t1,

Master.dbo.SysColumns t2 --Lack of join criteria makes this a CROSS-JOIN

--===== A table is not properly formed unless a Primary Key has been assigned

-- Takes about 1 second to execute.

ALTER TABLE dbo.JBMTest

ADD PRIMARY KEY CLUSTERED (RowNum)

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 2

Jeff Moden (8/13/2008)
andrew gothard (8/13/2008)
But, there's even a way around that (and many similar problems not listed) without an explicit cursor or While loop OR UDF using a single set-based update to solve a procedureal problem...
http://www.sqlservercentral.com/articles/Advanced+Querying/61716/
Oh - I'd use a UDF now, but they didn't have them in v7, hence the cursor for that one occasion for that particular problem
Heh... cool! You write your UDF code to do a running total on the "SomeMoney" column in the following test table, and let's have a race. 😉
--===== Create and populate a 1,000,000 row test table.
-- Column "RowNum" has a range of 1 to 1,000,000 unique numbers
-- Column "SomeInt" has a range of 1 to 50,000 non-unique numbers
-- Column "SomeLetters2" has a range of "AA" to "ZZ" non-unique 2 character strings
-- Column "SomeMoney has a range of 0.0000 to 99.9999 non-unique numbers
-- Column "SomeDate" has a range of >=01/01/2000 and <01/01/2010 non-unique date/times
-- Column "SomeCSV" contains 'Part01,Part02,Part03,Part04,Part05,Part06,Part07,Part08,Part09,Part10'
-- for all rows.
-- Column "SomeHex12" contains 12 random hex characters (ie, 0-9,A-F)
-- Jeff Moden
SELECT TOP 1000000
RowNum = IDENTITY(INT,1,1),
SomeInt = ABS(CHECKSUM(NEWID()))%50000+1,
SomeLetters2 = CHAR(ABS(CHECKSUM(NEWID()))%26+65)
+ CHAR(ABS(CHECKSUM(NEWID()))%26+65),
SomeCSV = CAST('Part01,Part02,Part03,Part04,Part05,Part06,Part07,Part08,Part09,Part10' AS VARCHAR(80)),
SomeMoney = CAST(ABS(CHECKSUM(NEWID()))%10000 /100.0 AS MONEY),
SomeDate = CAST(RAND(CHECKSUM(NEWID()))*3653.0+36524.0 AS DATETIME),
SomeHex12 = RIGHT(NEWID(),12)
INTO dbo.JBMTest
FROM Master.dbo.SysColumns t1,
Master.dbo.SysColumns t2 --Lack of join criteria makes this a CROSS-JOIN
--===== A table is not properly formed unless a Primary Key has been assigned
-- Takes about 1 second to execute.
ALTER TABLE dbo.JBMTest
ADD PRIMARY KEY CLUSTERED (RowNum)

Hehe. I always seem to pop by just when there's a throwdown about to go down....

All right all right. If you're going to challenge to a dual, you need to finish out the rules:

- any version requirements (does it need to run in 2000?)

- is this for display purposes only?

- if you are storing - are you updating the source table, or going to a new table?

I might not necessarily get to put an entry in (let's just say the work schedule is stacking up ugly right now.), but I can get the challenge heading the right way...:)

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Jeff Moden SSC Guru Points: 1004290 More actions · Answer 3

Hehe. I always seem to pop by just when there's a throwdown about to go down....
All right all right. If you're going to challenge to a dual, you need to finish out the rules:
- any version requirements (does it need to run in 2000?)
- is this for display purposes only?
- if you are storing - are you updating the source table, or going to a new table?
I might not necessarily get to put an entry in (let's just say the work schedule is stacking up ugly right now.), but I can get the challenge heading the right way...

Heh... you volunteering to be the "run judge"? 😛

- any version requirements (does it need to run in 2000?)

It can run on either 2k or 2k5... extra points if it runs on both.

- is this for display purposes only?

- if you are storing - are you updating the source table, or going to a new table?

Heh... who wants to read a million rows? Store it in a table but no mods to the original table. Let's make it consistent for testing/verification purposes... the running total must ascend in order by the SomeInt and SomeDate columns and be a single running total across all 1 million rows... Final table must have original RowNum, SomeInt, SomeDate, and RunningTotal columns. Add any other columns that are needed to do the task if needed.

Andrew writes the UDF method he spoke of... I'll write using my method. Matt, you already know what my method will be and would probably write identical code... you wanna throw in a CLR method, instead, to finally put that one to bed? If anyone wants to throw in a cursor method, bless you for rounding out the field.

CPU time, duration, and Disk I/O will be checked for all the runs. Recommend we use profiler to determine that so no one has to be bothered with wrapping the code in checks.

If anyone can do it in a view and win the race, I'm up for learning something new and absolutely extraordinary. :w00t:

Everyone has to post all the code that supports their method to deliver the final result. I'm hoping Matt will run the tests because I don't have the capability to run CLR's if someone decides to submit one.

OR!!! Someone can go read the article I suggested and see why the Cursor, UDF, and the supposedly Set Based correlated sub-query method will always loose on this test. If someone still wants to race a CLR, I'm all for it... that's one piece of data we don't have yet on this type of thing.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 4

I will see what I can do. I think I might want to try a CLR proc on this one.....

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 5

Matt Miller (8/14/2008)
I will see what I can do. I think I might want to try a CLR proc on this one.....

By the way - that was a "sure I can run judge" acknowledgement.

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 6

Most of the applications that I see that make heavy use of cursors were either ported from Oracle to SQL Server or created by developers who only had Oracle experience.

We have an application that is performing very badly. When I started investigating I was shocked to see in excess of 2000 batch requests per second with just a very small number of users. Most of these are server-side cursor fetches. When I looked into this application in more detail, I was told that it was actually developed by Oracle, so I am not shocked that it was not optimized for SQL Server. There are also significant issues with the table design; most of the tables do not have clustered indexes, so defragmentation of the indexes doesn’t really do that much.

The only solution I could suggest that would really solve the problem is to have them fix the application. Of course that is not going to happen, so I am going to throw a new server at it and tell them to live with it.

Eric_Gagne SSCrazy Points: 2062 More actions · Answer 7

Michael Valentine Jones (8/15/2008)
The only solution I could suggest that would really solve the problem is to have them fix the application. Of course that is not going to happen, so I am going to throw a new server at it and tell them to live with it.

My point exactly since I started this thread. I can ask my vendor to fix their application but it's not gonna happen. I can ask my management to trash 200K dollars and 6 months of work and start all over..........somehow I don't think it's gonna happen either.

So just like you Michael, we're throwing hardware at it to make the best out of the situation. Being a small shop with around 75 users and having the possibility to archive data onto a data warehouse server twice a year I think we can manage to make this thing run reasonably smoothly and prevent the database from growing to a size where all these cursors would make it crumble.

With that in mind, I'm sure if certain procedures turn out to be really too slow to execute I can talk to the vendor and find a solution. Their RAD environment creates procedures with that uses cursors but nothing prevents them from manually rewriting some of them properly.

Jeff Moden SSC Guru Points: 1004290 More actions · Answer 8

Matt Miller (8/15/2008)
Matt Miller (8/14/2008)
I will see what I can do. I think I might want to try a CLR proc on this one.....
By the way - that was a "sure I can run judge" acknowledgement.

Just waiting for Andrew to agree...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Scott Coleman One Orange Chip Points: 27452 More actions · Answer 9

After you add the maximum RAM to your server, I would recommend beefing up tempdb. All those cursors are going to be created in tempdb, so you would like to have it (and nothing else) on the fastest drives you can get. You seem to have a healthy hardware budget, so add a SAN or other attached storage with at least 1 drive per CPU and put a tempdb file on each.