时间:2021-07-01 10:21:17 帮助过:47人阅读
统计每个城市的最新10个产品本文采用了游标方法/Count查询/cross apply方法/row_number方法等等对比不难发现Group获取Top N场景时,可以首选row_number,游标cursor其次,另外两个就基本不考虑了
有产品表,包含id,name,city,addtime四个字段,因报表需要按城市分组,统计每个城市的最新10个产品,便向该表中插入了100万数据,做了如下系列测试:
- <br>CREATE TABLE [dbo].[products]( <br>[id] [int] IDENTITY(1,1) NOT NULL, <br>[name] [nvarchar](50) NULL, <br>[addtime] [datetime] NULL, <br>[city] [nvarchar](10) NULL, <br>CONSTRAINT [PK_products] PRIMARY KEY CLUSTERED <br>( <br>[id] ASC <br>)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] <br>) ON [PRIMARY] <br> <br>1、采用row_number方法,执行5次,平均下来8秒左右,速度最快。 <br> 代码如下:<pre class="brush:php;toolbar:false layui-box layui-code-view layui-code-notepad"><ol class="layui-code-ol"><li><br>select no, id,name,city <br>from (select no =row_number() over (partition by city order by addtime desc), * from products)t <br>where no< 11 order by city asc,addtime desc <br> <br>2、采用cross apply方法,执行了3次,基本都在3分5秒以上,已经很慢了。 <br> 代码如下:<pre class="brush:php;toolbar:false layui-box layui-code-view layui-code-notepad"><ol class="layui-code-ol"><li><br>select distinct b.id,b.name,b.city from products a <br>cross apply (select top 10 * from products where city = a.city order by addtime desc) b <br> <br>3、采用Count查询,只执行了两次,第一次执行到5分钟时,取消任务执行了;第二次执行到13分钟时,没有hold住又直接停止了,实在无法忍受。 <br> 代码如下:<pre class="brush:php;toolbar:false layui-box layui-code-view layui-code-notepad"><ol class="layui-code-ol"><li><br>select id,name,city from products a <br>where ( select count(city) from products where a.city = city and addtime>a.addtime) < 10 <br>order by city asc,addtime desc <br> <br>4、采用游标方法,这个最后测试的,执行了5次,每次都是10秒完成,感觉还不错。 <br> 代码如下:<pre class="brush:php;toolbar:false layui-box layui-code-view layui-code-notepad"><ol class="layui-code-ol"><li><br>declare @city nvarchar(10) <br>create table #Top(id int,name nvarchar(50),city nvarchar(10),addtime datetime) <br>declare mycursor cursor for <br>select distinct city from products order by city asc <br>open mycursor <br>fetch next from mycursor into @city <br>while @@fetch_status =0 <br>begin <br>insert into #Top <br>select top 10 id,name,city,addtime from products where city = @city <br>fetch next from mycursor into @city <br>end <br>close mycursor <br>deallocate mycursor <br>Select * from #Top order by city asc,addtime desc <br>drop table #Top <br> <br>通过上述对比不难发现,在面临Group获取Top N场景时,可以首选row_number,游标cursor其次,另外两个就基本不考虑了,数据量大的时候根本没法使用。 </li></ol></pre></li></ol></pre></li></ol></pre></li></ol></pre>