Ugly Index Spool

Question

Ugly Index Spool

sql-lover

SSCoach

Points: 18530
More actions
May 19, 2014 at 3:54 pm

#285666

Hi,
Trying to do some tuning on following T-SQL query:
SELECT RequestID,
RequestTypeID,
RequestStatusID
,"DateApproved" = (SELECT DateApproved FROM Ticket T Where T.TicketID = CR.TicketID)
,"DateFirstReleased" = CASE WHEN CR.RequestTypeID = 1 THEN
(SELECT NewAsOfDate FROM MyDatabase.dbo.Report R Where R.ReportID = CR.ReportID AND IsDotNetActive =1)
ELSE
DateFirstReleased
END
,"ProductionDays" = (
DateDiff(day,
(SELECT DateApproved FROM Ticket T Where T.TicketID = CR.TicketID)
,
CASE WHEN CR.RequestTypeID = 1 THEN
(SELECT NewAsOfDate FROM MyDatabase.dbo.Report R Where R.ReportID = CR.ReportID AND IsDotNetActive =1)
ELSE
DateFirstReleased
END )
)
,"TicketCompletedDate" = (SELECT CompletedDate FROM Ticket T Where T.TicketID = CR.TicketID)
FROM
MyDatabase.dbo.CustomRequest CR
WHERE DateDeactivated is null
Attached is the current execution plan.
Part of the problem is that "MyDatabase" is on another server so above query, so it runs as a remote query but I can't change that. But I see several Index Spools that I know are a problem as well.
Any suggestion?
By the way, I do have an Index on Report.ReportID but not on CustomRequest.ReportID.

Viewing 10 posts - 1 through 9 (of 9 total)

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

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

Why are you using subqueries in the output list instead of joins? You're reading the ticket table three times and the report table twice, instead of once each as you would with joins.

SELECT

cr.RequestID,

cr.RequestTypeID,

cr.RequestStatusID,

t.DateApproved,

x.DateFirstReleased,

[ProductionDays] = DATEDIFF(day, t.DateApproved, x.DateFirstReleased),

[TicketCompletedDate] = t.CompletedDate

FROM MyDatabase.dbo.CustomRequest CR

LEFT JOIN Ticket t -- Check your ERD - is this an inner or outer join

ON T.TicketID = CR.TicketID

LEFT JOIN MyDatabase.dbo.Report r -- Check your ERD - is this an inner or outer join

ON R.ReportID = CR.ReportID

AND r.IsDotNetActive =1

CROSS APPLY (

SELECT [DateFirstReleased] = CASE

WHEN CR.RequestTypeID = 1 THEN r.NewAsOfDate

ELSE cr.DateFirstReleased END

) x

WHERE cr.DateDeactivated IS NULL

The plan suggests that you're working cross-database rather than cross-server.

