question

sm082014 avatar image
sm082014 asked

Please tune the code

Dear All, Have wrote a script. Could you please tune the code. bit urgent. select J.RouteID TripID, J.JourneyAlias Journey_Trip_Id, J.OwningDepotDropPointID Depot, JD.DropPointID LocationID, JD.TimeWindowStart Appointment_Start, JD.TimeWindowEnd Appointment_End, case when (JD.PTA < JD.TimeWindowStart and JD.TimeWindowStart < JD.PTD) then JD.TimeWindowStart else JD.PTA end as PlannedArrival, JD.PTD PlannedDeparture, case when JD.ConfirmedArrivalTime is null then JD.ATA else JD.ConfirmedArrivalTime end as ActualArrival, case when JD.ConfirmedDepartureTime is null then JD.ATD else JD.ConfirmedDepartureTime end as ActualDeparture, case when (J.OwningDepotDropPointID in ('SKDC', 'WNDC', 'KKRDC', 'KKRFDC', 'BBTDC') and ST.SiteTypeName IN ('HyperExtra', 'Hypermarket', 'Compact Hypermarket', 'TaladExpress')) then '1:00:00' else --Added Somadatta for Talad case when (J.OwningDepotDropPointID in ('SKDC', 'WNDC', 'KKRDC', 'KKRFDC', 'BBTDC','CHMRDC', 'LLKDC') and ST.SiteTypeName = 'Talad') then tmp.Volume else case when (J.OwningDepotDropPointID in ('SKDC', 'WNDC', 'KKRDC', 'KKRFDC', 'BBTDC') and ST.SiteTypeName in ('HBA', 'Convenience', 'Express')) then '00:30:00' else case when (J.OwningDepotDropPointID in ('CHMRDC', 'LLKDC') and ST.SiteTypeName in ('HyperExtra', 'Hypermarket', 'Compact Hypermarket', 'TaladExpress')) then '00:45:00' else case when (J.OwningDepotDropPointID in ('CHMRDC', 'LLKDC') and ST.SiteTypeName in ('HBA', 'Convenience', 'Express')) then '00:30:00' end end end end end as PlannedTurnaroundTime, (case when JD.ConfirmedDepartureTime is null then JD.ATD else JD.ConfirmedDepartureTime end - case when JD.ConfirmedArrivalTime is null then JD.ATA else JD.ConfirmedArrivalTime end) as ActualTurnaround from tbl_J JD , tbl_Jo J , tbl_Si ST ,(select cd.PlannedVolume, JD.JourneyDropID,JD.PTA, CAST((Convert(time, CONVERT(datetime, '00:20:00' ) + CONVERT(datetime, (convert(varchar(10), dateadd(second, (((datepart(hour,'00:01:30') * 360) + (datepart(minute,'00:01:30') * 60) + datepart(second,'00:01:30'))* CD.PlannedVolume), 0), 108)) ))) as char(8)) as Volume from tbl_C CD, tbl_Co C, tbl_Con CS, tbl_Jou JD where IsNumeric(JD.DropPointID) = 1 AND JD.PTA BETWEEN '2014-04-01 00:00:00' and '2014-09-12 00:00:00' --change the date accordingly and CS.JourneyDropID = JD.JourneyDropID and C.ConsignmentSiteID = CS.ConsignmentSiteID and CD.ConsignmentHeaderID = C.ConsignmentHeaderID )tmp where IsNumeric(JD.DropPointID) = 1 AND JD.PTA BETWEEN '2014-04-01 00:00:00' and '2014-09-12 00:00:00' AND J.Status = 'C' and JD.JourneyID = J.JourneyID and JD.JourneyDropID=tmp.JourneyDropID and exists (select 1 from tbl_DropPoints DP where DP.DropPointID = JD.DropPointID and ST.SiteTypeID = DP.SiteTypeID)
performance-tuningquery-tuning
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Tune this - how? In terms of reducing CPU? Reducing disk i/o? Reducing memory requirements? Reducing duration? The quickest way of tuning this is to look at the execution plan and see where the resource is being spent.
5 Likes 5 ·
This web site operates by you voting. For each helpful answer below, indicate this by clicking on the thumbs up symbol next to those answers. If any one answer below lead to a solution, indicate this by clicking on the check mark next to that answer.
0 Likes 0 ·
Grant Fritchey avatar image
Grant Fritchey answered
There are no glaring and obvious issues with the code that I can spot on my first look through. Two things stand out that are going to cause you problems. You're using ANSI 89 style joins. These have long been replaced by the ANSI 92 style FROM TableA AS a INNER/OUTER JOIN TableB AS b ON a.ID = b.ID Since SQL Server 2012, the syntax for the ANSI 89 OUTER JOIN, *= or =*, has no longer been supported. You're going to hit issues because of this. The other thing is the function ISNUMERIC being run against JD.DropPointID could lead to scans on that column, hurting performance. The fact that you do that twice could mean two sets of scans (it depends on how the optimizer resolves the fact that you're running the same function on the column two times). Other than that, without your data, your structures and the ability to run the query to capture the execution plan, I can't make any other suggestions.
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

CirqueDeSQLeil avatar image
CirqueDeSQLeil answered
Looking at your code, I see one thing that I will occasionally change for clients with this type of code. I have seen it produce significant improvements in some cases, and less significant results in others. I like to find a way to reduce those in clauses to a join. If done properly and done well, you can reduce all of those in clauses from the equivalent of a multi-valued "or". Other than that, more info would be needed to be able to tune this query properly/effectively.
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.