Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Help required for performance tuning a view


Help required for performance tuning a view

Author
Message
mike.dinnis
mike.dinnis
SSC Rookie
SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)

Group: General Forum Members
Points: 49 Visits: 250
Here's some doctored INSERTS to populate the jobtran table. It covers two jobs which I think is sufficient for analysing the problem. Is this suitable?
Attachments
INSERTS_for_jobtran.txt (1 view, 33.00 KB)
mike.dinnis
mike.dinnis
SSC Rookie
SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)

Group: General Forum Members
Points: 49 Visits: 250
Don't know where my last post went so I apologise if this is posted twice.
I've attached a set of INSERTS for the jobtran table representing some doctored data. I hope this is sufficient for analysing the performance. Let me know if you need more.
Attachments
INSERTS_for_jobtran.txt (0 views, 33.00 KB)
ChrisM@Work
ChrisM@Work
SSCrazy Eights
SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)

Group: General Forum Members
Points: 8960 Visits: 19020
This, I think, would make a reasonable test query:
SELECT
jt.job, jt.suffix, jt.oper_num, jt.trans_num, jt.emp_num,

x.oper_num, x.trans_num, x.emp_num,

jt2.trans_num

FROM jobtran jt (NOLOCK)

OUTER APPLY (
SELECT TOP 1
jtx.emp_num, jtx.oper_num, jtx.trans_num
FROM jobtran jtx(NOLOCK)
WHERE jtx.job = jt.job
AND jtx.suffix = jt.suffix
AND jtx.oper_num >= jt.oper_num
AND jtx.emp_num IS NOT NULL
AND jtx.trans_num < jt.trans_num
ORDER BY jtx.oper_num, jtx.trans_num DESC
) x

OUTER APPLY ( -- Set up time only backflushed for first booking,
SELECT -- so check that no previous booking exists for op.
trans_num = MIN(trans_num)
FROM jobtran j (NOLOCK)
WHERE j.job = jt.job
AND j.suffix = jt.suffix
AND j.oper_num = jt.oper_num
) jt2

WHERE jt.trans_class = 'J'
AND ISNULL(jt.posted,0) = 1 -- Only show posted transactions




“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
mike.dinnis
mike.dinnis
SSC Rookie
SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)

Group: General Forum Members
Points: 49 Visits: 250
ChrisM@Work (8/12/2013)
I'd focus on the one table for now, which gives you this query:

SELECT   
jt.trans_num,
emp_num = CASE
WHEN jt.emp_num IS NOT NULL
THEN jt.emp_num
ELSE
(SELECT TOP 1
jtx.emp_num
FROM jobtran jtx(NOLOCK)
WHERE jtx.job = jt.job
AND jtx.suffix = jt.suffix
AND jtx.oper_num >= jt.oper_num
AND jtx.emp_num IS NOT NULL
AND jtx.trans_num < jt.trans_num
ORDER BY jtx.oper_num, jtx.trans_num DESC)
END

FROM jobtran jt (NOLOCK)

LEFT JOIN (
SELECT
trans_num = MIN(trans_num), -- Set up time only backflushed for first booking,
job, -- so check that no previous booking exists for op.
suffix,
oper_num
FROM jobtran (NOLOCK)
GROUP BY job, suffix, oper_num
) jt2
ON jt2.job = jt.job
AND jt2.suffix = jt.suffix
AND jt2.oper_num = jt.oper_num
AND jt2.trans_num < jt.trans_num

WHERE jt.trans_class = 'J'
AND ISNULL(jt.posted,0) = 1 -- Only show posted transactions



Why did you include the LEFT JOIN in this statement? It doesn't appear to be used anywhere.
Other than that the code is the same I posted in my original post.
Just curious as to how to diagnose these things for myself in the future.
ChrisM@Work
ChrisM@Work
SSCrazy Eights
SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)

Group: General Forum Members
Points: 8960 Visits: 19020
It is referenced:

CASE          -- All std time captured on Move*, as this is where quantity is recorded
-- where Move trans types don't exist, qty is recorded on other trans types (e.g C)
WHEN ISNULL(jt.qty_moved,0) <> 0
AND jt2.trans_num IS NULL



