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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy