Help to optimize select query

  • Need help to rewrite the query for faster performance.

    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.

    • I would start by replacing the old style joins with the new join style.  However, because your SQL references cc.ManagerId in your WHERE clause, and no tables have an alias of cc, it's not possible to know which fields to join on.
    • I would change the 2 CAST('xxx' as date) to CAST('xxx' as datetime) to match the data types of the tables

    Without seeing the structures of the tables and indexes, there is not much that can be looked at.

    Also, the actual execution plan is the best artifact to start looking for performance improvements

  • Can you tell us what indexes currently exist on the 3 tables?  If you could give us a .sqlplan file or .pesession file with the execution plan of the query it would give us a lot more to work with so we could help you.

  • Thank you Des and Chris for the help so far.. below is the query plan and table columns



    [ABC_Schema].[ABC1_Table] contain (shortname [varchar](50) +other columns

    [ABC_Schema].[ABC2_Table] contain ([OtherId] [varchar](50) NOT NULL,

    [priority] [smallint] NULL,

    [amended_date] [datetime] NOT NULL)

    +other columns

    [PQR_Schema].[PQR1_table] contain ([SomVarcharMaxColumn] [varchar](max) NOT NULL)

    + other columns

      |--Compute Scalar(DEFINE:([Expr1006]=CONVERT(xml,[PQR_Schema].[PQR1_table].[SomeVarcharMaxColumn] as [c].[SomeVarcharMaxColumn],0)))
    |--Nested Loops(Inner Join, OUTER REFERENCES:(.[Id], [Expr1010]) WITH UNORDERED PREFETCH)
    |--Hash Match(Inner Join, HASH:(.[Id])=([a].[ManagerId]), RESIDUAL:([ABC_DB].[ABC_Schema].[ABC1_Table].[ManagerId] as [a].[ManagerId]=[ABC_Schema].[ABC2_Table].[Id] as .[Id]))
    | |--Clustered Index Scan(OBJECT:([ABC_Schema].[ABC2_Table].[PK_ABC2_table] AS ), WHERE:([ABC_Schema].[ABC2_Table].[amended_date] as .[amended_date]>='2018-01-01' AND [ABC_Schema].[ABC2_Table].[amended_date] as .[amended_date]<='2018-10-30' AND [ABC_Schema].[ABC2_Table].[priority] as .[priority]>(8)))
    | |--Clustered Index Scan(OBJECT:([ABC_DB].[ABC_Schema].[ABC1_Table].[PK_shortname] AS [a]), WHERE:([ABC_DB].[ABC_Schema].[ABC1_Table].[shortname] as [a].[shortname]='ABC'))
    |--Clustered Index Seek(OBJECT:([PQR_Schema].[PQR1_table].[PK_PQR1_table] AS [c]), SEEK:([c].[ManagerId]=[ABC_Schema].[ABC2_Table].[Id] as .[Id]) ORDERED FORWARD)


  • You are not giving us much to work with.  But the 2 Clustered Index Scans are not doing you any favours

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

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