performance difference between queries
-
Hi Guys
Here's a scenario:
(1)
--create the table
CREATE table #TextDataArchive (TextID bigint PRIMARY KEY)
INSERT #TextDataArchive
SELECT dp.PhraseTextID --as TextID
FROM DocPhrase dp
JOIN Document as d on d.DocID = dp.DocID
WHERE d.FileID = @ArchiveFileID
and dp.PhraseTextID is not NULL
DELETE td
FROM TextData td
JOIN #TextDataArchive tda on tda.TextID = td.TextID
-----------------------------------------------------------
(2)
DELETE td
FROM TextData td
Where td.textid in ( SELECT dp.PhraseTextID --as TextID
FROM DocPhrase dp
JOIN Document as d on d.DocID = dp.DocID
WHERE d.FileID = @ArchiveFileID
and dp.PhraseTextID is not NULL )
--------------------------------------
(1) is timing out (takes about 2 minutes to complete).
(2) takes < 1s to finish executing. Can someone please explain the difference between the execution times.. Thanks,
Dinakar******************
Dinakar Nethi
Life is short. Enjoy it.
****************** -
Any differences in the execution plan?
Any convert_implicit in the 2nd plan?
Tried to clear the proc cache?
-
I cleared the cache, recompiled the proc too. I didnt check the execution plan but I had the profiler on and it kept getting stuck at this DELETE.
******************
Dinakar Nethi
Life is short. Enjoy it.
****************** -
Can't think of anything else at the moment... not without the plan anyways.
Updated the stats?
-
Creating a temp table inside a proc causing a recompile ?
Cardinality ? Is DocPhrase in a 1 to 1 relationship with the table you're deleting from ? If not, JOIN is not equivalent to IN (subquery) in terms of query plan.
-
Lol, that too... that's why I ask for a query plan for perf troubleshooting
. -
We have had the stored proc for years without issues. Suddenly it started going wild. I will check for the query plans. I did think about recompilations but have to brush up on the scenarios under which it gets recompiled.
******************
Dinakar Nethi
Life is short. Enjoy it.
******************
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply