-1

I have a stored procedure that is working perfectly but the issue is performance. There are so many joins in this stored procedure and unions... So after having the query executed, i need to send only 50 items to the application but as per my modification, it's taking the same time for 50 items or more... So can we tune it to reduce the time? I have done indexing and all but still its taking 12-15 secs for 3000 records.

ALTER PROCEDURE [dbo].[spGetForemanReportsCustomPaging]
    @OFficeStaffID INT = NULL,
    @SupervisorID INT = NULL,
    @CustomerID INT = NULL,
    @ContractAreaID INT = NULL,
    @JobID INT  = NULL,
    @JobDescription NVARCHAR(500) = NULL,   
    @CustomerSupervisorID INT = NULL,
    @ForemanID INT = NULL,
    @StartDate DATETIME = NULL,
    @EndDate DATETIME = NULL,
    @PageIndex INT = 0,    
    @PageSize INT = 50,
    @SortExpression nvarchar(50),
    @SortDirection nvarchar(50)

AS
    BEGIN

    Declare @StartRowIndex int   
    Declare @EndRowIndex int  

    Set @StartRowIndex = (@PageIndex * @PageSize) + 1;  
    Set @EndRowIndex = (@PageIndex + 1) * @PageSize;  

DECLARE @IsSupportServiceSupervisor tinyInt = null
SET @IsSupportServiceSupervisor = (SELECT COUNT(*) FROM tblEmployeeToAdditionalRole INNER JOIN tblUser ON tblEmployeeToAdditionalRole.EmployeeID = tblUser.ID 
                                    INNER JOIN tblAdditionalRoles on tblAdditionalRoles.ID = tblEmployeeToAdditionalRole.AdditionalRoleID
                                    WHERE tblUser.ID = @SupervisorID and tblAdditionalRoles.Role = 'Support Service Supervisor');

--Added by Rishi on 22-Nov-2016 to show all records related to the selected foreman and his all jobs...
DECLARE @JobIDs TABLE(val INT)
INSERT INTO @JobIDs
SELECT DISTINCT JobID FROM tblForemanReports 
WHERE tblForemanReports.ForemanID = @ForemanID
UNION
SELECT DISTINCT JobID FROM tblSubContractorForm 
WHERE tblSubContractorForm.CreatedBy = (SELECT LowerUserName from tblUser Where ID = @ForemanID)
UNION
SELECT DISTINCT JobID FROM tblFieldSupportServices 
WHERE tblFieldSupportServices.CreatedBy = (SELECT LowerUserName from tblUser Where ID = @ForemanID)
UNION
SELECT DISTINCT  JobID FROM tblPavingDetails 
INNER JOIN tblPavingOnlyForm ON tblPavingDetails.PavingOnlyFormID = tblPavingOnlyForm.ID
WHERE tblPavingOnlyForm.ForemanID = @ForemanID

UPDATE tblProbablePayItems SET Total = 0 WHERE Total IS NULL OR Total = ''
UPDATE tblProbableChangeOrders SET Total = 0 WHERE Total IS NULL OR Total = ''
UPDATE tblJobCostingSide SET Amount = 0 WHERE Amount IS NULL


