Query Problem

I have the following query that is giving me an error that I can't quite
figure out. The error I get is Server: Msg 8624, Level 16, State 3, Line 11
Internal SQL Server error. I think it is related to the sub-query or the
group by. The sub-query executes successfully if I run it by itself.
Any Ideas as to what I can try?
Thanks
Jeff
Declare @.StartDate VarChar(12)
SET @.StartDate = '04/07/03'
SELECT Plant, COUNT(Distinct PERSONFULLNAME) AS EmployeeCount, Period,
@.StartDate as StartDate
FROM (SELECT A.PERSONFULLNAME, C.LABORLEVELDSC1 AS PLANT,
CASE
WHEN A.APPLYDATE BETWEEN DateAdd(Day, 42, @.StartDate) And
DateAdd(Day, 55, @.StartDate) Then 'This Pay Period'
WHEN A.APPLYDATE BETWEEN DateAdd(Day, 28, @.StartDate) And
DateAdd(Day, 41, @.StartDate) Then 'Last Pay Period'
WHEN A.APPLYDATE BETWEEN DateAdd(Day, 00, @.StartDate) And
DateAdd(Day, 55, @.StartDate) Then 'Last 4 Pay Periods'
/* WHEN A.APPLYDATE BETWEEN DateAdd(Day, 00, @.StartDate)
And DateAdd(Day, 13, @.StartDate) Then 4 */
End AS Period
FROM VP_ALLTOTALS AS A, VP_EMPLOYEE AS B, VP_LABORACCOUNT AS C
WHERE A.WFCLABORLEVELNAME1 = C.LABORLEVELNAME1
AND A.WFCLABORLEVELNAME2 = C.LABORLEVELNAME2
AND A.WFCLABORLEVELNAME3 = C.LABORLEVELNAME3
AND A.WFCLABORLEVELNAME4 = C.LABORLEVELNAME4
AND A.EMPLOYEEID = B.EMPLOYEEID
AND B.PayRuleName <> 'All Exempt'
AND A.APPLYDATE Between DateAdd(Day, 0, @.StartDate) And
DateAdd(Day, 55, @.StartDate)
GROUP BY A.PERSONFULLNAME, C.LABORLEVELDSC1, A.APPLYDATE) AS TEST
GROUP BY Plant, Period
ORDER BY Plant, Period DESC
I found the problem.
Thanks
Jeff
"Jeff Cichocki" <jeffc@.belgioioso.com> wrote in message
news:OyvRuYWDGHA.2320@.TK2MSFTNGP11.phx.gbl...
>I have the following query that is giving me an error that I can't quite
>figure out. The error I get is Server: Msg 8624, Level 16, State 3, Line 11
>Internal SQL Server error. I think it is related to the sub-query or the
>group by. The sub-query executes successfully if I run it by itself.
> Any Ideas as to what I can try?
> Thanks
> Jeff
>
> Declare @.StartDate VarChar(12)
> SET @.StartDate = '04/07/03'
> SELECT Plant, COUNT(Distinct PERSONFULLNAME) AS EmployeeCount, Period,
> @.StartDate as StartDate
> FROM (SELECT A.PERSONFULLNAME, C.LABORLEVELDSC1 AS PLANT,
> CASE
> WHEN A.APPLYDATE BETWEEN DateAdd(Day, 42, @.StartDate) And
> DateAdd(Day, 55, @.StartDate) Then 'This Pay Period'
> WHEN A.APPLYDATE BETWEEN DateAdd(Day, 28, @.StartDate) And
> DateAdd(Day, 41, @.StartDate) Then 'Last Pay Period'
> WHEN A.APPLYDATE BETWEEN DateAdd(Day, 00, @.StartDate) And
> DateAdd(Day, 55, @.StartDate) Then 'Last 4 Pay Periods'
> /* WHEN A.APPLYDATE BETWEEN DateAdd(Day, 00, @.StartDate)
> And DateAdd(Day, 13, @.StartDate) Then 4 */
> End AS Period
> FROM VP_ALLTOTALS AS A, VP_EMPLOYEE AS B, VP_LABORACCOUNT AS C
> WHERE A.WFCLABORLEVELNAME1 = C.LABORLEVELNAME1
> AND A.WFCLABORLEVELNAME2 = C.LABORLEVELNAME2
> AND A.WFCLABORLEVELNAME3 = C.LABORLEVELNAME3
> AND A.WFCLABORLEVELNAME4 = C.LABORLEVELNAME4
> AND A.EMPLOYEEID = B.EMPLOYEEID
> AND B.PayRuleName <> 'All Exempt'
> AND A.APPLYDATE Between DateAdd(Day, 0, @.StartDate) And
> DateAdd(Day, 55, @.StartDate)
> GROUP BY A.PERSONFULLNAME, C.LABORLEVELDSC1, A.APPLYDATE) AS TEST
> GROUP BY Plant, Period
> ORDER BY Plant, Period DESC
>

