Fine-tune SP
-
Hi All, I am trying to optimize a SP which involves cursor for the following DB structure and scenario. DB Structure: ------------- --Read it as TableName : (columnname(s)) TableBuyer : (BuyerName,BuyerType) -- BuyerName is UniqueID -- BuyerType can hold the value 1 or 2 TableCar : (BuyerName,Warranty,ChassisNumber) -- BuyerName UniqueID -- Warranty takes "DEF" as default value TableCarDetails : (ChassisNumber, BuyerName) Scenario: --------- - If BuyerType is 2 then 1) Update warranty column with the value which is equal to corresponding BuyerName(UniqueID) suffixed with / (forward slash) 2) A Corresponding entry has to be made (Insert Row) in TableCarDetails with that chassis number and BuyerName Existing Flow ------------- The sp involves a temp table which stores the BuyerName where the BuyerType is 2 Based on the BuyerName in temp table all the updations are done thro cursor. Can anyone throw somelight on how efficient I can tune this sp??
-
Hi All, I am trying to optimize a SP which involves cursor for the following DB structure and scenario. DB Structure: ------------- --Read it as TableName : (columnname(s)) TableBuyer : (BuyerName,BuyerType) -- BuyerName is UniqueID -- BuyerType can hold the value 1 or 2 TableCar : (BuyerName,Warranty,ChassisNumber) -- BuyerName UniqueID -- Warranty takes "DEF" as default value TableCarDetails : (ChassisNumber, BuyerName) Scenario: --------- - If BuyerType is 2 then 1) Update warranty column with the value which is equal to corresponding BuyerName(UniqueID) suffixed with / (forward slash) 2) A Corresponding entry has to be made (Insert Row) in TableCarDetails with that chassis number and BuyerName Existing Flow ------------- The sp involves a temp table which stores the BuyerName where the BuyerType is 2 Based on the BuyerName in temp table all the updations are done thro cursor. Can anyone throw somelight on how efficient I can tune this sp??
SwatCats wrote:
Can anyone throw somelight on how efficient I can tune this sp??
Figure out a way of doing the same without using cursors. Relational databases are very good at doing set based operations (i.e. operations on large numbers of rows at the same time). The are not very good at working on one row at a time. UPDATE statments can operate on many rows at once and I would imaging that the updates you want can be completed on large numbers of rows in one operation.
Upcoming events: * Glasgow: Mock Objects, SQL Server CLR Integration, Reporting Services, db4o, Dependency Injection with Spring ... "I wouldn't say boo to a goose. I'm not a coward, I just realise that it would be largely pointless." My website