rsbutterfly16
IS-IT--Management
hi guys, i hope you can help me, there is a stored procedure that i used in a filter form but it keeps timing out when i have over 20 users. I have tried optimizing it but i am stock can anyone tell me what else can i do?
Any help will be greatly apreciate it!!!
Code:
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].[US_SearchForm]
/*************************
Declare input parameters.
*************************/
@TBL_ID INT = NULL
,@AccountCodeID INT = NULL ,@LocationID INT = NULL
,@LaboratoryID INT = NULL
,@SOTypeID INT = NULL
,@PartID INT = NULL
,@PartConditionID INT = NULL
,@DispositionID INT = NULL
,@ArchiveID INT = NULL
,@CategoryID INT = NULL
,@HouseSerialNumber VARCHAR(50) = NULL
,@CallNumber VARCHAR(50) = NULL
,@SONumber VARCHAR(50) = NULL
,@SerialNumber VARCHAR(50) = NULL
,@SOStartFromDT DATETIME = NULL
,@SOStartToDT DATETIME = NULL
,@Received BIT = 0
,@InProgress BIT = 0
,@Closed BIT = 0
,@Cancelled BIT = 0
,@Out BIT = 0
,@iUserID INT = 1
,@SOEndFromDT DATETIME = NULL
,@SOEndToDT DATETIME = NULL
,@iSearchRowsQuantity INT = 0
AS
IF @iSearchRowsQuantity = NULL SET @iSearchRowsQuantity = 0
SET ROWCOUNT @iSearchRowsQuantity
/*************************
Declare local variables
*************************/
DECLARE @Completed INT
DECLARE @CompletedToWH INT
DECLARE @Cancelled INT
DECLARE @LabTransfer INT
DECLARE @ReceivedTypeID INT
DECLARE @InProgressTypeID INT
DECLARE @ClosedTypeID INT
DECLARE @CancelledTypeID INT
DECLARE @OutTypeID INT
/*************************
Initialize local variables
*************************/
SET @Completed = 45
SET @CompletedToWH = 52
SET @Cancelled = 01
SET @LabTransfer = 111
SET @ReceivedTypeID = 42
SET @InProgressTypeID = 66
SET @CancelledTypeID = 06
SET @OutTypeID = 152
/**************************
Execute sql statement
***************************/
SELECT DISTINCT
Items.ItemsID_PK
AS [ItemsID] ,
Accounts.Account AS [Account] ,
Codes.AccountCode AS [AccountCode] ,
Locations_V.locationCode
AS [FSL] ,
Labs_V.LabCode AS [Laboratory] ,
USTransfers.USTransferID_PK AS [SONumber] ,
USTransfers.CreatedDT AS [SODate] ,
StatusCodes_V.StatusCodeDesc AS [SOStatus] ,
OP.PartNumber AS [OrgPartNumber] , Items.SerialNumber AS [SerialNumber] ,
Calls.CallNumber AS [CallNumber] ,
(
SELECT
COUNT(ID_PK)
FROM
TBL_Items ST WITH (NOLOCK) WHERE
ST.AccountCodeID_FK = Items.AccountCodeID_FK AND
ST.PartID_FK = Items.PartID_FK AND
ST.LocationID_FK = Items.LocationID_FK AND
ST.StatusCodeID_FK = 20 AND ST.CallID_FK IS NULL
) AS [InvAvailable] ,
Items.StatusCodeID_FK
AS [StatusID] , PartReorderLevels.MinQty AS [Min] , PartReorderLevels.MaxQty AS [Max] Types_V.TypeCode AS [PartCondition] , OADC.DispositionCode AS [OrgDisposition] , OM.ArchiveCodeDescription AS [OrgArchive] , dbo.vchrGetDefectItems(Items.ItemsID_PK) AS [Defect]
, --AL
DP.PartNumber AS [DestPartNumber] , DP.PartDescription AS [DestPartDescription] , DADC.DispositionCode AS [DestDisposition] ,
DM.ArchiveCodeDescription AS [DestArchive] , Items.HouseSerialNumber AS [HouseSerialNumber] ,
MovItem_Log.CreatedDT AS [SOClosedDate]
FROM --Transactional Data
TBL_USTransfers USTransfers WITH (NOLOCK)
INNER JOIN TBL_Items Items WITH (NOLOCK) ON USTransfers.USTransferID_PK = Items.USTransferID_FK
INNER JOIN TBL_Items Items WITH (NOLOCK) ON Items.ID_FK = Items.ID_PK
LEFT JOIN TBL_USTransferItem_Log MovItem_Log WITH (NOLOCK) ON Items.ItemsID_PK = MovItem_Log.ItemsID_FK
AND MovItem_Log.StatusCodeID_FK = @CompletedToWH
LEFT JOIN TBL_PartReorderLevels PartReorderLevels WITH (NOLOCK) ON Items.PartID_FK = PartReorderLevels.PartID_FK
AND USTransfers.LocationID_FK = PartReorderLevels.LocationID_FK
LEFT JOIN CM_Calls Calls WITH (NOLOCK) ON Items.CallID_FK = Calls.CallID_PK
INNER JOIN Accounts WITH (NOLOCK) ON Accounts.AccountID_PK = USTransfers.AccountID_FK
INNER JOIN Codes WITH (NOLOCK) ON Codes.AccountCodeID_PK = Items.AccountCodeID_FK
INNER JOIN Locations_V WITH (NOLOCK) ON Locations_V.LocationID_PK = USTransfers.LocationID_FK
LEFT JOIN Labs_V WITH (NOLOCK) ON Labs_V.LabID_PK = USTransfers.LabID_FK
INNER JOIN StatusCodes_V WITH (NOLOCK) ON StatusCodes_V.StatusCodeID_PK = Items.StatusCodeID_FK
INNER JOIN TBL_Parts_V OP WITH (NOLOCK) ON OP.PartID_PK = Items.Org_PartID_FK
INNER JOIN TBL_Parts_V DP WITH (NOLOCK) ON DP.PartID_PK = Items.Dest_PartID_FK
INNER JOIN Types_V WITH (NOLOCK) ON Types_V.TypeID_PK = Items.PartConditionID_FK
LEFT JOIN TBL_AccountDispositionCodes OADC WITH (NOLOCK) ON OADC.AccountDispositionCodeID_PK = Items.Org_DispositionCodeID_FK
LEFT JOIN TBL_AccountDispositionCodes DADC WITH (NOLOCK) ON DADC.AccountDispositionCodeID_PK = Items.Dest_DispositionCodeID_FK
LEFT JOIN Archivees_V OM WITH (NOLOCK) ON OM.ArchiveID_PK = Items.Org_ArchiveID_FK
LEFT JOIN Archivees_V DM WITH (NOLOCK) ON DM.ArchiveID_PK = Items.Dest_ArchiveID_FK
WHERE ((USTransfers.AccountID_FK = @TBL_ID AND @TBL_ID IS NOT NULL) OR @TBL_ID IS NULL)
AND ((Items.AccountCodeID_FK = @AccountCodeID AND @AccountCodeID IS NOT NULL)
OR @AccountCodeID IS NULL)
AND((USTransfers.LocationID_FK = @LocationID AND @LocationID IS NOT NULL) OR @LocationID IS NULL) AND
((USTransfers.LabID_FK = @LaboratoryID AND @LaboratoryID IS NOT NULL) OR @LaboratoryID IS NULL) AND
((Items.PartID_FK = @PartID AND @PartID IS NOT NULL) OR @PartID IS NULL) AND
((OADC.AccountDispositionCodeID_PK = @DispositionID AND @DispositionID IS NOT NULL) OR @DispositionID IS NULL) AND --AL
((Items.PartConditionID_FK = @PartConditionID AND @PartConditionID IS NOT NULL) OR @PartConditionID IS NULL) AND
((OM.ArchiveID_PK = @ArchiveID AND @ArchiveID IS NOT NULL) OR @ArchiveID IS NULL) AND ((USTransfers.USTransferTypeID_FK = @SOTypeID AND @SOTypeID IS NOT NULL) OR @SOTypeID IS NULL)
AND ((Items.CategoryID_PK = @CategoryID AND @CategoryID IS NOT NULL) OR @CategoryID IS NULL)
AND ((Calls.CallNumber = @CallNumber AND @CallNumber IS NOT NULL) OR @CallNumber IS NULL) AND ((USTransfers.USTransferID_PK = @SONumber AND @SONumber IS NOT NULL) OR @SONumber IS NULL) AND
((Items.SerialNumber = @SerialNumber AND @SerialNumber IS NOT NULL) OR @SerialNumber IS NULL) AND
((Items.HouseSerialNumber = @HouseSerialNumber AND @HouseSerialNumber IS NOT NULL) OR @HouseSerialNumber IS NULL) AND --AL
((USTransfers.CreatedDT >= @SOStartFromDT AND @SOStartFromDT IS NOT NULL) OR @SOStartFromDT IS NULL) AND
((USTransfers.CreatedDT <= @SOStartToDT AND @SOStartToDT IS NOT NULL) OR @SOStartToDT IS NULL) AND
(Items.IsComponent <> 1 ) AND --RM
(
(Items.StatusCodeID_FK = @ReceivedTypeID AND @Received = 1) OR
(Items.StatusCodeID_FK = @InProgressTypeID AND @InProgress = 1) OR
(
Items.StatusCodeID_FK = @Completed AND @Closed = 1 AND
((MovItem_Log.CreatedDT >= @SOEndFromDT AND @SOEndFromDT IS NOT NULL) OR @SOEndFromDT IS NULL) AND --AL
((MovItem_Log.CreatedDT <= @SOEndToDT AND @SOEndToDT IS NOT NULL) OR @SOEndToDT IS NULL) ) OR
(
Items.StatusCodeID_FK = @CompletedToWH AND @Closed = 1 AND
((MovItem_Log.CreatedDT >= @SOEndFromDT AND @SOEndFromDT IS NOT NULL) OR @SOEndFromDT IS NULL) AND
((MovItem_Log.CreatedDT <= @SOEndToDT AND @SOEndToDT IS NOT NULL) OR @SOEndToDT IS NULL)
) OR (Items.StatusCodeID_FK = @CancelledTypeID AND @Cancelled = 1) OR
(Items.StatusCodeID_FK = @OutTypeID AND @Out = 1) )
AND USTransfers.USTransferTypeID_FK = @LabTransfer
AND USTransfers.AccountID_FK IN
(
SELECT tbl_gra.accountID_FK
FROM UserGroups_L tbl_us WITH(NOLOCK)
INNER JOIN Groups_V tbl_gr WITH(NOLOCK)
ON tbl_us.groupID_FK = tbl_gr.groupID_PK
INNER JOIN GroupsAccounts_L tbl_gra WITH(NOLOCK)
ON tbl_gr.groupID_PK = tbl_gra.groupID_FK
WHERE tbl_us.userID_FK = @iUserID
)
SET ROWCOUNT 0
/***** To provide the second table containing the table style info ***/
Exec _ADM_Get_TableStyle 'US_SearchForm'
/********************************************************************/
RETURN 0Any help will be greatly apreciate it!!!