“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
mike.dinnis
mike.dinnis
SSC Rookie
SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)

Group: General Forum Members
Points: 49 Visits: 250
ChrisM@Work (8/12/2013)
This, I think, would make a reasonable test query:
SELECT
jt.job, jt.suffix, jt.oper_num, jt.trans_num, jt.emp_num,

x.oper_num, x.trans_num, x.emp_num,

jt2.trans_num

FROM jobtran jt (NOLOCK)

OUTER APPLY (
SELECT TOP 1
jtx.emp_num, jtx.oper_num, jtx.trans_num
FROM jobtran jtx(NOLOCK)
WHERE jtx.job = jt.job
AND jtx.suffix = jt.suffix
AND jtx.oper_num >= jt.oper_num
AND jtx.emp_num IS NOT NULL
AND jtx.trans_num < jt.trans_num
ORDER BY jtx.oper_num, jtx.trans_num DESC
) x

OUTER APPLY ( -- Set up time only backflushed for first booking,
SELECT -- so check that no previous booking exists for op.
trans_num = MIN(trans_num)
FROM jobtran j (NOLOCK)
WHERE j.job = jt.job
AND j.suffix = jt.suffix
AND j.oper_num = jt.oper_num
) jt2

WHERE jt.trans_class = 'J'
AND ISNULL(jt.posted,0) = 1 -- Only show posted transactions




So what you're doing here is testing for the result of different scenarios? I like the way I can see how the emp_num can be traced through the different scenarios.
I'd tried using OUTER APPLY before posting and found that (with my statement) that there was no performance gain.
mike.dinnis
mike.dinnis
SSC Rookie
SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)

Group: General Forum Members
Points: 49 Visits: 250
ChrisM@Work (8/12/2013)
It is referenced:

CASE          -- All std time captured on Move*, as this is where quantity is recorded
-- where Move trans types don't exist, qty is recorded on other trans types (e.g C)
WHEN ISNULL(jt.qty_moved,0) <> 0
AND jt2.trans_num IS NULL



Doh! Sorry!
ChrisM@Work
ChrisM@Work
SSCrazy Eights
SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)

Group: General Forum Members
Points: 8960 Visits: 19020
Try this index, Mike:

CREATE INDEX ix_Test ON Jobtran (oper_num, trans_num DESC, job, suffix) INCLUDE (emp_num)

Edit: CREATE INDEX ix_New ON Jobtran (job, suffix, oper_num, trans_num DESC) INCLUDE (emp_num)

“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
mike.dinnis
mike.dinnis
SSC Rookie
SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)

Group: General Forum Members
Points: 49 Visits: 250
The first suggested index made little to no difference (probably why you edited the post!) and so I removed from my DB after testing.
The second suggested index halved the running time. This is without the other previously suggested indexes in play. I don't know if this was intentional or not but the second one is also the same as you suggested earlier implying that your first instincts were correct.
With this second query in place my 'problem' query example is now running in under 30 seconds compared to the original posted 50+ seconds. Even better, the 'full' query is now running in under 60 seconds when previously it was taking nearly 2. Quite a result thanks to you.

I really appreciate you taking the time to help me in this matter. In deciding on the most efficient index to build did you look at the fields used in the query and include those in a query, or was there some other clue to help you have that suggestion?
ChrisM@Work
ChrisM@Work
SSCrazy Eights
SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)

Group: General Forum Members
Points: 8960 Visits: 19020
Picking the right columns to use for a covering index is mostly straightforward. List all of the columns used in the (sub)query. If the column is used as a filter or a join then it's probably a key column, otherwise it's an INCLUDE column. Getting the order of the columns correct in the key column list can be a little hit or miss (see today's ssc headlines). What you're looking for in the plan is to see as many as possible of your key columns used in the seek predicate and as few as possible remaining in the residual predicate, and this often takes a little experimentation.

The new index is designed to help the emp_num subquery. It supports seeks albeit with a residual predicate and the data comes out already sorted for the TOP operator i.e. in the same order as the sort operator in the original query, which, in the test harness and without the index, appears as about 95% of the total query cost.
In the little test harness, the new index is also used by the second subquery although with a less dramatic effect.
I'm sure more improvements are possible if you wish to pursue it.

“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search