April 16, 2012 at 2:27 pm
Hi!
I need help with a optimization of my SP.
The issue is this.
Users insert records in Table "Operation", those records describes an accion made to the customers.
this table contain: CustomerId, Date (datetime of action), ActionId, and other fields more.
I need to find the best ActionId, and this is the clasification.
Table GroupActionId
GroupID, Detail, GroupPriority
1, First, 1
2, Second, 2
3, Thirth, 3
Table RankActionId
GroupId, ActionId, Ranking
1, 10, 1
1, 15, 2
1, 20, 3
2, 25, 1
2, 30, 2
2, 35, 3
3, 40, 1
3, 45, 2 .....
First: I need find the best GroupActionId (Min Field GroupPriority), Then, in table RankActionId, i have to search for the minimun ranking of ActionId, associated to MIN of GroupPriority field. With this, I obtain the best ActionId of each customer. Each customer may have the same ActionId in a different space of time. in this case i need obtain the MAX date of the best ActionId.
The final results is an ActionId for each customer, and this ActionId must be the best.
But my updates are not optimal, because I make the update with a Subquery.
Can help me to optimize this code?
Greetings
Fernando
/* Find and Update the Best ActionId Group */
Update BestAction
Set BestGroupPriority = (Select Min(en.GroupPriority)
From BestAction mg Join Operation op On op.CustomerId = mg.CustomerId
Join dbo.RankActionId de On de.ActionId = op.ActionId
Join dbo.GroupActionId en On en.GroupID = de.GroupID
Where mg.CustomerId = mm.CustomerId)
From BestAction mm
/* Find the best Rank, asocciate with best group */
Update BestAction
Set BestRank = (Select min(de.Ranking)
From BestAction mg Join dbo.Operation op On op.CustomerId = mg.CustomerId
Join dbo.RankActionId de On de.ActionId = op.ActionId
Join dbo.GroupActionId en On en.GroupID = de.GroupID
And en.GroupPriority = mg.BestGroupPriority
Where mg.CustomerId = mm.CustomerId)
From BestAction mm
Where Isnull(mm.BestGroupPriority, -1) <> -1
/* Update the best ActionId */
Update BestAction
Set BestActionId = de.ActionId
From BestAction mg Join dbo.GroupActionId en On en.GroupPriority = mg.BestGroupPriority
Join dbo.RankActionId de On en.GroupID = de.GroupID
And de.Ranking = mg.BestRank
Where Isnull(mg.BestGroupPriority, -1) <> -1
/* Update the Date of the Best ActionID */
Update BestAction
Set BestDate = (Select Max(op.Date)
From BestAction mg Join dbo.Operation op On op.CustomerId = mg.CustomerId
And mg.BestActionId = op.ActionId
Where mg.CustomerId = mm.CustomerId)
From BestAction mm
Where Isnull(mm.BestGroupPriority, -1) <> -1
April 16, 2012 at 2:57 pm
Are these updates slow? Not sure if you are just unsure of your approach of if you are having performance problems. You have several where clauses that are not sargable.
Instead of
Where Isnull(mm.BestGroupPriority, -1) <> -1
You could use
Where mm.BestGroupPriority > -1
or mm.BestGroupPriority < -1
This would accomplish the same thing. It will not return NULL or -1 and will still allow an index seek on BestGroupPriority instead of index scan.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 16, 2012 at 3:45 pm
Thanks Sean!
I made the change
Well, i'm in testing stage, and I have a table with 10 million of records. In production, the table "Operation" have 20 million aprox. and I not sure if the code will be fast in producton stage.
Thanks in advance
Greetings
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply