从SQL Server表中获取随机的行数 [英] Get random number of rows from SQL Server table

问题描述

我正试图通过一种快速方法从一个大表(超过一百万行)中随机获得5行.

到目前为止，我已经使用以下SQL查询进行了测试:

方法1

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

此方法估计的I/O成本为0.0127546，因此速度非常快(不进行索引扫描)

方法2

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

此方法的排序估计I/O成本为117.21189，而索引扫描非聚集估计I/O成本为2.8735，因此这会影响性能

方法3

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

此方法的排序估计I/O成本为117.212，而索引扫描非群集估计I/O成本为213.149，此查询的速度比所有查询都要慢，因为估计的子树成本为213.228，因此非常慢.

更新:

方法4

select top 5 customer_id, customer_name, product_id
from Customer 
Join Product on product_id = product_id
where (customer_active = 'TRUE')
order by checksum(newid())

此方法更好且非常快速.所有基准测试都很好.

问题

如何将方法4 转换为LINQ-to-SQL?谢谢

解决方案

如果要将方法2 转换为Linq To Entities，只需使用jitender回答的解决方案，如下所示:

var randomCoustmers = context.Customers.OrderBy(x => Guid.NewGuid()).Take(5);

但是对于基准测试之后非常快的方法1 ，您需要执行以下C#代码，因为Linq To Entities没有与此SQL语句TABLESAMPLE(1000 rows)等效的LINQ. /p>

var randomCoustmers = context.Customers.SqlQuery("Select TOP 5 customer_id, customer_name from Customer TABLESAMPLE(1000 rows) order by newid()").ToList();

您可以将SQL语句移到SQL视图或存储过程中，该视图或存储过程将接收要接受的客户数量.

更新

对于看来非常快的方法4 (始终遵循基准)，您可以执行以下Linq To Entities:

var randomCoustmers = context.Customers.OrderBy(c => SqlFunctions.Checksum(Guid.NewGuid()).Take(5);

实体框架可以将定义为 解决方案

If you want to convert Method 2 into Linq To Entities just use the solution answered by jitender which look like this:

var randomCoustmers = context.Customers.OrderBy(x => Guid.NewGuid()).Take(5);

But for Method 1 which is very fast following your benchmarking, you need to do the following C# code because Linq To Entities doesn't have a LINQ equivalent for this SQL statement TABLESAMPLE(1000 rows).

var randomCoustmers = context.Customers.SqlQuery("Select TOP 5 customer_id, customer_name from Customer TABLESAMPLE(1000 rows) order by newid()").ToList();

You can move the SQL statements into a SQL View or Stored Procedure which will receive the number of customers to take.

UPDATE

For Method 4 which seems to be very fast (always by following your benchmark), you can do the following Linq To Entities:

var randomCoustmers = context.Customers.OrderBy(c => SqlFunctions.Checksum(Guid.NewGuid()).Take(5);

Entity Framework can translate into SQL all functions that are defined into SqlFunctions class. In those functions we have Checksum function which will do what you want.

If you want to join with other tables you can do it without difficulty with Linq To Entites so I just simplified my version by querying only the Customers DbSets.

这篇关于从SQL Server表中获取随机的行数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！