October 18, 2019 at 5:21 pm
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)
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. Can we tune it just rewriting?
October 18, 2019 at 6:07 pm
October 18, 2019 at 6:16 pm
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