We have some SSRS reports which run slowly with large amounts of data.
We thought to do some query optimization in backend stored procedures in the reports.
My question is, is it good to add with (nolock) hints in stored procedures (only in select statements) for reporting purposes?
DB Locks of course are important to ensure you aren't getting 'dirty' data involved in your transactions. Still, there are conditions where you can safely use NOLOCK. For instance, if you must use a cursor ( best avoided, but sometimes necessary ), the cursor can be written to it picks up just the minimal key fields, under NOLOCK conditions, then inside the loop you read the tables as needed ( with locks ) using the cursor field values, carry out your changes, commit, and move to the next cursor rec to get you next set of keys ( under nolock ). It's asset if used wisely.
