It was a nightmare finding a solution to my MS SQL problem. Once I finally found the answer I decided I must share with the world just in case you are struggling too. The problem is that with large tables in MS SQL using the below Query to return a random row is VERY SLOW.
SELECT TOP 1 column FROM table ORDER BY NEWID()
In my table of 13 Million, and growing, records this will take about 10 min’s to execute. This is not acceptable. MS SQL has some cool things, but also some very silly things. Why they don’t have LIMIT X, N like MySQL, makes no sense to me. Anyway. The solution was to find the Max ID in the database and find a random in that range. This surprisingly turns out to be very fast. This was a workable solution for me although it is NOT TRULY random.
SELECT TOP 1 Thing FROM thingsdb.dbo.things WHERE ThingID >= RAND() * (SELECT MAX(ThingID) FROM thingsdb.dbo.thing )
