timeout expired error - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Administration

timeout expired error

Author

Topic

kotavenu
Starting Member

1 Post

Posted - 2010-10-19 : 11:30:57

I am getting timeout expired error, my application in ASP.NET

Could you please gide me how to optimize sql query

My SP is..........................

ALTER proc [dbo].[UPS_GET_CUSTOM_CLOSED_SUMMARY]
@IN_PROCESS_AREA VARCHAR(100)= NULL,
@IN_STATUS VARCHAR(200) = NULL,
@IN_FROM_DATE VARCHAR(100) = NULL,
@IN_TO_DATE VARCHAR(100) =NULL
AS
Begin

DECLARE @Loc_Table TABLE ( Counts INT , Status VARCHAR(20), Process_Area VARCHAR(1000))

INSERT INTO @Loc_Table
SELECT COUNT(*),'CANCELLED',Process_Area
FROM dbo.AgedRequestAll (NOLOCK)
WHERE (Status = 'CANCELLED')
AND (Process_Area = @IN_PROCESS_AREA OR @IN_PROCESS_AREA IS NULL)
AND (ACTUAL_FINISH_DATE > @IN_FROM_DATE OR @IN_FROM_DATE IS NULL)
AND (ACTUAL_FINISH_DATE < @IN_TO_DATE OR @IN_TO_DATE IS NULL)
GROUP BY Process_Area
UNION ALL
SELECT COUNT(*),'RESOLVED', Process_Area
FROM dbo.AgedRequestAll (NOLOCK)
WHERE (Status = 'RESOLVED')
AND (Process_Area = @IN_PROCESS_AREA OR @IN_PROCESS_AREA IS NULL)
AND (ACTUAL_FINISH_DATE > @IN_FROM_DATE OR @IN_FROM_DATE IS NULL)
AND (ACTUAL_FINISH_DATE < @IN_TO_DATE OR @IN_TO_DATE IS NULL)
GROUP BY Process_Area
UNION ALL
SELECT COUNT(*),'CLOSED', Process_Area
FROM dbo.AgedRequestAll (NOLOCK)
WHERE (Status = 'CLOSED')
AND (Process_Area = @IN_PROCESS_AREA OR @IN_PROCESS_AREA IS NULL)
AND (ACTUAL_FINISH_DATE > @IN_FROM_DATE OR @IN_FROM_DATE IS NULL)
AND (ACTUAL_FINISH_DATE < @IN_TO_DATE OR @IN_TO_DATE IS NULL)
GROUP BY Process_Area

DECLARE @loc_Result Table (Process_Area VARCHAr(1000),CLOSED INT,RESOLVED INT,CANCELLED INT)

INSERT INTO @loc_Result
SELECT Process_Area ,CLOSED ,RESOLVED ,CANCELLED
FROM
(SELECT Counts,Status,Process_Area
from @Loc_Table
) p
PIVOT
(sum(Counts)
for Status IN (CLOSED,RESOLVED,CANCELLED))
AS pvt

SELECT TOP (100) PERCENT Process_Area, CLOSED, RESOLVED, CANCELLED
FROM @loc_Result
UNION ALL
select 'Total',sum(CLOSED),sum(RESOLVED),sum(CANCELLED) from @loc_Result

END

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-10-19 : 11:46:00

The first thing I see is that you can do your counts in only one statement instead of three wit union all:
Try it:

INSERT INTO @Loc_Table
SELECT COUNT(*),Status,Process_Area
FROM dbo.AgedRequestAll (NOLOCK)
WHERE (Status in('CANCELLED','RESOLVED','CLOSED')
AND (Process_Area = @IN_PROCESS_AREA OR @IN_PROCESS_AREA IS NULL) 
AND (ACTUAL_FINISH_DATE > @IN_FROM_DATE OR @IN_FROM_DATE IS NULL) 
AND (ACTUAL_FINISH_DATE < @IN_TO_DATE OR @IN_TO_DATE IS NULL) 
GROUP BY Process_Area,Status

No, you're never too old to Yak'n'Roll if you're too young to die.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources