|
|
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
Navigation:
[Reply to this message]
|