Re: faster way to write tables?

Paul Scott formulated on Δευτέρα :
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!

Now we are getting in to app specific domain in which I have no insite and no opinion. The fact remains that even in this case you have possible conflicts which must be dealed with in a seperate step and avoid using the exception handler as a solution.

regards
Yannis.

--
You talk a great deal about building a better world for your children, but when you are young you can no more envision a world inherited by your children than you can conceive of dying. The society you mold, you mold for yourself.
----Russell Baker-------


.