SQL Server Execution Timeout Expired
-
I have a situation where when I run this stored proc on the live database I get a timeout error. if I copy the two tables into a different dead database where there are no transactions happening, then I get a result instantly the table Jn has over 2 million records.
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
has not helped. Even WITH NOLOCK has not helped. any ideas pls ? stored proc is below
create PROCEDURE [dbo].[mysp]
@sRefDateFrom AS CHAR(8) , @sRefDateTo AS CHAR(8) -- Dates are expected in 'YYYYMMDD'
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SELECT A.FormatCode, A.AcctName , SUM(J.Debit) AS Debit, SUM (J.Credit) AS Credit
FROM JN J
JOIN AC A
ON A.AcctCode = J.Account
WHERE J.RefDate >= @sRefDateFrom
AND J.RefDate <= @sRefDateTo
GROUP BY A.FormatCode , A.AcctName
ORDER BY A.FormatCode
END -
I have a situation where when I run this stored proc on the live database I get a timeout error. if I copy the two tables into a different dead database where there are no transactions happening, then I get a result instantly the table Jn has over 2 million records.
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
has not helped. Even WITH NOLOCK has not helped. any ideas pls ? stored proc is below
create PROCEDURE [dbo].[mysp]
@sRefDateFrom AS CHAR(8) , @sRefDateTo AS CHAR(8) -- Dates are expected in 'YYYYMMDD'
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SELECT A.FormatCode, A.AcctName , SUM(J.Debit) AS Debit, SUM (J.Credit) AS Credit
FROM JN J
JOIN AC A
ON A.AcctCode = J.Account
WHERE J.RefDate >= @sRefDateFrom
AND J.RefDate <= @sRefDateTo
GROUP BY A.FormatCode , A.AcctName
ORDER BY A.FormatCode
ENDMake sure you have an index on RefDate. Probably another one on FormatCode and AcctName. Beyond that, you need to use the database tools to track down which part is taking the most time. If it is Microsoft Sql Server then you can use the Estimated Execution Plan and other tools.