^{“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

sql-lover SSCoach Points: 18530 More actions · Answer 2

ChrisM@Work (5/20/2014)
Why are you using subqueries in the output list instead of joins? You're reading the ticket table three times and the report table twice, instead of once each as you would with joins.
SELECT
cr.RequestID,
cr.RequestTypeID,
cr.RequestStatusID,
t.DateApproved,
x.DateFirstReleased,
[ProductionDays] = DATEDIFF(day, t.DateApproved, x.DateFirstReleased),
[TicketCompletedDate] = t.CompletedDate
FROM MyDatabase.dbo.CustomRequest CR
LEFT JOIN Ticket t -- Check your ERD - is this an inner or outer join
ON T.TicketID = CR.TicketID
LEFT JOIN MyDatabase.dbo.Report r -- Check your ERD - is this an inner or outer join
ON R.ReportID = CR.ReportID
AND r.IsDotNetActive =1
CROSS APPLY (
SELECT [DateFirstReleased] = CASE
WHEN CR.RequestTypeID = 1 THEN r.NewAsOfDate
ELSE cr.DateFirstReleased END
) x
WHERE cr.DateDeactivated IS NULL
The plan suggests that you're working cross-database rather than cross-server.

Didn't know any better 😉

I'm an operational DBA and a bit rusty on TSQL. This was someone else old code but knew those subqueries were an issue.

I deployed and tested the code on my Dev environment, which runs a single instance of SQL. That's why the plan shows a cross database query. Pro is two separate SQL boxes.

I'll check the result data set and query plan with your suggested solution.

Thanks!

sql-lover SSCoach Points: 18530 More actions · Answer 3

@chris-2,

Thank you very much! That eliminated those Index Spools and made the T-SQL code query more efficient.

I checked the result set on my Dev box and it is fine. It's also more efficient.

I'm still dealing with the last part though, probably less complicated. That main query is being inserted on a variable table and there are like 10 select statements that filter data from that variable table and re-insert it on another one. I do believe that can be improved too. I am validating now.

Here's the 2nd part, those INSERT INTO statements ...

DECLARE @RESULTS TABLE (SortID int, DESCR varchar(50), TheValue int)

INSERT INTO @RESULTS (SortID, DESCR, TheValue)

SELECT 10, 'REPORTS: Open', Count(RequestID)

FROM @Result

WHERE

RequestTypeID = 1

AND

RequestStatusID not in (6,7)

AND

TicketCompletedDate is null

INSERT INTO @RESULTS (SortID, DESCR, TheValue)

SELECT 20, 'Pending Work:', Count(RequestID)

FROM @Result

WHERE

RequestTypeID = 1

AND

RequestStatusID not in (6,7)

AND

DateApproved is null

AND

TicketCompletedDate is null

INSERT INTO @RESULTS (SortID, DESCR, TheValue)

SELECT 30, 'Active Development:', Count(RequestID)

FROM @Result

WHERE

RequestTypeID = 1

AND

RequestStatusID not in (6,7)

AND

DateApproved is not null

AND

DateFirstReleased is null

AND

TicketCompletedDate is null

INSERT INTO @RESULTS (SortID, DESCR, TheValue)

SELECT 40, 'Pending Signoff:', Count(RequestID)

FROM @Result

WHERE

RequestTypeID = 1

AND

RequestStatusID not in (6,7)

AND

DateApproved is not null

AND

DateFirstReleased is not null

AND

TicketCompletedDate is null

INSERT INTO @RESULTS (SortID, DESCR, TheValue)

SELECT 45, 'Avg Prod Time Last 8 Wks:', Avg(ProductionDays)

FROM @Result

WHERE

RequestTypeID = 1

AND

RequestStatusID in (6,8)

AND

DateApproved is not null

AND

DateFirstReleased is not null

AND

TicketCompletedDate is not null

AND

TicketCompletedDate > DateAdd(week,-8, getdate())

INSERT INTO @RESULTS (SortID, DESCR, TheValue)

SELECT 50, 'EXPORTS: Open', Count(RequestID)

FROM @Result

WHERE

RequestTypeID = 2

AND

RequestStatusID not in (6,7)

AND

TicketCompletedDate is null

INSERT INTO @RESULTS (SortID, DESCR, TheValue)

SELECT 60, 'Pending Work:', Count(RequestID)

FROM @Result

WHERE

RequestTypeID = 2

AND

RequestStatusID not in (6,7)

AND

DateApproved is null

AND

TicketCompletedDate is null

INSERT INTO @RESULTS (SortID, DESCR, TheValue)

SELECT 70, 'Active Development:', Count(RequestID)

FROM @Result

WHERE

RequestTypeID = 2

AND

RequestStatusID not in (6,7)

AND

DateApproved is not null

AND

DateFirstReleased is null

AND

TicketCompletedDate is null

INSERT INTO @RESULTS (SortID, DESCR, TheValue)

SELECT 80, 'Pending Signoff:', Count(RequestID)

FROM @Result

WHERE

RequestTypeID = 2

AND

RequestStatusID not in (6,7)

AND

DateApproved is not null

AND

DateFirstReleased is not null

AND

TicketCompletedDate is null

INSERT INTO @RESULTS (SortID, DESCR, TheValue)

SELECT 80, 'Avg Prod Time Last 8 Wks:', Avg(ProductionDays)

FROM @Result

WHERE

RequestTypeID = 2

AND

RequestStatusID in (6,8)

AND

DateApproved is not null

AND

DateFirstReleased is not null

AND

TicketCompletedDate is not null

AND

TicketCompletedDate > DateAdd(week,-8, getdate())

SELECT DESCR, TheValue

FROM @RESULTS

ORDER BY SortID

Even though those are small sets, I may test with temporary tables and put and Index on SortID? I see several table scans on each of them.

sql-lover SSCoach Points: 18530 More actions · Answer 4

I take that back.

The new code seems to be more slim and faster on the remote server ...

... but there is more data traversing the network.

:ermm:

ChrisM@home SSC-Insane Points: 24260 More actions · Answer 5

On phone so long shot - try LEFT REMOTE JOIN ticket.

[font="Arial"]^{Low-hanging fruit picker and defender of the moggies}[/font]

For better assistance in answering your questions, please read this[/url].

Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]

sql-lover SSCoach Points: 18530 More actions · Answer 6

ChrisM@home (5/20/2014)
On phone so long shot - try LEFT REMOTE JOIN ticket.

