By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,856 Members | 2,149 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,856 IT Pros & Developers. It's quick & easy.

SQL tuneing

P: 90
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
Share this Question
Share on Google+
3 Replies


Delerna
Expert 100+
P: 1,134
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
Expert 100+
P: 1,134
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
Expert 2.5K+
P: 2,878
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.