Home Forums SQL Server 2017 SQL Server 2017 - Administration Tool for SQL Server code tuning RE: Tool for SQL Server code tuning

  • November 22, 2017 at 2:42 pm

    #1969277

    ffarouqi - Wednesday, November 22, 2017 10:01 AM

    Hello experts,

    Is there a free or a paid tool/software that can be used to provide t-sql recommendations or that can provide corrections to the t-sql statement to optimize the code for e.g see below...suggesting some changes to use "in" instead of "exists". I knew of a tool that was used to do this in the past and it was "Lecco SQL Expert". However, this isn't available anymore. Is there an alternative SQL tool that can be a paid tool or free which can do this.

    SELECT
    some_data
    FROM details_table D
    WHERE EXISTS (SELECT
    1
    FROM lookup_table L
    WHERE L.lookup_column = D.lookup_column
    AND L.matching_column = 'Yes')
    can be written as
    SELECT /*+ FIRST_ROWS */
    some_data
    FROM details_table D
    WHERE lookup_column IN (SELECT
    L.lookup_column
    FROM lookup_table L
    WHERE L.matching_column = 'Yes')

    As a bit of a sidebar, if the lookup column on the lookup table is unique and has an index on it, both of the methods might suck compare to a good ol' fashioned INNER JOIN.  If you need to be able to answer "No" when not found, then both will be better than an outer join and, depending on the data and table structure, could be a tie with each other for performance.

    I guess my question here is... you seem to have some practical knowledge... why not just do the test yourself instead of using software that you'd have to test the recommendations of anyway?

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)