Help required for performance tuning a view

Question

Help required for performance tuning a view

Viewing 6 posts - 16 through 20 (of 20 total)

You must be logged in to reply to this topic. Login to reply

MikeRen Ten Centuries Points: 1295 More actions · Answer 1

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
ANDjtx.suffix = jt.suffix
AND jtx.oper_num >= jt.oper_num
ANDjtx.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'
ANDISNULL(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.

MikeRen Ten Centuries Points: 1295 More actions · Answer 2

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 SSC Guru Points: 186127 More actions · Answer 3

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

MikeRen Ten Centuries Points: 1295 More actions · Answer 4

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 SSC Guru Points: 186127 More actions · Answer 5

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

MikeRen Ten Centuries Points: 1295 More actions · Answer 6

Thank you for your help and for the insight on picking the correct fields for new indexes. (thinking of old proverb about fishing and eating)

I like learning and hope that this will bring untold benefits to my administration of my servers.

All I have to do now is remember that indexes are not always the solution to the problem!