CODEWITHSUNDEEP
sqlsql-server
Lance S
Lance S

Reputation: 125

Strange performance issue with SELECT (SUBQUERY)

I have a stored procedure that has been having some issues lately and I finally narrowed it down to 1 SELECT. The problem is I cannot figure out exactly what is happening to kill the performance of this one query. I re-wrote it, but I am not sure the re-write is the exact same data.

Original Query:

SELECT 
    @userId, p.job, p.charge_code, p.code
, (SELECT SUM(b.total) FROM dbo.[backorder w/total] b WHERE b.ponumber = p.ponumber AND b.code = p.code)
, ISNULL(jm.markup, 0)
, (SELECT SUM(b.TOTAL_TAX) FROM dbo.[backorder w/total] b WHERE b.ponumber = p.ponumber AND b.code = p.code)
, p.ponumber
, p.billable
, p.[date]
FROM dbo.PO p
INNER JOIN dbo.JobCostFilter jcf 
    ON p.job = jcf.jobno AND p.charge_code = jcf.chargecode AND jcf.userno = @userId
LEFT JOIN dbo.JobMarkup jm
    ON jm.jobno = p.job
    AND jm.code = p.code
LEFT JOIN dbo.[Working Codes] wc
    ON p.code = wc.code
INNER JOIN dbo.JOBFILE j
    ON j.JOB_NO = p.job
WHERE (wc.brcode <> 4 OR @BmtDb = 0)
GROUP BY p.job, p.charge_code, p.code, p.ponumber, p.billable, p.[date], jm.markup, wc.brcode

This query will practically never finish running. It actually times out for some larger jobs we have.

And if I change the 2 subqueries in the select to read like joins instead:

SELECT
    @userid, p.job, p.charge_code, p.code
    , (SELECT SUM(b.TOTAL))
    , ISNULL(jm.markup, 0)
    , (SELECT SUM(b.TOTAL_TAX))
    , p.ponumber, p.billable, p.[date]
FROM dbo.PO p
INNER JOIN dbo.JobCostFilter jcf 
    ON p.job = jcf.jobno AND p.charge_code = jcf.chargecode AND jcf.userno = 11190030
INNER JOIN [BACKORDER W/TOTAL] b
    ON P.PONUMBER = b.ponumber AND P.code = b.code
LEFT JOIN dbo.JobMarkup jm
    ON jm.jobno = p.job
    AND jm.code = p.code
LEFT JOIN dbo.[Working Codes] wc
    ON p.code = wc.code
INNER JOIN dbo.JOBFILE j
    ON j.JOB_NO = p.job
WHERE (wc.brcode <> 4 OR @BmtDb = 0)
GROUP BY p.job, p.charge_code, p.code, p.ponumber, p.billable, p.[date], jm.markup, wc.brcode

The data comes out looking very nearly identical to me (though there are thousands of lines overall so I could be wrong), and it runs very quickly.

Any ideas appreciated..

Upvotes: 0

Views: 146

Answers (2)

Martin
Martin

Reputation: 1622

In terms of correctness, you are inner joining [BACKORDER W/TOTAL] in the second query, so if the first query has Null values in the subqueries, these rows would be missing in the second query.

For performance, the optimizer is a heuristic - it will sometimes use spectacularly bad query plans, and even minimal changes can sometimes lead to a completely different query plan. Your best chance is to compare the query plans and see what causes the difference.

Upvotes: 1

Milos Radivojevic
Milos Radivojevic

Reputation: 36

  • Performace

In the second query you have less logical reads because the table [BACKORDER W/TOTAL] has been scanned only once. In the first query two separate subqueries are processed indenpendent and the table is scanned twice although both subqueries have the same predicates.

  • Correctness

If you want to check if two queries return the same resultset you can use the EXCEPT operator:

If both statements:

First SELECT Query... EXCEPT Second SELECT Query...

and

Second SELECT Query.. EXCEPT First SELECT Query...

return an empty set the resultsets are identical.

Upvotes: 2

Related Questions