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.

 All Forums
 SQL Server 2000 Forums
 SQL Server Administration (2000)
 Profiler numbers

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????
thanks
CPU
-----
7297
7078
6203
4391
4375


Durations
----------
26810
20826
20106
18780
18453


reads
------
1191426
615523
613201
613000
612938

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
Go to Top of Page

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,
Go to Top of Page

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.
Go to Top of Page

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?
Go to Top of Page

john.burns
Posting Yak Master

100 Posts

Posted - 2006-09-29 : 13:44:53
Thanks for your efforts


Call:

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 output


Procedure:




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) = NULL
AS
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
END
GO


Go to Top of Page

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 to
re-use execution plan.
Go to Top of Page

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 sql
2. Get rid of the cursors
3. Try to simplify/modularize your code.



-ec



Go to Top of Page

john.burns
Posting Yak Master

100 Posts

Posted - 2006-09-29 : 15:23:15
thanks
Go to Top of Page

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 to
re-use execution plan.



You are correct with that assessment. It is a classic side-effect.
Go to Top of Page
   

- Advertisement -