July 8, 2002 at 8:28 am
I have a stored procedure that takes 38 seconds during an Application stress test BUT when I run it within Query Analyzer it only takes 1-2 seconds.
The below statement is whats taking so long.
Any ideas???
-- SPEVReport
SELECT
#TmpEVListTb.EventID,
#TmpEVListTb.EventName
INTO #TmpEV
FROM #TmpEVListTb
WHERE #TmpEVListTb.EventId In (SELECT TMP2.EventId
FROM #TmpEVListTb TMP2
WHERE TMP2.RowId >= @StartRecord And TMP2.RowId < @EndRecord
)
/* Create the final temp table for reporting */
July 8, 2002 at 8:37 am
Running with same permissions?
Andy
July 8, 2002 at 8:45 am
No. For the Application stress test the user has only EXECUTE rights.
In Query Anal., I run as SA.
July 8, 2002 at 8:57 am
Maybe you're seeing some contention in tempdb when you test? Tried doing an explicit create table (or use a perm table) instead of select into?
Andy
July 8, 2002 at 9:02 am
how can I capture contection in TEMPDB?
July 8, 2002 at 9:24 am
Wow... that's a great example of bad code. It should be illegal to use temp tables like that. For starters, clean it up a bit... If it's still slow figure out a way to write it without using any temporary storage.
This will clean it up a bit...
SELECT
#TmpEVListTb.EventID,
#TmpEVListTb.EventName
INTO #TmpEV
FROM #TmpEVListTb
WHERE #TmpEVListTb.RowId >= @StartRecord And #TmpEVListTb.RowId < @EndRecord
July 8, 2002 at 9:52 am
Should show up as normal blocking. For a 30 second delay you should be able to catch using just sp_who.
Andy
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply