Optimize select Query performance that is having join with various other tables in MS SQL

Question

I have a select query that retrieve huge amount of data based upon some joins with other tables and all the tables are being used by other processes(Some of them are writing data to these tables and some other are retrieving from). The simultaneous operations put locks on the tables.

Is there any way in the select query that can optimize the query response time even there is an write/Shared lock on the table? Can "With (NOLOCK)" with table help?

Thanks Manoj

Answer 1

You can try these options too
- Remove unnecessary left joins
- Remove where clause if it can be used along with Inner join condition - May create indexes on your columns - Select desired columns, avoid using * for all columns - Avoid giving large lengths for your columns

Answer 2

With (NOLOCK) will improve the performance but it will give you dirty reads which are not committed yet. Idealy this is not recommended on transactional tables, if you are fine with this dirty reads, you can use it,

And other optimizations are like maintain proper indexes on tables columns which are being used in joins. and the other one point is join the tables from small to bigger in data, and fucntion call in select clause and where clause.

hope this will help you!