I need some help with a test, please.

Question

I need some help with a test, please.

Viewing 15 posts - 136 through 150 (of 214 total)

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

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

Jeff Moden (9/20/2010)
mazzz (9/20/2010)
Jeff
I've just seen your update in the OP - do you need any more results?
My home setup is Win 7, Intel core duo CPU, 4GB RAM, 64bit, SQL2008 R2 if it's of any interest
I'll never turn down a set of data points, Mazzz. I'd love to see the results from above including that second test I asked folks with 2K8 to run. Thanks for your effort on this.

Jeff..need any more?

with all the posts I am not sure sure which is "first" and "second version" of the test....???

Vista Business , SQL 2k8 R2 Dev...throttled at 1GB

Also have SQL 2K5 Std if needed.

regards Graham

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

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

Jeff Moden (9/20/2010)
The reason why I mention SARGable is because I believe that it might be able to do a better job at cost estimation if it were SARGable.. As Brad points out, it's currently doing the equivalent of a Cross Join between the 1000 elements in the parameter and 121 K rows in the SalesOrderDetails table.

There are certainly many potential ways to improve the 2008 iTVF Tally performance with hints, query rewrites, temporary tables, computed columns, extra indexes, and so on and so on...but those would break the 'spirit' of the challenge, at least to my mind. You could probably even force the 2005 plan, for example, but the 'natural' plan produced by QO is a disaster on 2008 with the iTVF Tally.

It seems to me that the XML solution performs exceptionally well (and so much better than any previous XML attempt) where a single large delimited string in a variable needs to be used to drive a more complex query. That's pretty much exactly what I read in Brad's various (and awesome!) blog posts on the subject, so I'm happy to leave it there. Brad deserves much praise for his work on this.

The SQLCLR solution does not suffer the same issues as Tally on 2K8 in the variable query - again down to cost estimation and cardinality guesses - but even it is beaten by the XML!

If the task is to split a large number of delimited strings stored in a table, SQLCLR and Tally both have advantages and disadvantages. I would prefer one or the other depending on context.

So, I'm not sure there's much else for me in this thread, though I'll stay subscribed in case anyone asks me a direct question.

Paul

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Brad Schulz Old Hand Points: 395 More actions · Answer 3

Jeff Moden (9/20/2010)
Brad... what do you get in 2K8 for that same test with the Trace Flag turn on?

I'm attaching it... along with the execution plan. The plan is almost exactly like the one in SQL2005... but parallelism comes into play in different places. The performance is on par with SQL2005.

In SQL2008, if you add the hints OPTION (FORCE ORDER, MAXDOP 1, LOOP JOIN), then the performance comes down to this (and this is without the 8690 trace being on):

CPU=16ms, Duration=26ms

I talked about this here: http://bradsruminations.blogspot.com/2010/08/taking-hint.html

Still, it's a shame that one must resort to doing this with a simple tally table query in SQL2008.

--Brad

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

Paul White NZ (9/20/2010)
Jeff Moden (9/20/2010)
The reason why I mention SARGable is because I believe that it might be able to do a better job at cost estimation if it were SARGable.. As Brad points out, it's currently doing the equivalent of a Cross Join between the 1000 elements in the parameter and 121 K rows in the SalesOrderDetails table.
There are certainly many potential ways to improve the 2008 iTVF Tally performance with hints, query rewrites, temporary tables, computed columns, extra indexes, and so on and so on...but those would break the 'spirit' of the challenge, at least to my mind. You could probably even force the 2005 plan, for example, but the 'natural' plan produced by QO is a disaster on 2008 with the iTVF Tally.
It seems to me that the XML solution performs exceptionally well (and so much better than any previous XML attempt) where a single large delimited string in a variable needs to be used to drive a more complex query. That's pretty much exactly what I read in Brad's various (and awesome!) blog posts on the subject, so I'm happy to leave it there. Brad deserves much praise for his work on this.
The SQLCLR solution does not suffer the same issues as Tally on 2K8 in the variable query - again down to cost estimation and cardinality guesses - but even it is beaten by the XML!
If the task is to split a large number of delimited strings stored in a table, SQLCLR and Tally both have advantages and disadvantages. I would prefer one or the other depending on context.
So, I'm not sure there's much else for me in this thread, though I'll stay subscribed in case anyone asks me a direct question.
Paul

