Posted by JayCallas on 12/06/05 19:11

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

[Back to original message]