performance difference between queries

Question

Post reply

performance difference between queries

Dinakar Nethi-176633

SSCarpal Tunnel

Points: 4689
More actions
November 22, 2006 at 12:44 pm

#117809

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.
******************

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 1

Any differences in the execution plan?

Any convert_implicit in the 2nd plan?

Tried to clear the proc cache?

Dinakar Nethi-176633 SSCarpal Tunnel Points: 4689 More actions · Answer 2

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.
******************

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 3

Can't think of anything else at the moment... not without the plan anyways.

Updated the stats?

PW-201837 SSC-Insane Points: 20805 More actions · Answer 4

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.

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 5

Lol, that too... that's why I ask for a query plan for perf troubleshooting .

Dinakar Nethi-176633 SSCarpal Tunnel Points: 4689 More actions · Answer 6

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.
******************