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 2 years, 1 month 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
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

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

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

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