Query Problem

Can you tell me what is wrong with this line of SQL statement?
if not exists(select @.InitialPasswordInd = InitialPasswordInd
where signonname = @.signonname
and userpassword = @.userpassword)
goto passwordinvalid
Assuming that @.InitialPasswordInd is declare and is the same type as
"InitialPasswordInd".
It does not like @.InitialPasswordInd = InitialPasswordInd
Is this not the proper way to get values form the database?
The query should only return one record. I tried Select Distinct... but I
get the same error (Something wrong near the equals sign.
Thanks in advance for your assistance!!!!!!!!!!!!!!It may be a typo, but I didn't see an FROM clause. Let us know...
James|||You were correct, I did not have a "from" - But I still get same error.
if not exists(select @.InitialPasswordInd = InitialPasswordInd
from dbo.Signon
where signonname = @.signonname
and userpassword = @.userpassword)
goto passwordinvalid|||You can't assign to a variable in a subquery. If you want to get the value,
just
select @.InitialPasswordInd ...
If you want to use the existence in a query,
if not exists (
select InitialPasswordInd
from ...
)
You can't do both in the subquery.
SK
"CJ Silin" <cjssilin@.nospam.com> wrote in message
news:Xns9479867BCDF3Acsilinhotmailcom@.207.46.248.16...
> Can you tell me what is wrong with this line of SQL statement?
> if not exists(select @.InitialPasswordInd = InitialPasswordInd
> where signonname = @.signonname
> and userpassword = @.userpassword)
> goto passwordinvalid
> Assuming that @.InitialPasswordInd is declare and is the same type as
> "InitialPasswordInd".
> It does not like @.InitialPasswordInd = InitialPasswordInd
> Is this not the proper way to get values form the database?
> The query should only return one record. I tried Select Distinct... but I
> get the same error (Something wrong near the equals sign.
> Thanks in advance for your assistance!!!!!!!!!!!!!!|||An EXISTS test is not a data retrieval operation so you can't specify the
variable assignment in the WHERE clause. Also, you have no FROM clause.
If you need to check for data existence and retrieve data, you can check for
a NULL variable value (of a non-NULL column) following the select. For
example:
SELECT @.InitialPasswordInd = InitialPasswordInd
FROM MyTable
WHERE signonname = @.signonname
AND userpassword = @.userpassword
IF @.InitialPasswordInd IS NULL GOTO passwordinvalid
Hope this helps.
Dan Guzman
SQL Server MVP
"CJ Silin" <cjssilin@.nospam.com> wrote in message
news:Xns9479867BCDF3Acsilinhotmailcom@.207.46.248.16...
> Can you tell me what is wrong with this line of SQL statement?
> if not exists(select @.InitialPasswordInd = InitialPasswordInd
> where signonname = @.signonname
> and userpassword = @.userpassword)
> goto passwordinvalid
> Assuming that @.InitialPasswordInd is declare and is the same type as
> "InitialPasswordInd".
> It does not like @.InitialPasswordInd = InitialPasswordInd
> Is this not the proper way to get values form the database?
> The query should only return one record. I tried Select Distinct... but I
> get the same error (Something wrong near the equals sign.
> Thanks in advance for your assistance!!!!!!!!!!!!!!

Query Problem

Can you tell me what is wrong with this line of SQL statement?
if not exists(select @.InitialPasswordInd = InitialPasswordInd
where signonname = @.signonname
and userpassword = @.userpassword)
goto passwordinvalid
Assuming that @.InitialPasswordInd is declare and is the same type as
"InitialPasswordInd".
It does not like @.InitialPasswordInd = InitialPasswordInd
Is this not the proper way to get values form the database?
The query should only return one record. I tried Select Distinct... but I
get the same error (Something wrong near the equals sign.
Thanks in advance for your assistance!!!!!!!!!!!!!!It may be a typo, but I didn't see an FROM clause. Let us know...
James|||You were correct, I did not have a "from" - But I still get same error.
if not exists(select @.InitialPasswordInd = InitialPasswordInd
from dbo.Signon
where signonname = @.signonname
and userpassword = @.userpassword)
goto passwordinvalid|||You can't assign to a variable in a subquery. If you want to get the value,
just
select @.InitialPasswordInd ...
If you want to use the existence in a query,
if not exists (
select InitialPasswordInd
from ...
)
You can't do both in the subquery.
SK
"CJ Silin" <cjssilin@.nospam.com> wrote in message
news:Xns9479867BCDF3Acsilinhotmailcom@.20
7.46.248.16...

quote:

---

> Can you tell me what is wrong with this line of SQL statement?
> if not exists(select @.InitialPasswordInd = InitialPasswordInd
> where signonname = @.signonname
> and userpassword = @.userpassword)
> goto passwordinvalid
> Assuming that @.InitialPasswordInd is declare and is the same type as
> "InitialPasswordInd".
> It does not like @.InitialPasswordInd = InitialPasswordInd
> Is this not the proper way to get values form the database?
> The query should only return one record. I tried Select Distinct... but I
> get the same error (Something wrong near the equals sign.
> Thanks in advance for your assistance!!!!!!!!!!!!!!

---

|||An EXISTS test is not a data retrieval operation so you can't specify the
variable assignment in the WHERE clause. Also, you have no FROM clause.
If you need to check for data existence and retrieve data, you can check for
a NULL variable value (of a non-NULL column) following the select. For
example:
SELECT @.InitialPasswordInd = InitialPasswordInd
FROM MyTable
WHERE signonname = @.signonname
AND userpassword = @.userpassword
IF @.InitialPasswordInd IS NULL GOTO passwordinvalid
Hope this helps.
Dan Guzman
SQL Server MVP
"CJ Silin" <cjssilin@.nospam.com> wrote in message
news:Xns9479867BCDF3Acsilinhotmailcom@.20
7.46.248.16...

quote:

---

> Can you tell me what is wrong with this line of SQL statement?
> if not exists(select @.InitialPasswordInd = InitialPasswordInd
> where signonname = @.signonname
> and userpassword = @.userpassword)
> goto passwordinvalid
> Assuming that @.InitialPasswordInd is declare and is the same type as
> "InitialPasswordInd".
> It does not like @.InitialPasswordInd = InitialPasswordInd
> Is this not the proper way to get values form the database?
> The query should only return one record. I tried Select Distinct... but I
> get the same error (Something wrong near the equals sign.
> Thanks in advance for your assistance!!!!!!!!!!!!!!

---

sql

Query Problem

I have the following query that is giving me an error that I can't quite
figure out. The error I get is Server: Msg 8624, Level 16, State 3, Line 11
Internal SQL Server error. I think it is related to the sub-query or the
group by. The sub-query executes successfully if I run it by itself.
Any Ideas as to what I can try?
Thanks
Jeff
Declare @.StartDate VarChar(12)
SET @.StartDate = '04/07/03'
SELECT Plant, COUNT(Distinct PERSONFULLNAME) AS EmployeeCount, Period,
@.StartDate as StartDate
FROM (SELECT A.PERSONFULLNAME, C.LABORLEVELDSC1 AS PLANT,
CASE
WHEN A.APPLYDATE BETWEEN DateAdd(Day, 42, @.StartDate) And
DateAdd(Day, 55, @.StartDate) Then 'This Pay Period'
WHEN A.APPLYDATE BETWEEN DateAdd(Day, 28, @.StartDate) And
DateAdd(Day, 41, @.StartDate) Then 'Last Pay Period'
WHEN A.APPLYDATE BETWEEN DateAdd(Day, 00, @.StartDate) And
DateAdd(Day, 55, @.StartDate) Then 'Last 4 Pay Periods'
/* WHEN A.APPLYDATE BETWEEN DateAdd(Day, 00, @.StartDate)
And DateAdd(Day, 13, @.StartDate) Then 4 */
End AS Period
FROM VP_ALLTOTALS AS A, VP_EMPLOYEE AS B, VP_LABORACCOUNT AS C
WHERE A.WFCLABORLEVELNAME1 = C.LABORLEVELNAME1
AND A.WFCLABORLEVELNAME2 = C.LABORLEVELNAME2
AND A.WFCLABORLEVELNAME3 = C.LABORLEVELNAME3
AND A.WFCLABORLEVELNAME4 = C.LABORLEVELNAME4
AND A.EMPLOYEEID = B.EMPLOYEEID
AND B.PayRuleName <> 'All Exempt'
AND A.APPLYDATE Between DateAdd(Day, 0, @.StartDate) And
DateAdd(Day, 55, @.StartDate)
GROUP BY A.PERSONFULLNAME, C.LABORLEVELDSC1, A.APPLYDATE) AS TEST
GROUP BY Plant, Period
ORDER BY Plant, Period DESCI found the problem.
Thanks
Jeff
"Jeff Cichocki" <jeffc@.belgioioso.com> wrote in message
news:OyvRuYWDGHA.2320@.TK2MSFTNGP11.phx.gbl...
>I have the following query that is giving me an error that I can't quite
>figure out. The error I get is Server: Msg 8624, Level 16, State 3, Line 11
>Internal SQL Server error. I think it is related to the sub-query or the
>group by. The sub-query executes successfully if I run it by itself.
> Any Ideas as to what I can try?
> Thanks
> Jeff
>
> Declare @.StartDate VarChar(12)
> SET @.StartDate = '04/07/03'
> SELECT Plant, COUNT(Distinct PERSONFULLNAME) AS EmployeeCount, Period,
> @.StartDate as StartDate
> FROM (SELECT A.PERSONFULLNAME, C.LABORLEVELDSC1 AS PLANT,
> CASE
> WHEN A.APPLYDATE BETWEEN DateAdd(Day, 42, @.StartDate) And
> DateAdd(Day, 55, @.StartDate) Then 'This Pay Period'
> WHEN A.APPLYDATE BETWEEN DateAdd(Day, 28, @.StartDate) And
> DateAdd(Day, 41, @.StartDate) Then 'Last Pay Period'
> WHEN A.APPLYDATE BETWEEN DateAdd(Day, 00, @.StartDate) And
> DateAdd(Day, 55, @.StartDate) Then 'Last 4 Pay Periods'
> /* WHEN A.APPLYDATE BETWEEN DateAdd(Day, 00, @.StartDate)
> And DateAdd(Day, 13, @.StartDate) Then 4 */
> End AS Period
> FROM VP_ALLTOTALS AS A, VP_EMPLOYEE AS B, VP_LABORACCOUNT AS C
> WHERE A.WFCLABORLEVELNAME1 = C.LABORLEVELNAME1
> AND A.WFCLABORLEVELNAME2 = C.LABORLEVELNAME2
> AND A.WFCLABORLEVELNAME3 = C.LABORLEVELNAME3
> AND A.WFCLABORLEVELNAME4 = C.LABORLEVELNAME4
> AND A.EMPLOYEEID = B.EMPLOYEEID
> AND B.PayRuleName <> 'All Exempt'
> AND A.APPLYDATE Between DateAdd(Day, 0, @.StartDate) And
> DateAdd(Day, 55, @.StartDate)
> GROUP BY A.PERSONFULLNAME, C.LABORLEVELDSC1, A.APPLYDATE) AS TEST
> GROUP BY Plant, Period
> ORDER BY Plant, Period DESC
>|||Hi Jeff,
Do you still know what the solution for your problem was? I get this error
also:
Server: Msg 8624, Level 16, State 3, Line 1
According to MSFT i should install the latest SP but that did not help.
Thanks,
STanley
"Jeff Cichocki" wrote:

> I found the problem.
> Thanks
> Jeff
> "Jeff Cichocki" <jeffc@.belgioioso.com> wrote in message
> news:OyvRuYWDGHA.2320@.TK2MSFTNGP11.phx.gbl...
>
>

Query Performance Problem

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]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