• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 411
  • Last Modified:

Tune this stored procedure ( ETL)

Hi

I would like to tune this query here.

About this Query: Used to populate the simplified production tracking table based on production tacking table based on the STATUS of the product. Production tracking table is populated by application. Here, I Used a flag field called " Simplified"  in the production tracking table to distinguish the previous records and new records.

The main idea here is to simplify multiple records( in  production tracking table) to single record (in simplified production tracking)

Notes: When STATUS(=1,2......6  used in the query below)
1      Start      Used when the employee begins working on a product
2      Working      Used when the employee stop working on the product but still not completed
3      Finish      Used when the employee completes the work for a product on a station
4      Rework (Started)      Used when the product is reworked on a station
5      Scrap      Used when the product becomes scrap
6      Rework (Finished)      Used when the product is finished rework on a station
7      Rework (Detected)      Used when the product is found to need a rework by other station
8      Start (Rework Detected)      Used when the product was started and no finish because a rework was found


=========================================================================
insert      into SimplifiedProductionTracking
        (OrderNumber, SerialNumber, CatalogNumber, WorkCenter, Status, StartDate, FinishDate, Employees, StationID, FinishShiftID, TimeElapsedSeconds, IDStart, IDEnd)
        (select      SN.OrderID,
                        A.SerialNumber,
                        SA.Catalog_Number,
                        --S.WorkCenter,
                        SMWC.WorkCenter,
                        B.Status,
                        A.DateRegistered,
                        B.Dateregistered,
                        A.Employee + Coalesce('','' + nullif(B.Employee, A.Employee), '''') as Employees,
                        A.StationID,
                        B.shiftID,
                        datediff(ss, A.DateRegistered, B.DateRegistered) as Actualtime,
                        A.ProductTrackID,
                        B.ProductTrackID
from      ProductionTracking as A  with (nolock)
        inner join
        ProductionTracking as B  with (nolock)
        on A.SerialNumber = B.SerialNumber and A.StationID = B.StationID
           and ((A.Status = 1 and B.Status = 3) or (A.Status = 4 and B.Status = 6)
                  or(A.Status =8 and B.Status =7))
            inner join SerialNumbers SN with (nolock)
            on A.SerialNumber = SN.SerialNumber
            inner join SapOrders SA with (nolock)
            on SN.OrderID = SA.Order_Number
            inner join Station S with (nolock)
            on A.StationID = S.StationId
            inner join IdealCycleTimes ICT with (nolock)
            on ICT.Order_Number = SN.OrderID
            inner join SapMultipleWorkCenters SMWC with (nolock)
            on SMWC.StationID = S.StationID and SMWC.WorkCenter= ICT.Work_Center
where      A.Simplified is null
         and B.Simplified is null)

update      ProductionTracking
set      simplified = 1
where      simplified is null
and ProductTrackID  in (
select IDEnd from SimplifiedProductionTracking)

update      ProductionTracking
set      simplified = 1
where      simplified is null
and ProductTrackID in (
select IDStart from SimplifiedProductionTracking)
END
===================================================================
I think there are ways to tune this query. Instead of using IN (in the UPDATE Statement). Can someone come up with your suggestions. This is the ETL job query.Now this is taking a lot of time. Let me know any suggestions on this.

Let me know which part of query can be optimized and any solution for better performance of this query.

I was trying to optimize this  query but couldn't come up to any solution, really need some help here.
0
srionline2k6
Asked:
srionline2k6
  • 5
  • 3
2 Solutions
 
kamindaCommented:
This part Should be in the where clause not in the INNER JOIN

((A.Status = 1 and B.Status = 3) or (A.Status = 4 and B.Status = 6)
                  or(A.Status =8 and B.Status =7))


For Update statements you can use an INNER JOIN instead of IN but that doesnt make any difference as IN is the best way for this. If all you need is to check for matching rows in the other table but don’t need any columns from that table, IN is the best way as in your case. If you do need columns from the second table, use Inner Join.

Also look at the possiblity of creating indexes for Predicates in the query.

Please let me know if there any improvement


0
 
srionline2k6Author Commented:
Iam doing a self join on Production tracking table based on the condition(which forms pairs of records)

((A.Status = 1 and B.Status = 3) or (A.Status = 4 and B.Status = 6)
                  or(A.Status =8 and B.Status =7))

can you be specific when you mention the changes( using the above condition in WHERE clause ) if possible could you rewrite the query for me.

As you mentioned can you let me know where can i use indexes. Your inputs are appreciated.
0
 
kamindaCommented:
Here is the changed query,

For Index recommendations I have to look at your execution plan. You can anyway use Database Enging Tunning Advisor get recommendations for Indexes as well. Good tutorial here

http://msdn.microsoft.com/en-us/library/ms166575.aspx

By just looking at your query I gan guess the having indexes on simplified of ProductionTracking table would be helpful. and other columns used in joins also needed to be indexed but just only by comparing the cost in the execution plan. Anyway I still dont get it why you join the same table on same keys.
insert      into SimplifiedProductionTracking
        (OrderNumber, SerialNumber, CatalogNumber, WorkCenter, Status, StartDate, FinishDate, Employees, StationID, FinishShiftID, TimeElapsedSeconds, IDStart, IDEnd)
        (select      SN.OrderID,
                        A.SerialNumber,
                        SA.Catalog_Number,
                        --S.WorkCenter,
                        SMWC.WorkCenter,
                        B.Status,
                        A.DateRegistered,
                        B.Dateregistered,
                        A.Employee + Coalesce('','' + nullif(B.Employee, A.Employee), '''') as Employees,
                        A.StationID,
                        B.shiftID,
                        datediff(ss, A.DateRegistered, B.DateRegistered) as Actualtime,
                        A.ProductTrackID,
                        B.ProductTrackID
from      ProductionTracking as A  with (nolock)
        inner join
        ProductionTracking as B  with (nolock)
        on A.SerialNumber = B.SerialNumber and A.StationID = B.StationID
            inner join SerialNumbers SN with (nolock)
            on A.SerialNumber = SN.SerialNumber
            inner join SapOrders SA with (nolock)
            on SN.OrderID = SA.Order_Number
            inner join Station S with (nolock)
            on A.StationID = S.StationId
            inner join IdealCycleTimes ICT with (nolock)
            on ICT.Order_Number = SN.OrderID
            inner join SapMultipleWorkCenters SMWC with (nolock)
            on SMWC.StationID = S.StationID and SMWC.WorkCenter= ICT.Work_Center
where      A.Simplified is null
         and B.Simplified is null
 and ((A.Status = 1 and B.Status = 3) or (A.Status = 4 and B.Status = 6)
                  or(A.Status =8 and B.Status =7))

)

Open in new window

0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
kamindaCommented:
Also is there any reason for these two joins to be there

inner join Station S with (nolock)
            on A.StationID = S.StationId
            inner join IdealCycleTimes ICT with (nolock)
            on ICT.Order_Number = SN.OrderID

Looks like never being used unlyess you want to make sure there is a related record.
0
 
srionline2k6Author Commented:
Thanks for changing the query. I would like know what is the difference in changing the condition (including it  in WHERE clause) would it result in same output as the main query.

condition:
 ((A.Status = 1 and B.Status = 3) or (A.Status = 4 and B.Status = 6)
                  or(A.Status =8 and B.Status =7))

And regarding the indexes I shall follow the above weblink.

0
 
kamindaCommented:
In the INNER JOIN ON conditions you should only use conditions which helps to join two tables. In your code these are not related with the join as it does not evaluate any values in two tables together. There should be no difference to the result but would help the query optimizer to have a optimized plan than the current one.
0
 
srionline2k6Author Commented:
I checked both queries with for execution plan using
set showplan_all  on
go

I see that both the old and new queries return the same EstimateIO and EstimateCPU (191.2462544,16.71949239). Can you guide me on this. I see that changing this does not improve any performance.Guide me if i am wrong on this.
0
 
kamindaCommented:
Try and compare the actual execution times and IO using

set statistics IO on
set statistics TIME on

also agian I am asking

 is there any reason for these two joins to be there

inner join Station S with (nolock)
            on A.StationID = S.StationId
            inner join IdealCycleTimes ICT with (nolock)
            on ICT.Order_Number = SN.OrderID

Looks like never being used unlyess you want to make sure there is a related record.
0
 
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
check out the modified code:
insert      into SimplifiedProductionTracking
        (OrderNumber, SerialNumber, CatalogNumber, WorkCenter, Status, StartDate, FinishDate, Employees, StationID, FinishShiftID, TimeElapsedSeconds, IDStart, IDEnd)
        (select      SN.OrderID,
                        A.SerialNumber,
                        SA.Catalog_Number,
                        --S.WorkCenter,
                        SMWC.WorkCenter,
                        B.Status,
                        A.DateRegistered,
                        B.Dateregistered,
                        A.Employee + Coalesce('','' + nullif(B.Employee, A.Employee), '''') as Employees,
                        A.StationID,
                        B.shiftID,
                        datediff(ss, A.DateRegistered, B.DateRegistered) as Actualtime,
                        A.ProductTrackID,
                        B.ProductTrackID
from      ProductionTracking as A  with (nolock)
        inner join
        ProductionTracking as B  with (nolock)
        on A.SerialNumber = B.SerialNumber and A.StationID = B.StationID and A.Simplified is null
         and B.Simplified is null
           and ((A.Status = 1 and B.Status = 3) or (A.Status = 4 and B.Status = 6)
                  or(A.Status =8 and B.Status =7))
            inner join SerialNumbers SN with (nolock)
            on A.SerialNumber = SN.SerialNumber
            inner join SapOrders SA with (nolock)
            on SN.OrderID = SA.Order_Number
            inner join Station S with (nolock)
            on A.StationID = S.StationId
            inner join IdealCycleTimes ICT with (nolock)
            on ICT.Order_Number = SN.OrderID
            inner join SapMultipleWorkCenters SMWC with (nolock)
            on SMWC.StationID = S.StationID and SMWC.WorkCenter= ICT.Work_Center)

update      ProductionTracking
set      simplified = 1
where      simplified is null
and EXISTS (
select IDEnd from SimplifiedProductionTracking WHERE ProductionTracking.ProductTrackID = SimplifiedProductionTracking.IDEnd)

update      ProductionTracking
set      simplified = 1
where      simplified is null
and EXISTS(
select 1 from SimplifiedProductionTracking where ProductionTracking.ProductTrackID = SimplifiedProductionTracking.IDStart)

Open in new window

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

  • 5
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now