SQL query very slow and need to be improved

Question

I have a select query that I need to improve to make it fast.

SELECT 
    st.SigHistUID, st.RelationType, st2.RelationType, 
    case 
       when st.RelationType <> st2.RelationType 
          then 'N' 
          else st.RelationType 
    end as [NewRelationType],
    st.ToSigUID , sh.FullDescription, sh.SigHistUID AS ToSigHistUID
FROM 
    [Stackability] (nolock) st
INNER JOIN 
    [SignatureHistory] (nolock) sh ON sh.SigUID = st.ToSigUID
                                    AND sh.SigHistUID = (SELECT TOP 1 SigHistUID FROM [SignatureHistory] (nolock) tmp where tmp.SigUID = sh.SigUID ORDER BY SigHistUID DESC)
INNER JOIN 
    [SignatureHistory] (nolock) sh2 ON st.SigHistUID = sh2.SigHistUID
INNER JOIN 
    Stackability (nolock) st2 on st2.ToSigUID = sh2.SigUID and st2.SigHistUID = sh.SigHistUID
WHERE 
    st.SigHistUID < 1000 and
    st.RelationType <> st2.RelationType
ORDER BY 
    st.SigHistUID

This is **way too little** information! What are the table structures? What kind of indexes are defined on those tables?? What kind of data (in terms of **volume**) does each table have?? — marc_s, Jun 29 '17 at 15:27
At a very minimum you should [include the actual Execution Plan](https://stackoverflow.com/a/7359705/1260204), you could use [paste the plan](https://www.brentozar.com/pastetheplan/) and share the link in your question. Also [try to read it yourself](https://stackoverflow.com/a/759097/1260204), maybe you can figure out the performance issue(s) with your query. Finally include the [schema ddl](https://en.wikipedia.org/wiki/Data_definition_language) along with the query you are executing. — Igor, Jun 29 '17 at 15:33
Depending on the size of SignatureHistory, your subquery could be slowing it down on the sh join due to the subquery results not being indexed. Maybe find a way to avoid that subquery. — justiceorjustus, Jun 29 '17 at 15:37
Side note: I see you are using `(nolock)` on all your tables. I hope you understand those implications. If you are not sure [review them here - When should you use “with (nolock)”](https://stackoverflow.com/q/686724/1260204) — Igor, Jun 29 '17 at 15:53
Thank you so much everyone for your responses. I have a last question, does someone know how to write this when stack.RelationType <> ISNULL(stack_recip.RelationType, '-') in mybatis? — N. Leroy, Jun 29 '17 at 16:08

cloudsafe · Answer 1 · 2017-06-29T16:17:34.883

Try having taken out the top 1 condition:

SELECT 
    st.SigHistUID, st.RelationType, st2.RelationType, 
    case 
       when st.RelationType <> st2.RelationType 
          then 'N' 
          else st.RelationType 
    end as [NewRelationType],
    st.ToSigUID , sh.FullDescription, sh.SigHistUID AS ToSigHistUID
FROM 
    [Stackability] (nolock) st
INNER JOIN 
    (select distinct siguid, max(SigHistUID) OVER(PARTITION BY siguid ) as  SigHistUID from [SignatureHistory] (nolock))  sh ON sh.SigUID = st.ToSigUID

INNER JOIN 
    [SignatureHistory] (nolock) sh2 ON st.SigHistUID = sh2.SigHistUID
INNER JOIN 
    Stackability (nolock) st2 on st2.ToSigUID = sh2.SigUID and st2.SigHistUID = sh.SigHistUID
WHERE 
    st.SigHistUID < 1000 and
    st.RelationType <> st2.RelationType
ORDER BY 
    st.SigHistUID

SQL query very slow and need to be improved

1 Answers1