November 7, 2001 at 1:50 am
We have a proc which selects row from a table and returns duplicate rows. Now we're using a temp-table to get rid of duplicates befor returning data. But now it seems to make the stored procedure to recompile to often. I tried to use a table variable instead of a temporary table, but it doesn't seem to work. I can give you the example of the deletion of the duplicate rows(The temp table is created in the very beginning of the sp) that works fine except recompilations:
First we fill the table, then
DELETE FROM #TMPTB
WHERE EXISTS
(
SELECT ID
FROM #TMPTB DUP
WHERE
DUP.COL1 = #TMPTB.COL1 AND
DUP.COL2 = #TMPTB.COL2 AND
DUP.ID < #TMPTB.ID
)
If I do the same with a table variable instead like:
DELETE FROM @TMPTB
WHERE EXISTS
(
SELECT ID
FROM @TMPTB DUP
WHERE
DUP.COL1 = @TMPTB.COL1 AND
DUP.COL2 = @TMPTB.COL2 AND
DUP.ID < @TMPTB.ID
)
I get an error saying that the variable @TMPTB should be declared.
Can anyone tell me why, and perhaps give me a solution.
In this case, I'm not happy to the fact that we must have duplicates at all, and that the procedure recompiles at execution.
AW
AW
November 7, 2001 at 3:15 am
I forgot to mention, Of course I declared the table variable in the beginning of the sp like :
Declare @TmpTb table (id int, col1 char(5), col2 char(5))
This is only an example, the name and length of the columns is different from this in real life..
AW
AW
November 7, 2001 at 5:15 am
Why do you care if it recompiles? Do you execute this a lot?
Andy
Andy
November 7, 2001 at 5:40 am
This sp executes a lot. It's used within a public travel site with about 12 thousand sessions a day. This sp executes more than once a minute. The time for recompilation is included in the execution time almost every time it's executed.
AW
AW
November 7, 2001 at 10:23 am
Change to a real table. Make the same structure, but include a column for SPID.
At the beginning, detele from mytabel where Spid = @myspid
then insert your values with the current spid. Qualify all selects using the spid.
delete at the end. The beginning delete is protection in case a session explodes.
BTW, be sure you index on the spid column along with other appropriate columns.
Steve Jones
November 7, 2001 at 11:48 am
To get round the error
DELETE d
from @TMPTB d
WHERE EXISTS
(
SELECT ID
FROM @TMPTB DUP
WHERE
DUP.COL1 = d.COL1 AND
DUP.COL2 = d.COL2 AND
DUP.ID < d.ID
)
It is caused by the alias aliasing the table rather than creating a copy (I think).
You could also
DELETE d
from @TMPTB d, @TMPTB dup
where DUP.COL1 = d.COL1 AND
DUP.COL2 = d.COL2 AND
DUP.ID < d.ID
Cursors never.
DTS - only when needed and never to control.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply