求助一条SQL 查询的优化

Question

@qiangjin036a:
业务需求啊，必须按这个排序和分页的

Answer 1

看下你的完整语句?
你这句还没生成好rowid就开始top了……
[BirthDate] > '1985-10-10' 

Answer 2

看你的字段意思 resumecreatetime 是指此记录的建立时间而表中有 userresumeid 为标识列。也即是说，这两个字段值的变化趋势是相同的，即userresumeid大的，resumecreatetime也越靠后。这样，完全可以用 order by userresumeid 替代 resumecreatetime .int列的排序效率是高于datetime(smalldatetime)的。
则可以变为  ROW_NUMBER() over (order by [userresumeid]) as RowID
有必要的话，给int列建立聚集索引.

Answer 3

谢谢您的回复，我发的这个就是完整语句，如果选出来后再TOP，那么分页就不准确了。

Answer 4

不好意思，可能我字段名字起的有问题，但“ResumeCreateTime”以后是可以改变的，也就是可能跟UserResumeID可能会顺序不一致。
感觉这个时间的花费并不是因为数据类型，而是全文检索出来的数据需要重新排序，因为我不太了解全文索引，所以...

Answer 5

您好，需要包含其他列么？还只是birthDate列建立索引？

Answer 6

--换成ASC呢...哦哦,只是乱猜的..NONCLUSTERED INDEX [IX_Tuning_ResumeCreateTime] ON [dbo].[Tuning] 
(
    [resumecreatetime] ASC
)
INCLUDE ( [UserID],
[UserDisplayName],
[ResumeName],
[BirthDate],
[AgeRank],
[gender])

Answer 7

参考：
--[BirthDate]上建立降序索引
CREATE INDEX IX_Tuning_Bir ON [dbo].[Tuning] ([BirthDate] DESC)
--查询满足条件的前100条中的第20到30条记录
SELECT [UserResumeID],[UserID],[UserDisplayName],[ResumeName]
,[BirthDate],[AgeRank],[gender],[resumecreatetime]
FROM
(
SELECT [UserResumeID],[UserID],[UserDisplayName],[ResumeName]
,[BirthDate],[AgeRank],[gender],[resumecreatetime]
,ROW_NUMBER() over (order by [resumecreatetime]) as RowID
FROM
(
SELECT TOP 100 [UserResumeID],[UserID],[UserDisplayName],[ResumeName]
  ,[BirthDate],[AgeRank],[gender],[resumecreatetime]
FROM [dbo].[Tuning] 
WHERE [BirthDate] > '1985-10-10 00:00:00'
AND CONTAINS(summary,'业务')
) AS T
) AS T1
WHERE RowID BETWEEN 20 AND 30

Answer 8

执行计划：
  |--Filter(WHERE:([Expr1003]>=(20) AND [Expr1003]<=(30)))
       |--Top(TOP EXPRESSION:(CASE WHEN (30) IS NULL OR (30)<(0) THEN (0) ELSE (30) END))
            |--Sequence Project(DEFINE:([Expr1003]=row_number))
                 |--Segment
                      |--Sort(ORDER BY:([Hotsoft].[dbo].[Tuning].[resumecreatetime] ASC))
                           |--Top(TOP EXPRESSION:((100)))
                                |--Merge Join(Left Semi Join, MERGE:([Hotsoft].[dbo].[Tuning].[UserResumeID])=(FulltextMatch.[docid]), RESIDUAL:([Hotsoft].[dbo].[Tuning].[UserResumeID]=FulltextMatch.[docid]))
                                     |--Index Scan(OBJECT:([Hotsoft].[dbo].[Tuning].[IX_Tuning_ID]),  WHERE:([Hotsoft].[dbo].[Tuning].[BirthDate]>'1985-10-10 00:00:00.000') ORDERED FORWARD)
                                     |--Table-valued function
执行速度：
SQL Server 分析和编译时间: 
   CPU 时间 = 0 毫秒，占用时间 = 0 毫秒。(11 行受影响)
表 'Tuning'。扫描计数 1，逻辑读取 23 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。 SQL Server 执行时间:
   CPU 时间 = 15 毫秒，占用时间 = 9 毫秒。
非常感谢！执行速度很快，但是您这个查询改变了存储过程的逻辑，原来的存储过程是取得Craetetime倒序的前几条，您的应该是取得UserResumeID(PK)的前几条
我使用union all对比了一下，查询出的结果是不一样的

Answer 9

结果应该是一样的，都是按pk排列的前100条。
因为你和我的语句都没有指定order by从句，因此默认按PK。

Answer 10

rownumber这个函数好像是先排序再编号，不过您的语句对我很有帮助！

Answer 11

测试了一下，row_number()果然对前面的top有影响，
我还以为它只是为了生成编号……

Answer 12

呵呵，共同提高吧，现在想到是否可以建一份克隆表，多一个主键（标识字段），将update 换成del&insert。
然后在这个克隆表上查询，正在试验中。

Answer 13

目前问题基本解决，解决办法，在Tuning表上新建个列RowID，使用GUID或者timestamp,然后建立相应触发器，使得最后更新的RowID的值大于之前的值，在这个字段中建立唯一键，根据此唯一键建立全文索引。最后查询语句如下：
select * from tunning
where UserResumeID in(
select UserResumeID from(
select top 1000
ROW_NUMBER() over (order by rowid desc) as rowid
,userresumeid
from tunning
where contains(searchtext,'经理')
) t
where (rowid between 1 and 20)
)执行结果：
 SQL Server 执行时间:
   CPU 时间 = 203 毫秒，占用时间 = 230 毫秒。

Answer 14

我在上面的说法不准确，不是改进rank算法.而是搭配预配置的rank值，使用 top_n_by_rank参数。因为你的目的是分页所以是适合的。
具体写法参考
http://blog.csdn.net/ojekleen/archive/2007/09/25/1800137.aspx
更多内容见msdn
你自己的改进做法，实际上也是找了一个与userresumeid变化趋势相同的列。

Answer 15

分二步看会不会更快
SELECT TOP 100
      [UserResumeID]
      ,[UserID]
      ,[UserDisplayName]
      ,[ResumeName]
      ,[BirthDate]
      ,[AgeRank]
      ,[gender]
      ,[resumecreatetime]
  FROM [dbo].[Tuning] 
into #tmp  
WHERE 
    [BirthDate] > CONVERT(smalldatetime,'1985-10-10 00:00:00')
    AND contains(summary,'业务')
order by [resumecreatetime]
再对临时表查询

Answer 16

昨天又突然想到一种算法,效果也不错，代码如下：select * from
(select top 1000 ROW_NUMBER() OVER (ORDER BY urv.resumecreatetime) as RowID urv.resumecreatetime,urv.userresumeid 
from UserResumeView  urv 
inner join (select userresumeid 
from UserResumeView 
where contains(SearchText,'经理')) t
on t.UserResumeID=urv.UserResumeID
) t2
where RowID between 10 and 20其中的UserResumeView就是上面的Tuning啦～
感谢楼上各位兄弟的热情帮助，结贴了！