SQL tuneing

[raghulvarma]

How could I increase the performance of sql server 2000.
For example if write a query for update as

update tablename set column1=newcols1,column2=newcols2,column3=newcols 3 where keyvalue=somevalue
 

This would work,but if the user only updates only one Column for example column2 what is the use of sending other columns to update.
I do not want a query like updating every column in different query only one query that does my work perfectly.ie taking only the columns which has been updated and update that in the table.

Thanks in advance
Raghul

[Delerna]

you would need to use parameters and if statements to make the decision of which fields to update based on the parameters passed.
The overhead of making those decisions would probably negate any performance increase.

or

In the front end you could update each field as it changes via a change event.
But then, if you actually need to change more than one field then that would result in multiple update calls which would mean another loss in performance.

or

you could make the decision of which fields need updating in the front end and build the update query there. But again I wonder how much you will actually save.

If you are only updating 1 record then reducing the number of fields to update would only result in a miniscule increase in performance anyway. You could even make performance worse. You need to consider this carefully. Is the performance gain (if any) worth the cost of implementation?

Run some performance tests on any ideas you try.

[Delerna]

Has anyone done, or know of, any tests to measure the differnce between updating 1 Field of a record as opposed to updating all the fields of that record?

My guess is that it would be insignificant, but I could be wrong.
If you had a few large varchar fields, things might change.

If you were doing a mass update on many records the difference becomes significant, but in my experience, those tasks tend to be a 'one off' that you write a query for that specific task and then throw it away.

[ck9663]

Imagine, if you have 70 columns and you have a trigger on UPDATE, and you updated one column at a time. If not properly designed, that TRIGGER will fire 70x. Not to mention if any of those columns have CONSTRAINTS and REFERENTIAL INTEGRITY implementations.

-- CK