Recent PostsRecent Posts Popular TopicsPopular Topics
 Home Search Members Calendar Who's On

 T-SQL Counting: difference of two methods? Rate Topic Display Mode Topic Options
Author
 Message
 Posted Thursday, July 24, 2014 11:59 PM
 Forum Newbie Group: General Forum Members Last Login: Monday, July 28, 2014 4:30 PM Points: 6, Visits: 20
 Got this sample which works:SELECT TOP (5) ROW_NUMBER() OVER(ORDER BY somefield DESC) FROM sometableNow the following can also the same but what is the difference between the two? This is lifted from http://www.sqlservercentral.com/articles/T-SQL/74118/WITH E1(N) AS ( SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 ), -- 1*10^1 or 10 rows E2(N) AS (SELECT 1 FROM E1 a, E1 b), -- 1*10^2 or 100 rows E4(N) AS (SELECT 1 FROM E2 a, E2 b), -- 1*10^4 or 10,000 rows E8(N) AS (SELECT 1 FROM E4 a, E4 b) -- 1*10^8 or 100,000,000 rows SELECT TOP (5) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E8;
Post #1596089
 Posted Friday, July 25, 2014 12:06 AM
 Say Hey Kid Group: General Forum Members Last Login: Today @ 12:06 AM Points: 679, Visits: 3,649
 The only difference is that the CTEs are building a dynamic table that the query runs against. It is a very quick way to generate a lot a rows to see how well a query scales. __________________________________________________________________________________________________________How to Post to get the most: http://www.sqlservercentral.com/articles/Best+Practices/61537/
Post #1596090
 Posted Friday, July 25, 2014 12:41 AM
 SSCertifiable Group: General Forum Members Last Login: Wednesday, September 23, 2015 3:34 PM Points: 5,467, Visits: 7,660
 The second piece is a CTE Tally Table. If you just need a list of 1 through 5, that's usually faster than accessing a random table, particularly since you can strip it down to just E1 for that small of a range.What you're asking for is an odd setup, and needs context, to be able to help you figure out which is best for a particular issue. If all you need is 1-5 as a joinable table to split rows, the Tally's usually your best approach. - Craig FarrellNever 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 | Forum NetiquetteFor index/tuning help, follow these directions. |Tally TablesTwitter: @AnyWayDBA
Post #1596095
 Posted Saturday, July 26, 2014 1:10 AM
 Forum Newbie Group: General Forum Members Last Login: Monday, July 28, 2014 4:30 PM Points: 6, Visits: 20
 It is not actually for me and I just want to test if the 2nd option is really better than the first one. Upon running them in the Profiler they almost got same performance only that the first one has 4 reads compared to the 0 read of the 2nd, is the difference negligible?Thanks guys!
Post #1596458
 Posted Saturday, July 26, 2014 1:24 AM
 SSCertifiable Group: General Forum Members Last Login: Today @ 5:38 AM Points: 6,114, Visits: 16,485
 rodeliorodriguez 26453 (7/26/2014)It is not actually for me and I just want to test if the 2nd option is really better than the first one. Upon running them in the Profiler they almost got same performance only that the first one has 4 reads compared to the 0 read of the 2nd, is the difference negligible?Thanks guys!The difference between 0 and 4 is what the second option is all about, it is hugeIn other words, either having to do nothing versus N times somthing.
Post #1596460
 Posted Saturday, July 26, 2014 1:29 AM
 Forum Newbie Group: General Forum Members Last Login: Monday, July 28, 2014 4:30 PM Points: 6, Visits: 20
 The first option I am getting thisSQL Server parse and compile time: CPU time = 0 ms, elapsed time = 0 ms. 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. SQL Server Execution Times: CPU time = 0 ms, elapsed time = 0 ms. SQL Server Execution Times: CPU time = 0 ms, elapsed time = 0 ms.(1000 row(s) affected)Table 'iwItems'. Scan count 1, logical reads 9, 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 = 0 ms, elapsed time = 51 ms.SQL Server parse and compile time: CPU time = 0 ms, elapsed time = 0 ms. SQL Server Execution Times: CPU time = 0 ms, elapsed time = 0 ms.And in the second one I am getting thisSQL Server parse and compile time: CPU time = 0 ms, elapsed time = 0 ms. 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. SQL Server Execution Times: CPU time = 0 ms, elapsed time = 0 ms. SQL Server Execution Times: CPU time = 0 ms, elapsed time = 0 ms.(1000 row(s) affected)(1 row(s) affected) SQL Server Execution Times: CPU time = 0 ms, elapsed time = 40 ms.SQL Server parse and compile time: CPU time = 0 ms, elapsed time = 0 ms. SQL Server Execution Times: CPU time = 0 ms, elapsed time = 0 ms.The elapsed time is not consistent, at times the first one has lesser elapsed time, why is that?
Post #1596461
 Posted Saturday, July 26, 2014 2:52 AM
 SSCertifiable Group: General Forum Members Last Login: Today @ 5:38 AM Points: 6,114, Visits: 16,485
Post #1596465
 Posted Monday, July 28, 2014 1:39 AM
 Forum Newbie Group: General Forum Members Last Login: Monday, July 28, 2014 4:30 PM Points: 6, Visits: 20
 Thanks guys but just to straighten things out I am not really a DBA, I know the basic stuffs but digesting the difference of that posted code is out of my league, can anybody explain to me what is it really trying to tell me?Thanks for the patience!
Post #1596710
 Posted Monday, July 28, 2014 8:49 AM
 SSCertifiable Group: General Forum Members Last Login: Today @ 5:32 AM Points: 7,963, Visits: 17,234
 Just wanted to note that you won't see much difference with 5 rows. Anything will run fast with 5 rows. That's why performance tests usually start at least with 1000 rows and go on for several millions depending on the expected workload. Luis C.General Disclaimer:Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?How to post data/code on a forum to get the best help: Option 1 / Option 2
Post #1596901

 Permissions