Can anyone help me improving the performance of the query in a better way?

Question

I am using below query as a dataset to fetch a document body and some other details to print on a SSRS report. This query is consuming more time than expected to execute. Can anyone help me with a better way to optimize the query. Any help is greatly appreciated.

SELECT DISTINCT ROW_NUMBER() OVER (PARTITION BY LA.table5id
ORDER BY APP.subDate ASC) AS rownum,
APP.name1 AS name, APP.subDate AS date, AN.documentbody,
APP.sgntry AS APPSgn
FROM Table1(nolock) AS AN INNER JOIN
Table2(nolock) AS DM ON DM.table2id = AN.table1id INNER JOIN
Table3(nolock) AS ID ON (ID.table3id = DM.typeid) INNER JOIN
Table4(nolock) APP ON DM.apcntid = APP.table4id INNER JOIN
Table5(nolock) LA ON APP.appid = LA.table5id INNER JOIN
Table6(nolock) BA ON LA.table5id = BA.appnid
WHERE ID.code = 'AB' AND APP.sgntry = 1 AND AN.documentbody IS NOT NULL AND LA.appNo = 'APN-12345'
ORDER BY APP.subDate

Answer 1

Hi @Srinivas Kamutam ,

It could be better for you to provide the execution plan, table structure of all 6 tables, together with some sample data, index details.

Only according to your query provided, please refer below tips and check whether it is helpful to you:

1. Make sure all columns in where condition and select part have clustered index or non-clustered index.
2. Consider to remove the DISTINCT/order by.
3. Consider to remove the nolock hint.

Best regards
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.