Heh... the While Loop method beat the XML method.

So far as the Tally Table and the "spirit" of things go... I agree. None of those tricks should be necessary and just like everyone tweeking the XML method, I'm going to try the right way to do this type of thing in the Tally Table. I'd also like to remind everyone that "Set Based" doesn't mean "All-in-one-query". 😉

--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: 1003863 More actions · Answer 5

Brad Schulz (9/20/2010)
Jeff Moden (9/20/2010)
Brad... what do you get in 2K8 for that same test with the Trace Flag turn on?
I'm attaching it... along with the execution plan.

Thanks, Brad.

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

Brad Schulz Old Hand Points: 395 More actions · Answer 6

Paul White NZ (9/20/2010)
Jeff Moden (9/20/2010)
The reason why I mention SARGable is because I believe that it might be able to do a better job at cost estimation if it were SARGable.. As Brad points out, it's currently doing the equivalent of a Cross Join between the 1000 elements in the parameter and 121 K rows in the SalesOrderDetails table.
There are certainly many potential ways to improve the 2008 iTVF Tally performance with hints, query rewrites, temporary tables, computed columns, extra indexes, and so on and so on...but those would break the 'spirit' of the challenge, at least to my mind. You could probably even force the 2005 plan, for example, but the 'natural' plan produced by QO is a disaster on 2008 with the iTVF Tally.
It seems to me that the XML solution performs exceptionally well (and so much better than any previous XML attempt) where a single large delimited string in a variable needs to be used to drive a more complex query. That's pretty much exactly what I read in Brad's various (and awesome!) blog posts on the subject, so I'm happy to leave it there. Brad deserves much praise for his work on this.
The SQLCLR solution does not suffer the same issues as Tally on 2K8 in the variable query - again down to cost estimation and cardinality guesses - but even it is beaten by the XML!
If the task is to split a large number of delimited strings stored in a table, SQLCLR and Tally both have advantages and disadvantages. I would prefer one or the other depending on context.
So, I'm not sure there's much else for me in this thread, though I'll stay subscribed in case anyone asks me a direct question.
Paul

Thanks for the kind words, Paul!

The conclusion I came to is that the simple multi-line looping traditional (RBAR!!) splitting method may work best across all platforms when it comes to actually JOINing the results to a table. It requires the smallest memory grant (by far) than the other methods do, and it brings about more reasonable estimates by the optimizer, and therefore does not force parallelism (because the estimation costs are not so high). CLR is certainly fastest in and of itself, but it requires a big memory grant (almost a 100MB) and brings about more reads than necessary (because of the optimizer kind of treating it like a "black box" that spits out an estimated 1000 rows and therefore constructs a plan with hash matches because of the estimated 360,000 rows that the final plan expects to produce).

I tried also converting the Tally approach and the XML approach to multi-line functions as opposed to inline functions (based on a suggestion by Adam Machanic), and also put a "wrapper" around the CLR function to force it to act like a multi-line function, and all approaches came out pretty much on par, though the CLR approach required some more reads than the other approaches because of the "wrapper".

I ran these tests in anticipation of a follow-up post to my list-splitting blog post, but other things got in the way, and I haven't had a chance to write anything up yet. Of course, now the water's become even more muddy because of SQL2005 throwing a monkey wrench into the mix with its Tally table performance.

--Brad

mazzz SSCertifiable Points: 6139 More actions · Answer 7

