Help to optimize select query

Question

Post reply

Help to optimize select query

U J

SSCrazy

Points: 2423
More actions
October 18, 2019 at 5:19 pm

#3690242

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)
where
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.

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

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

DesNorton SSC-Insane Points: 24399 More actions · Answer 1

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

How to post data/code on a forum to get the best help.

Chris Harshman SSC-Forever Points: 42192 More actions · Answer 2

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.

U J SSCrazy Points: 2423 More actions · Answer 3

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)

DesNorton SSC-Insane Points: 24399 More actions · Answer 4

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

How to post data/code on a forum to get the best help.