Posted by Hugo Kornelis on 02/17/06 00:25

On 16 Feb 2006 12:54:19 -0800, ibiza wrote:

>Hi Hugo,
>
>thank you very much for your excellent reply.
>Is there a big performance difference between the two methods? I guess
>the only difference is that the identity numbers increment a lot
>quicker with the first method of deleting a batch then reinserting a
>batch, even if only a few records differ.

Hi ibiza,

Why would you even want to have an identity on the linking table
FolioTitles? Are there many other tables that refer to rows in this
table? I don't think so, since that would make deleting and re-inserting
rows a very bad idea!

If not, then stick to using only the natural key (FolioID + TitreID) and
leave the identity column out.

>
>If I have to run this kind of update quite frequently (once/twice a
>day, with thousands of records), what method would you suggest me?

If performance matters, than you should test both versions on your
hardware, with your data. That's the only way to get relevant data.

(My recommended test procedure is: clear cache; run one method a few
times, then clear cache again and run second method the same number of
times. Do thin on a test system that is as much as possible like your
production system. Compare average execution times).

--
Hugo Kornelis, SQL Server MVP

[Back to original message]