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