Should I worry

[JayCallas]

I have a stored procedure where I need to pass in a comma separated
list of symbols and return back results.

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...)

Am I doing something wrong? Is using sp_executesql a better way to go?
If SQL is not using my indices, should I trust it or be worried that I
am not getting the best performance that I could be?

I am posting the queries for review but no code for recreating tables
since this is more of an opinion question.

Old Query
----------------------------------------------
CREATE PROCEDURE spFetchIndexComponentsBySymbol (
@indexSymbols VARCHAR(2000) = NULL
)
AS
SET NOCOUNT ON

DECLARE @sql NVARCHAR(4000)

-- build base SQL statement
SELECT @sql =
'SELECT
IC.[IndexSymbol],
IC.[IndexIdentity],
IC.[Symbol],
IC.[Identity],
SM.[Name],
SME.[Exchange],
IC.[Shares]
, BBO.[Last] AS [ClosingPrice]
FROM
Trading.dbo.IndexComponent IC
JOIN
Trading.dbo.SecurityMaster SM
ON
SM.[Symbol] = IC.[Symbol]
AND SM.[Identity] = IC.[Identity]
LEFT JOIN
Trading.dbo.SM_mm_Exchange SME
ON
SME.[Symbol] = IC.[Symbol]
AND SME.[Identity] = IC.[Identity]
AND SME.[PrimaryExchangeFlag] = 1
LEFT JOIN
[Trading].[dbo].[BestBidOffer] BBO
ON
BBO.[Symbol] = IC.[Symbol]
AND BBO.[Identity] = IC.[Identity]
WHERE
1 = 1'

-- process symbol parameter
IF @indexSymbols IS NOT NULL
SELECT @sql = @sql + ' AND [IndexSymbol] IN (' + @indexSymbols + ')'

-- execute it
EXEC sp_executesql @sql
GO

New Query
--------------------------------------------------------------------
CREATE PROCEDURE spFetchIndexComponentsBySymbol (
@indexSymbols VARCHAR(2000) = NULL
)
AS
SET NOCOUNT ON

-- perform query
SELECT
IC.[IndexSymbol]
, IC.[IndexIdentity]
, IC.[Symbol]
, IC.[Identity]
, SM.[Name]
, SME.[Exchange]
, IC.[Shares]
, BBO.[Last] AS [ClosingPrice]
FROM
[Trading].[dbo].[IndexComponent] IC
JOIN
[Trading].[dbo].[SecurityMaster] SM
ON
SM.[Symbol] = IC.[Symbol]
AND SM.[Identity] = IC.[Identity]
LEFT JOIN
[Trading].[dbo].[SM_mm_Exchange] SME
ON
SME.[Symbol] = IC.[Symbol]
AND SME.[Identity] = IC.[Identity]
AND SME.[PrimaryExchangeFlag] = 1
LEFT JOIN
[Trading].[dbo].[BestBidOffer] BBO
ON
BBO.[Symbol] = IC.[Symbol]
AND BBO.[Identity] = IC.[Identity]
-- Join against temp table to expand comma separated list of symbols
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)
GO

[Erland Sommarskog]

(Ja*******@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, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[JayCallas]

Thanks for the reply Erland.

As you can see, I did the left-join so the user can pass null and get
everything or they can ask for specific symbols.

As to having different queries, what is the communities opinion about
having two queries in the same stored procedure and using an IF
statement to branch to one of them? Does that play havoc with the
execution plan since either query can be called? (I could create
another stored procedure to handle ALL securities but I already have
applications out there that use my existing one.)

[Erland Sommarskog]

(Ja*******@hotmail.com) writes:

As you can see, I did the left-join so the user can pass null and get
everything or they can ask for specific symbols.

As to having different queries, what is the communities opinion about
having two queries in the same stored procedure and using an IF
statement to branch to one of them? Does that play havoc with the
execution plan since either query can be called? (I could create
another stored procedure to handle ALL securities but I already have
applications out there that use my existing one.)

There is a risk at some occassions due to parameter sniffing. If you have
say:

IF @x IS NOT NULL
SELECT ... FROM tbl WHERE x = @x
ELSE IF @y IS NOT NULL
SELECT ... FROM tbl WHERE y = @y

Say that first time the procedure is called with a value @x in. The plan
will then be built with the assumption that @y is NULL, which may not be
the best.

One way to handle this, is to have sub-procedures for each query.
Admittedly, this is not very manageable. Another way is to use dynamic
SQL, and I have an article on my web site about this:
http://www.sommarskog.se/dyn-search.html.

In this particular case, I don't think there is any reason for concern
though, as the optimizer is not likely to be able to make anything useful
from the parameter with the list.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx