Query Performance Problem

[Jason]

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. GetPreviousTrad eDate()

-- Get closing prices from historical positions
INSERT INTO @PriceTable
SELECT
[Symbol],
[Identity],
[Exchange],
[ClosingPrice]
FROM
Historical.dbo. ClearingPositio n
WHERE
[TradeDate] = CONVERT(NVARCHA R(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(NVARCHA R(10), [TradeTimestamp], 101) = CONVERT(NVARCHA R(10),
@TradeDate, 101)
GROUP BY tblTrade.[Symbol],tblTrade.[Identity],tblTrade.[Exchange],tblTrade.[Account]

[Erland Sommarskog]

Re: Query Performance Problem

Jason (JayCallas@hotm ail.com) writes:[color=blue]
> 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?[/color]

No, SUM and abs() are not slow. The problem is likely to lie elsewhere:
[color=blue]
> WHERE
> CONVERT(NVARCHA R(10), [TradeTimestamp], 101) =[/color]
CONVERT(NVARCHA R(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(NVARCHA R(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/techinfo/productdoc/2000/books.asp

[Gert-Jan Strik]

Re: Query Performance Problem

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.Identi ty,
tblTrade.Exchan ge,
tblTrade.Accoun t,
SUM(CASE WHEN tblTrade.Side = 'B'
THEN tblTrade.Quanti ty * (tblTrade.Price -
tblPrice.Closin gPrice)
ELSE tblTrade.Quanti ty * (tblPrice.Closi ngPrice -
tblTrade.Price)
END) AS Value
FROM Historical.dbo. ClearingTrade tblTrade
LEFT JOIN @PriceTable tblPrice
ON tblTrade.Symbol = tblPrice.Symbol
AND tblTrade.Identi ty = tblPrice.Identi ty
WHERE CONVERT(NVARCHA R(10), TradeTimestamp, 101) = CONVERT(NVARCHA R(10),
@TradeDate, 101)
GROUP BY
tblTrade.Symbol ,tblTrade.Ident ity,tblTrade.Ex change,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:[color=blue]
>
> 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. GetPreviousTrad eDate()
>
> -- Get closing prices from historical positions
> INSERT INTO @PriceTable
> SELECT
> [Symbol],
> [Identity],
> [Exchange],
> [ClosingPrice]
> FROM
> Historical.dbo. ClearingPositio n
> WHERE
> [TradeDate] = CONVERT(NVARCHA R(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(NVARCHA R(10), [TradeTimestamp], 101) = CONVERT(NVARCHA R(10),
> @TradeDate, 101)
> GROUP BY tblTrade.[Symbol],tblTrade.[Identity],tblTrade.[Exchange],tblTrade.[Account][/color]

[Jason]

Re: Query Performance Problem

Gert-Jan Strik <sorry@toomuchs pamalready.nl> wrote in message news:<3F736C4E. 5F8F43A3@toomuc hspamalready.nl >...[color=blue]
> 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.Identi ty,
> tblTrade.Exchan ge,
> tblTrade.Accoun t,
> SUM(CASE WHEN tblTrade.Side = 'B'
> THEN tblTrade.Quanti ty * (tblTrade.Price -
> tblPrice.Closin gPrice)
> ELSE tblTrade.Quanti ty * (tblPrice.Closi ngPrice -
> tblTrade.Price)
> END) AS Value
> FROM Historical.dbo. ClearingTrade tblTrade
> LEFT JOIN @PriceTable tblPrice
> ON tblTrade.Symbol = tblPrice.Symbol
> AND tblTrade.Identi ty = tblPrice.Identi ty
> WHERE CONVERT(NVARCHA R(10), TradeTimestamp, 101) = CONVERT(NVARCHA R(10),
> @TradeDate, 101)
> GROUP BY
> tblTrade.Symbol ,tblTrade.Ident ity,tblTrade.Ex change,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:[color=green]
> >
> > 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. GetPreviousTrad eDate()
> >
> > -- Get closing prices from historical positions
> > INSERT INTO @PriceTable
> > SELECT
> > [Symbol],
> > [Identity],
> > [Exchange],
> > [ClosingPrice]
> > FROM
> > Historical.dbo. ClearingPositio n
> > WHERE
> > [TradeDate] = CONVERT(NVARCHA R(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(NVARCHA R(10), [TradeTimestamp], 101) = CONVERT(NVARCHA R(10),
> > @TradeDate, 101)
> > GROUP BY tblTrade.[Symbol],tblTrade.[Identity],tblTrade.[Exchange],tblTrade.[Account][/color][/color]

I know for a fact the the problem (whatever it is) is on line :

SUM(CASE WHEN tblTrade.Side = 'B' THEN tblTrade.Quanti ty *
(tblTrade.Price -
tblPrice.Closin gPrice) ELSE tblTrade.Quanti ty * (tblPrice.Closi ngPrice
-
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.

[Jason]

Re: Query Performance Problem

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.T radeDate made no perceptible difference.

But when I removed the variable @PriceTable and included the reference
to ClearingPositio n 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. GetPreviousTrad eDate()

-- 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. ClearingPositio n 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]

[Erland Sommarskog]

Re: Query Performance Problem

Jason (JayCallas@hotm ail.com) writes:[color=blue]
> 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.T radeDate made no perceptible difference.[/color]

In the query you refer to ClearingTrade.T radeTimestamp. If you have
a column ClearingTrade.T radeDate 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.
[color=blue]
> But when I removed the variable @PriceTable and included the reference
> to ClearingPositio n 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?[/color]

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/techinfo/productdoc/2000/books.asp