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

Question

WITHtAS(
SELECT*,
ROW_NUMBER()over(orderbyCommentDateDesc)ASss
from(
SELECT
id,userid,date,CommentDate,mediaurl,mediatype,
content,locationdec,contenttype,messagetype,
location.Latlatitude,location.Longlongitude
FROMMessage
WHERE
location.STIntersects(geography::STPointFromText(
'POINT('+@P1+''+@P2+')',4326).STBuffer(@P3)
)=1And(contenttypein(1,2,8,9,7,6))
UNIONALL
SELECT
id,userid,date,CommentDate,mediaurl,mediatype,
content,locationdec,contenttype,messagetype,
location.Latlatitude,location.Longlongitude
FROMMessage
WHEREContenttype=4
)A
)SELECT*FROMtWHEREssBETWEEN21AND40
当记录大于70万条之后很慢，其中Contenttype=4和geography查询是必须，contenttype1,2,8,9,7,6是不确定的查询参数。

Answer 1

题主太放任SQL接管一切了。这么写好了完全甩手交给SQL服务器去做，程序对查询的中间过程就失去了很多介入和控制的机会，恐怕不是好事。（看着就头痛……排了下版，估计会有错误请轻拍）