October 26, 2005 at 12:48 am
Hi,
This stored procedure has been in used for some time. But, only recently, it runs into a timeout error.
Here's the code:
CREATE PROCEDURE User_SelectActivity
@ManagedCountry varchar(20),
@CustomerNo varchar(20),
@LocationCode varchar(10),
@IsWholesaler tinyint,
@SalespersonPurchaserCode varchar(10),
@CaseCount int OUTPUT,
@ProformaCount int OUTPUT,
@RejectedCount int OUTPUT,
@TransferCount int OUTPUT
AS
SET NOCOUNT ON
DECLARE @PartnerAccess tinyint
SELECT @PartnerAccess = [Partner Access] FROM [Web Salesperson_Purchaser] WHERE [Code] = @SalespersonPurchaserCode
SELECT
@CaseCount = COUNT(DISTINCT [No_])
FROM
[Web Case Header] WCH
WHERE
(
(@ManagedCountry = '' AND
(
WCH.[Customer No_] = @CustomerNo OR WCH.[Shortcut Dimension 2 Code] = @CustomerNo
OR (@PartnerAccess > 0 AND WCH.[Customer No_] IN
(SELECT [Code] FROM [Web Partner Access]
WHERE [Salesperson_Purchaser Code] = @SalespersonPurchaserCode))
)
)
OR
(@ManagedCountry <> '' AND WCH.[Shortcut Dimension 1 Code] = @ManagedCountry)
)
SELECT
@ProformaCount = COUNT(DISTINCT [No_])
FROM
[Web Proforma Collection Report] PR
WHERE
(
(@ManagedCountry = '' AND
(
PR.[Customer No_] = @CustomerNo OR PR.[Shortcut Dimension 2 Code] = @CustomerNo
OR (@PartnerAccess > 0 AND PR.[Customer No_] IN
(SELECT [Code] FROM [Web Partner Access]
WHERE [Salesperson_Purchaser Code] = @SalespersonPurchaserCode))
)
)
OR
(@ManagedCountry <> '' AND PR.[Shortcut Dimension 1 Code] = @ManagedCountry)
)
SELECT
@RejectedCount = COUNT(DISTINCT [No_])
FROM
[Web Case Header]WCH
INNER JOIN [Web Case Line]WCL ON WCL.[Case No_] = WCH.[No_]
WHERE
(
(@ManagedCountry = '' AND
(
WCH.[Customer No_] = @CustomerNo OR WCH.[Shortcut Dimension 2 Code] = @CustomerNo
OR (@PartnerAccess > 0 AND WCH.[Customer No_] IN
(SELECT [Code] FROM [Web Partner Access]
WHERE [Salesperson_Purchaser Code] = @SalespersonPurchaserCode))
)
)
OR
(@ManagedCountry <> '' AND WCH.[Shortcut Dimension 1 Code] = @ManagedCountry)
)
AND (WCL.[Status] = 1 OR WCL.[Status] = 11)
SELECT
@TransferCount = COUNT(distinct [No_])
FROM
[HP-SQTS$Transfer Header] TH INNER JOIN [HP-SQTS$Transfer Line] TL ON (TH.[No_] = TL.[Document No_])
WHERE
( @LocationCode = '' OR TH.[Transfer-to Code] IN
(
SELECT @LocationCode
UNION
SELECT [Web Customer].[Web Location Code]
FROM [Web Salesperson_Purchaser]
INNER JOIN [Web Partner Access] ON ([Web Partner Access].[Salesperson_Purchaser Code] = [Web Salesperson_Purchaser].[Code])
INNER JOIN [Web Customer] ON ([Web Customer].[No_] = [Web Partner Access].[Code])
WHERE
[Web Salesperson_Purchaser].[Code] = @SalespersonPurchaserCode
AND [Web Salesperson_Purchaser].[Partner Access] = 1
)
)
OR ( @LocationCode = '' OR TH.[Transfer-from Code] IN
(
SELECT @LocationCode
UNION
SELECT [Web Customer].[Web Location Code]
FROM [Web Salesperson_Purchaser]
INNER JOIN [Web Partner Access] ON ([Web Partner Access].[Salesperson_Purchaser Code] = [Web Salesperson_Purchaser].[Code])
INNER JOIN [Web Customer] ON ([Web Customer].[No_] = [Web Partner Access].[Code])
WHERE
[Web Salesperson_Purchaser].[Code] = @SalespersonPurchaserCode
AND [Web Salesperson_Purchaser].[Partner Access] = 1
)
)
AND TH.[Status] = 1 -- Released
AND (@IsWholesaler = 1 OR [No_] NOT IN -- Already Received
(
SELECT [No_]
FROM [HP-SQTS$Web Transfer Header]
WHERE [Status] = 1
AND ([Transfer-to Code] = @LocationCode)
)
)
AND TL.[Derived From Line No_] = 0
AND TL.[Quantity Received] = 0
GO
I tried executing the procedure and it takes a few seconds to complete execution. Should I be adding the (NoLock) command?
Please advise.
Thanks.
October 26, 2005 at 1:18 am
I just glanced over it quickly, but I've got a feeling that it can be optimised a bit.
Can you please post table definitions, sample data and expected output. Also the indexes defined on the tables.
Specfying WITH (NOLOCK) tell SQL to neither take locks nor honour locks. It means there is a definate possibility of dirty reads (reading data partway through a modification)
Nolock may help if the problem is the Sp getting blocked. Can you confirm wheter or not that is the case? (run sp_who2 and sp_lock to check processes, locks and blocks)
Is this getting called from a web page?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply