RE: Query of 100 mil rows with multiple parameters

SSC Guru

Points: 186127

February 28, 2014 at 6:45 am

Hi Mark

Two or three points to consider.

Firstly this is a "Catch-all query". In a nutshell, the number of rows returned could vary dramatically depending upon the parameters passed in. You'll have a minimum number of rows of n and a maximum of about 25 million. No single execution plan could be optimal for both. Gail Shaw has an excellent article here [/url]describing how you can deal with this type of query.

Secondly, your ordinary index has most of the columns of the clustered index as INCLUDE columns. This is unnecessary because nonclustered indexes contain the cluster keys - they're already included behind the scenes.

Thirdly, and this point follows on from 2: since every nonclustered index contains the cluster keys, it might be advantageous for you to use a single surrogate key instead, such as an identity column.

SSC has an excellent indexing article by David Durant here[/url].

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden