0

How would I go about tuning the following t-sql query? The execution plan looks ok (i.e it is using index seeks, index scans). 

SELECT 
    CodeID = CL.codeID,
    CodeName = PR.PetTypeCode,
    DisplayName = PR.PetTypeDisplayName,
    AdditionalOptionID = coalesce(currAID.aidID, AID.aidID, 0),
    AdditionalOptionDescription = case
                                     when currAID.aidID is not null 
                                        then currAID.Description
                                     when AID.aidID is not null 
                                        then AID.Description
                                     else ''''
                                   end,
    TransactionCodeID = case
                           when currAID.aidID is not null 
                              then currAID.trancID
                           when AID.aidID is not null 
                              then AID.trancID
                           else 0
                        end,
    Amount = case
                when currAID.aidID is not null 
                   then convert(numeric(10, 2), currAID.TransactionAmount)
                when AID.aidID is not null 
                   then convert(numeric(10, 2), AID.TransactionAmount)
                else 0.00
             end,
    FrequencyCode = 'M',AllowOverrideBit = '0'
FROM
    PetRents PR with (nolock) 
INNER JOIN
    CodeLookup CL with (nolock) ON CL.codeCodeName = PR.PetTypeCode
LEFT JOIN
    AdditionalItemDetail AID with (nolock) ON PR.AdditionalOptionID = AID.aidID
LEFT JOIN
    AdditionalItemDetail currAID with (nolock) ON AID.CurrentID = currAID.aidID
WHERE
    CL.codeDisplayBit = 1 
    AND CL.className = 'Pettypes' 
    AND PR.DisabledBit = 0 
    AND PR.PetTypeAllowedBit = 1

Overall query execution plan Query 1 properties Query 2 properties Query 3 properties Query 4 properties

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Ajit Goel
  • 4,180
  • 7
  • 59
  • 107
  • What query? What table definitions? What execution plan? – Martin Smith Jul 01 '16 at 20:35
  • @Martin Smith: Stackoverflow did not like my formatting so I was trying to get it right by posting parts that worked and then editing the post. Please have a look. – Ajit Goel Jul 01 '16 at 20:40
  • 2
    I don't see anything wrong with the query. Why do you want to "tune" it? Are you getting bad performance? – Siyual Jul 01 '16 at 20:52
  • Yeh, this query shows up as a second biggest "hotspot" in our slow running application. – Ajit Goel Jul 01 '16 at 20:59
  • 2
    Set [Bad Habits to kick - putting NOLOCK everywhere](http://blogs.sqlsentry.com/aaronbertrand/bad-habits-nolock-everywhere/) - it is *not recommended* to use this everywhere - quite the contrary! – marc_s Jul 01 '16 at 21:52
  • How long does that take to execute? – Martin Smith Jul 01 '16 at 22:44
  • You are getting all index scans or index seeks. I don't think it is going to get better. Make sure you indexes are defragmented. You say application. How are you reading this data and how much? Faster disk, more memory, more CPU? – paparazzo Jul 01 '16 at 22:45
  • 1
    Define "hotspot". It may identified due to this query being run multiple times. When assessed individually however the query is OK; except perhaps for the (over)use of `nolock`. Put simply there is almost no scope for "tuning" in that query as it is presented and without any functional context/knowledge. – Paul Maxwell Jul 02 '16 at 01:33
  • @AjitGoel so you are trying to "optimize" query that reads from tables with: 26 rows (2 rows after applying filter after scan), 2 rows (with estimation of 6 rows)? It scans now not because of no appropriate index - it scans because it's too small to look for any workaround, to make choice of some indexes. Tables are smaller than granularity of physical reads. – Ivan Starostin Jul 02 '16 at 09:14

0 Answers0