Replace cursor with while loop / compare cursor with while loop / optimize cursor

Question

Replace cursor with while loop / compare cursor with while loop / optimize cursor

Viewing 15 posts - 16 through 30 (of 44 total)

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

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

clive-421796 (12/9/2009)
Hi Lynn, I attached the script to create the table and insert the data.
Please view attachment: script for tmpTable1.rar

Does absolutely nothing for me... I can't open a .rar file on my machine.

--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)

Jeff Moden SSC Guru Points: 1004312 More actions · Answer 2

Lynn Pettis (12/9/2009)
Jeff Moden (12/9/2009)
Lynn Pettis (12/9/2009)
If Jeff suggested that you do this, I suggest you figure out how to write the query using a while loop. Give it a shot. If you have problems, post your code and what the problem is, and I'm sure some one will step up to help.
Just to be clear... Clive suggested that he wanted to replace a Cursor with a While Loop on the other post. My suggestion was that if Clive wanted to replace the Cursor code with While Loop code, that he shouldn't bother because a Forward_Only, Read_Only Cursor would do just as well.
I also suggested that he post it on a T-SQL forum instead of an article thread to get better help considering that there are also other problems in the code besides the RBAR.
Okay, clear. Still not going to write a WHILE loop psudo cursor when there is a better, set-based way to accomplish the same task. He wants a WHILE loop, he needs to try writing it. No problem helping, just not doing. 😛

I agree... I don't believe he's even tried Kevin's good code.

And, no... I wasn't suggesting that anyone write a While Loop for this. 😉

I was also hoping that someone besides me would bring up what's wrong with using such things as 23:59:59 for date/time comparisons.

--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)

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 3

I haven't checked for certain Jeff, but I have seen these types of manipulations make proper dates for range queries:

SET @ldtStartDate = ISNULL(@dStartDate,DATEADD(month,-1,DATEADD(mm, DATEDIFF(mm,0,GETDATE()), 0)))

SET @ldtEndDate = ISNULL(@dEndDate,DATEADD(ms, -3, DATEADD(mm, DATEDIFF(mm, 0, GETDATE()), 0)))

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 4

Jeff Moden (12/9/2009)
Lynn Pettis (12/9/2009)
Jeff Moden (12/9/2009)
Lynn Pettis (12/9/2009)
If Jeff suggested that you do this, I suggest you figure out how to write the query using a while loop. Give it a shot. If you have problems, post your code and what the problem is, and I'm sure some one will step up to help.
Just to be clear... Clive suggested that he wanted to replace a Cursor with a While Loop on the other post. My suggestion was that if Clive wanted to replace the Cursor code with While Loop code, that he shouldn't bother because a Forward_Only, Read_Only Cursor would do just as well.
I also suggested that he post it on a T-SQL forum instead of an article thread to get better help considering that there are also other problems in the code besides the RBAR.
Okay, clear. Still not going to write a WHILE loop psudo cursor when there is a better, set-based way to accomplish the same task. He wants a WHILE loop, he needs to try writing it. No problem helping, just not doing. 😛
I agree... I don't believe he's even tried Kevin's good code.
And, no... I wasn't suggesting that anyone write a While Loop for this. 😉
I was also hoping that someone besides me would bring up what's wrong with using such things as 23:59:59 for date/time comparisons.

I can tell you why I didn't comment on that; I didn't see it as I didn't really take a really close look at the cursor code. Definately not the way to do it.

SQLRNNR SSC Guru Points: 281334 More actions · Answer 5

I went straight to a query to meet the requirements outlined rather than review the cursor. Thus I missed the datetime comparison being employed.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Jeff Moden SSC Guru Points: 1004312 More actions · Answer 6

TheSQLGuru (12/10/2009)
I haven't checked for certain Jeff, but I have seen these types of manipulations make proper dates for range queries:
SET @ldtStartDate = ISNULL(@dStartDate,DATEADD(month,-1,DATEADD(mm, DATEDIFF(mm,0,GETDATE()), 0)))
SET @ldtEndDate = ISNULL(@dEndDate,DATEADD(ms, -3, DATEADD(mm, DATEDIFF(mm, 0, GETDATE()), 0)))

I frequently use things like the first one. The second one works but I prefered never to use it left a small hole if they ever upgraded datetime data types.... just like they did in 2k8.

Anyway, thanks for the effort and the feedback, Kevin. I hope the op takes heed.

--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)

kevin_nikolai SSCarpal Tunnel Points: 4343 More actions · Answer 7

