Improve the efficiency of SQL query?

Question

Post reply

Improve the efficiency of SQL query?

Dom Horton

SSCrazy

Points: 2202
More actions
July 22, 2011 at 3:56 am

#100771

Hello,
I've got a relatively long-winded query containing a number of subqueries and I'm asking for advice on ways to improve it's efficiency. For anyone familiar with Crystal Reports, the query basically replicates a Report containing a number of subreports. Currently the performance of the subreport version is vastly superior to using the SQL version which has raised some eyebrows on a Crystal Report forum (for info the PK/FK relationship for all the subqueries is PROJECT.PROJECTID=subquerytable.PROJECTID).
Original code below:
SELECT PROJECT.PROJECTID, PROJECT.TITLE, PROJECT.REFERENCE, PROJECTSTATUS.PROJECTSTATUS, PROJECT.INPUT_DATE,
(SELECT MAX(INPUT_DATE) AS Expr1
FROM V_PROJECT_NOTE AS VPN
WHERE (PROJECT.PROJECTID = PROJECTID)) AS LastHeadline,
(SELECT MAX(CASE WHEN MODIFIED_DATE IS NULL THEN INPUT_DATE WHEN INPUT_DATE > MODIFIED_DATE THEN INPUT_DATE ELSE MODIFIED_DATE END)
AS Expr1
FROM ISSUE
WHERE (PROJECT.PROJECTID = PROJECTID)) AS LastNewIssue,
(SELECT MAX(ISSUENOTE.INPUT_DATE) AS Expr1
FROM ISSUE INNER JOIN
ISSUENOTE ON ISSUE.ISSUEID = ISSUENOTE.ISSUEID
WHERE (PROJECT.PROJECTID = ISSUE.PROJECTID)) AS LastIssueNote,
(SELECT MAX(modified_date) AS Expr1
FROM V_PROJECT_RISK_LAST_AMMENDED AS VPR
WHERE (PROJECT.PROJECTID = projectid)) AS LastRiskLogUpdate,
(SELECT MAX(INPUT_DATE) AS Expr1
FROM PROJECT_CHECKLIST AS PC
WHERE (PROJECT.PROJECTID = PROJECTID)) AS LastChecklistUpdate,
(SELECT MAX(input_date) AS Expr1
FROM V_PROJECT_ACTIVITY_LAST_AMMENDED AS VPA
WHERE (PROJECT.PROJECTID = projectid)) AS LastGANTTUpdate, V_PROJECT_KEYCONTACT.USERNAME AS KeyContact,
(SELECT USERPROFILE.USERNAME AS Supervisor
FROM USERPROFILE INNER JOIN
PROJECT_MEMBER AS SUPERVISOR ON USERPROFILE.USERID = SUPERVISOR.USERiD RIGHT OUTER JOIN
PROJECT_MEMBER AS KEYCONTACT ON SUPERVISOR.PROJECT_MEMBERID = KEYCONTACT.PARENTID
WHERE (PROJECT.PROJECTID = KEYCONTACT.PROJECTID) AND (KEYCONTACT.KEY_CONTACT = 1)) AS Supervisor
FROM PROJECT INNER JOIN
PROJECTSTATUS ON PROJECT.PROJECTSTATUSID = PROJECTSTATUS.PROJECTSTATUSID LEFT OUTER JOIN
V_PROJECT_KEYCONTACT ON PROJECT.PROJECTID = V_PROJECT_KEYCONTACT.PROJECTID
WHERE (V_PROJECT_KEYCONTACT.USERNAME IN ('aaa', 'bbb', 'ccc')) AND (PROJECTSTATUS.PROJECTSTATUS IN ('111', '222', '333')) AND (PROJECT.TITLE NOT LIKE 'xxx%')
AND (PROJECT.TITLE NOT LIKE 'yyy%') AND (PROJECT.TITLE NOT LIKE 'zzz%')
ORDER BY V_PROJECT_KEYCONTACT.USERNAME, PROJECT.INPUT_DATE
I've been advised to rewrite the query and move the subqueries down to the FROM clause. I don't believe this offers any benefit over the original as the execution plan is the same for both and similar reads/writes/cpu are observed in SQL Server Profiler. I am correct in this deduction?
Query below:
SELECT
PROJECT.PROJECTID,
PROJECT.TITLE,
PROJECT.REFERENCE,
PROJECTSTATUS.PROJECTSTATUS,
PROJECT.INPUT_DATE,
VPN.LastHeadline,
ISSUE.LastNewIssue,
ISSUE_NOTE.LastIssueNote,
VPR.LastRiskLogUpdate,
PC.LastChecklistUpdate,
VPA.LastGANTTUpdate,
V_PROJECT_KEYCONTACT.USERNAME AS KeyContact,
Supervisor.Supervisor
FROM PROJECT
INNER JOIN PROJECTSTATUS ON PROJECT.PROJECTSTATUSID = PROJECTSTATUS.PROJECTSTATUSID
LEFT OUTER JOIN V_PROJECT_KEYCONTACT ON PROJECT.PROJECTID = V_PROJECT_KEYCONTACT.PROJECTID
LEFT OUTER JOIN (
SELECT PROJECTID,
max(INPUT_DATE) AS LastHeadline
FROM V_PROJECT_NOTE
GROUP BY PROJECTID) AS VPN ON PROJECT.PROJECTID = VPN.PROJECTID
LEFT OUTER JOIN (
SELECT PROJECTID,
MAX(CASE
WHEN MODIFIED_DATE IS NULL THEN INPUT_DATE
WHEN INPUT_DATE > MODIFIED_DATE THEN INPUT_DATE
ELSE MODIFIED_DATE END) AS LastNewIssue
FROM ISSUE
GROUP BY PROJECTID) AS ISSUE ON PROJECT.PROJECTID = ISSUE.PROJECTID
LEFT OUTER JOIN (
SELECT ISSUE.PROJECTID,
max(ISSUENOTE.INPUT_DATE) AS LastIssueNote
FROM ISSUE
INNER JOIN ISSUENOTE ON ISSUE.ISSUEID = ISSUENOTE.ISSUEID
GROUP BY ISSUE.PROJECTID) AS ISSUE_NOTE ON PROJECT.PROJECTID = ISSUE_NOTE.PROJECTID
LEFT OUTER JOIN (
SELECT PROJECTID,
max(modified_date) AS LastRiskLogUpdate
FROM V_PROJECT_RISK_LAST_AMMENDED
GROUP BY PROJECTID) AS VPR ON PROJECT.PROJECTID = VPR.PROJECTID
LEFT OUTER JOIN (
SELECT PROJECTID,
max(INPUT_DATE) AS LastChecklistUpdate
FROM PROJECT_CHECKLIST
GROUP BY PROJECTID) AS PC ON PROJECT.PROJECTID = PC.PROJECTID
LEFT OUTER JOIN (
SELECT PROJECTID,
max(input_date) AS LastGANTTUpdate
FROM V_PROJECT_ACTIVITY_LAST_AMMENDED
GROUP BY PROJECTID) AS VPA ON PROJECT.PROJECTID = VPA.PROJECTID
LEFT OUTER JOIN (
SELECT PROJECTID,
USERPROFILE.USERNAME AS Supervisor
FROM USERPROFILE
INNER JOIN PROJECT_MEMBER AS SUPERVISOR ON USERPROFILE.USERID = SUPERVISOR.USERID RIGHT
OUTER JOIN PROJECT_MEMBER AS KEYCONTACT ON SUPERVISOR.PROJECT_MEMBERID = KEYCONTACT.PARENTID
WHERE KEYCONTACT.KEY_CONTACT =1) AS Supervisor ON PROJECT.PROJECTID = Supervisor.PROJECTID
WHERE V_PROJECT_KEYCONTACT.USERNAME IN ('aaa','bbb','ccc')
AND (PROJECTSTATUS.PROJECTSTATUS IN ('111', '222', '333'))
AND ((PROJECT.TITLE NOT LIKE 'xxx%')
AND (PROJECT.TITLE NOT LIKE 'yyy%')
AND (PROJECT.TITLE NOT LIKE 'zzz%'))
ORDER BY V_PROJECT_KEYCONTACT.USERNAME, PROJECT.INPUT_DATE
thanks in advance,
Dom

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

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

