|
|
Posted by Erland Sommarskog on 03/02/06 00:30
Doug (drmiller100@hotmail.com) writes:
> I guess I don't understand your comments Mr. Sommarskog. Dick's
> solution to me is better. It is simpler, and doesn't use dynamic SQL.
>
> Further, I've had the best success with GL's using a composite index on
> Account+Subaccount.
> If you don't know the account, you will end up doing a table scan
> pretty much no matter what. If you know the account and the subaccount,
> retrievals can be VERY fast.
I will have admit that I did not consider the particular business problem,
but more considered the general case. Let's say that you have something
like:
SELECT ...
FROM tbl
WHERE (indexedcol1 = @vall OR @val1 = '*')
AND (indexedcol2 = @val2 OR @val2 = '*')
AND (nonindexedcol = @val3 OR @val3 = '*')
This will most certainly table scan, even if @val1 has a distinct value and
@val3 is '*', because when the optimizer builds the plan it has no
knowledge what value the parameter will have. It can sniff parameter
values, but it cannot build plans that produce wrong results. So it must
be a table scan. (The exception in SQL 2005 is when you add the query hint
OPTION (RECOMPILE).)
> Finally, because Dick's solution doesn't use dynamic SQL, the optimizer
> can key in on the best way to do the search relatively easily.
If you build a dynamic SQL string and include only the the search
parameters that were actually given, you will give the optimizer the
exact right amount of information to work with. If you only supply
@val1, the optimizer will use that index (if it is selective enough).
If you only supply @val2, it will use thar index, and if you supply
only @val3, the query will table scan.
For a longer discsussion on the topic, see my article
http://www.sommarskog.se/dyn-search.html.
--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
[Back to original message]
|