Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««123»»

Help required for performance tuning a view Expand / Collapse
Author
Message
Posted Monday, August 12, 2013 7:15 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, September 29, 2014 7:39 AM
Points: 45, Visits: 233
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?



  Post Attachments 
INSERTS_for_jobtran.txt (1 view, 33.83 KB)
Post #1483291
Posted Monday, August 12, 2013 7:17 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, September 29, 2014 7:39 AM
Points: 45, Visits: 233
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.


  Post Attachments 
INSERTS_for_jobtran.txt (0 views, 33.83 KB)
Post #1483295
Posted Monday, August 12, 2013 7:17 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 7:59 AM
Points: 6,780, Visits: 13,980
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
Post #1483296
Posted Monday, August 12, 2013 7:37 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, September 29, 2014 7:39 AM
Points: 45, Visits: 233
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.
Post #1483309
Posted Monday, August 12, 2013 7:40 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 7:59 AM
Points: 6,780, Visits: 13,980
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
Post #1483312
Posted Monday, August 12, 2013 7:41 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, September 29, 2014 7:39 AM
Points: 45, Visits: 233
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.
Post #1483313
Posted Monday, August 12, 2013 7:42 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, September 29, 2014 7:39 AM
Points: 45, Visits: 233
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!
Post #1483315
Posted Monday, August 12, 2013 8:20 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 7:59 AM
Points: 6,780, Visits: 13,980
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
Post #1483344
Posted Monday, August 12, 2013 9:04 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, September 29, 2014 7:39 AM
Points: 45, Visits: 233
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?
Post #1483366
Posted Monday, August 12, 2013 9:36 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 7:59 AM
Points: 6,780, Visits: 13,980
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
Post #1483374
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse