I have a SQL table which contains 20 million+ rows. I need to randomly pull records from this table to update them.
I have the following query that I run for this:
SELECT top 1 @userId = userId
where location is null
order by newid()
This query is in a SQL procedure that is called from c#. When I run a signal thread, this query takes less than half a second to complete. However, when I have 10 threads going, it takes 6 seconds for each thread.
When I remove “ORDER BY NEWID()” the duration indicates 0. However, I end up with all 10 threads working on the same user.
I got these numbers by using SQL profiler.
Each thread gets its own SQL Connection. I leave the connection open for each thread so a new one is not created each time.
Does anyone have any suggestions on a faster method to do this?