I need some help with a test, please.

Question

I need some help with a test, please.

Viewing 15 posts - 46 through 60 (of 214 total)

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

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

ColdCoffee (9/19/2010)
Jeff, this is from environment 2..
~Edit : all my tests will be over the desktop machines, i dont have laptop yet, Jeff.. Rest of the eonvironments, i will give u tomorrow..

Very cool (no pun intended) ColdCoffee. I really appreciate the help on this.

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

Paul White SSC Guru Points: 150467 More actions · Answer 2

Jeff Moden (9/19/2010)
Again, still in the "I don't know so be gentle with me" mode, why is it then that you have to run it a couple of times to get it to "sink in"?

No worries.

The conversion from MSIL to native code is done by a JIT compiler. The Just-In-Time aspect means that code is only compiled to native code just before it is required. So, depending on the code paths taken on a particular execution, different parts of the assembly might be optimized on different runs.

Put another way: JIT compiling does not compile the whole assembly up front - just the individual code lines that are actually scheduled for execution.

The JIT compiler might also skip some optimization if it is 'busy' - in which case the un-optimized MSIL is run directly. So, several runs are often required to give JIT time to compile all code paths that benefit from conversion to native machine code. Not all code does, BTW. Complex topic!

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

J Livingston SQL SSC Guru Points: 51550 More actions · Answer 3

Jeff Moden (9/19/2010)
gah (9/19/2010)
version 2 resullts attached.
regards Graham
Now THAT's interesting! Even though the "XML-1 (Split8KXML1 mlTVF)" multi-line table valued still uses more logical reads, it still beat the new addititions for CPU and Duration by a factor of more than 3:1.

fyi....forgot to mention...SQL service was restarted on the desktop before each run....will that effect your analysis?

________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day

Jeff Moden SSC Guru Points: 1004365 More actions · Answer 4

Paul White NZ (9/19/2010)
Version 2 results...:satisfied:

Hmmmm... The XML-1 mlTVF that I originally cobbled together beat the new additions by almost 4:1 that time.

Just to give credit where credit is due, I got the basis of that code from a fellow called "Oleg". I forget his last name but I'll look it up on one of the other posts that started this quest for test information.

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

Ron McCullough SSC Guru Points: 63877 More actions · Answer 5

Results for SQL 2008 Developer edition run on a desk top with slightly more memory and slightly higher speed Running Windows XP - code run was that after you edited same at 10:48 AM

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

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

gah (9/19/2010)
Jeff Moden (9/19/2010)
gah (9/19/2010)
version 2 resullts attached.
regards Graham
Now THAT's interesting! Even though the "XML-1 (Split8KXML1 mlTVF)" multi-line table valued still uses more logical reads, it still beat the new addititions for CPU and Duration by a factor of more than 3:1.
fyi....forgot to mention...SQL service was restarted on the desktop before each run....will that effect your analysis?

You, good Sir, are definitely a man after my own heart when it comes to testing! 🙂

No, it shouldn't affect anything other than, maybe, how fast the new Tally table is built. If TempDB is too small, it'll also affect how quickly the CsvTest table is built. Since we're not measuring those "one time" events, we're good to go.

Thanks again for the help on this and the information you've been passig along on how you did the tests.

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

Ron McCullough SSC Guru Points: 63877 More actions · Answer 7

Results for SQL 2005 Developer edition run on a desk top with slightly more memory and slightly higher speed Running Windows XP

Using the code you edited at 10:48 edit

So now you have two one for before the code was edited and one after the code was edited for both 2005 and 2008 Developer Edition - all run on the same machine.

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

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

Paul White NZ (9/19/2010)
steve-893342 (9/19/2010)
Yes, but if you compare the two to shift data either with a SELECT INTO or INSERT query you won't see that sort of difference
I think I see what you mean, and the answer is very much "It Depends" 😉
For example, let's use a bcp export to a file (this removes a lot of variable factors):
Tally:
bcp "SELECT csv.RowNum, split.ItemNumber, item = CONVERT(INTEGER, split.ItemValue) FROM tempdb.dbo.CsvTest csv CROSS APPLY tempdb.dbo.Split8KTally(csv.CsvParameter,',') AS split" queryout tally.bcp -n -S .\SQL2008 -T
Results:
1000000 rows copied.
Clock Time (ms.) Total : 11406 Average : (87673.15 rows per sec.)
SQLCLR:
bcp "SELECT CSV.RowNum, iTVF.sequence, item = CONVERT(INTEGER, iTVF.item) FROM tempdb.dbo.CsvTest CSV CROSS APPLY tempdb.dbo.SplitString_Multi(CSV.CsvParameter, N',') iTVF" queryout sqlclr.bcp -n -S .\SQL2008 -T
Results:
1000000 rows copied.
Clock Time (ms.) Total : 3610 Average : (277008.31 rows per sec.)
Paul

