Facebook Sign in | Join
Getting Started with Adobe After Effects - Part 6: Motion Blur

TSQL Challenge 50

rated by 0 users
This post has 26 Replies | 11 Followers

Top 50 Contributor
Posts 43
Points 1,814
puzzles Posted: 02-15-2011 7:24 AM
Use this thread to discuss TSQL Challenge 50
  • Post Points: 59
Top 10 Contributor
Male
Posts 208
Points 1,120

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:

GO
insert into TC50
select * from TC50
GO 12
select * 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.

 

  • Post Points: 35
Top 10 Contributor
Posts 831
Points 12,705

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.

Never knew about this 'GO n' trick.

But what to you get with this data?

truncate table tc50
insert into TC50
select 'n'+CONVERT(varchar,n)
from tsqlc_Tally
where N<100000

After 5 minutes at 100% CPU I'm still waiting.

  • Post Points: 20
Top 50 Contributor
Posts 83
Points 1,165

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 '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 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.

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

 

  • Post Points: 20
Top 10 Contributor
Male
Posts 208
Points 1,120

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

  • Post Points: 20
Top 10 Contributor
Posts 144
Points 2,645

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 ?

  • Post Points: 20
Top 50 Contributor
Posts 83
Points 1,165

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)

  • Post Points: 5
Top 50 Contributor
Posts 83
Points 1,165

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

  • Post Points: 20
Top 50 Contributor
Posts 83
Points 1,165

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.

  • Post Points: 5
Top 10 Contributor
Posts 831
Points 12,705

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 allowed

12 mins on a 2.4Ghz dual core laptop - just trying another tweak.... 
BTW - also note that you cannot use ">" 

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.

  • Post Points: 35
Top 10 Contributor
Posts 144
Points 2,645

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.

  • Post Points: 20
Top 50 Contributor
Posts 83
Points 1,165

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

 

  • Post Points: 20
Top 10 Contributor
Posts 831
Points 12,705

I have no problems doing without and/or. But I will get this stated in a rule.

  • Post Points: 5
Top 10 Contributor
Male
Posts 208
Points 1,120

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.

  • Post Points: 20
Top 10 Contributor
Posts 831
Points 12,705

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.

Leszek,
The '>' has been fixed in rule 3.
And rule 4 now officially does not allow and/or in where clauses. Now don't you realy feel like being in a straight jacket? :-) Would anyone care to be squeezed a bit more? :-)


  • Post Points: 35
Page 1 of 2 (27 items) 12Next
| RSS
Contact US

Copyright © Rivera Informatic Private Ltd.