求高手解答，怎么优化这条sql语句？现在查询速度很慢

Question

WITH t AS (
    SELECT *, 
    ROW_NUMBER() over(order by CommentDate Desc) AS ss 
    from (
        SELECT 
            id, userid, date, CommentDate, mediaurl, mediatype, 
            content, locationdec, contenttype, messagetype, 
            location.Lat latitude, location.Long longitude 
        FROM Message 
        WHERE 
            location.STIntersects(geography::STPointFromText(
                'POINT(' + @P1 + ' ' + @P2 + ')', 4326).STBuffer(@P3)
            )=1 And ( contenttype in ( 1,2,8,9,7,6)) 
        UNION ALL
        SELECT 
            id, userid, date, CommentDate, mediaurl, mediatype, 
            content, locationdec, contenttype, messagetype, 
            location.Lat latitude, location.Long longitude 
        FROM Message 
        WHERE Contenttype =4
    ) A 
) SELECT * FROM t WHERE ss BETWEEN 21 AND 40

当记录大于70万条之后很慢， 其中Contenttype=4 和geography查询是必须， contenttype 1,2,8,9,7,6 是不确定的查询参数。

Answer 1

题主太放任SQL接管一切了。这么写好了完全甩手交给SQL服务器去做，程序对查询的中间过程就失去了很多介入和控制的机会，恐怕不是好事。

（看着就头痛……排了下版，估计会有错误请轻拍）

Answer 2

有两个地方可以优化
1、 在contenttype 字段上增加索引
2、 ROW_NUMBER() over语句换成两层rownum的方式，如：

select * from (
  select t_adam.*, rownum as row_num from t_adam
  where rownum <= 40
) where row_num > 20

关键是看执行计划中是否使用了COUNT STOPKEY，如

SELECT STATEMENT, GOAL = ALL_ROWS           2   40  1520
 VIEW   SYSTEM      2   40  1520
  COUNT STOPKEY                 
   TABLE ACCESS FULL    SYSTEM  T_ADAM  2   40  360

如果对排序没有严格要求，可以将排序语句去掉。