| Temp tbl query in stored proc creating performance issues-any thoughts? [message #371727] |
Wed, 29 November 2000 16:29 |
reverend
Messages: 1
Registered: November 2000
|
Junior Member |
|
|
I am running the following sp in a sql7 db:
CREATE PROCEDURE lesson
(
@DATE1 smalldatetime,
@DATE2 smalldatetime
)
AS
SET NOCOUNT ON
BEGIN
UPDATE STATISTICS contestants
CREATE TABLE #rEmail
(
email VARCHAR(50),
regtime smalldatetime
)
INSERT #rEmail
SELECT email, regtime
FROM registrations
WHERE regtime >= @DATE1 AND regtime <= @DATE2 AND email NOT IN (SELECT email from registrations where regtime < @DATE1)
SELECT DISTINCT(email) FROM #rEmail WHERE email IN (SELECT email FROM contestants WHERE lesson = 'y')
Drop table #rEmail
END
When run via query analyzer the execution time varies from < 1 second to over three minutes. (record set is < 2000, entire db holds < 35,000 records) When called from an asp page it works once or twice and then the timeout errors begin. Five minutes later it will work fine again. Very frustrating, any thoughts would be appreciated. Thank you in advance.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
