locked
SQL query performance question RRS feed

  • Question

  • Hi All, 

    Recently, I have undergone a computer based exam where in they asked below question.Need some inputs from experts.

    1. Below correlated query is causing performance issue. Please explain why it is causing performance issue and optimize the query.

    SELECT DISTINCT col1 FROM tab1 WHERE col2 = 'A' AND NOT EXISTS (SELECT 'x’ from tab2 where tab2.col2 = 'B’ and tab2.col1 = tab1.col1)

    I don't any idea on how many rows each table has or what indexes does exist on the tables. Looking at the above question can anyone tell this is a bad query or how can we re-write the query to perform better ?

    Thanks,

    Sam

    Thursday, November 29, 2018 6:34 AM

All replies

  • Hi Sam,

    Per your description,  I think it might be a good query. If you think it has a bad performance, it might have a lot of data or has bad indexes.

     

    In your original script , you use NOT EXISTS which usually has a good performance. You also can use NOT IN , LEFT JOIN or EXCEPT to  achieve your requirement. However, NOT EXISTS might have a good performance . For more details you can refer to this article  and you will see the difference among them clearly : https://www.sqlshack.com/t-sql-commands-performance-comparison-not-vs-not-exists-vs-left-join-vs-except/

     

    Also you can create some indexes on the columns in these two table's WHERE clause to improve your performance, like tab2.col2 or tab1.col2.

     

    If you have any question ,please let me know .

     

    Best Regards,

    Rachel


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, November 29, 2018 7:47 AM
  • Hi,

    So NOT EXISTS can cause the performance issue in case Large subset table correlated query. instead of NOT EXISTS you may go with JOIN which give sigificient difference in Query execution Plan.

    You can also choose the CTE instead of writting the correlated query which also be a best practice in term of query performance.

    HTH


    Hope this Helps!!!! Regards, Note: Please Mark As Answered if you satisfy with Reply.

    Thursday, November 29, 2018 8:31 AM
  • Compeltely useless question with the information given. Giv me query plans and the available indexes and I might be able to tell. But I'd say that with proper indexes in place this is a good query.

    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Thursday, November 29, 2018 8:35 AM
  • Hi All, 

    Recently, I have undergone a computer based exam where in they asked below question.Need some inputs from experts.

    1. Below correlated query is causing performance issue. Please explain why it is causing performance issue and optimize the query.

    SELECT DISTINCT col1 FROM tab1 WHERE col2 = 'A' AND NOT EXISTS (SELECT 'x’ from tab2 where tab2.col2 = 'B’ and tab2.col1 = tab1.col1)

    I don't any idea on how many rows each table has or what indexes does exist on the tables. Looking at the above question can anyone tell this is a bad query or how can we re-write the query to perform better ?

    Thanks,

    Sam

    Then its hard to suggest anything.

    Your first point should be to analyze the execution plan. That should give you an idea on the bottleneck steps within execution plan.

    I hope you've proper indexes on the columns used for comparison in the above query


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    • Proposed as answer by pituachMVP Thursday, November 29, 2018 12:39 PM
    Thursday, November 29, 2018 8:46 AM
  • Thank you all.
    Thursday, November 29, 2018 11:09 AM
  • Hi  Samantha v,

    You're welcome.

     

    We are glad to hear that they are helpful to you. If you have solved your issue ,please kindly mark the helpful replies as answers. By doing so, it will benefit all community members who are having this similar issue.  Your contribution is highly appreciated.

     

    Best Regards,

    Rachel


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, November 30, 2018 3:16 AM