SELECT * FROM
(SELECT ROW_NUMBER() over 
  (
   order by
    case when @SortExpression = 'JobID' and @SortDirection = 'ASC' 
      then ActiveForemanReports.JobID end asc, 
    case when @SortExpression = 'JobID' and @SortDirection = 'DESC' 
      then ActiveForemanReports.JobID end desc,
    case when @SortExpression = 'JobDescription' and @SortDirection = 'ASC' 
      then ActiveForemanReports.JobDescription end asc, 
    case when @SortExpression = 'JobDescription' and @SortDirection = 'DESC' 
      then ActiveForemanReports.JobDescription end desc,
    case when @SortExpression = 'WorkDate' and @SortDirection = 'ASC' 
      then ActiveForemanReports.WorkDate end asc, 
    case when @SortExpression = 'WorkDate' and @SortDirection = 'DESC' 
      then ActiveForemanReports.WorkDate end desc,
    case when @SortExpression = 'Foreman' and @SortDirection = 'ASC' 
      then ActiveForemanReports.Foreman end asc, 
    case when @SortExpression = 'Foreman' and @SortDirection = 'DESC' 
      then ActiveForemanReports.Foreman end desc,
    case when @SortExpression = 'FormType' and @SortDirection = 'ASC' 
      then ActiveForemanReports.FormType end asc, 
    case when @SortExpression = 'FormType' and @SortDirection = 'DESC' 
      then ActiveForemanReports.FormType end desc,
    --case when @SortExpression = 'UnitilWorkSheetID' and @SortDirection = 'ASC' 
    --  then finalPayToInvoice.WorkSheet end asc, 
    --case when @SortExpression = 'UnitilWorkSheetID' and @SortDirection = 'DESC' 
    --  then finalPayToInvoice.WorkSheet end desc,
    case when @SortExpression = 'ProbablePayItem' and @SortDirection = 'ASC' 
      then ActiveForemanReports.PayItem end asc, 
    case when @SortExpression = 'ProbablePayItem' and @SortDirection = 'DESC' 
      then ActiveForemanReports.PayItem end desc,
    case when @SortExpression = 'ChangeOrder' and @SortDirection = 'ASC' 
      then ActiveForemanReports.ChangeOrder end asc, 
    case when @SortExpression = 'ChangeOrder' and @SortDirection = 'DESC' 
      then ActiveForemanReports.ChangeOrder end desc,
    case when @SortExpression = 'TotalInvoiceAmount' and @SortDirection = 'ASC' 
      then ActiveForemanReports.InvoiceAmount end asc, 
    case when @SortExpression = 'TotalInvoiceAmount' and @SortDirection = 'DESC' 
      then ActiveForemanReports.InvoiceAmount end desc
  ) as RowNumber,
*,
dbo.GetProfitPercentage(ActiveForemanReports.PayItem, ActiveForemanReports.ChangeOrder, ActiveForemanReports.CostItem) AS Percentage  FROM
(SELECT tblForemanReports.JobID, dbo.fnGetJobIDStringByJobId(tblForemanReports.JobID) AS JobIDString, tblForemanReports.ID AS ForemanReportID, tblForemanReports.ReviewDone, tblForemanReports.ReadyForInvoice,
tblForemanReports.AllWorkCompletedForJob, tblForemanReports.IsAllWorkDoneForProject, statusForeman.StatusName,
tblJobDetails.JobDescription, 
tblCustomer.FullName As Customer,

tblUser.FullName AS [Foreman],
tblUser.ID AS [ForemanID],
'Foreman Report' AS [FormType],

tblForemanReports.WorkDate,
tblForemanReports.PremiumTime,

CASE WHEN tblForemanReports.ContractTypeID = 2
THEN CAST(1 AS BIT)
ELSE
CAST(0 AS BIT)
END AS TandM,

((SELECT SUM((CAST(Total AS Decimal(20,2)))) FROM tblProbablePayItems
--INNER JOIN tblStatus ON tblStatus.ID = tblProbablePayItems.StatusID
WHERE WorkDate = tblForemanReports.WorkDate 
AND JobID = tblForemanReports.JobID
AND (ForemanReportID = tblForemanReports.ID)
AND Total IS NOT NULL AND Total <> ''
AND tblProbablePayItems.IsDeleted IS NULL
--AND tblStatus.StatusName = 'Approved by Customer Supervisor'
)) AS PayItem,

--((SELECT SUM((CAST(Total AS Decimal(20,2)))) FROM tblProbableChangeOrders
----INNER JOIN tblStatus ON tblStatus.ID = tblProbableChangeOrders.StatusID
--WHERE WorkDate = tblForemanReports.WorkDate 
--AND JobID = tblForemanReports.JobID
--AND (ForemanReportID = tblForemanReports.ID)
----AND (tblStatus.StatusName NOT LIKE 'Rejected by Customer Supervisor'
--AND InvoiceID IS NULL
--AND Total IS NOT NULL AND Total <> ''
--)) AS ChangeOrder,

NULLIF(((SELECT ISNULL(SUM((CAST(Total AS Decimal(20,2)))), 0) FROM tblProbableChangeOrders
--INNER JOIN tblStatus ON tblStatus.ID = tblProbableChangeOrders.StatusID
WHERE WorkDate = tblForemanReports.WorkDate 
AND JobID = tblForemanReports.JobID
AND (ForemanReportID = tblForemanReports.ID)
AND tblProbableChangeOrders.IsDeleted IS NULL
--AND (tblStatus.StatusName NOT LIKE 'Rejected by Customer Supervisor'
AND InvoiceID IS NULL
AND Total IS NOT NULL AND Total <> ''
) + 
(SELECT ISNULL(SUM(Total), 0) FROM tblProbableChangeOfScope
INNER JOIN tblChangeOfScope ON tblChangeOfScope.ID = tblProbableChangeOfScope.ChangeOfScopeID
WHERE (tblProbableChangeOfScope.WorkDate = tblForemanReports.WorkDate)
AND (tblProbableChangeOfScope.JobID = tblForemanReports.JobID)
AND (tblChangeOfScope.ForemanID = tblForemanReports.ForemanID))),0) AS ChangeOrder,

--(SUM(CAST(tblJobCostingSide.Amount AS Decimal(20,2)))) AS CostItem,

((SELECT SUM((CAST(Amount AS Decimal(20,2)))) FROM tblJobCostingSide
--INNER JOIN tblStatus ON tblStatus.ID = tblProbablePayItems.StatusID
WHERE WorkDate = tblForemanReports.WorkDate 
AND JobID = tblForemanReports.JobID
AND (tblJobCostingSide.ForemanReportID = tblForemanReports.ID)
AND tblJobCostingSide.Amount IS NOT NULL
--AND tblStatus.StatusName = 'Approved by Customer Supervisor'
)) AS CostItem,

0.0 AS InvoiceAmount,

CASE 
    WHEN (SELECT COUNT(tblChangeOrderGenerated.ID) 
            FROM tblChangeOrderGenerated Inner join tblChangeOrderGeneratedToDates 
            ON tblChangeOrderGenerated.ID = tblChangeOrderGeneratedToDates.ChangeOrderID
            WHERE tblChangeOrderGenerated.JobID = tblForemanReports.JobID AND tblChangeOrderGeneratedToDates.[Date] = tblForemanReports.WorkDate) > 0
    THEN 1
        ELSE 0
END AS IsChangeOrderGenerated,
tblJobType.Title AS JobType

FROM tblForemanReports

--Commented By Noor 12th May 2016 :- For Foreman report the foreman name will be the one selected on Foreman report form.
--LEFT JOIN tblUser ON LOWER(tblForemanReports.CreatedBy) = LOWER(tblUser.Username)
LEFT JOIN tblUser ON tblForemanReports.ForemanID = tblUser.ID

LEFT JOIN tblJobCostingSide ON (tblJobCostingSide.ForemanReportID = tblForemanReports.ID
AND tblJobCostingSide.Amount IS NOT NULL)

INNER JOIN tblJobDetails ON tblJobDetails.ID = tblForemanReports.JobID
INNER JOIN tblJobType ON tblJobDetails.JobTypeID = tblJobType.ID
INNER JOIN tblCustomer ON tblCustomer.ID = tblJobDetails.CustomerID

LEFT JOIN tblProbablePayItems ON tblProbablePayItems.ForemanReportID = tblForemanReports.ID
--LEFT JOIN tblSubContractorForm ON tblSubContractorForm.ID = tblProbablePayItems.SubcontractorFormID

LEFT JOIN tblStatus statusForeman ON tblForemanReports.StatusID = statusForeman.ID

LEFT JOIN tblJobToWorkOrder ON tblJobToWorkOrder.ID = tblForemanReports.JobToWorkOrderID

LEFT JOIN tblScatteredServices ON 
(tblScatteredServices.JobID = tblJobDetails.ID 
AND tblScatteredServices.ID = tblJobToWorkOrder.ScatteredServiceID)

LEFT JOIN tblEncapsulationRequest ON
(tblEncapsulationRequest.JobID = tblJobDetails.ID
AND tblEncapsulationRequest.ID = tblJobToWorkOrder.EncapsulationRequestID)

LEFT JOIN tblMiscellaneousRequest ON
(tblMiscellaneousRequest.JobID = tblJobDetails.ID
AND tblMiscellaneousRequest.ID = tblJobToWorkOrder.MiscellaneousRequestID)

WHERE (tblJobDetails.CustomerID = @CustomerID OR @CustomerID IS NULL)
AND (tblJobDetails.OfficeStaffID = @OFficeStaffID 
    OR tblMiscellaneousRequest.OfficeStaffID = @OFficeStaffID 
    OR @OFficeStaffID IS NULL)
AND (tblJobDetails.NeuCoSupervisorID = @SupervisorID 
    OR tblScatteredServices.NeuCoSupervisorID = @SupervisorID 
    OR tblEncapsulationRequest.NeuCoSupervisorID = @SupervisorID
    OR tblMiscellaneousRequest.NeucoSupervisorID = @SupervisorID
    OR @SupervisorID IS NULL)
AND (tblForemanReports.ReadyForInvoice IS NULL OR tblForemanReports.ReadyForInvoice = 0)
AND (tblProbablePayItems.InvoiceID IS NULL)
AND (tblJobDetails.ContractAreaID = @ContractAreaID OR @ContractAreaID IS NULL)
AND (tblJobDetails.ID = @JobID OR @JobID IS NULL)
AND (tblJobDetails.JobDescription like  '%' + @JobDescription +'%' OR @JobDescription IS NULL)
AND (tblJobDetails.CustomerSupervisorID = @CustomerSupervisorID
    OR tblScatteredServices.CustomerSupervisorID = @CustomerSupervisorID
    OR tblMiscellaneousRequest.CustomerSupervisorID = @CustomerSupervisorID
    OR tblEncapsulationRequest.CustomerSupervisorID = @CustomerSupervisorID
    OR @CustomerSupervisorID IS NULL)
--Commented By Noor 12th May 2016 :- Foreman reports will be filtered based on the foreman selected on FR and not the one who has created it.
--AND (tblUser.ID = @ForemanID OR @ForemanID is Null)
--AND (tblForemanReports.ForemanID = @ForemanID OR @ForemanID is Null)
AND (tblForemanReports.WorkDate >= @StartDate OR @StartDate IS NULL)
AND (tblForemanReports.WorkDate <= @EndDate OR @EndDate IS NULL)


GROUP BY  tblJobDetails.JobDescription, 
tblCustomer.FullName,

tblForemanReports.ID,
tblForemanReports.ForemanID,
tblForemanReports.WorkDate, 
tblForemanReports.PremiumTime,
tblForemanReports.ContractTypeID,
tblForemanReports.JobID,
tblUser.FullName,
tblUser.ID,

tblJobCostingSide.ForemanReportID, tblJobCostingSide.WorkDate,
tblJobCostingSide.FieldSupportServicesID,
tblJobCostingSide.SubcontractorFormID,
tblJobCostingSide.JobID,
tblForemanReports.ReviewDone, tblForemanReports.ReadyForInvoice,
tblForemanReports.AllWorkCompletedForJob,
tblForemanReports.IsAllWorkDoneForProject,
tblForemanReports.CreatedBy,
statusForeman.StatusName,
tblJobType.Title


UNION 

SELECT tblPavingDetails.JobID, dbo.fnGetJobIDStringByJobId(tblPavingDetails.JobID) AS JobIDString, tblPavingDetails.ID AS PavingDetailID, tblPavingDetails.ReviewDone, tblPavingDetails.ReadyForInvoice,
'' AS [AllWorkCompletedForJob], '' AS [IsAllWorkDoneForProject], statusPavingOnly.StatusName,
tblJobDetails.JobDescription,
NULL AS Customer,

tblUser.FullName AS [Foreman],
tblUser.ID AS [ForemanID],
'Paving Only' AS [FormType],

tblPavingOnlyForm.WorkDate, 
NULL AS PremiumTime,
CAST(0 AS BIT) AS TandM,

((SELECT SUM((CAST(Total AS Decimal(20,2)))) FROM tblProbablePayItems
--INNER JOIN tblStatus ON tblStatus.ID = tblProbablePayItems.StatusID
WHERE WorkDate = tblPavingOnlyForm.WorkDate 
AND JobID = tblPavingDetails.JobID
AND (tblProbablePayItems.PavingDetailID = tblPavingDetails.ID)
AND Total IS NOT NULL AND Total <> ''
AND tblProbablePayItems.IsDeleted IS NULL
--AND tblStatus.StatusName = 'Approved by Customer Supervisor'
)) AS PayItem,

0.0 AS ChangeOrder,

((SELECT SUM((CAST(Amount AS Decimal(20,2)))) FROM tblJobCostingSide
--INNER JOIN tblStatus ON tblStatus.ID = tblProbablePayItems.StatusID
WHERE WorkDate = tblPavingOnlyForm.WorkDate 
AND JobID = tblPavingDetails.JobID
AND (tblJobCostingSide.PavingOnlyFormID = tblPavingOnlyForm.ID)
AND (tblJobCostingSide.PavingDetailID = tblPavingDetails.ID)
AND tblJobCostingSide.Amount IS NOT NULL
--AND tblStatus.StatusName = 'Approved by Customer Supervisor'
)) AS CostItem,

0.0 AS InvoiceAmount,

CASE 
    WHEN (SELECT COUNT(tblChangeOrderGenerated.ID) 
            FROM tblChangeOrderGenerated Inner join tblChangeOrderGeneratedToDates 
            ON tblChangeOrderGenerated.ID = tblChangeOrderGeneratedToDates.ChangeOrderID
            WHERE tblChangeOrderGenerated.JobID = tblPavingDetails.JobID AND tblChangeOrderGeneratedToDates.[Date] = tblPavingOnlyForm.WorkDate) > 0
    THEN 1
        ELSE 0
END AS IsChangeOrderGenerated,
tblJobType.Title AS JobType

FROM tblPavingDetails

INNER JOIN tblPavingOnlyForm ON tblPavingOnlyForm.ID = tblPavingDetails.PavingOnlyFormID

--Replaced by Rishi on 8-Nov-2016 to filter Paving only records based on Foreman ID.
LEFT JOIN tblUser ON tblPavingOnlyForm.ForemanID = tblUser.ID



INNER JOIN tblJobDetails ON tblJobDetails.ID = tblPavingDetails.JobID
INNER JOIN tblJobType ON tblJobDetails.JobTypeID = tblJobType.ID
--LEFT JOIN tblForemanReports ON tblProbablePayItems.ForemanReportID = tblForemanReports.ID
--LEFT JOIN tblSubContractorForm ON tblSubContractorForm.ID = tblProbablePayItems.SubcontractorFormID

LEFT JOIN tblStatus statusPavingOnly ON tblPavingOnlyForm.StatusID = statusPavingOnly.ID

LEFT JOIN tblJobToWorkOrder ON tblJobToWorkOrder.ID = tblPavingDetails.WOID

LEFT JOIN tblScatteredServices ON 
(tblScatteredServices.JobID = tblJobDetails.ID 
AND tblScatteredServices.ID = tblJobToWorkOrder.ScatteredServiceID)

LEFT JOIN tblEncapsulationRequest ON
(tblEncapsulationRequest.JobID = tblJobDetails.ID
AND tblEncapsulationRequest.ID = tblJobToWorkOrder.EncapsulationRequestID)

LEFT JOIN tblMiscellaneousRequest ON
(tblMiscellaneousRequest.JobID = tblJobDetails.ID
AND tblMiscellaneousRequest.ID = tblJobToWorkOrder.MiscellaneousRequestID)

WHERE (tblJobDetails.CustomerID = @CustomerID OR @CustomerID IS NULL)
AND (tblJobDetails.OfficeStaffID = @OFficeStaffID 
    OR tblMiscellaneousRequest.OfficeStaffID = @OFficeStaffID 
    OR @OFficeStaffID IS NULL)
AND (tblJobDetails.NeuCoSupervisorID = @SupervisorID 
    OR tblScatteredServices.NeuCoSupervisorID = @SupervisorID 
    OR tblEncapsulationRequest.NeuCoSupervisorID = @SupervisorID
    OR tblMiscellaneousRequest.NeucoSupervisorID = @SupervisorID
    OR @SupervisorID IS NULL)
AND (tblPavingDetails.ReadyForInvoice IS NULL OR tblPavingDetails.ReadyForInvoice = 0)
AND (tblJobDetails.ContractAreaID = @ContractAreaID OR @ContractAreaID IS NULL)
AND (tblJobDetails.ID = @JobID OR @JobID IS NULL)
AND (tblJobDetails.JobDescription like  '%' + @JobDescription +'%' OR @JobDescription IS NULL)
AND (tblJobDetails.CustomerSupervisorID = @CustomerSupervisorID
    OR tblScatteredServices.CustomerSupervisorID = @CustomerSupervisorID
    OR tblMiscellaneousRequest.CustomerSupervisorID = @CustomerSupervisorID
    OR tblEncapsulationRequest.CustomerSupervisorID = @CustomerSupervisorID
    OR @CustomerSupervisorID IS NULL)
--AND (tblUser.ID = @ForemanID OR @ForemanID is Null)
AND (tblPavingOnlyForm.WorkDate >= @StartDate OR @StartDate IS NULL)
AND (tblPavingOnlyForm.WorkDate <= @EndDate OR @EndDate IS NULL)

GROUP BY  tblJobDetails.JobDescription, 

tblPavingOnlyForm.ID,
tblPavingDetails.ID,
tblPavingOnlyForm.WorkDate, 
tblPavingDetails.JobID,
tblUser.FullName,
tblUser.ID,

tblPavingDetails.ReviewDone, tblPavingDetails.ReadyForInvoice,

tblPavingDetails.CreatedBy,

statusPavingOnly.StatusName,
tblJobType.Title

UNION

SELECT tblFieldSupportServices.JobID, dbo.fnGetJobIDStringByJobId(tblFieldSupportServices.JobID) AS JobIDString, tblFieldSupportServices.ID AS FieldSupportID, tblFieldSupportServices.ReviewDone, NULL,
NULL, NULL, statusFieldSupport.StatusName,
tblJobDetails.JobDescription, 
tblCustomer.FullName As Customer,

tblUser.FullName AS [Foreman],
tblUser.ID AS [ForemanID],
'Field Support' AS [FormType],

tblFieldSupportServices.Date,
NULL,

NULL,

((SELECT SUM((CAST(Total AS Decimal(20,2)))) FROM tblProbablePayItems
--INNER JOIN tblStatus ON tblStatus.ID = tblProbablePayItems.StatusID
WHERE WorkDate = tblFieldSupportServices.Date 
AND JobID = tblFieldSupportServices.JobID
AND (FieldSupportServicesID = tblFieldSupportServices.ID)
AND Total IS NOT NULL AND Total <> ''
AND tblProbablePayItems.IsDeleted IS NULL
--AND tblStatus.StatusName = 'Approved by Customer Supervisor'
)) AS PayItem,

--((SELECT SUM((CAST(Total AS Decimal(20,2)))) FROM tblProbableChangeOrders
----INNER JOIN tblStatus ON tblStatus.ID = tblProbableChangeOrders.StatusID
--WHERE WorkDate = tblForemanReports.WorkDate 
--AND JobID = tblForemanReports.JobID
--AND (ForemanReportID = tblForemanReports.ID)
----AND (tblStatus.StatusName NOT LIKE 'Rejected by Customer Supervisor'
--AND InvoiceID IS NULL
--AND Total IS NOT NULL AND Total <> ''
--)) AS ChangeOrder,

NULLIF(((SELECT ISNULL(SUM((CAST(Total AS Decimal(20,2)))), 0) FROM tblProbableChangeOrders
--INNER JOIN tblStatus ON tblStatus.ID = tblProbableChangeOrders.StatusID
WHERE WorkDate = tblFieldSupportServices.Date 
AND JobID = tblFieldSupportServices.JobID
AND (FieldSupportServicesID = tblFieldSupportServices.ID)
AND tblProbableChangeOrders.IsDeleted IS NULL
--AND (tblStatus.StatusName NOT LIKE 'Rejected by Customer Supervisor'
AND InvoiceID IS NULL
AND Total IS NOT NULL AND Total <> ''
)),0) AS ChangeOrder,

--(SUM(CAST(tblJobCostingSide.Amount AS Decimal(20,2)))) AS CostItem,

((SELECT SUM((CAST(Amount AS Decimal(20,2)))) FROM tblJobCostingSide
--INNER JOIN tblStatus ON tblStatus.ID = tblProbablePayItems.StatusID
WHERE WorkDate = tblFieldSupportServices.Date 
AND JobID = tblFieldSupportServices.JobID
AND (tblJobCostingSide.FieldSupportServicesID = tblFieldSupportServices.ID)
AND tblJobCostingSide.Amount IS NOT NULL
--AND tblStatus.StatusName = 'Approved by Customer Supervisor'
)) AS CostItem,

0.0 AS InvoiceAmount,

CASE 
    WHEN (SELECT COUNT(tblChangeOrderGenerated.ID) 
            FROM tblChangeOrderGenerated Inner join tblChangeOrderGeneratedToDates 
            ON tblChangeOrderGenerated.ID = tblChangeOrderGeneratedToDates.ChangeOrderID
            WHERE tblChangeOrderGenerated.JobID = tblFieldSupportServices.JobID AND tblChangeOrderGeneratedToDates.[Date] = tblFieldSupportServices.Date) > 0
    THEN 1
        ELSE 0
END AS IsChangeOrderGenerated,
tblJobType.Title AS JobType

FROM tblFieldSupportServices

LEFT JOIN tblUser ON LOWER(tblFieldSupportServices.CreatedBy) = LOWER(tblUser.Username)

LEFT JOIN tblJobCostingSide ON (tblJobCostingSide.FieldSupportServicesID = tblFieldSupportServices.ID
AND tblJobCostingSide.Amount IS NOT NULL)

INNER JOIN tblJobDetails ON tblJobDetails.ID = tblFieldSupportServices.JobID
INNER JOIN tblJobType ON tblJobDetails.JobTypeID = tblJobType.ID
INNER JOIN tblCustomer ON tblCustomer.ID = tblJobDetails.CustomerID

LEFT JOIN tblProbablePayItems ON tblProbablePayItems.FieldSupportServicesID = tblFieldSupportServices.ID
--LEFT JOIN tblSubContractorForm ON tblSubContractorForm.ID = tblProbablePayItems.SubcontractorFormID

LEFT JOIN tblStatus statusFieldSupport ON tblFieldSupportServices.StatusID = statusFieldSupport.ID

--LEFT JOIN tblJobToWorkOrder ON tblJobToWorkOrder.ID = tblFieldSupportServices.JobToWorkOrderID

LEFT JOIN tblScatteredServices ON 
(tblScatteredServices.JobID = tblJobDetails.ID)
--AND tblScatteredServices.ID = tblJobToWorkOrder.ScatteredServiceID)

LEFT JOIN tblEncapsulationRequest ON
(tblEncapsulationRequest.JobID = tblJobDetails.ID)
--AND tblEncapsulationRequest.ID = tblJobToWorkOrder.EncapsulationRequestID)

LEFT JOIN tblMiscellaneousRequest ON
(tblMiscellaneousRequest.JobID = tblJobDetails.ID)
--AND tblMiscellaneousRequest.ID = tblJobToWorkOrder.MiscellaneousRequestID)

WHERE (tblJobDetails.CustomerID = @CustomerID OR @CustomerID IS NULL)
AND (tblJobDetails.OfficeStaffID = @OFficeStaffID 
    OR tblMiscellaneousRequest.OfficeStaffID = @OFficeStaffID 
    OR @OFficeStaffID IS NULL)

AND ((@IsSupportServiceSupervisor = 1 
                AND ((tblFieldSupportServices.IsVacTruckAdded IS NULL 
                        AND (tblJobDetails.NeuCoSupervisorID = @SupervisorID 
                        OR tblScatteredServices.NeuCoSupervisorID = @SupervisorID 
                        OR tblEncapsulationRequest.NeuCoSupervisorID = @SupervisorID 
                        OR tblMiscellaneousRequest.NeucoSupervisorID = @SupervisorID 
                        OR @SupervisorID IS NULL))
                OR (tblFieldSupportServices.IsVacTruckAdded = 1 OR @SupervisorID IS NULL)))
        OR
        (@IsSupportServiceSupervisor = 0 AND (tblJobDetails.NeuCoSupervisorID = @SupervisorID 
            OR tblScatteredServices.NeuCoSupervisorID = @SupervisorID 
            OR tblEncapsulationRequest.NeuCoSupervisorID = @SupervisorID
            OR tblMiscellaneousRequest.NeucoSupervisorID = @SupervisorID
            OR @SupervisorID IS NULL)))

AND (tblFieldSupportServices.ReviewDone IS NULL OR tblFieldSupportServices.ReviewDone = 0)
AND (tblProbablePayItems.InvoiceID IS NULL)
AND (tblJobDetails.ContractAreaID = @ContractAreaID OR @ContractAreaID IS NULL)
AND (tblJobDetails.ID = @JobID OR @JobID IS NULL)
AND (tblJobDetails.JobDescription like  '%' + @JobDescription +'%' OR @JobDescription IS NULL)
AND (tblJobDetails.CustomerSupervisorID = @CustomerSupervisorID
    OR tblScatteredServices.CustomerSupervisorID = @CustomerSupervisorID
    OR tblMiscellaneousRequest.CustomerSupervisorID = @CustomerSupervisorID
    OR tblEncapsulationRequest.CustomerSupervisorID = @CustomerSupervisorID
    OR @CustomerSupervisorID IS NULL)
--AND (tblUser.ID = @ForemanID OR @ForemanID is Null)
AND (tblFieldSupportServices.[Date] >= @StartDate OR @StartDate IS NULL)
AND (tblFieldSupportServices.[Date] <= @EndDate OR @EndDate IS NULL)


GROUP BY  tblJobDetails.JobDescription, 
tblCustomer.FullName,

tblFieldSupportServices.ID,
tblFieldSupportServices.ForemanID,
tblFieldSupportServices.Date, 
tblFieldSupportServices.JobID,
tblUser.FullName,
tblUser.ID,

tblJobCostingSide.ForemanReportID, tblJobCostingSide.WorkDate,
tblJobCostingSide.FieldSupportServicesID,
tblJobCostingSide.SubcontractorFormID,
tblJobCostingSide.JobID,
tblFieldSupportServices.ReviewDone, 
tblFieldSupportServices.CreatedBy,
statusFieldSupport.StatusName,
tblJobType.Title

) AS ActiveForemanReports

WHERE     
      (ActiveForemanReports.JobID = @JobID OR (@JobID IS NULL AND @ForemanID IS NULL) 
      OR (ActiveForemanReports.JobID IN (SELECT * FROM @JobIDs) AND @JobID IS NULL ))) AS ActiveForemanReportsOuter

Where RowNumber >= @StartRowIndex and RowNumber <= @EndRowIndex  OR (@StartRowIndex IS NULL AND @EndRowIndex IS NULL)


END
3
  • This has to run the query, evaluate the entire result set and then reurn the selection of rows that you are looking for (given that you are doing this dynamic sorting). You would be better off returning the entire result set (within reason) and then paging it in the app layer.\ – Nic Dec 14 '16 at 15:40
  • Okay, I understand you point.. But can we make it more efficient in terms of joins and unions.. – Rishi Samadhiya Dec 15 '16 at 7:30
  • That is too localized a question. It would require knowing all the schema, having the data, and knowing the results that were required. – Nic Dec 15 '16 at 14:50
0

Optimizing a Stored procedure is too broad of a topic. Firstly if you are getting a consistent run time from your stored procedure we may be able to rule our parameter sniffing issue

I would suggest you start with querying the procedure cache to find out which query inside the stored procedure is taking up time and then start optimizing it

Sample query

select top 100 object_name(object_id),ps.execution_count as proc_exec_count, qs.plan_generation_num, qs.execution_count as Stmt_exec_count,qs.total_elapsed_time/qs.execution_count as AVG_Run_Time,   qs.total_worker_time/qs.execution_count AS Avg_CPU_Time,
qs.last_elapsed_time, qs.min_elapsed_time, qs.max_elapsed_time,qs.min_rows,qs.max_rows,
SUBSTRING ([st].[text], 
        ([qs].[statement_start_offset] / 2) + 1, 
        ((CASE [statement_end_offset] 
            WHEN -1 THEN DATALENGTH ([st].[text]) 
            ELSE [qs].[statement_end_offset] END 
            - [qs].[statement_start_offset]) / 2) + 1) 
            AS [StatementText]--,qp.query_plan 
from sys.dm_exec_procedure_stats ps  
JOIN [sys].[dm_exec_query_stats] AS [qs]
        ON [ps].[plan_handle] = [qs].[plan_handle]
--CROSS APPLY sys.dm_exec_query_plan      (ps.plan_handle) qp
CROSS APPLY [sys].[dm_exec_sql_text]    ([qs].[sql_handle]) AS [st]
where database_id=db_id('databasename') --and qs.execution_count >100 
and object_id in (object_id('sp')) 
ORDER BY avg_run_time DESC 
2
  • Yes its taking consistent execution time... – Rishi Samadhiya Dec 14 '16 at 6:46
  • I executed the above query and found this result: (No column name): spGetActiveForemanReportsCustomPaging proc_exec_count: 1 plan_generation_num: 1 Stmt_exec_count: 1 AVG_Run_Time: 310 Avg_CPU_Time: 310 last_elapsed_time: 310 min_elapsed_time: 310 max_elapsed_time: 310 min_rows: 0 max_rows: 0 StatementText: SELECT * FROM… (Query) – Rishi Samadhiya Dec 14 '16 at 6:51
0

You can use OFFSET ROWS FETCH NEXT ROWS ONLY instead of row number for using pagination purpose.

And remove row number wherever you use:

Bcz first of all it will select all the record after that pagination is done but in offset it fetching pagination rows only.

Here one example of pagination with offset : EX:

SELECT Id, ProductName, UnitPrice, Package
FROM Product
ORDER BY UnitPrice DESC
OFFSET 10 ROWS
FETCH NEXT 5 ROWS ONLY

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.