Got this error:

Msg 1072, Level 15, State 1, Line 22

A REMOTE hint can only be specified with an INNER JOIN clause.

Msg 102, Level 15, State 1, Line 33

Incorrect syntax near 'x'.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 7

sql-lover (5/20/2014)
ChrisM@home (5/20/2014)
On phone so long shot - try LEFT REMOTE JOIN ticket.
Got this error:
Msg 1072, Level 15, State 1, Line 22
A REMOTE hint can only be specified with an INNER JOIN clause.
Msg 102, Level 15, State 1, Line 33
Incorrect syntax near 'x'.

My bad = the REMOTE join hint only works with inner joins.

Have you checked that (left) outer joins are appropriate?

Can you post the number of rows in each table in the distributed query?

^{“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

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 8

-- Read (and aggregate) table variable @Result only once.

-- Note that a row will match more than one filter.

-- Note use of NULL in case expression for averages -

-- this is to eliminate rows (which don't match the filter)

-- from the aggregate computation.

SELECT

[SortID10] = SUM(CASE WHEN RequestTypeID = 1 AND RequestStatusID not in (6,7) AND TicketCompletedDate IS NULL

THEN 1 ELSE 0 END),

[SortID20] = SUM(CASE WHEN RequestTypeID = 1 AND RequestStatusID not in (6,7) AND TicketCompletedDate IS NULL

AND DateApproved is null

THEN 1 ELSE 0 END),

[SortID30] = SUM(CASE WHEN RequestTypeID = 1 AND RequestStatusID not in (6,7) AND TicketCompletedDate IS NULL

AND DateApproved is not null AND DateFirstReleased is null

THEN 1 ELSE 0 END),

[SortID40] = SUM(CASE WHEN RequestTypeID = 1 AND RequestStatusID not in (6,7) AND TicketCompletedDate IS NULL

AND DateApproved is not null AND DateFirstReleased is not null

THEN 1 ELSE 0 END),

[SortID45] = AVG(CASE WHEN RequestTypeID = 1 AND RequestStatusID in (6,8) AND TicketCompletedDate > DateAdd(week,-8, getdate())

AND DateApproved is not null AND DateFirstReleased is not null

THEN ProductionDays ELSE NULL END),

[SortID50] = SUM(CASE WHEN RequestTypeID = 2 AND RequestStatusID not in (6,7) AND TicketCompletedDate is null

THEN 1 ELSE 0 END),

[SortID60] = SUM(CASE WHEN RequestTypeID = 2 AND RequestStatusID not in (6,7) AND TicketCompletedDate is null

AND DateApproved is null

THEN 1 ELSE 0 END),

[SortID70] = SUM(CASE WHEN RequestTypeID = 2 AND RequestStatusID not in (6,7) AND TicketCompletedDate is null

AND DateApproved is not null AND DateFirstReleased is null

THEN 1 ELSE 0 END),

[SortID80] = SUM(CASE WHEN RequestTypeID = 2 AND RequestStatusID not in (6,7) AND TicketCompletedDate is null

AND DateApproved is not null AND DateFirstReleased is not null

THEN 1 ELSE 0 END),

[SortID90] = AVG(CASE WHEN RequestTypeID = 2 AND RequestStatusID in (6,8) AND TicketCompletedDate > DateAdd(week,-8, getdate())

AND DateApproved is not null AND DateFirstReleased is not null

THEN ProductionDays ELSE NULL END)

INTO #AggregatedResults

FROM @Result

WHERE TicketCompletedDate is null

OR TicketCompletedDate > DateAdd(week,-8, getdate()) ;

WITH Results (SortID, DESCR, TheValue) AS (

SELECT 10, 'REPORTS: Open', SortID10 FROM #AggregatedResults UNION ALL

SELECT 20, 'Pending Work:', SortID20 FROM #AggregatedResults UNION ALL

SELECT 30, 'Active Development:', SortID30 FROM #AggregatedResults UNION ALL

SELECT 40, 'Pending Signoff:', SortID40 FROM #AggregatedResults UNION ALL

SELECT 45, 'Avg Prod Time Last 8 Wks:', SortID45 FROM #AggregatedResults UNION ALL

SELECT 50, 'EXPORTS: Open', SortID50 FROM #AggregatedResults UNION ALL

SELECT 60, 'Pending Work:', SortID60 FROM #AggregatedResults UNION ALL

SELECT 70, 'Active Development:', SortID70 FROM #AggregatedResults UNION ALL

SELECT 80, 'Pending Signoff:', SortID80 FROM #AggregatedResults UNION ALL

SELECT 90, 'Avg Prod Time Last 8 Wks:', SortID90 FROM #AggregatedResults

)

SELECT DESCR, TheValue

FROM Results

ORDER BY SortID

^{“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

sql-lover SSCoach Points: 18530 More actions · Answer 9

ChrisM@Work (5/21/2014)
-- Read (and aggregate) table variable @Result only once. -- Note that a row will match more than one filter. -- Note use of NULL in case expression for averages - -- this is to eliminate rows (which don't match the filter) -- from the aggregate computation. SELECT [SortID10] = SUM(CASE WHEN RequestTypeID = 1 AND RequestStatusID not in (6,7) AND TicketCompletedDate IS NULL THEN 1 ELSE 0 END), [SortID20] = SUM(CASE WHEN RequestTypeID = 1 AND RequestStatusID not in (6,7) AND TicketCompletedDate IS NULL AND DateApproved is null THEN 1 ELSE 0 END), [SortID30] = SUM(CASE WHEN RequestTypeID = 1 AND RequestStatusID not in (6,7) AND TicketCompletedDate IS NULL AND DateApproved is not null AND DateFirstReleased is null THEN 1 ELSE 0 END), [SortID40] = SUM(CASE WHEN RequestTypeID = 1 AND RequestStatusID not in (6,7) AND TicketCompletedDate IS NULL AND DateApproved is not null AND DateFirstReleased is not null THEN 1 ELSE 0 END), [SortID45] = AVG(CASE WHEN RequestTypeID = 1 AND RequestStatusID in (6,8) AND TicketCompletedDate > DateAdd(week,-8, getdate()) AND DateApproved is not null AND DateFirstReleased is not null THEN ProductionDays ELSE NULL END), [SortID50] = SUM(CASE WHEN RequestTypeID = 2 AND RequestStatusID not in (6,7) AND TicketCompletedDate is null THEN 1 ELSE 0 END), [SortID60] = SUM(CASE WHEN RequestTypeID = 2 AND RequestStatusID not in (6,7) AND TicketCompletedDate is null AND DateApproved is null THEN 1 ELSE 0 END), [SortID70] = SUM(CASE WHEN RequestTypeID = 2 AND RequestStatusID not in (6,7) AND TicketCompletedDate is null AND DateApproved is not null AND DateFirstReleased is null THEN 1 ELSE 0 END), [SortID80] = SUM(CASE WHEN RequestTypeID = 2 AND RequestStatusID not in (6,7) AND TicketCompletedDate is null AND DateApproved is not null AND DateFirstReleased is not null THEN 1 ELSE 0 END), [SortID90] = AVG(CASE WHEN RequestTypeID = 2 AND RequestStatusID in (6,8) AND TicketCompletedDate > DateAdd(week,-8, getdate()) AND DateApproved is not null AND DateFirstReleased is not null THEN ProductionDays ELSE NULL END) INTO #AggregatedResults FROM @Result WHERE TicketCompletedDate is null OR TicketCompletedDate > DateAdd(week,-8, getdate()) ; WITH Results (SortID, DESCR, TheValue) AS ( SELECT 10, 'REPORTS: Open', SortID10 FROM #AggregatedResults UNION ALL SELECT 20, 'Pending Work:', SortID20 FROM #AggregatedResults UNION ALL SELECT 30, 'Active Development:', SortID30 FROM #AggregatedResults UNION ALL SELECT 40, 'Pending Signoff:', SortID40 FROM #AggregatedResults UNION ALL SELECT 45, 'Avg Prod Time Last 8 Wks:', SortID45 FROM #AggregatedResults UNION ALL SELECT 50, 'EXPORTS: Open', SortID50 FROM #AggregatedResults UNION ALL SELECT 60, 'Pending Work:', SortID60 FROM #AggregatedResults UNION ALL SELECT 70, 'Active Development:', SortID70 FROM #AggregatedResults UNION ALL SELECT 80, 'Pending Signoff:', SortID80 FROM #AggregatedResults UNION ALL SELECT 90, 'Avg Prod Time Last 8 Wks:', SortID90 FROM #AggregatedResults ) SELECT DESCR, TheValue FROM Results
ORDER BY SortID

Thank you!

Not at work yet, but I will try.

I think I will leave it with the 1st option you suggested or a modified one I made. The remote tables are small so I basically getting those two sets, create variable tables locally, and doing the joins locally. Still is hitting the local table way more faster than before, around 400 pages instead of 1200 pages that was doing before.

I think I will focus on the consolidation of those INSERT statements into a single one. But I probably it is better or more clear if I create a different thread for it?

Thanks again.