February 20, 2017 at 5:36 am
Normally the process finishes in couple of minutes. But this is taking forever. I cant figure out what is causing this to stuck.
<?query --
INSERT @JOBSTEP000 (
PROCPLANID
, JSID
, DESCR
, NEXTJSID
, STEPTIME
, STEPTMRL
, STEPEXPRL
, RESACTN1
, RESACTN2
, RESACTN3
, RESACTN4
, RESACTN5
, RESACTN6
, RESID1
, RESID2
, RESID3
, RESID4
, RESID5
, RESID6
, RESNMBR1
, RESNMBR2
, RESNMBR3
, RESNMBR4
, RESNMBR5
, RESNMBR6
, RefRowPointer
, EFFDATE
, OBSDATE
, FLAGS
)
SELECT
/* PROCPLANID = */ dbo.ApsRouteId(job.job, job.suffix) --dbo.ApsCurrentRouteId(job.job, job.suffix, ApsRoute.start_date)
, /* JSID = */ dbo.ApsOperationId(jobroute.job, jobroute.suffix, jobroute.oper_num)
, /* DESCR = */ jobroute.wc
, /* NEXTJSID = */ ''
, /* STEPTIME = */ dbo.ApsStepTime(jobroute.job, jobroute.suffix, jobroute.oper_num, 0)
, /* STEPTMRL = */
CASE
WHEN job.is_external = 1
THEN 4
WHEN jrt_sch.sched_hrs IS NOT NULL AND
ISNULL(jrt_sch.plannerstep,0) = 0
THEN 0
WHEN ISNULL(jrt_sch.plannerstep,0) = 0
THEN 1
WHEN jrt_sch.sched_hrs IS NOT NULL
THEN 2
ELSE 3
END
, /* stepexprl = */
CASE
WHEN job.is_external = 1
THEN 11
ELSE ISNULL(jrt_sch.schedsteprule,0)
END
, /* RESACTN1 = */ ISNULL(jrtresourcegroup1.resactn,'')
, /* RESACTN2 = */ ISNULL(jrtresourcegroup2.resactn,'')
, /* RESACTN3 = */ ISNULL(jrtresourcegroup3.resactn,'')
, /* RESACTN4 = */ ISNULL(jrtresourcegroup4.resactn,'')
, /* RESACTN5 = */ ISNULL(jrtresourcegroup5.resactn,'')
, /* RESACTN6 = */ ISNULL(jrtresourcegroup6.resactn,'')
, /* RESID1 = */ ISNULL(jrtresourcegroup1.rgid,'')
, /* RESID2 = */ ISNULL(jrtresourcegroup2.rgid,'')
, /* RESID3 = */ ISNULL(jrtresourcegroup3.rgid,'')
, /* RESID4 = */ ISNULL(jrtresourcegroup4.rgid,'')
, /* RESID5 = */ ISNULL(jrtresourcegroup5.rgid,'')
, /* RESID6 = */ ISNULL(jrtresourcegroup6.rgid,'')
, /* RESNMBR1 = */ ISNULL(jrtresourcegroup1.qty_resources,'')
, /* RESNMBR2 = */ ISNULL(jrtresourcegroup2.qty_resources,'')
, /* RESNMBR3 = */ ISNULL(jrtresourcegroup3.qty_resources,'')
, /* RESNMBR4 = */ ISNULL(jrtresourcegroup4.qty_resources,'')
, /* RESNMBR5 = */ ISNULL(jrtresourcegroup5.qty_resources,'')
, /* RESNMBR6 = */ ISNULL(jrtresourcegroup6.qty_resources,'')
, /* RefRowPointer = */ jobroute.rowpointer
, /* EFFDATE = */ CASE
WHEN job.type = 'S'
THEN dbo.MidnightOf(ISNULL (jobroute.effect_date, dbo.lowdate()))
ELSE dbo.MidnightOf(dbo.lowdate())
End
, /* OBSDATE = */ CASE
WHEN job.type = 'S'
THEN dbo.MidnightOf (ISNULL (jobroute.obs_date, dbo.highdate()))
ELSE dbo.MidnightOf(dbo.highdate())
End
, CASE
WHEN jrt_sch.allow_reallocation = 1
THEN 4
ELSE 0
END
FROM TrackRows with (readuncommitted)
JOIN jobroute with (readuncommitted) ON jobroute.rowpointer = TrackRows.rowpointer
JOIN job with (readuncommitted) ON job.job = jobroute.job AND job.suffix = jobroute.suffix
JOIN @QPHelp qph ON qph.job = job.job AND qph.suffix = job.suffix
JOIN jrt_sch with (readuncommitted) ON
jrt_sch.job = jobroute.job AND
jrt_sch.suffix = jobroute.suffix AND
jrt_sch.oper_num = jobroute.oper_num
LEFT JOIN @jrtresourcegroup as jrtresourcegroup1 ON
jrtresourcegroup1.job = jobroute.job AND
jrtresourcegroup1.suffix = jobroute.suffix AND
jrtresourcegroup1.oper_num = jobroute.oper_num AND
jrtresourcegroup1.seq = 1
LEFT JOIN @jrtresourcegroup as jrtresourcegroup2 ON
jrtresourcegroup2.job = jobroute.job AND
jrtresourcegroup2.suffix = jobroute.suffix AND
jrtresourcegroup2.oper_num = jobroute.oper_num AND
jrtresourcegroup2.seq = 2
LEFT JOIN @jrtresourcegroup as jrtresourcegroup3 ON
jrtresourcegroup3.job = jobroute.job AND
jrtresourcegroup3.suffix = jobroute.suffix AND
jrtresourcegroup3.oper_num = jobroute.oper_num AND
jrtresourcegroup3.seq = 3
LEFT JOIN @jrtresourcegroup as jrtresourcegroup4 ON
jrtresourcegroup4.job = jobroute.job AND
jrtresourcegroup4.suffix = jobroute.suffix AND
jrtresourcegroup4.oper_num = jobroute.oper_num AND
jrtresourcegroup4.seq = 4
LEFT JOIN @jrtresourcegroup as jrtresourcegroup5 ON
jrtresourcegroup5.job = jobroute.job AND
jrtresourcegroup5.suffix = jobroute.suffix AND
jrtresourcegroup5.oper_num = jobroute.oper_num AND
jrtresourcegroup5.seq = 5
LEFT JOIN @jrtresourcegroup as jrtresourcegroup6 ON
jrtresourcegroup6.job = jobroute.job AND
jrtresourcegroup6.suffix = jobroute.suffix AND
jrtresourcegroup6.oper_num = jobroute.oper_num AND
jrtresourcegroup6.seq = 6
WHERE
TrackRows.SessionId = @Partition AND
TrackRows.TrackedOperType = 'Sync jobroute' AND
jobroute.complete = 0 AND
(qph.ApsPlannerNeedsRoute = 1 OR
qph.ApsSchedulerNeedsJob = 1 OR
(job.type = 'S' AND job.suffix = 0)
)
OPTION (KEEPFIXED PLAN)
--?>
February 20, 2017 at 6:30 am
First, change all those table variables to temp tables.
The Query engine assumes that a table variable has one row in it, and builds a plan which assumes that one row.(in SQL2014 and above,t he assumption is 100 rows)
you can easily get 10 to 100 times better performance just by switching to temp tables in this situation.
using temp tables instead allows the engine to use statistics form the tempt able to properly estimate the number of rows,and build a better plan.
see if that addresses the performance thing first.
the other thing that bothers me is all the ORs in the WHERE statement section; that's going to end up causing table scans; i'd look to change that to use UNION ALL and multiple queries to take advantage of any indexes
Lowell
February 20, 2017 at 11:43 am
skb 44459 - Monday, February 20, 2017 5:36 AMNormally the process finishes in couple of minutes. But this is taking forever. I cant figure out what is causing this to stuck.
You would typically want to check waiting tasks when this kind of thing happens to see what the process is hung up on.
Using Keepfixed plan can have some side effects and it's possible the query plan is no longer optimal - that's one of the risks of using that hint. You may want to look at the query plan and work on some of the changes as Lowell suggested to see if you can't tune the query in general.
Sue
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply