Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
sachinsqldb
Starting Member
8 Posts |
 Posted - 2006-03-28 : 07:01:49
|
| Hi Gurus,I am stuck on a legacy code project based on SQL Server 2000. The issue is that I have a table which itself requires a hell lot of time for select query (It has more than 10 lakh entries and still growing). I have implemented a trigger on the table that does some calculations on Update and insert and create a column through some formula expression. The trigger has made the already bogged down table slower than before. Is there a better way of updating the table so that the updation time taken by the trigger on each update is looked into.Happy coding,Sachin |
|
|
sachinsqldb
Starting Member
8 Posts |
Posted - 2006-03-28 : 07:16:54
|
| Hi Gurrus,Just to be more informative about my issue, just in case some of the gurus would like some more light to be shed on the problem, My table consists of say 5 columns in allS1 S2 S3 S4 S5The S5 column is the one which should have value in each row calculated on the basis of the calculations made on the columns (S1+S2-S3)*S4Presently this is acheived through the use of triggers updating each row on each updation (...ooof slowing the table a lot). Any better ideas. I have another option of running a cursor later whenever I have the need to use S5 somewhere in my application. But again I think this is not an elegant solution. Any better ideas?Happy Coding,Sachin |
 |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2006-03-28 : 08:03:52
|
| Create a computed column to give the value.Access the table via a view which calculates the value.Why do you want to do this anyway - why not calculate it when you read the rows.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
sachinsqldb
Starting Member
8 Posts |
Posted - 2006-03-29 : 23:01:08
|
| Hi nr,Thanks for the reply. True as it is that I could have always used a computed column in this scenario but it is more true that in a team when you are being led by the policies and rules set by your seniors and moreover when the code is incomprehensible legacy code. Then also if I have to use a computed column I would really appreciate if you could give me an example on how I can acheive the desired result using a view and computed column. Once again thanks for the help,Happy Codng,Sachin |
|
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2006-03-30 : 07:38:08
|
| >>how I can acheive the desired result using a view and computed column.gocreate view myView asselect s1, s2, s3, s4, (S1+S2-S3)*S4 as s5from myTablegoBe One with the OptimizerTG |
|
|
|
blindman
Master Smack Fu Yak Hacker
2365 Posts |
Posted - 2006-03-30 : 09:08:27
|
| I'm surprised this simple trigger is having an impact on performance. How often is this data updated, and how many records at a time? |
|
|
|
blindman
Master Smack Fu Yak Hacker
2365 Posts |
Posted - 2006-03-30 : 09:08:55
|
| ...and please, tell me you didn't use a cursor in your trigger... |
|
|
|
rockmoose
SQL Natt Alfen
3279 Posts |
Posted - 2006-03-30 : 14:53:33
|
quote:
Originally posted by blindman
...and please, tell me you didn't use a cursor in your trigger...
The whole table in one set-based operation maybe?  every time...alter table xxx add S5 as ((S1+S2-S3)*S4)rockmoose |
|
|
|
|
|
|
|
|
Triggers....Oh Triggers