Help to optimize select query

  • Need help to rewrite the query for faster result.

    use ABC_DB;

    SELECT c.otherID, a.shortname, c.priority, c.amended_date, cast(c.SomeVarcharMaxColumn as xml)

    FROM [ABC_Schema].[ABC1_Table] a WITH (NOLOCK),

    [ABC_Schema].[ABC2_Table] b WITH (NOLOCK),

    [PQR_Schema].[PQR1_table] c WITH (NOLOCK)


    cc.ManagerId = c.Id

    and a.ManagerId = c.ManagerId

    and a.shortname = 'ABC'

    and c.amended_date >= cast('2018-10-01 00:00:00' as DATE) --(amended_date is the datetime column and having 9 years data)

    and c.amended_date <= cast('2018-10-30 00:00:00' as DATE) --(amended_date is the datetime column and having 9 years data)

    and c.priority >7-- ( b.priority column has values from 1 to 16)

    order by c.amended_date desc

    here [PQR_Schema].[PQR1_table] is 120GB table ; rest 2 are less than 1 GB tables

    creating any index isn't an option as we are thinking just not to disturb any other inserts/updates/delete. db maintenance jobs(index rebuild/reorg/stats) jobs are regularly running fine. Can we tune it just rewriting?

    • This topic was modified 4 years, 7 months ago by  U J.
  • You have include the table [ABC_Schema].[ABC2_Table] b - with no join predicates (cross join) and you don't return any columns from that table or filter by any results in that table.

    If it isn't needed - remove it and that should improve performance.

    I would also recommend changing the query to use the ON clause in your joins - this will make the query easier to read and will be easier to identify how the tables are related.

    FROM ABC_Schema.ABC1_Table a 
    INNER JOIN ABC_Schema.ABC2_Table b ON {???}
    INNER JOIN PQR_Schema.PQR1_table c ON c.ManagerID = a.ManagerID
    WHERE ...

    You mention that b.priority has certain values but reference c.priority - which one is actually used?  If b.priority is actually utilized then you definitely need to include the relationship on that table.

    Jeffrey Williams
    “We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

    ― Charles R. Swindoll

    How to post questions to get better answers faster
    Managing Transaction Logs

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply