StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>I really love these types of questions since you can get into doing performance analysis.</p> <p>First, lets create a sample [test] database with a [urls] table with a million random records. </p> <p>See code below.</p> <pre><code>-- Switch databases USE [master]; go -- Create simple database CREATE DATABASE [test]; go -- Switch databases USE [test]; go -- Create simple table CREATE TABLE [urls] ( my_id INT IDENTITY(1, 1) PRIMARY KEY , my_link VARCHAR(255) , my_status VARCHAR(15) ); go -- http://stackoverflow.com/questions/1393951/what-is-the-best-way-to-create-and-populate-a-numbers-table -- Load table with 1M rows of data ; WITH PASS0 AS ( SELECT 1 AS C UNION ALL SELECT 1 ), --2 rows PASS1 AS ( SELECT 1 AS C FROM PASS0 AS A , PASS0 AS B ), --4 rows PASS2 AS ( SELECT 1 AS C FROM PASS1 AS A , PASS1 AS B ), --16 rows PASS3 AS ( SELECT 1 AS C FROM PASS2 AS A , PASS2 AS B ), --256 rows PASS4 AS ( SELECT 1 AS C FROM PASS3 AS A , PASS3 AS B ), --65536 rows PASS5 AS ( SELECT 1 AS C FROM PASS4 AS A , PASS4 AS B ), --4,294,967,296 rows TALLY AS ( SELECT ROW_NUMBER() OVER ( ORDER BY C ) AS Number FROM PASS5 ) INSERT INTO urls ( my_link , my_status ) SELECT -- top 10 search engines + me CASE ( Number % 11 ) WHEN 0 THEN 'www.ask.com' WHEN 1 THEN 'www.bing.com' WHEN 2 THEN 'www.duckduckgo.com' WHEN 3 THEN 'www.dogpile.com' WHEN 4 THEN 'www.webopedia.com' WHEN 5 THEN 'www.clusty.com' WHEN 6 THEN 'www.archive.org' WHEN 7 THEN 'www.mahalo.com' WHEN 8 THEN 'www.google.com' WHEN 9 THEN 'www.yahoo.com' ELSE 'www.craftydba.com' END AS my_link , -- ratings scale CASE ( Number % 5 ) WHEN 0 THEN 'poor' WHEN 1 THEN 'fair' WHEN 2 THEN 'good' WHEN 3 THEN 'very good' ELSE 'excellent' END AS my_status FROM TALLY AS T WHERE Number <= 1000000 go </code></pre> <p>Second, we always want to clear the buffers and cache when doing performance analysis in our test environment. Also, we want to turn on statistics I/O and time to compare the results.</p> <p>See code below.</p> <pre><code>-- Show time & i/o SET STATISTICS TIME ON SET STATISTICS IO ON GO -- Remove clean buffers & clear plan cache CHECKPOINT DBCC DROPCLEANBUFFERS DBCC FREEPROCCACHE GO </code></pre> <p>Third, we want to try the first TSQL statement. Look at the execution plan and capture the statistics.</p> <pre><code>-- Try 1 SELECT * FROM urls ORDER BY my_status /* Table 'urls'. Scan count 5, logical reads 4987, physical reads 1, read-ahead reads 4918, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. SQL Server Execution Times: CPU time = 3166 ms, elapsed time = 8130 ms. */ </code></pre> <p><img src="https://i.stack.imgur.com/KXgXT.jpg" alt="enter image description here"></p> <p>Fourth, we want to try the second TSQL statement. Do not forget to clear the query plan cache and buffers. If you do not, the query takes less than 1 sec since most of the information is in memory. Look at the execution plan and capture the statistics.</p> <pre><code>-- Try 2 SELECT ROW_NUMBER() OVER (ORDER BY my_status) as my_rownum, * FROM urls /* Table 'urls'. Scan count 5, logical reads 4987, physical reads 1, read-ahead reads 4918, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. SQL Server Execution Times: CPU time = 3276 ms, elapsed time = 8414 ms. */ </code></pre> <p><img src="https://i.stack.imgur.com/Sird9.jpg" alt="enter image description here"></p> <p>Last but not least, here is the fun part, the performance analysis.</p> <p>1 - We can see that the second plan is a super set of the first. So both plans scan the clustered index and sort the data. Parallelism is used to put the results together.</p> <p>2 - The second plan / query needs to calculate the row number. It segments the data and calculates this scalar. Therefore, we end up with two more operators in the plan.</p> <p>It is not surprising that the first plan runs in 8130 ms and the second plan runs in 8414 ms.</p> <p>Always look at the query plan. Both estimated and actual. They tell you want the engine is planning to do and what it actually does.</p> <p>In this example, two different TSQL statements come up with almost identical plans.</p> <p>Sincerely</p> <p>John</p> <p><a href="http://www.craftydba.com" rel="nofollow noreferrer">www.craftydba.com</a></p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload