Re: faster way to write tables?

On Mon, 25 Feb 2008 08:06:06 -0000, yannis <none@xxxxxxxxxx> wrote:

Well that is an example of what you shoold never do in my opinion.

("delete from perm where exists... Insert into perm where not exists")

Well, I did say it was "probably much *too* clever" :)


I know for a fact that it will be faster to execute a

Delete * from <temp> where exists(select 1 from <Perm> where <TEmp>.id = <Perm>.ID

That would certainly be faster - but deleting (& thus not updating) those duplicate-keyed Temp rows would not give the same results unless all the *data* fields in the new rows in Temp were identical to those in the old rows in Perm.

And in the case when I'm inserting thousands of calculation results, the data in any duplicate-keyed <AssetId,CalcId> result rows has a high probability of being different - or else they would not have been recalculated!

--
Paul Scott
Information Management Systems
Macclesfield, UK.
.

Quantcast