I have a quite complex Reporting View using other views.
The output of this view is being sent over a v.slow network connection.
It is causing other apps on that DB that write to the DB to slow down. It's is Locking rather than I/O issue.
Are the locks held for the time it takes to run the query only or also the time taken to return results to the client?
i.e. is the client's connection speed relevant?
If I were to change it to a SP, is it worth selecting everything First into a temp table and then returning the select from temp table. So only the temp table is locked while the results are sent.
I think the cursor/recordset at the client end is readonly, not the dynamic sort (notify of changes)
create proc mySP
Select fieldlist INTO #t from (reproduce view's joins and logic)
SELECT * FROM #t
I think it should not
make a difference, but wanted to check to clear up my understanding.
P.S. I do NOT intend to use NOLOCK