Posted by Erland Sommarskog on 12/07/05 00:29

(JayCallas@hotmail.com) writes:
> My original code built up the query (as a string) and used
> sp_executesql to execute it. I took the comma separated list of symbols
> and appended it to the query as part of the WHERE clause.
>
> When viewing the execution plan, I noted that the query used the
> indices I set on the tables (as I expected it would).
>
> I now want to move away from the sp_executesql statement. My approach
> is to follow what Erland says (on his website) and break out the comma
> separated list to a temp table and JOIN that to my main query. Except
> for the additional JOIN to the temp table, my new query looks just like
> my old query.
>
> But now when I look at the execution plan, almost NONE of lookups are
> using indices but are doing table scans instead. (And I think it
> actually runs a bit slower...)

It is true that with dynamic SQL, the optimizer has more information
about what rows that are affected. A table-valued function is opaque to the
optimizer; it has no idea what is in it. This can be cirumvented by
getting the data from the list into a temp table (not a table variable),
as a temp table has statistics.

But you problem is another:

LEFT JOIN
[Supporting].[dbo].[fDelimetedStringToTable](@indexSymbols,
DEFAULT)
ST
ON
ST.[Value] = IC.[Symbol]
WHERE
-- check if symbol was one of those specified
(NOT ST.[Value] IS NULL OR @indexSymbols IS NULL)

Since you left-join with the table-function, you are including all
rows from IndexComponent, and the function cannot be used for selecting
at all. You would have to have to different queries, one with the
list and one without it.

--
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]