Basic "UPDATE" or "DELETE then INSERT" query help

[petebristol]

Dear All,

Please can you help/advise on the best way forward?

Assume I have a single table, containing 22 fields - 1 * varchar(10) for Productno (key field), 1 * varchar(10) for location (Unique for product, but potentially duplicated across products) and 20 * numeric(18,2) containing various numerical ratios, figures etc.

There are approxiamtely 300 products in the table each having up to 20 locations i.e max of 6000 records in total.

The database table is being updated in real time by about 5 users each updating at most 10 products a day each (using an linked Excel spreadsheet). There is almost a zero % chance that any two users will attempt to update the SAME product at the SAME time.

Of the following two scenarios, Which is the best way to update this table? When I say *best*, this might be the "fastest" way if it makes a significant difference or it might be the "safest" if again there is a big difference in realibility.

1. Using an UPDATE table query on only those fields that have changed (this will be between 50%-75% of them most of the time)i.e.

UPDATE table
SET field3 = new value,
SET field4 = new value
...
WHERE product = 'xxx' and location = 'yyy'

for each of the locations

or

2. Dont bother to write each change seperately but get rid of all records and then insert new ones i.e.

DELETE FROM table //Get rid of them all each time
WHERE product = 'xxx'

Followed by

INSERT INTO table VALUES (.....) //Insert all new values even though some may not have changed.

I have tried both scenarios, each appearing to work equally as well from a performance point of view and neither obviously putting any kind of strain on the SQL2000 database so I am keen to do what is "best".

Many thanks for any help offered.


Regards,

Pete

 

[ca8msm]

Try both methods with the execution plan on and see which performs better. You should find that the update is quicker due to less reads/writes.


-------------------------------------------------------

Mark,
[URL unfurl="true"]http://aspnetlibrary.com[/url]
[URL unfurl="true"]http://mdssolutions.co.uk[/url] - Delivering professional ASP.NET solutions
[URL unfurl="true"]http://weblogs.asp.net/marksmith[/url]

 

[johnherman]

Generally speaking, SELECTS cost 1 unit, INSERTS and DELETES cost 2 units each, and UPDATES cost 3 units.
thus 1 UPDATE is usually more efficient than a DELETE and INSERT.

As for what is best, it depends on your user community. If your users don't mind having the dates being inaccurate (create date, update date, et), the delete (or TRUNCATE if posssible) and reload will probably be better.

-------------------------
The trouble with doing something right the first time is that nobody appreciates how difficult it was - Steven Wright

 

[petebristol]

Thank you for your help.

I think you are both more-or-less saying that there is no *definative* best way to do it but probably an update rather than a delete/insert would be more efficient.

Thanks again

Pete

 

[petebristol]

Apologies, I've just remembered a question that I meant to ask first time round to do with errors.

Lets say that the database is updated from a stored procedured called from the Excel App, in the event of an error if the sp only contains UPDATE presumably nothing gets committed? But what about if it contained a DELETE then an INSERT and the INSERT part fails for some reson (duplicate key) - does the DELETE part stay or does the whole sp fail?

Thanks in anticipation


Pete