SELECT statement slow only inside stored procedure

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

SELECT statement slow only inside stored procedure

Author

Topic

profquail
Starting Member

5 Posts

Posted - 2007-12-07 : 23:02:58

I have a relatively simple SELECT statement that self-joins a table on a date column given a difference in days between the dates. This query works quickly (about 20 seconds) when I copy it into a query window and put all the parameters in manually; however, once I add it into a function to automate it (generating data for different time spans), the query just sits there and grinds away and never returns. I have commented different parts of the procedure out and confirmed that it is indeed the SELECT statement that is taking forever. I looked into the 'parameter sniffing' fix, but that didn't change anything.

If anyone has any ideas, I would be very grateful to hear them. I've enclosed the function code below (and bolded the select statement in question) in case you want to inspect it.

ALTER FUNCTION [dbo].[CalcCloseVolumeCorrelation]
(
@Ticker varchar(5),
@DayRange int
)
RETURNS @VolumeCorrelationTable TABLE
(
DayDiff int,
Correlation float
)
AS
BEGIN
DECLARE @DiffCounter int
SET @DiffCounter = (-1 * @DayRange)

DECLARE @TempDataTable table
(
[DateClose] datetime,
[DateVolume] datetime,
[Close] float,
[Volume] int,
[CloseVolProduct] float
)

DECLARE @CloseMean float
DECLARE @VolumeMean float
DECLARE @CloseStdDev float
DECLARE @VolumeStdDev float
DECLARE @CloseVolProductMean float
DECLARE @Covariance float
DECLARE @Correlation float

WHILE (@DiffCounter <= @DayRange)
BEGIN
INSERT @TempDataTable
SELECT a.[Date] as [DateClose], b.[Date] as [DateVolume], a.[Close], b.[Volume], (a.[Close] * b.[Volume]) as [CloseVolProduct]
FROM [DataSmall] as a INNER JOIN [DataSmall] as b
ON a.Ticker = b.Ticker AND dbo.PrevCloseDateSmall(a.[Date], @Ticker, @DayRange) = b.[Date]
WHERE a.Ticker = @Ticker

SET @CloseMean = (SELECT AVG([Close]) FROM @TempDataTable)
SET @VolumeMean = (SELECT AVG([Volume]) FROM @TempDataTable)
SET @CloseStdDev = (SELECT STDEV([Close]) FROM @TempDataTable)
SET @VolumeStdDev = (SELECT STDEV([Volume]) FROM @TempDataTable)
SET @CloseVolProductMean = (SELECT AVG([CloseVolProduct]) FROM @TempDataTable)
SET @Covariance = (@CloseVolProductMean - (@CloseMean * @VolumeMean))
SET @Correlation = (@Covariance / (@CloseMean * @VolumeMean))

INSERT INTO @VolumeCorrelationTable(DayDiff, Correlation) VALUES (@DayRange, @Correlation)

DELETE FROM @TempDataTable

SET @DiffCounter = @DiffCounter + 1
END

RETURN
END

profquail
Starting Member

5 Posts

Posted - 2007-12-07 : 23:05:45

EDIT: The contents of the while loop should be this instead (I was fooling around with it to try and get it working...)

WHILE (@DiffCounter <= @DayRange)
BEGIN
INSERT @TempDataTable
SELECT a.[Date] as [DateClose], b.[Date] as [DateVolume], a.[Close], b.[Volume], (a.[Close] * b.[Volume]) as [CloseVolProduct]
FROM [DataSmall] as a INNER JOIN [DataSmall] as b
ON a.Ticker = b.Ticker AND dbo.PrevCloseDateSmall(a.[Date], @Ticker, @DiffCounter) = b.[Date]
WHERE a.Ticker = @Ticker

SET @CloseMean = (SELECT AVG([Close]) FROM @TempDataTable)
SET @VolumeMean = (SELECT AVG([Volume]) FROM @TempDataTable)
SET @CloseStdDev = (SELECT STDEV([Close]) FROM @TempDataTable)
SET @VolumeStdDev = (SELECT STDEV([Volume]) FROM @TempDataTable)
SET @CloseVolProductMean = (SELECT AVG([CloseVolProduct]) FROM @TempDataTable)
SET @Covariance = (@CloseVolProductMean - (@CloseMean * @VolumeMean))
SET @Correlation = (@Covariance / (@CloseMean * @VolumeMean))

INSERT INTO @VolumeCorrelationTable(DayDiff, Correlation) VALUES (@DiffCounter, @Correlation)

DELETE FROM @TempDataTable

SET @DiffCounter = @DiffCounter + 1
END

georgev
Posting Yak Master

122 Posts

Posted - 2007-12-08 : 15:03:11

Your first insert statement should look a bit more like this
[CODE]
INSERT INTO @TempDataTable (dateClose, dateVolume, close, volume, closeVolProduct)
SELECT a.Date
, b.Date
, a.Close
, b.Volume
,(a.Close * b.Volume)
FROM DataSmall As a
INNER
JOIN DataSmall As b
ON a.Ticker = b.Ticker
AND dbo.PrevCloseDateSmall(a.[Date], @Ticker, @DayRange) = b.[Date]
WHERE a.Ticker = @Ticker
[/CODE]
Also, the function you're using... Wat is it, wat does it do and why is it on the join?
Eithe way, the fuction will kill your performance (index usage).

George
<3Engaged!

profquail
Starting Member

5 Posts

Posted - 2007-12-08 : 15:59:32

The join self-joins the table [DataSmall] to itself with the data on two different dates. For example, dbo.PrevCloseDateSmall(N'2007-08-20 00:00:00.000', 'MSFT', 1) returns '2007-08-17 00:00:00.000' since that was the previous trading day. I tried to avoid having a function call there since it would be called so many times, but even so, when substituting values into the parameters in the SELECT statement and running it as a regular query, the query completes in a very short time. My problem occurs when I package the whole thing up inside a function or stored procedure in order to iterate over a number of values -- it just sits there and grinds on the select statement until I stop it.

I read some other material today that said something about 'explain plan' and the sql server engine not being able to optimize the query correctly. Perhaps that is the case?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources