row_number命令减慢了我的数据库，有人会有办法优化速度 [英] the row_number command slowed down my database, someone would have a way to optimize the speed

问题描述

SELECT *



FROM

（SELECT *， ROW_NUMBER（）OVER（[Matricule] ORDER BY [Matricule] ASC，[Matricule] DESC）AS RowNumber

 

FROM 

[名称视图] 



WHERE 

[Usine]喜欢'usinename'AND（[DateHeure]> DATEADD（HOUR， - 24，GETUTCDATE（）））AND（[DateHeure]< GETUTCDATE（） ）和[Matricule]<>''） 

AS T 

SELECT *

FROM
( SELECT * , ROW_NUMBER() OVER(PARTITION BY [Matricule] ORDER BY [Matricule] ASC, [Matricule] DESC) AS RowNumber
 
FROM 
[Name view] 

WHERE 
[Usine] Like 'usinename' AND ([DateHeure] > DATEADD(HOUR, - 24,GETUTCDATE())) AND ([DateHeure] < GETUTCDATE()) AND [Matricule] <> '' ) 
AS T 

推荐答案

有机会帮助您解决性能问题，我们需要查看视图的定义，这只是一个开始。它也有助于查看查询计划（XML格式），添加和不添加row_number（这样我们就可以理解
的差异。）

To have a chance to help you with the performance issue, we need to see the definition of the view, and that is only the start. It also helps to see the query plan (in XML format), both with and without the row_number added (so that we can understand the difference.)

顺便说一下，你对row_number的使用有点好笑。通常，您按一个或多个列进行分区，然后按一个或多个其他列排序。与分区相同的列不是真正有意义的顺序，因为该列在分区中是常量。

By the way, your usage of row_number is a little funny. Normally you partition by one or more columns, and then order by one or more others. It is not really meaningful order by the same column as you partition by, as the column is constant within the partition.

这篇关于row_number命令减慢了我的数据库，有人会有办法优化速度的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！