spaghettidba SSC Guru Points: 105732 More actions · Answer 1

It's very hard to tell without table scripts, indexes script and actual execution plan.

Can you post the above?

If in doubt, you can check this article[/url] to see how to gather all the information and post it here.

-- Gianluca Sartori

Dom Horton SSCrazy Points: 2202 More actions · Answer 2

Hi,

I thought that may put people off!

Unfortunately I do not have permission to save execution plans on this db (its a third party one..we only have read access).

With regards to table defs. I can post info but would rather not due to it being a third party application.

Basically at this stage I wish to know if, on the face of it, is my SQL logic sound? Are the two SQL versions logically equivalent? and, can it theoretically be improved?

If on the otherhand no improvements to the code can be made then I'll look at approaching the third party company to discuss indexes/views/sps and the like.

thanks,

Dom

Jayanth_Kurup SSC-Insane Points: 22967 More actions · Answer 3

overall without the table def and execution plan the only thing i can think of is the where clause where your using the like operator

maybe you cud try something like Left(PROJECT.TITLE ,3) != 'xxx'

Jayanth Kurup[/url]

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

Jayanth_Kurup (7/22/2011)
overall without the table def and execution plan the only thing i can think of is the where clause where your using the like operator
maybe you cud try something like Left(PROJECT.TITLE ,3) != 'xxx'

The LIKE is SARGable, the LEFT is not.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

Dom Horton (7/22/2011)
Basically at this stage I wish to know if, on the face of it, is my SQL logic sound? Are the two SQL versions logically equivalent?

In this particular case, yes.

The reason that the suggestion to move subqueries into the FROM is usually given is that you can then reference the columns from the subquery more than onece. If you have subqueries in the SELECT and you need to reference the columns more than once you have to duplicate the subquery and the optimiser isn't smart enough to figure out that it could run the subquery only once, if it's in the SELECT or FROM more than once it's run more than once.

I can't see anything obvious that can be improved, and without the exec plan or index/table defs it's very hard. Make sure that you have indexes to support the where clause and joins. Other than that, not really sure.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Dom Horton SSCrazy Points: 2202 More actions · Answer 6

Dom Horton

SSCrazy

Points: 2202

July 22, 2011 at 5:23 am

#1356951

Thanks Gail for the info.

Much appreciated.

Jayanth_Kurup SSC-Insane Points: 22967 More actions · Answer 7

Jayanth_Kurup (7/22/2011)
overall without the table def and execution plan the only thing i can think of is the where clause where your using the like operator
maybe you cud try something like Left(PROJECT.TITLE ,3) != 'xxx'

Sorry my mistake , but it wouldn't make much difference if there's no index on this column.

Jayanth Kurup[/url]

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

Dom, can you confirm which version of SQL Server you are connecting to? Cheers.

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

Dom Horton SSCrazy Points: 2202 More actions · Answer 9

Dom Horton

SSCrazy

Points: 2202

July 22, 2011 at 5:52 am

#1356975

version 2000,

thanks