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
是不确定的查询参数。
题主太放任SQL接管一切了。这么写好了完全甩手交给SQL服务器去做,程序对查询的中间过程就失去了很多介入和控制的机会,恐怕不是好事。
(看着就头痛……排了下版,估计会有错误请轻拍)
有两个地方可以优化
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
如果对排序没有严格要求,可以将排序语句去掉。