Jeff Moden (9/20/2010)
mazzz (9/20/2010)
Jeff
I've just seen your update in the OP - do you need any more results?
My home setup is Win 7, Intel core duo CPU, 4GB RAM, 64bit, SQL2008 R2 if it's of any interest
I'll never turn down a set of data points, Mazzz. I'd love to see the results from above including that second test I asked folks with 2K8 to run. Thanks for your effort on this.

My pleasure, Jeff. you've helped me so much, whether directly or indirectly, it's the least I can do.

Just to clarify, the second test for 2k8 is this post ?

Or is there another one I've missed?

------------------------------------------------------------------------
Bite-sized fiction (with added teeth) [/url]

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

mazzz (9/20/2010)
Jeff Moden (9/20/2010)
mazzz (9/20/2010)
Jeff
I've just seen your update in the OP - do you need any more results?
My home setup is Win 7, Intel core duo CPU, 4GB RAM, 64bit, SQL2008 R2 if it's of any interest
I'll never turn down a set of data points, Mazzz. I'd love to see the results from above including that second test I asked folks with 2K8 to run. Thanks for your effort on this.
My pleasure, Jeff. you've helped me so much, whether directly or indirectly, it's the least I can do.
Just to clarify, the second test for 2k8 is this post ?
Or is there another one I've missed?

That's the one, Mazzz. I appreciate 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)

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

Paul White NZ (9/20/2010)
It seems to me that the XML solution performs exceptionally well (and so much better than any previous XML attempt) where a single large delimited string in a variable needs to be used to drive a more complex query. That's pretty much exactly what I read in Brad's various (and awesome!) blog posts on the subject, so I'm happy to leave it there. Brad deserves much praise for his work on this.

BTW and just to be sure.... I absolutely agree with the above. I just wanted to find out more about "why" than just "The RBAR Method Wins". 😉 And, we have! 😀

Thank you one and all for your help. 🙂

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

mazzz SSCertifiable Points: 6139 More actions · Answer 10

Jeff Moden (9/20/2010)
mazzz (9/20/2010)
Jeff Moden (9/20/2010)
mazzz (9/20/2010)
Jeff
I've just seen your update in the OP - do you need any more results?
My home setup is Win 7, Intel core duo CPU, 4GB RAM, 64bit, SQL2008 R2 if it's of any interest
I'll never turn down a set of data points, Mazzz. I'd love to see the results from above including that second test I asked folks with 2K8 to run. Thanks for your effort on this.
My pleasure, Jeff. you've helped me so much, whether directly or indirectly, it's the least I can do.
Just to clarify, the second test for 2k8 is this post ?
Or is there another one I've missed?
That's the one, Mazzz. I appreciate it.

Here we go, Jeff

...qry1.txt is the one from the OP, and ..._qry2.txt is the one for 2k8

------------------------------------------------------------------------
Bite-sized fiction (with added teeth) [/url]

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

Thanks Mazzz. All of these are matching up ver consistently. Tally table on 2k8 on the join as it's currently written is just absolutely crawling. I appreciate you taking the time to run these.

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

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

Jeff Moden (9/20/2010)
Thank you one an all for your help. 🙂

Jeff...you are more than welcome, it was the least I could do to repay you for the amount of help you have provided to so many others like myself in the past.

Kind regards Graham

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

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

Jeff using Brad Schulz blog post on using option(Force order,Maxdop 1, Loop Join).

http://bradsruminations.blogspot.com/2010/08/taking-hint.html

Again SQL Server 2008 on my bigger machine

