I need some help with a test, please.

Question

Post reply

I need some help with a test, please.

Viewing 15 posts - 196 through 210 (of 214 total)

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

Paul White SSC Guru Points: 150457 More actions · Answer 1

steve-893342 (9/24/2010)

Unless BETWEEN is more efficient here, you just need the LESS THAN

I tend to agree. I'm patiently waiting for Jeff to post the new Tally code in full so we can see how it performs against the other approaches (including the WHILE loop with SET STATISTICS...OFF).

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

UMG Developer SSChampion Points: 13482 More actions · Answer 2

Paul White NZ (9/24/2010)

Sorry about that. Not having separate trace flags seems a backward step to me.

No problem, I agree it seems to be a step backwards. I like the idea of having "One trace flag to rule them all." :-P, but being able to turn individual optimizations/fixes on would be very useful in situations like mine.

steve-893342 SSCarpal Tunnel Points: 4439 More actions · Answer 3

Paul White NZ (9/24/2010)

steve-893342 (9/24/2010)

Unless BETWEEN is more efficient here, you just need the LESS THAN

I tend to agree. I'm patiently waiting for Jeff to post the new Tally code in full so we can see how it performs against the other approaches (including the WHILE loop with SET STATISTICS...OFF).

Yes thanks for that Paul. My conversation with Jeff was getting rather tall!

I also have some misgivings about SET STATISTICS... Better to look up the results in sys.dm_exec_query_stats then or use a stopwatch:-)

And don't worry, I haven't forgotten that pint;-)

Dave Ballantyne SSC-Dedicated Points: 33556 More actions · Answer 4

Dave Ballantyne

SSC-Dedicated

Points: 33556

September 25, 2010 at 2:48 am

#1227349

steve-893342 (9/25/2010)

Better to look up the results in sys.dm_exec_query_stats then or use a stopwatch:-)

Profiler will be fine , as long as only SQL:StmtCompleted events are used and not SP:StmtCompleted events.

Clear Sky SQL
My Blog[/url]

WayneS SSC Guru Points: 95405 More actions · Answer 5

Jeff - just saw this thread after returning from my vacation. If needed, I can run this on a Vista Ultimate 64-bit laptop, on 2005 / 2008 (not R2). I can also run it on a XP 32-bit VM. Let me know if you need more data points.

Edit: At work, I have access to SQL 2000/2005/2008/2008R2.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 6

Urrrr... R2 did not get installed this weekend. Don't know when I'm going to get to it.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 7

Not a problem, Gail. I know what you've been going through the last couple of weeks. I think I may have enough information on all of this. Now all I need to do is find the time to summarize all the data for both sets of runs and all the good input folks have made on this thread.

--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)
Intro to Tally Tables and Functions

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

WayneS (9/26/2010)

Jeff - just saw this thread after returning from my vacation. If needed, I can run this on a Vista Ultimate 64-bit laptop, on 2005 / 2008 (not R2). I can also run it on a XP 32-bit VM. Let me know if you need more data points.

Edit: At work, I have access to SQL 2000/2005/2008/2008R2.

Apologies... I'm way behind on the power curve. I could use the extra data point for the R2. If you decide to run either of the code snippets on any of the others, I'll never turn down datapoints. Thanks, Wayne.

--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)
Intro to Tally Tables and Functions

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 9

steve-893342 (9/25/2010)

My conversation with Jeff was getting rather tall!

Heh... I've gotten way behind on things and I'm really tired this last week. Too many fires with sticks in them and all. So tell me... what do you mean by the above and should I be offended or not? 😉

--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)
Intro to Tally Tables and Functions

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 10

Paul White NZ (9/24/2010)

steve-893342 (9/24/2010)

Unless BETWEEN is more efficient here, you just need the LESS THAN

I tend to agree. I'm patiently waiting for Jeff to post the new Tally code in full so we can see how it performs against the other approaches (including the WHILE loop with SET STATISTICS...OFF).

Sorry... I'm behind on just about everything. Thanks for your patience.

--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)
Intro to Tally Tables and Functions

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 11

Jeff Moden (9/30/2010)

steve-893342 (9/25/2010)

My conversation with Jeff was getting rather tall!

Heh... I've gotten way behind on things and I'm really tired this last week. Too many fires with sticks in them and all. So tell me... what do you mean by the above and should I be offended or not? 😉

Heh... never mind. I just went back and looked. The quotes were making the posts physically tall. Like I said... I'm really tired this last week. 🙂

--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)
Intro to Tally Tables and Functions

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 12

Ah... by the way. The BETWEEN 1 AND... was to make sure that the code worked correctly just in case someone had a Tally Table of their own that started at zero and they decided to use it instead of the code I built. Considering I had Tally Table creation code and it was all going into TempDB, it probably would have been fine to leave it off but old test habits are hard to kick.

--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)
Intro to Tally Tables and Functions

UMG Developer SSChampion Points: 13482 More actions · Answer 13

UMG Developer

SSChampion

Points: 13482

September 30, 2010 at 10:08 pm

#1230018

Jeff Moden (9/30/2010)

I could use the extra data point for the R2.

Since you wanted some more R2 data points, I ran both of the tests on our SQL Server 2008 R2 CU3 development server. The hardest part was getting the AdventureWorks DB on it since the installer wouldn't work...

Paul White SSC Guru Points: 150457 More actions · Answer 14

Paul White

SSC Guru

Points: 150457

October 1, 2010 at 4:51 am

#1230103

Runs from my new laptop attached.

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Paul White SSC Guru Points: 150457 More actions · Answer 15

Just for completeness, these are the results of the SQLCLR implementation on the original rig, now that I have a machine that can actually use parallelism 🙂

Table 'CsvTest'. Scan count 9, logical reads 860, physical reads 0, read-ahead reads 0



Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0

 SQL Server Execution Times:

   CPU time = 5366 ms,  elapsed time = 894 ms.

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi