Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
john.burns
Posting Yak Master
100 Posts |
Posted - 2006-09-29 : 10:13:07
|
I have sample profile from this morning (not the busiest time of day)Are these numbers especially high????thanksCPU-----72977078620343914375Durations----------2681020826201061878018453reads------1191426615523613201613000612938 |
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2006-09-29 : 11:15:42
|
cpu times are in milliseconds, durations are in milliseconds and reads are in number of 8k blocks read. If this is an oltp environment then these figures are high. If this is olap then these might be acceptable.do you feel your queries are slow? do your customers complain that the system is slow?-ec |
|
|
john.burns
Posting Yak Master
100 Posts |
Posted - 2006-09-29 : 11:23:13
|
This is OLTP environment.Yes, users to complain that perf. gets slow throughout day.thanks, |
|
|
Luis Martin
Yak Posting Veteran
54 Posts |
Posted - 2006-09-29 : 11:25:30
|
I firt row of each counter match same query, I suggest to review the query with 26 seconds and 1191426 reads. May be you need to optimize that query. |
|
|
mcrowley
Aged Yak Warrior
771 Posts |
Posted - 2006-09-29 : 13:34:04
|
For an OLTP environment, these are pretty unacceptable. I smell a table scan in the 4 614k read results. Can you post that query? |
|
|
john.burns
Posting Yak Master
100 Posts |
Posted - 2006-09-29 : 13:44:53
|
Thanks for your effortsCall:exec HHOrders_Paging @PageNum = 1, @PageSize = 20, @SortColumn = N'HHRefNumberColumn', @SortOrder = N'DESC', @CSRFirstName = N'karen', @AssignedToCSR = N'0', @DueForTransfer = N'0', @Preset = N'1', @QANotFinished = N'0', @WaitExpired = N'0', @CPRProblem = N'0', @ExcludeTransferred = N'0', @Unassigned = N'0', @BranchID = '64E65BE2-90AF-4932-A427-8C3B6D5E271C', @Count = @P1 outputProcedure:CREATE PROCEDURE HHOrders_Paging @PageNum INT, @PageSize INT, @SortColumn VARCHAR(20), @SortOrder VARCHAR(5), @Count INT OUTPUT, @BranchID VARCHAR(255) = NULL, @ExaminerID VARCHAR(255) = NULL, @UserID VARCHAR(255) = NULL, @HHRefNumber VARCHAR(255) = NULL, @OrderDateFrom VARCHAR(255) = NULL, @OrderDateTo VARCHAR(255) = NULL, @RemoteID VARCHAR(255) = NULL, @RemoteNumber VARCHAR(255) = NULL, @Origin VARCHAR(255) = NULL, @OrderType VARCHAR(255) = NULL, @Product VARCHAR(255) = NULL, @Scheduling VARCHAR(255) = NULL, @ExamZip VARCHAR(255) = NULL, @ApplicantFirstName VARCHAR(255) = NULL, @ApplicantLastName VARCHAR(255) = NULL, @Age VARCHAR(255) = NULL, @ApplicantDOB VARCHAR(50) = NULL, @SSN VARCHAR(255) = NULL, @ExaminerFirstName VARCHAR(255) = NULL, @ExaminerLastName VARCHAR(255) = NULL, @AssignedToCSR VARCHAR(255) = NULL, @CSRFirstName VARCHAR(255) = NULL, @CSRLastName VARCHAR(255) = NULL, @Facility VARCHAR(255) = NULL, @FollowUpFrom VARCHAR(255) = NULL, @FollowUpTo VARCHAR(255) = NULL, @OpenDateFrom VARCHAR(255) = NULL, @OpenDateTo VARCHAR(255) = NULL, @AssignDateFrom VARCHAR(255) = NULL, @AssignDateTo VARCHAR(255) = NULL, @ScheduleDateFrom VARCHAR(255) = NULL, @ScheduleDateTo VARCHAR(255) = NULL, @WaitDateFrom VARCHAR(255) = NULL, @WaitDateTo VARCHAR(255) = NULL, @ProblemDateFrom VARCHAR(255) = NULL, @ProblemDateTo VARCHAR(255) = NULL, @CancellationDateFrom VARCHAR(255) = NULL, @CancellationDateTo VARCHAR(255) = NULL, @CompleteDateFrom VARCHAR(255) = NULL, @CompleteDateTo VARCHAR(255) = NULL, @QADateFrom VARCHAR(255) = NULL, @QADateTo VARCHAR(255) = NULL, @ReleaseDateFrom VARCHAR(255) = NULL, @ReleaseDateTo VARCHAR(255) = NULL, @Orderer VARCHAR(255) = NULL, @Carrier VARCHAR(255) = NULL, @Statuses VARCHAR(255) = NULL, @AccountNumber VARCHAR(255) = NULL, @BranchRegion VARCHAR(50) = NULL, @BranchArea VARCHAR(50) = NULL, @TimeServiceFrom VARCHAR(50) = NULL, @TimeServiceTo VARCHAR(50) = NULL, @TransferDateFrom VARCHAR(50) = NULL, @TransferDateTo VARCHAR(50) = NULL, @SourceBranchID VARCHAR(50) = NULL, @CPRProblem VARCHAR(255) = NULL, @OrderAppSchFrom VARCHAR(255) = NULL, @OrderAppSchTo VARCHAR(255) = NULL, @WaitExpired VARCHAR(255) = NULL, @QANotFinished VARCHAR(255) = NULL, @DueForTransfer VARCHAR(255) = NULL, @Preset VARCHAR(255) = NULL, @Unassigned VARCHAR(255) = NULL, @ExcludeTransferred VARCHAR(255) = 1, @NotPresetReq VARCHAR(255) = NULL, @DefProduct VARCHAR(255) = NULLAS BEGIN SET NOCOUNT ON SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED IF @HHRefNumber IS NOT NULL BEGIN IF LEN( @HHRefNumber ) > 6 BEGIN SET @HHRefNumber = REPLICATE('0', 9-LEN(@HHRefNumber)) + @HHRefNumber END ELSE BEGIN IF @BranchID IS NOT NULL BEGIN SELECT @HHRefNumber=DepNumber + REPLICATE('0', 6-LEN(@HHRefNumber)) + @HHRefNumber FROM Departments WHERE ID=@BranchID END END END DECLARE @Tables VARCHAR(2000) DECLARE @PKField VARCHAR(100) DECLARE @Fields VARCHAR(2000) DECLARE @Where VARCHAR(2000) DECLARE @Order VARCHAR(2000) SET @PKField = 'o.ID' SET @Tables = ' from HHOrders o ' + 'INNER JOIN Departments d ON d.[ID]=o.ID_Branch ' + 'LEFT OUTER JOIN HHOrderExams oe ON oe.ID_HHOrder=o.ID ' + 'LEFT OUTER JOIN Users exu ON exu.ID=oe.ID_Examiner ' SET @Where = ' WHERE 1=1 ' IF @BranchRegion IS NOT NULL BEGIN SET @Where = @Where + ' AND d.Region=' + @BranchRegion END IF @BranchArea IS NOT NULL BEGIN SET @Where = @Where + ' AND d.ID_HHDistrict=''' + @BranchArea + ''' ' END IF @UserID IS NOT NULL OR @CSRFirstName IS NOT NULL OR @CSRLastName IS NOT NULL BEGIN SET @Tables = @Tables + 'LEFT OUTER JOIN HHOrdersStuff os ON os.ID_HHOrder=o.ID ' END IF @UserID IS NOT NULL BEGIN SET @Where = @Where + ' AND os.ID_User = ''' + @UserID + ''' ' END IF @OrderDateFrom IS NOT NULL BEGIN SET @Where = @Where + ' AND o.OrderDate >= ''' + @OrderDateFrom + '''' END IF @OrderDateTo IS NOT NULL BEGIN SET @Where = @Where + ' AND o.OrderDate <= ''' + @OrderDateTo + '''' END IF @RemoteID IS NOT NULL BEGIN SET @Where = @Where + ' AND o.RemoteID=''' + @RemoteID + ''' ' END IF @RemoteNumber IS NOT NULL BEGIN SET @Where = @Where + ' AND o.RemoteNumber=''' + @RemoteNumber + ''' ' END IF @Origin IS NOT NULL BEGIN SET @Where = @Where + ' AND o.Origin=''' + @Origin + ''' ' END IF @OrderType IS NOT NULL BEGIN SET @Where = @Where + ' AND o.IsDirect=''' + @OrderType + ''' ' END IF @TransferDateFrom IS NOT NULL BEGIN SET @Where = @Where + ' AND o.TransferDate >= ''' + @TransferDateFrom + '''' END IF @TransferDateTo IS NOT NULL BEGIN SET @Where = @Where + ' AND o.TransferDate <= ''' + @TransferDateTo + '''' END IF @Product IS NOT NULL BEGIN SET @Where = @Where + ' AND o.ID_HHProduct = ''' + @Product + ''' ' END IF @Scheduling = '0' BEGIN SET @Where = @Where + ' AND o.Status = 2 ' END IF @Scheduling = '1' BEGIN SET @Where = @Where + ' AND o.Status = 4 ' END IF @ExamZip IS NOT NULL BEGIN SET @Where = @Where + ' AND o.ExamZip like ''' + @ExamZip + '%'' ' END IF @ApplicantFirstName IS NOT NULL BEGIN SET @Where = @Where + ' AND o.ApplicantFirstName like ''' + @ApplicantFirstName + '%'' ' END IF @ApplicantLastName IS NOT NULL BEGIN SET @Where = @Where + ' AND o.ApplicantLastName like ''' + @ApplicantLastName + '%'' ' END IF @Age IS NOT NULL BEGIN SET @Where = @Where + ' AND o.ApplicantAge=''' + @Age + ''' ' END IF @ApplicantDOB IS NOT NULL BEGIN SET @Where = @Where + ' AND o.ApplicantDOB = ''' + @ApplicantDOB + '''' END IF @SSN IS NOT NULL BEGIN SET @Where = @Where + ' AND o.ApplicantSSN=''' + @SSN + ''' ' END IF @AssignedToCSR = '1' BEGIN SET @Where = @Where + ' AND o.CSRType IN (2,3,4) ' END IF @CSRFirstName IS NOT NULL BEGIN SET @Where = @Where + ' AND os.FirstName like ''' + @CSRFirstName + '%'' ' END IF @CSRLastName IS NOT NULL BEGIN SET @Where = @Where + ' AND os.LastName like ''' + @CSRLastName + '%'' ' END IF @Facility IS NOT NULL SET @Where = @Where + ' AND o.Facility like ''' + @Facility + '%'' ' IF @FollowUpFrom IS NOT NULL BEGIN SET @Where = @Where + ' AND o.FollowUpDate >= ''' + @FollowUpFrom + '''' END IF @FollowUpTo IS NOT NULL BEGIN SET @Where = @Where + ' AND o.FollowUpDate <= ''' + @FollowUpTo + '''' END IF @OpenDateFrom IS NOT NULL BEGIN SET @Where = @Where + ' AND o.OpenDate >= ''' + @OpenDateFrom + '''' END IF @OpenDateTo IS NOT NULL BEGIN SET @Where = @Where + ' AND o.OpenDate <= ''' + @OpenDateTo + '''' END IF @AssignDateFrom IS NOT NULL BEGIN SET @Where = @Where + ' AND oe.AssignDate >= ''' + @AssignDateFrom + '''' END IF @AssignDateTo IS NOT NULL BEGIN SET @Where = @Where + ' AND oe.AssignDate <= ''' + @AssignDateTo + '''' END IF @ScheduleDateFrom IS NOT NULL BEGIN SET @Where = @Where + ' AND oe.ScheduleDate >= ''' + @ScheduleDateFrom + '''' END IF @ScheduleDateTo IS NOT NULL BEGIN SET @Where = @Where + ' AND oe.ScheduleDate <= ''' + @ScheduleDateTo + '''' END IF @WaitDateFrom IS NOT NULL BEGIN SET @Where = @Where + ' AND o.WaitDate >= ''' + @WaitDateFrom + '''' END IF @WaitDateTo IS NOT NULL BEGIN SET @Where = @Where + ' AND o.WaitDate <= ''' + @WaitDateTo + '''' END IF @ProblemDateFrom IS NOT NULL BEGIN SET @Where = @Where + ' AND o.ProblemDate >= ''' + @ProblemDateFrom + '''' END IF @ProblemDateTo IS NOT NULL BEGIN SET @Where = @Where + ' AND o.ProblemDate <= ''' + @ProblemDateTo + '''' END IF @CancellationDateFrom IS NOT NULL BEGIN SET @Where = @Where + ' AND o.CancellationDate >= ''' + @CancellationDateFrom + '''' END IF @CancellationDateTo IS NOT NULL BEGIN SET @Where = @Where + ' AND o.CancellationDate <= ''' + @CancellationDateTo + '''' END IF @CompleteDateFrom IS NOT NULL BEGIN SET @Where = @Where + ' AND o.CompleteDate >= ''' + @CompleteDateFrom + '''' END IF @CompleteDateTo IS NOT NULL BEGIN SET @Where = @Where + ' AND o.CompleteDate <= ''' + @CompleteDateTo + '''' END IF @QADateFrom IS NOT NULL BEGIN SET @Where = @Where + ' AND o.QADate >= ''' + @QADateFrom + '''' END IF @QADateTo IS NOT NULL BEGIN SET @Where = @Where + ' AND o.QADate <= ''' + @QADateTo + '''' END IF @ReleaseDateFrom IS NOT NULL BEGIN SET @Where = @Where + ' AND o.ReleaseDate >= ''' + @ReleaseDateFrom + '''' END IF @ReleaseDateTo IS NOT NULL BEGIN SET @Where = @Where + ' AND o.ReleaseDate <= ''' + @ReleaseDateTo + '''' END IF @ExaminerID IS NOT NULL BEGIN SET @Where = @Where + ' AND oe.ID_Examiner = ''' + @ExaminerID + ''' ' END IF @ExaminerFirstName IS NOT NULL BEGIN SET @Where = @Where + ' AND exu.FirstName like ''' + @ExaminerFirstName + '%'' ' END IF @ExaminerLastName IS NOT NULL BEGIN SET @Where = @Where + ' AND exu.LastName like ''' + @ExaminerLastName + '%'' ' END IF @OrderAppSchFrom IS NOT NULL BEGIN SET @Where = @Where + ' AND o.Status IN (5,6,10,11,12) AND oe.ExamStartDate >= ''' + @OrderAppSchFrom + '''' END IF @OrderAppSchTo IS NOT NULL BEGIN SET @Where = @Where + ' AND o.Status IN (5,6,10,11,12) AND dateadd(day, -1, oe.ExamEndDate) <= ''' + @OrderAppSchTo + '''' END-- IF @Orderer IS NOT NULL BEGIN SET @Where = @Where + ' AND (o.OrdererOrgName like ''' + @Orderer + '%'' OR o.WritingOrdererOrgName like ''' + @Orderer + '%'') ' END IF @Orderer IS NOT NULL BEGIN SET @Where = @Where + ' AND CONTAINS(o.*, '' "' + @Orderer + '*" '') ' END IF @Carrier IS NOT NULL BEGIN SET @Where = @Where + ' AND o.CarrierOrgName like ''' + @Carrier + '%'' ' END IF @AccountNumber IS NOT NULL BEGIN SET @Where = @Where + ' AND o.ReqCode like ''' + @AccountNumber + '%'' ' END IF @TimeServiceFrom IS NOT NULL BEGIN SET @Where = @Where + ' AND CASE WHEN o.Status IN (0,1,9, 99) THEN 0 ELSE DATEDIFF(d, COALESCE(CASE WHEN o.ProductName=''Paramed'' THEN COALESCE(o.TransferDate, o.OpenDate) ELSE o.PresetStartDate END , GETDATE()), COALESCE(CASE WHEN o.ProductName=''Paramed'' AND o.Status IN (10,11,12) THEN DATEADD(DAY, DATEDIFF(day, 0, oe.ExamStartDate ), 0) WHEN o.ProductName=''APS'' THEN o.CompleteDate ELSE NULL END, GETDATE())) END >= ' + @TimeServiceFrom END IF @TimeServiceTo IS NOT NULL BEGIN SET @Where = @Where + ' AND CASE WHEN o.Status IN (0,1,9, 99) THEN 0 ELSE DATEDIFF(d, COALESCE(CASE WHEN o.ProductName=''Paramed'' THEN COALESCE(o.TransferDate, o.OpenDate) ELSE o.PresetStartDate END , GETDATE()), COALESCE(CASE WHEN o.ProductName=''Paramed'' AND o.Status IN (10,11,12) THEN DATEADD(DAY, DATEDIFF(day, 0, oe.ExamStartDate ), 0) WHEN o.ProductName=''APS'' THEN o.CompleteDate ELSE NULL END, GETDATE())) END <= ' + @TimeServiceTo END IF @CPRProblem = '1' BEGIN SET @Where = @Where + ' AND o.IsProblem=1 ' END IF @WaitExpired = '1' BEGIN SET @Where = @Where + ' AND o.Status=7 AND o.WaitDate <= GetDate() ' END IF @QANotFinished = '1' BEGIN SET @Where = @Where + ' AND o.QADate IS NULL ' END IF @QANotFinished = '2' BEGIN SET @Where = @Where + ' AND o.QADate IS NOT NULL ' END IF @DueForTransfer = '1' BEGIN SET @Where = @Where + ' AND o.IsOutCoverage=1 AND o.Status In (2, 4, 5, 6) ' END IF @Preset = '1' BEGIN SET @Where = @Where + ' AND o.IsPreset=1 ' END IF @NotPresetReq IS NOT NULL BEGIN SET @Where = @Where + ' AND oe.PresetStatus<>1 ' END IF @ExcludeTransferred = '0' BEGIN SET @Fields = ' o.ID, bo.HHBranchRefNumber + CASE WHEN o.Origin=1 THEN '' (B)'' WHEN o.Origin=2 THEN '' (E)'' WHEN o.Origin=3 THEN '' (W)'' WHEN o.Origin=4 THEN '' (T)'' ELSE '''' END AS HHRefNumber, ' + 'o.OrderDate, CASE WHEN o.ID_Branch <> '''+@BranchID + ''' THEN 99 ELSE o.Status END AS Status, CASE WHEN o.ID_Branch <> '''+@BranchID + ''' THEN 99 ELSE o.Status END AS iStatus, 0 AS CheckBox, exu.LastName + '' '' + exu.FirstName AS Examiner, ' + 'COALESCE(o.IsDirect, 0) AS IsDirect, COALESCE(o.ProductName, '''') AS ProductName, o.OrdererOrgName AS Orderer, o.CarrierOrgName AS Carrier, o.ReqCode AS AccountNumber, o.ApplicantFirstName + '' '' + o.ApplicantLastName AS Applicant, o.ApplicantLastName, CASE WHEN o.IsOldOrder IS NOT NULL THEN 1 WHEN o.LoadingStatus IS NOT NULL THEN 1 ELSE 0 END AS IsOld, ' + 'o.QADate, o.DateCreate, CASE WHEN o.Status IN (0,1,9, 99) THEN 0 ELSE DATEDIFF(d, COALESCE(CASE WHEN o.ProductName=''Paramed'' THEN COALESCE(o.TransferDate, o.OpenDate) ELSE o.PresetStartDate END , GETDATE()), COALESCE(CASE WHEN o.ProductName=''Paramed'' AND o.Status IN (10,11,12) THEN DATEADD(DAY, DATEDIFF(day, 0, oe.ExamStartDate ), 0) WHEN o.ProductName=''APS'' THEN o.CompleteDate ELSE NULL END, GETDATE())) END AS TimeService, d.DepNumber ' IF @DefProduct IS NULL OR @DefProduct <> 'APS' BEGIN SET @Fields = @Fields + ', CASE WHEN o.Status IN (1,2) AND o.IsPreset=1 THEN o.PresetStartDate ELSE CASE WHEN o.Status IN (5,6,10,11,12) THEN oe.ExamStartDate ELSE NULL END END AS ExamDate' END ELSE BEGIN SET @Fields = @Fields + ', o.FollowUpDate AS ExamDate' END SET @Tables = @Tables + ' INNER JOIN ( SELECT DISTINCT ID_HHOrder , ID_Branch , HHBranchRefNumber FROM HHBranchOrders ' IF @HHRefNumber IS NOT NULL BEGIN IF LEN( @HHRefNumber ) > 6 BEGIN SET @Tables = @Tables + ' WHERE HHRefNumber=''' + @HHRefNumber + ''' ' END ELSE BEGIN IF @BranchID IS NOT NULL BEGIN SET @Tables = @Tables + ' WHERE HHRefNumber=''' + @HHRefNumber + ''' ' END END END SET @Tables = @Tables + ' ) bo ON bo.ID_HHOrder = o.ID AND bo.ID_Branch = ''' + @BranchID + ''' ' SET @Where = @Where + ' AND (o.ID_Branch <> '''+@BranchID + ''' OR o.Status <> 99) ' IF @Statuses IS NOT NULL BEGIN SET @Where = @Where + ' AND CASE WHEN o.ID_Branch <> '''+@BranchID + ''' THEN 99 ELSE o.Status END IN (' + @Statuses + ') ' END IF @Unassigned = '1' BEGIN SET @Where = @Where + ' AND o.Status=2 AND (o.CSRType IS NULL OR o.CSRType=5) AND o.ID_Branch='''+@BranchID + ''' ' END SELECT @Order = CASE @SortColumn WHEN 'HHRefNumberColumn' THEN ' ORDER BY bo.HHBranchRefNumber' WHEN 'OrderDateColumn' THEN ' ORDER BY o.OrderDate' WHEN 'StatusColumn' THEN ' ORDER BY CASE WHEN o.ID_Branch<> '''+@BranchID + ''' THEN 99 ELSE o.Status END' WHEN 'ExaminerColumn' THEN ' ORDER BY exu.LastName + '' '' + exu.FirstName' WHEN 'ExamDateColumn' THEN CASE WHEN @DefProduct IS NULL OR @DefProduct <> 'APS' THEN ' ORDER BY CASE WHEN o.Status IN (1,2) AND o.IsPreset=1 THEN o.PresetStartDate ELSE CASE WHEN o.Status IN (5,6,10,11,12) THEN oe.ExamStartDate ELSE NULL END END' ELSE ' ORDER BY o.FollowUpDate ' END WHEN 'OrdererColumn' THEN ' ORDER BY o.OrdererOrgName' WHEN 'CarrierColumn' THEN ' ORDER BY o.CarrierOrgName' WHEN 'AccountNumberColumn' THEN ' ORDER BY o.ReqCode' WHEN 'ApplicantColumn' THEN ' ORDER BY o.ApplicantLastName' WHEN 'DepNumberColumn' THEN ' ORDER BY d.DepNumber' WHEN 'TimeServiceColumn' THEN ' ORDER BY CASE WHEN o.Status IN (0,1,9, 99) THEN 0 ELSE DATEDIFF(d, COALESCE(CASE WHEN o.ProductName=''Paramed'' THEN COALESCE(o.TransferDate, o.OpenDate) ELSE o.PresetStartDate END , GETDATE()), COALESCE(CASE WHEN o.ProductName=''Paramed'' AND o.Status IN (10,11,12) THEN DATEADD(DAY, DATEDIFF(day, 0, oe.ExamStartDate ), 0) WHEN o.ProductName=''APS'' THEN o.CompleteDate ELSE NULL END, GETDATE())) END' ELSE ' ORDER BY o.OrderDate' END END ELSE BEGIN SET @Fields = ' o.ID, o.HHRefNumber + CASE WHEN o.Origin=1 THEN '' (B)'' WHEN o.Origin=2 THEN '' (E)'' WHEN o.Origin=3 THEN '' (W)'' WHEN o.Origin=4 THEN '' (T)'' ELSE '''' END AS HHRefNumber, ' + 'o.OrderDate, o.Status, o.Status AS iStatus, 0 AS CheckBox, exu.LastName + '' '' + exu.FirstName AS Examiner, ' + 'COALESCE(o.IsDirect, 0) AS IsDirect, COALESCE(o.ProductName, '''') AS ProductName, o.OrdererOrgName AS Orderer, o.CarrierOrgName AS Carrier, o.ReqCode AS AccountNumber, o.ApplicantFirstName + '' '' + o.ApplicantLastName AS Applicant, o.ApplicantLastName, CASE WHEN o.IsOldOrder IS NOT NULL THEN 1 WHEN o.LoadingStatus IS NOT NULL THEN 1 ELSE 0 END AS IsOld, ' + 'o.QADate, o.DateCreate, CASE WHEN o.Status IN (0,1,9, 99) THEN 0 ELSE DATEDIFF(d, COALESCE(CASE WHEN o.ProductName=''Paramed'' THEN COALESCE(o.TransferDate, o.OpenDate) ELSE o.PresetStartDate END , GETDATE()), COALESCE(CASE WHEN o.ProductName=''Paramed'' AND o.Status IN (10,11,12) THEN DATEADD(DAY, DATEDIFF(day, 0, oe.ExamStartDate ), 0) WHEN o.ProductName=''APS'' THEN o.CompleteDate ELSE NULL END, GETDATE())) END AS TimeService, d.DepNumber ' IF @DefProduct IS NULL OR @DefProduct <> 'APS' BEGIN SET @Fields = @Fields + ', CASE WHEN o.Status IN (1,2) AND o.IsPreset=1 THEN o.PresetStartDate ELSE CASE WHEN o.Status IN (5,6,10,11,12) THEN oe.ExamStartDate ELSE NULL END END AS ExamDate' END ELSE BEGIN SET @Fields = @Fields + ', o.FollowUpDate AS ExamDate' END SET @Where = @Where + ' AND o.Status <> 99 ' IF @BranchID IS NOT NULL BEGIN SET @Where = @Where + ' AND o.ID_Branch= '''+@BranchID + ''' ' END IF @HHRefNumber IS NOT NULL BEGIN IF LEN( @HHRefNumber ) > 6 BEGIN SET @Where = @Where + ' AND o.HHRefNumber=''' + @HHRefNumber + ''' ' END ELSE BEGIN IF @BranchID IS NOT NULL BEGIN SET @Where = @Where + ' AND o.HHRefNumber=''' + @HHRefNumber + ''' ' END END END IF @Statuses IS NOT NULL BEGIN SET @Where = @Where + ' AND o.Status IN (' + @Statuses + ') ' END IF @Unassigned = '1' BEGIN SET @Where = @Where + ' AND o.Status=2 AND (o.CSRType IS NULL OR o.CSRType=5) ' END SELECT @Order = CASE @SortColumn WHEN 'HHRefNumberColumn' THEN ' ORDER BY o.HHRefNumber' WHEN 'OrderDateColumn' THEN ' ORDER BY o.OrderDate' WHEN 'StatusColumn' THEN ' ORDER BY o.Status' WHEN 'ExaminerColumn' THEN ' ORDER BY exu.LastName + '' '' + exu.FirstName' WHEN 'ExamDateColumn' THEN CASE WHEN @DefProduct IS NULL OR @DefProduct <> 'APS' THEN ' ORDER BY CASE WHEN o.Status IN (1,2) AND o.IsPreset=1 THEN o.PresetStartDate ELSE CASE WHEN o.Status IN (5,6,10,11,12) THEN oe.ExamStartDate ELSE NULL END END' ELSE ' ORDER BY o.FollowUpDate ' END WHEN 'OrdererColumn' THEN ' ORDER BY o.OrdererOrgName' WHEN 'CarrierColumn' THEN ' ORDER BY o.CarrierOrgName' WHEN 'AccountNumberColumn' THEN ' ORDER BY o.ReqCode' WHEN 'ApplicantColumn' THEN ' ORDER BY o.ApplicantLastName' WHEN 'DepNumberColumn' THEN ' ORDER BY d.DepNumber' WHEN 'TimeServiceColumn' THEN ' ORDER BY CASE WHEN o.Status IN (0,1,9, 99) THEN 0 ELSE DATEDIFF(d, COALESCE(CASE WHEN o.ProductName=''Paramed'' THEN COALESCE(o.TransferDate, o.OpenDate) ELSE o.PresetStartDate END , GETDATE()), COALESCE(CASE WHEN o.ProductName=''Paramed'' AND o.Status IN (10,11,12) THEN DATEADD(DAY, DATEDIFF(day, 0, oe.ExamStartDate ), 0) WHEN o.ProductName=''APS'' THEN o.CompleteDate ELSE NULL END, GETDATE())) END' ELSE ' ORDER BY o.OrderDate' END END SELECT @Order = @Order + CASE @SortOrder WHEN 'DESC' THEN ' DESC' ELSE ' ASC' END DECLARE @StartRow INT SET @StartRow = (@PageNum - 1) * @PageSize + 1 EXEC (' DECLARE @PK UNIQUEIDENTIFIER DECLARE @PageSize INT SET @PageSize = ' + @PageSize + ' DECLARE @tblPK TABLE ( PK UNIQUEIDENTIFIER ) SET ROWCOUNT ' + @StartRow + @PageSize + ' DECLARE PagingCursor CURSOR LOCAL READ_ONLY SCROLL FOR SELECT TOP 1000 ' + @PKField + @Tables + @Where + @Order +' OPEN PagingCursor FETCH RELATIVE ' + @StartRow +' FROM PagingCursor INTO @PK WHILE @PageSize > 0 AND @@FETCH_STATUS = 0 BEGIN INSERT INTO @tblPK(PK) VALUES(@PK) FETCH NEXT FROM PagingCursor INTO @PK SET @PageSize = @PageSize - 1 END CLOSE PagingCursor DEALLOCATE PagingCursor SELECT TOP 1000 '+ @Fields + @Tables + ' INNER JOIN @tblPK t ON t.PK = '+ @PKField + @Where + @Order ) DECLARE @Sql NVARCHAR(4000) SET @Sql = 'SELECT @Count = COUNT(*) FROM ( SELECT TOP 1000 ' + @PKField + @Tables + @Where + ' ) o' EXEC sp_executesql @Sql, N'@Count INT OUTPUT', @Count = @Count OUTPUT ENDGO |
|
|
john.burns
Posting Yak Master
100 Posts |
Posted - 2006-09-29 : 13:46:36
|
I also have high avg. number of compliations/sec (around 160)I presume this is due t o the dyanamic queries and inability tore-use execution plan. |
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2006-09-29 : 15:15:36
|
the code that you posted is just so hard to look at that I doubt you will get much in the way of help on this. My recommendations:1. Get rid of the dynamic sql2. Get rid of the cursors3. Try to simplify/modularize your code.-ec |
|
|
john.burns
Posting Yak Master
100 Posts |
Posted - 2006-09-29 : 15:23:15
|
thanks |
|
|
mcrowley
Aged Yak Warrior
771 Posts |
Posted - 2006-09-29 : 16:23:26
|
quote: Originally posted by john.burns I also have high avg. number of compliations/sec (around 160)I presume this is due to the dyanamic queries and inability tore-use execution plan.
You are correct with that assessment. It is a classic side-effect. |
|
|
|
|
|
|
|