Good one:-)

I shall remember that for next time I need to bcp and split at the same time;-)

LutzM SSC Guru Points: 107049 More actions · Answer 9

Hi Jeff,

attached the results for version 2 (19min 35sec).

Side note: I guess you figured the reason why I mentioned the Profiler trace in my previous post... 😉

Of course I did read the test code. Not that I don't trust you... more because I wanted to see how those functions you're testing actually work.

The other reason: it became a habit to "eye-scan" each code snippet I run off the web, regardless of the source.

System is the same as before:

System: WIN XP SP3 (SS2K5, Dev. Ed SP 3)

Hardware: DELL Vostro, 2QuadCPU @ 2.4GHz, 3.25Gb RAM

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

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

LutzM (9/19/2010)
The other reason: it became a habit to "eye-scan" each code snippet I run off the web, regardless of the source.

Ohhh yeahhh... do I ever agree with that. Thanks, Lutz.

--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: 1004365 More actions · Answer 11

steve-893342 (9/19/2010)
Paul White NZ (9/19/2010)
steve-893342 (9/19/2010)
Yes, but if you compare the two to shift data either with a SELECT INTO or INSERT query you won't see that sort of difference
I think I see what you mean, and the answer is very much "It Depends" 😉
For example, let's use a bcp export to a file (this removes a lot of variable factors):
Tally:
bcp "SELECT csv.RowNum, split.ItemNumber, item = CONVERT(INTEGER, split.ItemValue) FROM tempdb.dbo.CsvTest csv CROSS APPLY tempdb.dbo.Split8KTally(csv.CsvParameter,',') AS split" queryout tally.bcp -n -S .\SQL2008 -T
Results:
1000000 rows copied.
Clock Time (ms.) Total : 11406 Average : (87673.15 rows per sec.)
SQLCLR:
bcp "SELECT CSV.RowNum, iTVF.sequence, item = CONVERT(INTEGER, iTVF.item) FROM tempdb.dbo.CsvTest CSV CROSS APPLY tempdb.dbo.SplitString_Multi(CSV.CsvParameter, N',') iTVF" queryout sqlclr.bcp -n -S .\SQL2008 -T
Results:
1000000 rows copied.
Clock Time (ms.) Total : 3610 Average : (277008.31 rows per sec.)
Paul
Good one:-)
I shall remember that for next time I need to bcp and split at the same time;-)

Hi Steve. Thanks for your involvement on this thread. Any chance of your running the code and returning the results for some extra data points for me?

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

Ron McCullough SSC Guru Points: 63877 More actions · Answer 12

Jeff ran test on the oldest, least amount of memory, slowest desktop, age - older than dirt. Running Windows XP

Result for SQL 2005 Express using code after it was edited by you at 10:48 in attached word doc.

So this is two sets of results one before your edit and one after on same machine ..

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

Jeff Moden SSC Guru Points: 1004365 More actions · Answer 13

bitbucket-25253 (9/19/2010)
Jeff ran test on the oldest, least amount of memory, slowest desktop, age - older than dirt. Running Windows XP
Result for SQL 2005 Express using code after it was edited by you at 10:48 in attached word doc.
So this is two sets of results one before your edit and one after on same machine ..

Thanks, Ron. I appreciate the extra effort.

Shifting gears, I'm going to wait until Monday night to put all of this together. That way I can have the extra data that ColdCoffee is talking about. It would also be nice if Brad could run the test code on his machine because that's where it seems that the XML is working the best.

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

Ron McCullough SSC Guru Points: 63877 More actions · Answer 14

Jeff Moden (9/19/2010)
bitbucket-25253 (9/19/2010)
Jeff ran test on the oldest, least amount of memory, slowest desktop, age - older than dirt. Running Windows XP
Result for SQL 2005 Express using code after it was edited by you at 10:48 in attached word doc.
So this is two sets of results one before your edit and one after on same machine ..
Thanks, Ron. I appreciate the extra effort.
Shifting gears, I'm going to wait until Monday night to put all of this together. That way I can have the extra data that ColdCoffee is talking about. It would also be nice if Brad could run the test code on his machine because that's where it seems that the XML is working the best.

Jeff I said I would attempt to run the code on SQL2008 R2. Been attempting to install the %&@ thing since 10 AM this morning. Things are not proceeding well or rapidly. Things are going so #$%@ bad that I feel like taking the DVD out of the machine and sailing it across the back yard never to be seen again.

I will keep trying but if you know someone with R2 installed suggest you request that they run the test.

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 15

SQL Express 2k5 9.00.3042.00 Desktop machine.

Sorry it took a bit, I went out and partied last night. 🙂

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA