468,316 Members | 2,106 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,316 developers. It's quick & easy.

SQL tuneing

How could I increase the performance of sql server 2000.
For example if write a query for update as
Expand|Select|Wrap|Line Numbers
  1. update tablename set column1=newcols1,column2=newcols2,column3=newcols 3 where keyvalue=somevalue
  2.  
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
Sep 19 '08 #1
3 924
Delerna
1,134 Expert 1GB
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.
Sep 19 '08 #2
Delerna
1,134 Expert 1GB
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.
Sep 21 '08 #3
ck9663
2,878 Expert 2GB
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
Sep 22 '08 #4

Post your reply

Sign in to post your reply or Sign up for a free account.

By using this site, you agree to our Privacy Policy and Terms of Use.