Hi SQLGuru, I tried your code but unfortunately it does not work. I get 670 rows instead of 692, the columns are also out of sync. If you want to test it yourself I added the script to create table and insert data to my post.

kevin_nikolai SSCarpal Tunnel Points: 4343 More actions · Answer 8

Hi Lynn, I tested your code and it works 100%, on the example table I posted on 692 rows its just as quick as the cursor I added to my post. Compared it to live data at employer, it ran 6 minutes and 29 seconds compared to 6 minutes and 30 seconds of the cursor. (this was over a period of 2 days, for monthly reports it runs over 30 days, so will test and compare).

Thanks, you're a guru. So to all the cursor die-hard followers, they are good in some cases, but in most cases they can be replaced with just as fast non-cursor T-SQL.

Over 15 days your code took 14 mins and 49 seconds, the cursor took 14 mins and 57 seconds.

Over 30 days the set-based code was 2 minutes 17 seconds faster than the cursor.

In the above comparison I only selected one subtree, for some clients there is over 40.

Will check duration for 30 days with over 30 subtrees.

kevin_nikolai SSCarpal Tunnel Points: 4343 More actions · Answer 9

Cant open a .rar (winrar) file ? Ah come on Jeff, never heard of anyone working in IT for years that does not have winrar installed. Go to this URL. http://www.win-rar.com/download.html (1.4 MB)

kevin_nikolai SSCarpal Tunnel Points: 4343 More actions · Answer 10

Hi Lynn, regarding the date issue which you and Jeff commented on. Well most of the stored procedures at my employer were written/coded from months to several years ago by dozens of DBA's and Developers, most of them have left my employer. So while doing my daily DBA calls/SQL reports calls/SSIS package calls, I also try to optmize T-SQL statements so SQL jobs (proc's + SSIS packages) complete quicker. I am one of those DBA's who tries to fix things whereas plenty DBA's and IT people out there have the perception 'if it works then leave it alone' - 'its too much hassle', irrespective of how crappy and slow it might be). I am not a T-SQL guru (dont have an answer for very problem), wish sometimes I had the T-SQL knowledge you, Jeff, Matt and RBarryYoung, and some others on this forum has. So please be patient with me.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 11

Just an fyi, when doing date range comparisions, especially when the datetime column you are using also stores time values other than 00:00:00.000, the best method is like this:

...

WHERE

tbl.YourDateColumn >= @StartDate -- where date looks like 2009-11-01 00:00:00.000

AND tbl.YourDateColumn < @EndDate -- where date looks like 2009-12-01 00:00:00.000

I try to do this even when I know the column doesn't have time values as you never know if that may change.

Calculating these dates is fairly easy. If you check out my blog entry Some Common Date Routines, you'll see what I mean.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 12

clive-421796 (12/10/2009)
Cant open a .rar (winrar) file ? Ah come on Jeff, never heard of anyone working in IT for years that does not have winrar installed. Go to this URL. http://www.win-rar.com/download.html (1.4 MB)

That one got me too. I have PKZip at home and it had no problem opening the file at all.

kevin_nikolai SSCarpal Tunnel Points: 4343 More actions · Answer 13

Hi Lynn, instead of using:

WHERE

tbl.YourDateColumn >= @StartDate

AND tbl.YourDateColumn < @EndDate

I prefer to use:

WHERE

tbl.YourDateColumn between @StartDate AND @EndDate.

-------------------------

Have a question for you:

What if for a SQL report you want to use @StartDate AND @EndDate parameters, but also have the option for @StartHour AND @EndHour. How would you implement this. The default would be @StartDate AND @EndDate. If @StartHour AND @EndHour is chosen, then it will override @StartHour AND @EndHour.

Jeff Moden SSC Guru Points: 1004312 More actions · Answer 14

clive-421796 (12/10/2009)
Cant open a .rar (winrar) file ? Ah come on Jeff, never heard of anyone working in IT for years that does not have winrar installed. Go to this URL. http://www.win-rar.com/download.html (1.4 MB)

Heh... you're the one that needs the help... upload it as a zip file and I'll be happy to take a look at it. 😉

--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)

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 15

Jeff Moden (12/10/2009)
clive-421796 (12/10/2009)
Cant open a .rar (winrar) file ? Ah come on Jeff, never heard of anyone working in IT for years that does not have winrar installed. Go to this URL. http://www.win-rar.com/download.html (1.4 MB)
Heh... you're the one that needs the help... upload it as a zip file and I'll be happy to take a look at it. 😉

Jeff, just curious, what package do you use?