Använd tablesample istället för newid() för slumpade rader

Den vanligaste lösningen för att returnera ett slumpmässigt urval från en tabell är att använda sig av ”order by newid()”.
Problemet med newid() är att den tar alla poster från hela tabellen, lägger på newid()-värdet, stoppar in det i en temptabell och sorterar. Denna lösning gör att det kan bli prestandaproblem, speciellt om tabellen är stor.
I mitt exempel har jag skapat en tabell med 1 miljon poster som jag vill hämta 10 slumpmässigt utvalda poster från.

SELECT TOP 10 username FROM users ORDER BY NEWID()
--Table 'users'. Scan count 1, logical reads 3495, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
--CPU time = 826 ms,  elapsed time = 817 ms.
--
--

Med sql-server 2005 kom TABLESAMPLE som på ett effektivt sätt returnerar bara ett subset av den riktiga tabellen. Man kan själv välja mellan att hämta en viss procent av tabellen, eller antal rader.

Om vi bara lägger till TABLESAMPLE på 10000 rader och behåller NEWID() så får vi följande prestanda

SELECT TOP 10 username FROM users TABLESAMPLE (10000 ROWS) ORDER BY NEWID()
--Table 'users'. Scan count 1, logical reads 29, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
--SQL Server Execution Times:   CPU time = 0 ms,  elapsed time = 8 ms.
--
--

Notera att den går från ungefär 3500 reads till 29. Samt från 817 ms till 8 ms.

Om vi dessutom tar bort sorteringen av NEWID() så blir det ännu bättre prestanda.

SELECT TOP 10 username FROM users TABLESAMPLE (10000 ROWS)
--Table 'users'. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
--SQL Server Execution Times:   CPU time = 0 ms,  elapsed time = 1 ms.
--
--

Anledningen till att jag hämtar så mycket som 10000 slumpmässiga rader är att det inte är exakt 10000 rader som kommer att hämtas. SQL-Server kommer att hämta x sidor som innehåller y rader. Baserat på antal rader i snitt per sida så kommer den att hämta vad den tror är tillräckligt för att nå upp till det antal rader man anger.
När jag testade med 500 rader istället för 10000 så hände det ungefär vid var 20:e körning att jag fick tillbaka färre eller inga poster.
När jag testade med 100 rader så hände det att jag fick poster vid ungefär var 3:e körning.

Såhär blev resultatet av fem körningar.

SELECT COUNT(*) FROM users TABLESAMPLE (500 ROWS) --285 rader
SELECT COUNT(*) FROM users TABLESAMPLE (500 ROWS) --866 rader
SELECT COUNT(*) FROM users TABLESAMPLE (500 ROWS) --0 rader
SELECT COUNT(*) FROM users TABLESAMPLE (500 ROWS) --570 rader
SELECT COUNT(*) FROM users TABLESAMPLE (500 ROWS) --296 rader
--
--

Ovanstående är baserat på min testdata och kan vara helt annorlunda vid ett annat tillfälle. Men slutsatsen blir ändå att man bör ta i rejält samt testa utförligt så att man vet att man helt säkert alltid får tillräckligt med rader.

Om man använder sig av sql-server 2000, som inte har stöd för tablesample så har microsoft en bra artikel om ett alternativt sätt att hämta slumpade poster som inte involverar en temptabell.
Läs mer om det här http://msdn.microsoft.com/en-us/library/cc441928.aspx

Kommentarer inaktiverade.

%d bloggare gillar detta: