problem seems to be the SUM line. When commented out the query takes a
second or two. When included the response time climbs to minute and a
half.
Is my code that inefficient or is SUM and ABS calls just that slow?
Any suggestions to spead this up?
Thanks,
- Jason
SET NOCOUNT ON
DECLARE @.PriceTable TABLE (
[Symbol] VARCHAR(15),
[Identity] VARCHAR(15),
[Exchange] VARCHAR(5),
[ClosingPrice] DECIMAL(18, 6)
)
-- Use previous trading date if none specified
IF @.TradeDate IS NULL
SET @.TradeDate = Supporting.dbo.GetPreviousTradeDate()
-- Get closing prices from historical positions
INSERT INTO @.PriceTable
SELECT
[Symbol],
[Identity],
[Exchange],
[ClosingPrice]
FROM
Historical.dbo.ClearingPosition
WHERE
[TradeDate] = CONVERT(NVARCHAR(10), @.TradeDate, 101)
-- Query the historical position table
SELECT
tblTrade.[Symbol],
tblTrade.[Identity],
tblTrade.[Exchange],
tblTrade.[Account],
SUM((CASE tblTrade.[Side] WHEN 'B' THEN -ABS(tblTrade.[Quantity])
ELSE ABS(tblTrade.[Quantity]) END) * (tblPrice.[ClosingPrice] -
tblTrade.[Price])) AS [Value]
FROM
Historical.dbo.ClearingTrade tblTrade
LEFT JOIN @.PriceTable tblPrice ON (tblTrade.[Symbol] =
tblPrice.[Symbol]AND tblTrade.[Identity] = tblPrice.[Identity])
WHERE
CONVERT(NVARCHAR(10), [TradeTimestamp], 101) = CONVERT(NVARCHAR(10),
@.TradeDate, 101)
GROUP BY tblTrade.[Symbol],tblTrade.[Identity],tblTrade.[Exchange],tblTrade.[Account]Jason (JayCallas@.hotmail.com) writes:
> The following stored procedure is taking too long (in my opinion). The
> problem seems to be the SUM line. When commented out the query takes a
> second or two. When included the response time climbs to minute and a
> half.
> Is my code that inefficient or is SUM and ABS calls just that slow?
No, SUM and abs() are not slow. The problem is likely to lie elsewhere:
> WHERE
> CONVERT(NVARCHAR(10), [TradeTimestamp], 101) =
CONVERT(NVARCHAR(10), @.TradeDate, 101)
I would guess that there is an index on TradeTimestamp. Or at least there
should be. Else SQL Server would have to traverse the entire ClearingTrade
table. I have no idea how big it is, but the Historical db name, make me
think it's huge!
The problem with this query is that even if there is an index on
TradeTimestamp, SQL Server cannot use it, because you have embedded
the column in an expression. SQL Server cannot assume that result the
expression agrees with the order in the index.
Thus you should rewrite this query as
TradeTimestamp >= @.TradeDate AND
TradeTimestamp < dateadd(DAY, 1, @.TradeDate)
I'm here assuming that TradeTimestamp is datetime and that @.TradeDate
is a datetime value with 00:00:00.000 in the time portion.
So why does the query run faster without the SUM? I don't know, but
I noitce that you comment away the SUM, the @.prices table is no longer
meaningful in the query, so SQL Server can simply skip reading that
table.
By the way, also the first query in the procedure can benefit from a
similar optimization:
WHERE [TradeDate] = CONVERT(NVARCHAR(10), @.TradeDate, 101)
If TradeDate is datetime, it will here be autoconverted to nvarchar(10),
and any index on the column will be ignored.
--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Do you need ABS? Do you ever expect a negative quantity? If not, you can
just leave it out. This could be the result:
SELECT
tblTrade.Symbol,
tblTrade.Identity,
tblTrade.Exchange,
tblTrade.Account,
SUM(CASE WHEN tblTrade.Side = 'B'
THEN tblTrade.Quantity * (tblTrade.Price -
tblPrice.ClosingPrice)
ELSE tblTrade.Quantity * (tblPrice.ClosingPrice -
tblTrade.Price)
END) AS Value
FROM Historical.dbo.ClearingTrade tblTrade
LEFT JOIN @.PriceTable tblPrice
ON tblTrade.Symbol = tblPrice.Symbol
AND tblTrade.Identity = tblPrice.Identity
WHERE CONVERT(NVARCHAR(10), TradeTimestamp, 101) = CONVERT(NVARCHAR(10),
@.TradeDate, 101)
GROUP BY
tblTrade.Symbol,tblTrade.Identity,tblTrade.Exchang e,tblTrade.Account
Note that this query cannot use any index on TradeTimestamp because of
the function it is wrapped in.
If possible, you might want to rewrite it so it become something like:
WHERE TradeTimestamp = CONVERT(...)
Final thought: you might want to drop the table variable, and join with
the selection that is used in the current insert statement.
Hope this helps,
Gert-Jan
Jason wrote:
> The following stored procedure is taking too long (in my opinion). The
> problem seems to be the SUM line. When commented out the query takes a
> second or two. When included the response time climbs to minute and a
> half.
> Is my code that inefficient or is SUM and ABS calls just that slow?
> Any suggestions to spead this up?
> Thanks,
> - Jason
> SET NOCOUNT ON
> DECLARE @.PriceTable TABLE (
> [Symbol] VARCHAR(15),
> [Identity] VARCHAR(15),
> [Exchange] VARCHAR(5),
> [ClosingPrice] DECIMAL(18, 6)
> )
> -- Use previous trading date if none specified
> IF @.TradeDate IS NULL
> SET @.TradeDate = Supporting.dbo.GetPreviousTradeDate()
> -- Get closing prices from historical positions
> INSERT INTO @.PriceTable
> SELECT
> [Symbol],
> [Identity],
> [Exchange],
> [ClosingPrice]
> FROM
> Historical.dbo.ClearingPosition
> WHERE
> [TradeDate] = CONVERT(NVARCHAR(10), @.TradeDate, 101)
> -- Query the historical position table
> SELECT
> tblTrade.[Symbol],
> tblTrade.[Identity],
> tblTrade.[Exchange],
> tblTrade.[Account],
> SUM((CASE tblTrade.[Side] WHEN 'B' THEN -ABS(tblTrade.[Quantity])
> ELSE ABS(tblTrade.[Quantity]) END) * (tblPrice.[ClosingPrice] -
> tblTrade.[Price])) AS [Value]
> FROM
> Historical.dbo.ClearingTrade tblTrade
> LEFT JOIN @.PriceTable tblPrice ON (tblTrade.[Symbol] =
> tblPrice.[Symbol]AND tblTrade.[Identity] = tblPrice.[Identity])
> WHERE
> CONVERT(NVARCHAR(10), [TradeTimestamp], 101) = CONVERT(NVARCHAR(10),
> @.TradeDate, 101)
> GROUP BY tblTrade.[Symbol],tblTrade.[Identity],tblTrade.[Exchange],tblTrade.[Account]|||Gert-Jan Strik <sorry@.toomuchspamalready.nl> wrote in message news:<3F736C4E.5F8F43A3@.toomuchspamalready.nl>...
> Do you need ABS? Do you ever expect a negative quantity? If not, you can
> just leave it out. This could be the result:
> SELECT
> tblTrade.Symbol,
> tblTrade.Identity,
> tblTrade.Exchange,
> tblTrade.Account,
> SUM(CASE WHEN tblTrade.Side = 'B'
> THEN tblTrade.Quantity * (tblTrade.Price -
> tblPrice.ClosingPrice)
> ELSE tblTrade.Quantity * (tblPrice.ClosingPrice -
> tblTrade.Price)
> END) AS Value
> FROM Historical.dbo.ClearingTrade tblTrade
> LEFT JOIN @.PriceTable tblPrice
> ON tblTrade.Symbol = tblPrice.Symbol
> AND tblTrade.Identity = tblPrice.Identity
> WHERE CONVERT(NVARCHAR(10), TradeTimestamp, 101) = CONVERT(NVARCHAR(10),
> @.TradeDate, 101)
> GROUP BY
> tblTrade.Symbol,tblTrade.Identity,tblTrade.Exchang e,tblTrade.Account
>
> Note that this query cannot use any index on TradeTimestamp because of
> the function it is wrapped in.
> If possible, you might want to rewrite it so it become something like:
> WHERE TradeTimestamp = CONVERT(...)
> Final thought: you might want to drop the table variable, and join with
> the selection that is used in the current insert statement.
> Hope this helps,
> Gert-Jan
>
> Jason wrote:
> > The following stored procedure is taking too long (in my opinion). The
> > problem seems to be the SUM line. When commented out the query takes a
> > second or two. When included the response time climbs to minute and a
> > half.
> > Is my code that inefficient or is SUM and ABS calls just that slow?
> > Any suggestions to spead this up?
> > Thanks,
> > - Jason
> > SET NOCOUNT ON
> > DECLARE @.PriceTable TABLE (
> > [Symbol] VARCHAR(15),
> > [Identity] VARCHAR(15),
> > [Exchange] VARCHAR(5),
> > [ClosingPrice] DECIMAL(18, 6)
> > )
> > -- Use previous trading date if none specified
> > IF @.TradeDate IS NULL
> > SET @.TradeDate = Supporting.dbo.GetPreviousTradeDate()
> > -- Get closing prices from historical positions
> > INSERT INTO @.PriceTable
> > SELECT
> > [Symbol],
> > [Identity],
> > [Exchange],
> > [ClosingPrice]
> > FROM
> > Historical.dbo.ClearingPosition
> > WHERE
> > [TradeDate] = CONVERT(NVARCHAR(10), @.TradeDate, 101)
> > -- Query the historical position table
> > SELECT
> > tblTrade.[Symbol],
> > tblTrade.[Identity],
> > tblTrade.[Exchange],
> > tblTrade.[Account],
> > SUM((CASE tblTrade.[Side] WHEN 'B' THEN -ABS(tblTrade.[Quantity])
> > ELSE ABS(tblTrade.[Quantity]) END) * (tblPrice.[ClosingPrice] -
> > tblTrade.[Price])) AS [Value]
> > FROM
> > Historical.dbo.ClearingTrade tblTrade
> > LEFT JOIN @.PriceTable tblPrice ON (tblTrade.[Symbol] =
> > tblPrice.[Symbol]AND tblTrade.[Identity] = tblPrice.[Identity])
> > WHERE
> > CONVERT(NVARCHAR(10), [TradeTimestamp], 101) = CONVERT(NVARCHAR(10),
> > @.TradeDate, 101)
> > GROUP BY tblTrade.[Symbol],tblTrade.[Identity],tblTrade.[Exchange],tblTrade.[Account]
I know for a fact the the problem (whatever it is) is on line :
SUM(CASE WHEN tblTrade.Side = 'B' THEN tblTrade.Quantity *
(tblTrade.Price -
tblPrice.ClosingPrice) ELSE tblTrade.Quantity * (tblPrice.ClosingPrice
-
tblTrade.Price) END) AS Value
When this line is included the query takes about 90 seconds, when it
is commented out the query takes 1 or 2 seconds.
And I have to have the ABS in there. SOME of the clearing firms report
sells as negative quantities.|||After further testing it seems that just the act of making a query
against @.PriceTable is causing the slow performance. Putting an index
on ClearingTrade.TradeDate made no perceptible difference.
But when I removed the variable @.PriceTable and included the reference
to ClearingPosition directly the response time became 2 seconds. (See
new code below)
Are table variables that slow? Should they be avoided whenever
possible? Is there any way to speed them up?
I want to thank everyone who made suggestions on this issue.
- Jason
DECLARE @.TradeDate DATETIME
-- Use previous trading date if none specified
IF @.TradeDate IS NULL
SET @.TradeDate = Supporting.dbo.GetPreviousTradeDate()
-- Make the query
SELECT
tblTrade.[Symbol],
tblTrade.[Identity],
tblTrade.[Exchange],
tblTrade.[Account],
SUM((CASE tblTrade.[Side] WHEN 'B' THEN -ABS(tblTrade.[Quantity])
ELSE ABS(tblTrade.[Quantity]) END) * (tblPos.[ClosingPrice] -
tblTrade.[Price])) AS [Value]
FROM
Historical.dbo.ClearingTrade tblTrade
LEFT JOIN Historical.dbo.ClearingPosition tblPos ON (@.TradeDate =
tblPos.[TradeDate] AND tblTrade.[Symbol] = tblPos.[Symbol] AND
tblTrade.[Identity] = tblPos.[Identity])
WHERE
([TradeTimestamp] >= @.TradeDate AND [TradeTimestamp] < DATEADD(DAY,
1, @.TradeDate))
GROUP BY tblTrade.[Symbol],tblTrade.[Identity],tblTrade.[Exchange],tblTrade.[Account]|||Jason (JayCallas@.hotmail.com) writes:
> After further testing it seems that just the act of making a query
> against @.PriceTable is causing the slow performance. Putting an index
> on ClearingTrade.TradeDate made no perceptible difference.
In the query you refer to ClearingTrade.TradeTimestamp. If you have
a column ClearingTrade.TradeDate which holds the value of TradeTimestamp
with the time portion cleared, you should probably use this column
insteatd in the query.
This is not the least important if the index you added is non-clustered.
It would however make sense to have the clsutered index on a historical
table on a date or datetime column.
> But when I removed the variable @.PriceTable and included the reference
> to ClearingPosition directly the response time became 2 seconds. (See
> new code below)
> Are table variables that slow? Should they be avoided whenever
> possible? Is there any way to speed them up?
No, table variables are not inherently slow. I had a performance problem
a couple of weeks ago that I was able to solve by replacing a temp
table with a table variable.
Table variable does however not have any statistics. Therefore the
assumptions that SQL Server makes when it builds the query plan for
a table variable may not be accurate.
Exactly what happened in you case, I don't know, since I don't know
how your tables look like, which indexes they have and much
data they contain, and the distribution of that data.
But I'm fairly certain that you get different query plans for slow
and fast queries, and study of these query plans may lead to an
understanding if what's happening.
One important factor here is that with a non-clustered index, it is not
always a good idea to use the index. If there are too many hits in
the index, SQL Server will have to go to the same data page more than
once. Thus, a table scan may be better. Or the optimizer may think so.
--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp
No comments:
Post a Comment