SQL 查询优化.....

mc_dv 2017-07-29 12:03:13
SELECT Row,hotelName,hotelEnName,hotelCode,HotelLevel,CityID,cityName,addstate,addStateVal FROM
( SELECT ROW_NUMBER() OVER (order by T.hotelCode desc) AS Row, T.* from
(select f.* from (select d.*,isnull(e.addState,'未新增') as addstate,case e.addState when '已新增' then '1' else '0' end as addStateVal
from (select a.name as hotelName,a.enname as hotelEnName,a.code as hotelCode,a.hotellevel,a.cityId,b.name as cityName from mt_hotel a
left join syscity b on a.cityid=b.id ) d left join (select '已新增' as addState,hotelCode from hotelandhoteluser where supplierCode='MT0000016')
e on d.hotelCode=e.hotelCode ) f where 1=1 ) T where 1=1 ) TT WHERE TT.Row between 333333 and 333343



SELECT Row,hotelName,hotelEnName,hotelCode,HotelLevel,CityID,cityName,addstate,addStateVal FROM ( SELECT ROW_NUMBER() OVER (order by T.hotelCode desc)AS Row, T.* from
(select f.* from (select d.*,isnull(e.addState,'未新增') as addstate,case e.addState when '已新增' then '1' else '0' end as addStateVal
from (select a.name as hotelName,a.enname as hotelEnName,a.code as hotelCode,a.hotellevel,a.cityId,b.name as cityName from mt_hotel a
left join syscity b on a.cityid=b.id ) d left join (select '已新增' as addState,hotelCode from hotelandhoteluser where supplierCode='MT0000016') e on d.hotelCode=e.hotelCode )
f where 1=1 ) T where 1=1 ) TT WHERE TT.Row between 21 and 30


第一个耗时20多秒,第二个毫秒级别.. 语句一样,就页码不一样.. SQL菜鸟,求大神指导.

...全文
567 6 打赏 收藏 转发到动态 举报
AI 作业
写回复
用AI写文章
6 条回复
切换为时间正序
请发表友善的回复…
发表回复
正怒月神 2017-09-20
  • 打赏
  • 举报
回复
我这里单独测试了一下 数据格式 select top 10 * from [dbo].[Test] 总条数 select COUNT(1) from [dbo].[Test] 简化的查询语句
select * from (
	select ROW_NUMBER() over(order by pname desc ) rowid, * from Test ) A
where rowid between 21 and 30

select * from (
	select ROW_NUMBER() over (order by pname desc ) rowid, * from Test ) A
where rowid between 333333 and 333343
查询出来,是1秒之内就可以的。 那么应该和页码没有关系。 我觉得你试试把left join去掉,然后ROW_NUMBER() OVER (order by T.hotelCode desc) 换ct
繁花尽流年 2017-09-20
  • 打赏
  • 举报
回复
嵌套东西有点多,可以考虑先用作业定时固化数据,再单表分页查询,会好很多
顺势而为1 2017-09-20
  • 打赏
  • 举报
回复
子查询太多, 建议先将子查询放到临时表中


SELECT  Row,hotelName,hotelEnName,hotelCode,HotelLevel,CityID,cityName,addstate,addStateVal 
 FROM ( SELECT ROW_NUMBER() OVER (order by T.hotelCode desc) AS Row, 
              T.*  
		from (select f.* 
		      from (select d.*,isnull(e.addState,'未新增') as addstate,case e.addState when '已新增' then '1' else '0' end as addStateVal
                    from (select a.name as hotelName,a.enname as hotelEnName,a.code as hotelCode,a.hotellevel,a.cityId,b.name as cityName 
					      from mt_hotel a 
                          left join syscity b on a.cityid=b.id ) d  
						  left join  (select '已新增' as addState,hotelCode 
						              from hotelandhoteluser 
									  where supplierCode='MT0000016')  e on d.hotelCode=e.hotelCode ) f  
			              where 1=1 ) T 
				     where 1=1  ) TT 
			 WHERE TT.Row between 333333 and 333343
 
mc_dv 2017-07-29
  • 打赏
  • 举报
回复
二月十六 版主 2017-07-29
  • 打赏
  • 举报
回复
先看看执行计划一样吗
OwenZeng_DBA 2017-07-29
  • 打赏
  • 举报
回复
引用 楼主 a419816897 的回复:
SELECT Row,hotelName,hotelEnName,hotelCode,HotelLevel,CityID,cityName,addstate,addStateVal FROM ( SELECT ROW_NUMBER() OVER (order by T.hotelCode desc) AS Row, T.* from (select f.* from (select d.*,isnull(e.addState,'未新增') as addstate,case e.addState when '已新增' then '1' else '0' end as addStateVal from (select a.name as hotelName,a.enname as hotelEnName,a.code as hotelCode,a.hotellevel,a.cityId,b.name as cityName from mt_hotel a left join syscity b on a.cityid=b.id ) d left join (select '已新增' as addState,hotelCode from hotelandhoteluser where supplierCode='MT0000016') e on d.hotelCode=e.hotelCode ) f where 1=1 ) T where 1=1 ) TT WHERE TT.Row between 333333 and 333343 SELECT Row,hotelName,hotelEnName,hotelCode,HotelLevel,CityID,cityName,addstate,addStateVal FROM ( SELECT ROW_NUMBER() OVER (order by T.hotelCode desc)AS Row, T.* from (select f.* from (select d.*,isnull(e.addState,'未新增') as addstate,case e.addState when '已新增' then '1' else '0' end as addStateVal from (select a.name as hotelName,a.enname as hotelEnName,a.code as hotelCode,a.hotellevel,a.cityId,b.name as cityName from mt_hotel a left join syscity b on a.cityid=b.id ) d left join (select '已新增' as addState,hotelCode from hotelandhoteluser where supplierCode='MT0000016') e on d.hotelCode=e.hotelCode ) f where 1=1 ) T where 1=1 ) TT WHERE TT.Row between 21 and 30 第一个耗时20多秒,第二个毫秒级别.. 语句一样,就页码不一样.. SQL菜鸟,求大神指导.
没有执行计划,吧执行计划贴一下吧。另外可以先尝试吧相关表的统计信息都更新下,看看先

34,838

社区成员

发帖
与我相关
我的任务
社区描述
MS-SQL Server相关内容讨论专区
社区管理员
  • 基础类社区
  • 二月十六
  • 卖水果的net
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