Hi,

I am working on an application which will have a db for appointment management.
The application if defined to be compatabile with any db(SQL Server,mySQl,postgres)
Presently in the design stages what we are most concerned about is efficiency,and soem of us were had experienced slowness with ORacle daatabases doign updates on large databases.

So instead of and UPDATE would a DELETE and INSERT be more efficent???

Now the problems comes when we have auto indexed records.The solution some of us came up with was to instead have a meta table which would store the lookup id of the autonumbered field.

However,I would still be intersted in knowign if this idea of using DELETE+INSERT for an UPDATE would increase efficiency ??? Has anyone come across this issue before??

I would be very surprised if a DELETE/INSERT combo query were better than a single UPDATE query. When you DELETE and INSERT, the db system has to deal with index maintenance where as an UPDATE just updates data (granted, if you update an indexed field then the db still needs to handle indexes).

When you speak of slowness with oracle, what kind of updates are you referring to? An update on a single record? Or an update on multiple records?

An update of a data row in SQL Server is sometimes the equivalent of a delete and insert operation. But, if you don't change the clustered index key, and you don't change the size of any variable length data (e.g. varchar columns), then the update can occur in place.

If the clustered index key does not change in the update, then no indexes need to be changed (unless affected by the update, of course), regardless of whether the update was in place or not.

If the update cannot be done in place, then the row may still be stored in its current page, if there is enough room (see Fill Factor).

Doing your own delete and add would be way slower.

If you are making design decisions based on your (mis)conceptions of the way SQL Server handles its internals, or based on the way you know or think it is done in other products, I strongly suggest you do yourself a favor and get a copy of the book "Inside Microsoft SQL Server 2000", by Kalen Delaney. It's an excellent book and it discusses, sometimes in excruciating detail, the internals of SQL Server and how it works.


Jeff Mason
Custom Apps, Inc.
www.custom-apps.com