lundi 8 mai 2017

get random number of Row from MS SQL Table

I am trying to get 5 random number of rows from a large table (over 1 million rows) with a fast method.

so far what I have tested with SQL Queries

Method 1

Select TOP 5 customer_id, customer_name 
from Customer TABLESAMPLE(1000 rows) 
order by newid()

This method Estimated I/O cost is 0.0127546 so this is very fast (Index scan NonClustered)

Method 2

select top 5 customer_id, customer_name 
from Customer 
order by newid()

This method's Sort Estimated I/O cost is 117.21189 and Index scan NonClustered Estimated I/O cost is 2.8735, so this is effecting performance

Method 3

select top 5 customer_id, customer_name 
from Customer 
order BY rand(checksum(*))

This method's Sort Estimated I/O cost is 117.212 and Index scan NonClustered Estimated I/O cost is 213.149, this query is slower than all because Estimated Subtree cost is 213.228 so it's very slow.

How can I convert Method 1 to SQL to Entities or SQL to LINQ, or please suggest me any better approach which I can use with EF. Thanks




Aucun commentaire:

Enregistrer un commentaire