3

I have a report that shows some data from the database and is quite slow. It takes around 2 minutes to finish. The problem is that while is executing the query is locking the table from which is getting the data. That locking makes some processes fail, like navision.

Is there any way to make it with a non blocking query?

Darren
  • 68,902
  • 24
  • 138
  • 144
Sinjuice
  • 532
  • 1
  • 4
  • 21

1 Answers1

5

You can pass a query hint and specify NOLOCK:

SELECT * FROM yourTable with (NOLOCK)
Darren
  • 68,902
  • 24
  • 138
  • 144
  • Alternatively if querying multiple tables, wrap your SQL in a transaction and use snapshot isolation: http://msdn.microsoft.com/en-us/library/tcbchxcb(v=vs.80).aspx – GShenanigan May 29 '12 at 12:26
  • And if you have to specify NOLOCK on many tables, you can also opt for [setting the transaction level](http://msdn.microsoft.com/en-us/library/ms173763.aspx), it's the [same thing with different scope](http://stackoverflow.com/questions/3550337/with-nolock-vs-set-transaction-isolation-level-read-uncommitted). – Jeroen Jun 16 '12 at 23:04