Query / Design Optimization help
-
I have a situation where i need to optimize the query to perform better. possibly a solution. Here is the situation. I have a table with the following fields(I am putting only those are significant to the scenirio) WorkItems[Table] StartDate EndDate Stage CompletedDate I need to calculate a value named status according to various conditions involving the above parameters. Like If StartDate < getdate() Status = Green etc.. I created a function called CalculateStatus Passing all the above parameters and added one computed colum in the WorkItems table to call this function. Since column is non deterministic i cant add a index to this column. Now when i blindly do a select of status the same is getting calculated very fast The issue is when i use this computed column with aggragate function like 1) SELECT count(Status) FROM WorkItems Or when doing a group by 2) SELECT Count(WorkItemId),Status FROM WorkITems Group BY Status Could you please guid me how i could optimize the situation so that i can make things faster. Thanks in advance for your valuable time Sreehari ARanghat
Thanks Laddie Kindly rate if the answer was helpful
-
I have a situation where i need to optimize the query to perform better. possibly a solution. Here is the situation. I have a table with the following fields(I am putting only those are significant to the scenirio) WorkItems[Table] StartDate EndDate Stage CompletedDate I need to calculate a value named status according to various conditions involving the above parameters. Like If StartDate < getdate() Status = Green etc.. I created a function called CalculateStatus Passing all the above parameters and added one computed colum in the WorkItems table to call this function. Since column is non deterministic i cant add a index to this column. Now when i blindly do a select of status the same is getting calculated very fast The issue is when i use this computed column with aggragate function like 1) SELECT count(Status) FROM WorkItems Or when doing a group by 2) SELECT Count(WorkItemId),Status FROM WorkITems Group BY Status Could you please guid me how i could optimize the situation so that i can make things faster. Thanks in advance for your valuable time Sreehari ARanghat
Thanks Laddie Kindly rate if the answer was helpful
Instead of a computed column, use a normal column and use an after update/insert trigger on the table that calls your function and sets the column value for the row in question. You then also have the advantage of adding an index on this column. :)
Chris Meech I am Canadian. [heard in a local bar] In theory there is no difference between theory and practice. In practice there is. [Yogi Berra]