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'