DBCC TRACEON ( With Option(Force order,Maxdop 1,Loop Join)

Routine CPU TimeElapsed TimeCPU TimeElapsed Time

Split8KTally 95 ms 40 ms 15 ms 14

Split8KXMLBrad 951 ms 934 ms. 32 ms 27 ms

Split8KL1 156 ms 173 ms156 ms 168 ms

Snippets to illustrate code modification:

PRINT '--===== Split8KTally ==========================================================================================='

declare @ProductID int, @ProductName varchar(50), @SalesOrderID int, @OrderDate datetime, @TerritoryName varchar(50);

select @ProductID=d.ProductID

,@ProductName=p.Name

,@SalesOrderID=h.SalesOrderID

,@OrderDate=h.OrderDate

,@TerritoryName=t.Name

from dbo.Split8KTally(@CSVList,',') a

join AdventureWorks2008.Sales.SalesOrderDetail d on cast(a.ItemValue as int)=d.ProductID

join AdventureWorks2008.Production.Product p on d.ProductID=p.ProductID

join AdventureWorks2008.Sales.SalesOrderHeader h on d.SalesOrderID=h.SalesOrderID

join AdventureWorks2008.Sales.SalesTerritory t on h.TerritoryID=t.TerritoryID

order by d.ProductID,h.SalesOrderID OPTION(Force Order,Maxdop 1,Loop Join);

--declare @ProductID int, @ProductName varchar(50), @SalesOrderID int, @OrderDate datetime, @TerritoryName varchar(50);

PRINT '--===== Split8KXMLBrad ==========================================================================================='

select @ProductID=d.ProductID

,@ProductName=p.Name

,@SalesOrderID=h.SalesOrderID

,@OrderDate=h.OrderDate

,@TerritoryName=t.Name

from dbo.Split8KXMLBrad(@CSVList,',') a

join AdventureWorks2008.Sales.SalesOrderDetail d on cast(a.ItemValue as int)=d.ProductID

join AdventureWorks2008.Production.Product p on d.ProductID=p.ProductID

join AdventureWorks2008.Sales.SalesOrderHeader h on d.SalesOrderID=h.SalesOrderID

join AdventureWorks2008.Sales.SalesTerritory t on h.TerritoryID=t.TerritoryID

order by d.ProductID,h.SalesOrderID OPTION(Force Order,Maxdop 1,Loop Join);

--declare @ProductID int, @ProductName varchar(50), @SalesOrderID int, @OrderDate datetime, @TerritoryName varchar(50);

PRINT '--===== Split8KL1 ==========================================================================================='

select @ProductID=d.ProductID

,@ProductName=p.Name

,@SalesOrderID=h.SalesOrderID

,@OrderDate=h.OrderDate

,@TerritoryName=t.Name

from dbo.Split8KL1(@CSVList,',') a

join AdventureWorks2008.Sales.SalesOrderDetail d on cast(a.ItemValue as int)=d.ProductID

join AdventureWorks2008.Production.Product p on d.ProductID=p.ProductID

join AdventureWorks2008.Sales.SalesOrderHeader h on d.SalesOrderID=h.SalesOrderID

join AdventureWorks2008.Sales.SalesTerritory t on h.TerritoryID=t.TerritoryID

order by d.ProductID,h.SalesOrderID OPTION(Force Order,Maxdop 1,Loop Join);

SET STATISTICS TIME,IO OFF;

--=============

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: 1003863 More actions · Answer 14

bitbucket-25253 (9/20/2010)
Jeff using Brad Schulz blog post on using option(Force order,Maxdop 1, Loop Join).
http://bradsruminations.blogspot.com/2010/08/taking-hint.html
Again SQL Server 2008 on my bigger machine
DBCC TRACEON ( With Option(Force order,Maxdop 1,Loop Join)
Routine CPU TimeElapsed TimeCPU TimeElapsed Time
Split8KTally 95 ms 40 ms 15 ms 14
Split8KXMLBrad 951 ms 934 ms. 32 ms 27 ms
Split8KL1 156 ms 173 ms156 ms 168 ms

Holy Moly! Those are some SMOKIN' times for the Tally Table AND the XML. Now all I have to do is figure out a way to do that without all the extra options. I'm backing up my nice new laptop before I install 2K8... it'll take a bit.

--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: 1003863 More actions · Answer 15

P.S. I really want to know when you took up mind-reading, Ron. I was about to post and ask for a full run like that. Thanks.

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