{"id":186,"date":"2016-03-31T09:30:42","date_gmt":"2016-03-31T08:30:42","guid":{"rendered":"https:\/\/sqldoubleg.live-website.com\/?p=186"},"modified":"2016-03-31T11:32:12","modified_gmt":"2016-03-31T10:32:12","slug":"index-creation-vs-ssms-query","status":"publish","type":"post","link":"https:\/\/www.sqldoubleg.com\/es\/2016\/03\/31\/index-creation-vs-ssms-query\/","title":{"rendered":"Index Creation VS SSMS Query"},"content":{"rendered":"

Is it true that creating the index is quicker than running the query you needed it for? Let me show you the answer 
\nTonight, Ladies and Gentlemen, we have one of the most expected fights in SQL Server!! Yeeeeeah!! Booooohhhh!!! (People cheering and booing)<\/p>\n

Hm, now seriously, I just read a comment in a blog post called Indexes Helping Indexes<\/a> and one user wondered why creating an index is always faster than running the same SELECT statement.<\/p>\n

My immediate thought was that cannot be true, but I need to prove it!<\/p>\n

For these cases where I need a slightly longer running query, I use the big version of AdventureWorks, you can find it in Adam Machanic’s blog<\/a>.<\/p>\n

I will create one index on [dbo].[bigTransactionHistory] and then create an index using the same columns and sorting<\/p>\n

First things first, here is the table, about 31M rows.<\/p>\n

\"COUNT_transactionBig\"<\/a><\/p>\n

To be fair to both opponents, I will do a little trick to get all data into cache, so both will benefit from not having to go disk to read it. (You’ll see it later)<\/p>\n

Now I can create the index<\/p>\n

\"create_index\"<\/a><\/p>\n

You can see that all reads are logical, no physical, no read aheads, hence no disk is involved. About 13 seconds<\/p>\n

Now let’s run the query again and see what happens<\/p>\n

\"select_same_columns_as_index\"<\/a><\/p>\n

BOOM! My system is not so bad… i7 16GB RAM, SSD … true that I keep opening new tabs everywhere, but… It really did take longer, 13 secs for CREATE INDEX vs Crashing after 23 secs.<\/p>\n

He might be right… a query takes longer than index creation. Let’s try a little trick before giving up<\/p>\n

\"discard_results\"<\/a><\/p>\n

And run the query again in a new window (IMPORTANT!!)<\/p>\n

\"select_same_columns_as_index_no_results\"<\/a><\/p>\n

That’s more like it! Obviously there are no results as we requested, not even STATISTICS, but we can see that numbers (10 vs 13 secs) are pretty similar (~ish) if not lower than CREATE INDEX as it actually does less.<\/p>\n

The reason is very simple, SSMS is pretty SLOOOOOOW and processes rows 1 by 1 so it consumes a lot time before it finishes displaying them in the default grid. Since CREATE INDEX happens internally, we have the illusion that is faster, but it’s only faster displaying the results.<\/p>\n

This trick is very useful together with stopping \u00abDisplay Actual Execution Plan\u00bb (As Erik Darling points) to tune queries as probably SSMS will not be the final user application, so removing the time to display the results will give you a better idea of how long the query really takes.<\/p>\n

Hope you enjoyed reading and feel free to comment here or in the original post<\/a>
\n 
\n <\/p>\n","protected":false},"excerpt":{"rendered":"

Is it true that creating the index is quicker than running the query you needed it for? Let me show you the answer  Tonight, Ladies and Gentlemen, we have one of the…<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[4],"tags":[32,5,31],"_links":{"self":[{"href":"https:\/\/www.sqldoubleg.com\/es\/wp-json\/wp\/v2\/posts\/186"}],"collection":[{"href":"https:\/\/www.sqldoubleg.com\/es\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sqldoubleg.com\/es\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sqldoubleg.com\/es\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sqldoubleg.com\/es\/wp-json\/wp\/v2\/comments?post=186"}],"version-history":[{"count":1,"href":"https:\/\/www.sqldoubleg.com\/es\/wp-json\/wp\/v2\/posts\/186\/revisions"}],"predecessor-version":[{"id":193,"href":"https:\/\/www.sqldoubleg.com\/es\/wp-json\/wp\/v2\/posts\/186\/revisions\/193"}],"wp:attachment":[{"href":"https:\/\/www.sqldoubleg.com\/es\/wp-json\/wp\/v2\/media?parent=186"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqldoubleg.com\/es\/wp-json\/wp\/v2\/categories?post=186"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqldoubleg.com\/es\/wp-json\/wp\/v2\/tags?post=186"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}