script optimization in sql

Question

I am running simple select script, which inner join with other 3 table . all the tables are big ( lots of data ) its taking around 20 sec to run. want to optimized it.

I tried to used nolock , but not much deference

SELECT RR.ReportID,
       RR.RequestFormat,
       RRP.SequenceNumber,
       RRP.ParameterName,
       RRP.ParameterValue
       CASE WHEN RP.ParameterLabelOvrrd IS NULL THEN P.ParameterLabel ELSE .ParameterLabelOvrrd END AS ParameterLabelChosen,
       RRP.ParameterValueEntered
FROM ReportRequestParameters AS RRP WITH (NOLOCK)
     INNER JOIN ReportRequests AS RR WITH (NOLOCK) ON RRP.RequestID = RR.RequestID
     INNER JOIN ReportParameter AS RP WITH (NOLOCK) ON RP.ReportID = RR.ReportID
                                                   AND RP.SequenceNumber = RRP.SequenceNumber
     INNER JOIN Parameter AS P WITH (NOLOCK) ON P.ParameterID = RP.ParameterID
WHERE RRP.RequestID = '2226765'
ORDER BY SequenceNumber;

Please advice.

The with (nolock) suggests SQL Server, so I added the tag. — Gordon Linoff
– Gordon Linoff, Commented Apr 26, 2019 at 15:05
What is your reason for choosing to use WITH (NOLOCK) against every table here? If it's to "improve" the performance then that isn't what WITH (NOLOCK) does and you should remove them all (your question suggests that's the reason). Your SQL is also malformed as you're missing a comma after ParameterValue, and .ParameterLabelOvrrd is missing the table alias. The quickest way for you to help us help you is for you to post the DDL of your tables, along with any indexes. It will likely be worth while giving us a copy of your query plan too. — Thom A
– Thom A ♦, Commented Apr 26, 2019 at 15:10

Gordon Linoff · Accepted Answer · 2019-04-26 15:15:40Z

1

This is your query:

SELECT RR.ReportID, RR.RequestFormat, RRP.SequenceNumber, 
       RRP.ParameterName,  RRP.ParameterValue 
       COALESCE(RP.ParameterLabelOvrrd, P.ParameterLabel) as ParameterLabelChosen,
       RRP.ParameterValueEntered
FROM ReportRequestParameters RRP JOIN
     ReportRequests RR 
     ON  RRP.RequestID = RR.RequestID JOIN
     ReportParameter RP 
     ON RP.ReportID = RR.ReportID AND
        RP.SequenceNumber = RRP.SequenceNumber JOIN
     Parameter P 
     ON P.ParameterID = RP.ParameterID
WHERE RRP.RequestID = 2226765
ORDER BY RRP.SequenceNumber;

I have removed the single quotes on 2226765, assuming that the id is a number. Mixing types can impede the optimizer.

Then, I recommend an index on ReportRequestParameters(RequestID, SequenceNumber). I assume the other tables have indexes on the appropriate columns, but these are:

ReportRequests(RequestID, ReportID, SequenceNumber)
ReportParameter(ReportID, SequenceNumber, ParameterID)
Parameter(ParameterID)

I strongly advise you not to use nolock, unless you know what you are doing. Aaron Bertrand has a good blog post on this subject.

edited Apr 26, 2019 at 15:15

answered Apr 26, 2019 at 15:10

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Yogesh Sharma Over a year ago

. . . I would preferred ISNULL instead of COALESCE().

Gordon Linoff Over a year ago

@YogeshSharma . . . And I don't. In this case, there is no performance difference and COALESCE() is a standard function.

Yogesh Sharma Over a year ago

. . . . In my experience i have faced performance difference so.

Thom A Over a year ago

Depends on the datatypes of RP.ParameterLabelOvrrd & P.ParameterLabel too, @YogeshSharma. The behaviour between the 2 is not the same, and COALESCE will produce the same behaviour that the OP currently has. ISNULL may not.

iainc · Accepted Answer · 2019-04-26 15:10:23Z

0

I would suggest running with the execution plan turned on and see if SSMS can advise you on additional indexing.

Other than that your query looks straight-forward, nothing code wise that is going to help make it faster, other than perhaps getting rid of the case statement and definitely getting rid of the NOLOCK statements.

answered Apr 26, 2019 at 15:10

iainc

8687 silver badges21 bronze badges

Collectives™ on Stack Overflow

script optimization in sql

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related