当记录超过35000时，查询需要很长时间才能执行 [英] Query takes long time to execute when records are more than 35000

问题描述

在下面的查询中，当记录超过35000时，ROW_NUMBER（）函数会导致长延迟。这里，行号函数用于获取序列号，如1,2,3,4等，作为listctrl中的第一列，带复选框。请告诉我如何以其他方式减少延迟。由于问题是在客户端，它需要尽早修复。

In following query ROW_NUMBER() function causes long delay when records are more than 35000. Here Row number function is used to get sequence number like 1,2,3,4 and so on as first column in listctrl with checkbox. Please let me know how can I do other way down to reduce delay.As issue is at client side ,it needs to fixed as early as possible.

SELECT R.ReadNo,ROW_NUMBER() OVER (ORDER BY R.ResultID_PK) AS Number,CONVERT(VARCHAR(20),R.ReadTime,113) AS ResultDate,TS.SampleID,ISNULL(P.FirstName + ' ' + P.LastName,'') AS Name,ISNULL(P.PatientID,'') AS PatientID,T.TestCode,TP.PlateNo,R.Result AS RESULT,ISNULL([DBEIAQUANT].[dbo].[GetUnit] (U.Unit,R.TestTypeRun),'') AS Unit,R.Flag,TR.FinalOD,ISNULL([DBEIAQUANT].[dbo].[GetCOV] (R.CutOff,T.TestType),'') AS CutOff,R.ReCalcResult AS RecalResult,R.ReCalcFlag AS RecalFlag,ISNULL(TS.PatientID,0) AS PatientID FROM TblResults R
INNER JOIN TblPlateMaster TP ON (TP.PlateID_PK = R.PlateID)
 INNER JOIN TblSchedule S ON (S.ScheduleID_PK = R.ScheduleID)
 INNER JOIN TblSample TS ON (TS.SampleID_PK = S.SampleID AND TS.Status = '1')
  INNER JOIN TblTestMaster T ON (T.TestCode_PK = R.TestCode)
   INNER JOIN TblReadings TR ON (TR.ReadNo_PK = R.ReadNo)
   LEFT OUTER JOIN TblUnitMaster U ON (U.UID_PK = R.Unit)
    LEFT OUTER JOIN TblPatient P ON (P.PatientId_PK = TS.PatientID) WHERE R.ReadTime
    BETWEEN '2014-04-28 00:00:00' AND '2015-11-14 23:59:59'

推荐答案

请阅读该问题的所有评论。



有很多关于查询性能的文章。请关注此链接 [ ^ ]。

Please, read all comments to the question.

There's tons of articles about query performance. Follow this link[^].

当你有一个性能问题，只有一个问题：我真的需要做什么？

答案通常是通过删除所有不需要的东西来简化请求。

另一种方法是查看是否可以更改请求以使服务器更简单，更快速。



有时它是未组织的数据库最佳方式。



根据你的评论，我看到我的问题很有用。



链接解决方案1当然很有趣。

When you have a performance issue, there is only 1 question: "What do I really need to do ?"
And the answer is usually to simplify the request by removing all what is not needed.
Another approach is to see if one can change the request to make it simpler and faster to the server.

Sometime it is the database that is not organized in the optimum way.

As per your comment, I see my question was useful.

links of solution 1 are certainly interesting.

这篇关于当记录超过35000时，查询需要很长时间才能执行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！