April 13, 2009 at 11:42 am
that's why I always include duration as a valuable test... it takes into account both CPU time and Disk Time. Of course, I list those, as well... good to know which form of poison one might be taking. 🙂
--Jeff Moden
April 13, 2009 at 11:44 am
GSquared (4/13/2009)
Jeff, in your posted test harness, you need to rename the functions in the final tests. It has "SELECT SomeInt, dbo.ConcatTest...", but it needs to be Concat8kTest and ConcatMaxTest, per the create scripts. No big deal, but you might want to edit the post accordingly.Also, I got very different CPU and total time readings than you on those. Average for the 8k UDF was 5300 milliseconds CPU, 9200 total; Max UDF was 8500 CPU, 13000 total; XML inline was 2000 CPU, 2000 total.
The XML version's IO stats are out the roof, with 154k logical reads, compared to 1985 for each of the other two, but that didn't slow it down apparently. On my system, anyway.
Of course, since the test data is pretty much random, the actual number of reads and scans will vary, as it depends on the number of unique SomeInt values and how many rows each of those has in the table.
Thanks for the heads up on the mis-naming... I'll correct the post tonight. Not sure what happend with what I posted... I do know there wasn't enough coffee involved. 😀
--Jeff Moden
April 13, 2009 at 11:47 am
I can almost guarantee that there will always be 50,000 distinct SomeInts on the million row test. And, although the SomeLetters2 column is pretty random, the average number per SomeInt works out to be very close on repeated runs.
--Jeff Moden
April 13, 2009 at 1:19 pm
Jeff Moden (4/13/2009)
I can almost guarantee that there will always be 50,000 distinct SomeInts on the million row test. And, although the SomeLetters2 column is pretty random, the average number per SomeInt works out to be very close on repeated runs.
Right, but both of those are "almost". I just brought it up because it might make for a slight difference in performance between your data and mine, since our results were significantly different.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 13, 2009 at 1:26 pm
Bob Hovious (4/13/2009)
Gus, I rewrote your function as an inline table valued function and it gets the job done in half the time of the ConcatMaxTest function, but still three times longer than the Concat8KTest function.There is just no beating the IO advantage of scanning the nonclustered index a single time.
ALTER FUNCTION [dbo].[itvfConcatXML]
(
@someInt int
)
RETURNS TABLE
AS
RETURN
(
SELECT STUFF((SELECT ',' + t2.SomeLetters2
FROM dbo.JBMTest t2
WHERE t2.SomeInt = @SomeInt
ORDER BY t2.SomeLetters2
FOR XML PATH(''),type).value('.[1]', 'varchar(MAX)'), 1, 1, '') as conCatString
)
I used a CTE to feed it a distinct list.
;with someints as (select distinct someint from jbmtest)
SELECT t1.SomeInt,f.ConCatString
FROM someints t1
cross apply dbo.itvfConcatXML(t1.someint) f
ORDER BY t1.SomeInt
Table 'JBMTest'. Scan count 50001, logical reads 154059, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 6864 ms, elapsed time = 6901 ms.
Since inline TVFs get expanded into the calling query, it's actually going to be the same execution as calling that as an inline cross apply. There's no advantage/disadvantage to it.
However, that still brings up the question of why the inline XML query was running in under half the time of the 8k UDF on my machine, and seems to be running in twice the time on yours. It's not a caching issue, since I ran each repeatedly, but I simply cannot reproduce your and Jeff's results.
I've done prior tests on the same thing, right after I first saw that XML concat method, and had the same results: it was slightly faster than other, comparable methods.
I've got a copy of SQL Dev Edition arriving at home tomorrow. When I get it, I'll run these tests there. See if a different computer gets different results for me.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 16, 2009 at 1:28 pm
Finally got a chance to speed test these things on a different computer.
Concat8kTest version took 1982 milliseconds total, 1328 CPU.
XML cross-apply took 1793 total, 1594 CPU.
Subsequent runs cut 8k down to as little as 1300 milliseconds total, but, increased the XML cross apply up to 1900 total.
That's on significantly better hardware than what I have at work, and on SQL 2008 Dev instead of 2005 Express (which is what my prior tests were on). (Work = Core 2 Duo, 2 Gig RAM; Home = Core i7 Quad, 6 Gig RAM; and that's just the start of the differences.)
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 6 posts - 16 through 20 (of 20 total)
You must be logged in to reply to this topic. Login to reply