-1

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

error_handler
  • 1,191
  • 11
  • 19

2 Answers2

0

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!

Saketh
  • 93
  • 6
  • 2
    Actually NOLOCK will NOT ONLY give him dirty reads, it may return data twice. Nolock also ignores locks during index page splits. Generally a big "no, never use it" except on read only tables. – TomTom Nov 18 '14 at 08:17
  • Thanks Saketh, I am having joins with proper Indexes con columns. Hope this will help me. Thanks – error_handler Nov 18 '14 at 12:56
0

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

Mahrukh Mehmood
  • 258
  • 2
  • 5
  • 17