Great challenge.
My last (i think) version stats:
(8 row(s) affected)Table 'tsqlc_Tally'. Scan count 6, logical reads 18, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.Table 'TC50'. Scan count 23, logical reads 23, 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 = 0 ms, elapsed time = 0 ms.SQL Server parse and compile time: CPU time = 0 ms, elapsed time = 0 ms.
And stats for 65536 rows:
GOinsert into TC50 select * from TC50GO 12select * from TC50
(32768 row(s) affected)Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.Table 'TC50'. Scan count 12, logical reads 2032, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.Table 'tsqlc_Tally'. Scan count 6, logical reads 127, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.Table 'Worktable'. Scan count 0, logical reads 0, 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 = 406 ms, elapsed time = 855 ms.SQL Server parse and compile time: CPU time = 0 ms, elapsed time = 0 ms.
Leszek,I have a Dual Core 2.8GHz. For your 'GO 12' data I get
(32768 row(s) affected)Table 'tsqlc_Tally'. Scan count 6, logical reads 157, physical reads 2, read-ahead reads 28, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.Table 'TC50'. Scan count 8, logical reads 1120, 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 = 343 ms, elapsed time = 796 ms.
truncate table tc50insert into TC50select 'n'+CONVERT(varchar,n)from tsqlc_Tallywhere N<100000
Agreed - good challenge - but boy it would stir up a storm with some people - a recent "discussion" on SSC about an interview question like this comes to mind...
Anyway, my stats:
Sample Data:
Table 'TC50'. Scan count 13, logical reads 13, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'tsqlc_Tally'. Scan count 6, logical reads 24, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, 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 = 0 ms, elapsed time = 3 ms.
And 65536 - (just like Leszek I just multiplied the existing rows)
Table 'TC50'. Scan count 12, logical reads 1120, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'tsqlc_Tally'. Scan count 6, logical reads 127, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
CPU time = 218 ms, elapsed time = 177 ms.
EDIT: To make it clear, I was "bitbucketing" the results - which is probably the only real difference between mine and Leszek's
dishdy:After 5 minutes at 100% CPU I'm still waiting.
15 min and still executing ;)
BTW to all, check your code, 'join', 'and', 'or' are not allowed
MisterMagoo: Agreed - good challenge - but boy it would stir up a storm with some people - a recent "discussion" on SSC about an interview question like this comes to mind...
Sounds interesting. Do you have a link to that discussion ?
Stefan_G: MisterMagoo: Agreed - good challenge - but boy it would stir up a storm with some people - a recent "discussion" on SSC about an interview question like this comes to mind... Sounds interesting. Do you have a link to that discussion ?
I do : http://www.sqlservercentral.com/Forums/Topic1059167-145-1.aspx
It descends into a bit of a scrap quite quickly - not really a technical debate so much as a fight.
(Can't create a link for some reason)
Leszek Gniadkowski: dishdy:After 5 minutes at 100% CPU I'm still waiting. 15 min and still executing ;) BTW to all, check your code, 'join', 'and', 'or' are not allowed
12 mins on a 2.4Ghz dual core laptop - just trying another tweak....
BTW - also note that you cannot use ">"
So,my final stats for 100,000 unique names (under parallelism) are horrible - let's hope the actual perf test data isn't that bad!
Table 'TC50'. Scan count 7, logical reads 702, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table 'Worktable'. Scan count 100000, logical reads 23188756, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table 'tsqlc_Tally'. Scan count 100000, logical reads 300000, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. (1 row(s) affected) SQL Server Execution Times: CPU time = 1507750 ms, elapsed time = 784408 ms.
MisterMagoo: Leszek Gniadkowski: dishdy:After 5 minutes at 100% CPU I'm still waiting.15 min and still executing ;)BTW to all, check your code, 'join', 'and', 'or' are not allowed12 mins on a 2.4Ghz dual core laptop - just trying another tweak.... BTW - also note that you cannot use ">"
Leszek Gniadkowski: dishdy:After 5 minutes at 100% CPU I'm still waiting.15 min and still executing ;)BTW to all, check your code, 'join', 'and', 'or' are not allowed
dishdy:Well, no 'joins' is easy to overcome.No '>' I'm pretty sure is an oversight. Instead of a>b you would say b<a.As for no 'and' and 'or', that's hard to believe. If 'not' was explicity not allowed and 'where... in (...)' was explicity not allowed then I think, by default, 'and' and 'or' should be allowed.So we'll check on this.
Well, since I know it is possible to create a solution without "and" or "or" I think we should let the challenge remain as it is.
But that is just my personal opinion.
Stefan_G: dishdy:Well, no 'joins' is easy to overcome.No '>' I'm pretty sure is an oversight. Instead of a>b you would say b<a.As for no 'and' and 'or', that's hard to believe. If 'not' was explicity not allowed and 'where... in (...)' was explicity not allowed then I think, by default, 'and' and 'or' should be allowed.So we'll check on this. Well, since I know it is possible to create a solution without "and" or "or" I think we should let the challenge remain as it is. But that is just my personal opinion.
I agree - the challenge is possible as it stands - we gain nothing from changing the rules now...
I have no problems doing without and/or. But I will get this stated in a rule.
dishdy:No '>' I'm pretty sure is an oversight. Instead of a>b you would say b<a.
I think that '>' will be allowed, small mistake with order in code causes fail.
Leszek Gniadkowski: dishdy:No '>' I'm pretty sure is an oversight. Instead of a>b you would say b<a.I think that '>' will be allowed, small mistake with order in code causes fail.