Improve SP performance
I have below code ...
UPDATE Sales
SET MappingOrderID = CAST ([dbo].[fRemoveNonNumericCharacters] (ltrim(rtrim(ActualOrderNumber))) AS INT)
FROM Sales
where isnumeric(ActualOrderNumber) = 1
and ActualOrderNumber not like '%.%'
and len(ActualOrderNumber) < 10
and MappingOrderID is null
and ActualOrderNumber is not null
and ltrim(rtrim(ActualOrderNumber)) in (select ltrim(rtrim(OrderID)) from dbo.Orders)
Participating columns:
From Sales: MappingOrderId INT ActualOrderNumber NVARCHAR (10)
From orders: OrderId INT
This is legacy code, I'm a DBA, I don't know why he / she used Trim so much ... the script takes 9 minutes to run and ideally needs to be run often ...
I also get this in the execution plan:
-
CONVERT_IMPLICIT (VARCHAR (12), [DataMart]. [DBO]. [Orders]. [OrderID], 0)
-
CONVERT_IMPLICIT (VARCHAR (20), [Datamart]. [DBO]. [Sales]. [ActualOrderNumber], 0)
-
Type conversion to expression (CONVERT_IMPLICIT (varchar (20), [DataMart]. [Dbo]. [Sales]. [ActualOrderNumber], 0)) may affect "CardinalityEstimate" in query plan selection, type conversion to expression (CONVERT_IMPLICIT (varchar (12), [Datamart]. [Dbo]. [Orders]. [OrderID], 0)) may affect "CardinalityEstimate" in query plan selection
What I feel ...
This update MappingOrderId (int) by casting ActualOrderNumber (varchar) ... It will only update if ActualOrderNumber exists in the Order ...
I am trying several modifications; but varchar ActualOrderNumber has values like 3545427103 and if I try to remove something like:
and len(ActualOrderNumber) < 10
It will break ...
My overall goal is to make this process as efficient as possible; is fixing data in source the only real solution?
I have implemented a lot of suggestions and it came off for a minute. Thank you! But I don't understand what it is:
Earlier, with ugly code, I had:
Now I have MORE reads ... and it takes a few seconds to start up ... how is this possible?
source to share
UPDATE Sales
SET MappingOrderID = CAST (ltrim(rtrim(ActualOrderNumber)) AS INT)
FROM Sales S
inner join dbo.Orders O on ltrim(rtrim(O.OrderID))=ltrim(rtrim(s.ActualOrderNumber))
where isnumeric(ActualOrderNumber) = 1
and ActualOrderNumber not like '%.%'
and len(ActualOrderNumber) < 10
and MappingOrderID is null
Changes
1. Remote function "fRemoveNonNumericCharacters"
- Since you are checking isnumeric in the where clause, there is no need to use this function.
2. Table of sales and orders
- if you use the table of orders in the where clause as you mentioned, then the complete table of orders will be displayed for each row of the sales table.
- To reduce this process, I added join.
3.removed ActualOrderNumeric is not null
- Since you are checking isnumeric in where where, there is no need to use this condition. Since for null, isnumeric will return 0.
source to share