Help with sp optimization wanted

I have the following stored procedure which gives me horrible
performance. It takes about 60-70 seconds to run so I get a timeout
message in my ASP.NET page. The purpose with the sp is to show those
project where forecast exceeds budget or where booked exceeds
forecast. I'm very grateful for help with optimization!

ALTER PROCEDURE [dbo].[spPROJECTDashboardGet]
@ProjectTypeID nvarchar(10)=''
AS
BEGIN

SET NOCOUNT ON;

DECLARE @CurrentYear smallint,
@YearFrom smallint


-- Get current year
SET @CurrentYear = (SELECT CurrentYear FROM tblCompany)


-- Set first year depending on if project type runs over multiple
years
IF (SELECT MultipleYears FROM tblProjectType WHERE ProjectTypeID =
@ProjectTypeID) = 0
BEGIN
SET @YearFrom = @CurrentYear
END
ELSE
BEGIN
SET @YearFrom = 0
END


-- Create temporary table
CREATE TABLE #tmpResult(
ProjectID nvarchar(10) NULL,
ProjectName1 nvarchar(50) NULL,
Budget numeric(18,2) NULL,
Booked numeric(18,2) NULL,
Forecast numeric(18,2) NULL,
DashboardDifference decimal(18,1) NULL,
DiffType int
) ON [PRIMARY]


-- Insert rows for DiffBudgetForecast
INSERT #tmpResult(ProjectID, ProjectName1, Budget, Forecast, Booked,
DashboardDifference, DiffType)
SELECT ProjectID,
ProjectName1,
(SELECT Sum(Budget)
FROM vwPROJECTForecast b
WHERE b.ProjectID = a.ProjectID
AND (Year = 0 OR Year = @CurrentYear)) AS Budget,
(SELECT Sum(Forecast)
FROM vwPROJECTForecast c
WHERE c.ProjectID = a.ProjectID
AND (Year = 0 OR Year = @CurrentYear)) AS Forecast,
(SELECT Sum(Amount)
FROM vwPROJECTTransaction d
WHERE d.ProjectID = a.ProjectID
AND Year BETWEEN @YearFrom AND @CurrentYear) AS Booked,
DashboardDifference,
1 AS DiffType
FROM vwPROJECTProject a
WHERE EXISTS (SELECT * FROM tblProjectTypeDetail WHERE ProjectID
BETWEEN ProjectIDFrom AND ProjectIDTo AND ProjectTypeID LIKE
@ProjectTypeID)


-- Insert rows for DiffForecastBooked
INSERT #tmpResult(ProjectID, ProjectName1, Budget, Forecast, Booked,
DashboardDifference, DiffType)
SELECT ProjectID,
ProjectName1,
(SELECT Sum(Budget)
FROM vwPROJECTForecast b
WHERE b.ProjectID = a.ProjectID
AND (Year = 0 OR Year = @CurrentYear)) AS Budget,
(SELECT Sum(Forecast)
FROM vwPROJECTForecast c
WHERE c.ProjectID = a.ProjectID
AND (Year = 0 OR Year = @CurrentYear)) AS Forecast,
(SELECT Sum(Amount)
FROM vwPROJECTTransaction d
WHERE d.ProjectID = a.ProjectID
AND Year BETWEEN @YearFrom AND @CurrentYear) AS Booked,
DashboardDifference,
2 AS DiffType
FROM vwPROJECTProject a
WHERE EXISTS (SELECT * FROM tblProjectTypeDetail WHERE ProjectID
BETWEEN ProjectIDFrom AND ProjectIDTo AND ProjectTypeID LIKE
@ProjectTypeID)


-- Show final rows
SELECT ProjectID,
ProjectName1,
Budget,
Forecast,
Booked,
DiffPercent =
CASE
WHEN DiffType = 1 THEN
CASE
WHEN (Sum(Forecast) - Sum(Budget)) <> 0 THEN
CASE
WHEN Sum(Budget) <> 0 THEN
Round((Sum(Forecast) - Sum(Budget)) / Sum(Budget) * 100, 1)
ELSE 0
END
ELSE 0
END
WHEN DiffType = 2 THEN
CASE
WHEN (Sum(Booked) - Sum(Forecast)) <> 0 THEN
CASE
WHEN Sum(Forecast) <> 0 THEN
Round((Sum(Booked) - Sum(Forecast)) / Sum(Forecast) * 100,
1)
ELSE 0
END
ELSE 0
END
END,
DiffText =
CASE
WHEN DiffType = 1 THEN 'PROJECT WHERE FORECAST EXCEEDS BUDGET'
WHEN DiffType = 2 THEN 'PROJECT WHERE BOOKED EXCEEDS FORECAST'
END
FROM #tmpResult
GROUP BY ProjectID, ProjectName1, Budget, Forecast, Booked,
DashboardDifference, DiffType
HAVING
CASE
WHEN DiffType = 1 THEN
CASE
WHEN (Sum(Forecast) - Sum(Budget)) <> 0 THEN
CASE
WHEN Sum(Budget) <> 0 THEN
Round((Sum(Forecast) - Sum(Budget)) / Sum(Budget) * 100, 1)
ELSE 0
END
ELSE 0
END
WHEN DiffType = 2 THEN
CASE
WHEN (Sum(Booked) - Sum(Forecast)) <> 0 THEN
CASE
WHEN Sum(Forecast) <> 0 THEN
Round((Sum(Booked) - Sum(Forecast)) / Sum(Forecast) * 100, 1)
ELSE 0
END
ELSE 0
END
END >= DashboardDifference


-- Delete temporary table
DROP TABLE #tmpResult
END



// S

Hi
1) Have you looked at execution plan of the query?
2) Can you provide a more info about indexes ?
3) Do you really need to query the tblCompany to get a current year?
4) How many rows you are inserting?
5) Have you seen Recompile event if SQL Server Profile while the sp is being
running ?
6) What is the version are you using?



wrote in message
news:91acef52-9ae7-48fe-bc3b-0ca5098b95b4-at-r66g2000hsg.googlegroups.com...
>I have the following stored procedure which gives me horrible
> performance. It takes about 60-70 seconds to run so I get a timeout
> message in my ASP.NET page. The purpose with the sp is to show those
> project where forecast exceeds budget or where booked exceeds
> forecast. I'm very grateful for help with optimization!
>
> ALTER PROCEDURE [dbo].[spPROJECTDashboardGet]
> @ProjectTypeID nvarchar(10)=''
> AS
> BEGIN
>
> SET NOCOUNT ON;
>
> DECLARE @CurrentYear smallint,
> @YearFrom smallint
>
>
> -- Get current year
> SET @CurrentYear = (SELECT CurrentYear FROM tblCompany)
>
>
> -- Set first year depending on if project type runs over multiple
> years
> IF (SELECT MultipleYears FROM tblProjectType WHERE ProjectTypeID =
> @ProjectTypeID) = 0
> BEGIN
> SET @YearFrom = @CurrentYear
> END
> ELSE
> BEGIN
> SET @YearFrom = 0
> END
>
>
> -- Create temporary table
> CREATE TABLE #tmpResult(
> ProjectID nvarchar(10) NULL,
> ProjectName1 nvarchar(50) NULL,
> Budget numeric(18,2) NULL,
> Booked numeric(18,2) NULL,
> Forecast numeric(18,2) NULL,
> DashboardDifference decimal(18,1) NULL,
> DiffType int
> ) ON [PRIMARY]
>
>
> -- Insert rows for DiffBudgetForecast
> INSERT #tmpResult(ProjectID, ProjectName1, Budget, Forecast, Booked,
> DashboardDifference, DiffType)
> SELECT ProjectID,
> ProjectName1,
> (SELECT Sum(Budget)
> FROM vwPROJECTForecast b
> WHERE b.ProjectID = a.ProjectID
> AND (Year = 0 OR Year = @CurrentYear)) AS Budget,
> (SELECT Sum(Forecast)
> FROM vwPROJECTForecast c
> WHERE c.ProjectID = a.ProjectID
> AND (Year = 0 OR Year = @CurrentYear)) AS Forecast,
> (SELECT Sum(Amount)
> FROM vwPROJECTTransaction d
> WHERE d.ProjectID = a.ProjectID
> AND Year BETWEEN @YearFrom AND @CurrentYear) AS Booked,
> DashboardDifference,
> 1 AS DiffType
> FROM vwPROJECTProject a
> WHERE EXISTS (SELECT * FROM tblProjectTypeDetail WHERE ProjectID
> BETWEEN ProjectIDFrom AND ProjectIDTo AND ProjectTypeID LIKE
> @ProjectTypeID)
>
>
> -- Insert rows for DiffForecastBooked
> INSERT #tmpResult(ProjectID, ProjectName1, Budget, Forecast, Booked,
> DashboardDifference, DiffType)
> SELECT ProjectID,
> ProjectName1,
> (SELECT Sum(Budget)
> FROM vwPROJECTForecast b
> WHERE b.ProjectID = a.ProjectID
> AND (Year = 0 OR Year = @CurrentYear)) AS Budget,
> (SELECT Sum(Forecast)
> FROM vwPROJECTForecast c
> WHERE c.ProjectID = a.ProjectID
> AND (Year = 0 OR Year = @CurrentYear)) AS Forecast,
> (SELECT Sum(Amount)
> FROM vwPROJECTTransaction d
> WHERE d.ProjectID = a.ProjectID
> AND Year BETWEEN @YearFrom AND @CurrentYear) AS Booked,
> DashboardDifference,
> 2 AS DiffType
> FROM vwPROJECTProject a
> WHERE EXISTS (SELECT * FROM tblProjectTypeDetail WHERE ProjectID
> BETWEEN ProjectIDFrom AND ProjectIDTo AND ProjectTypeID LIKE
> @ProjectTypeID)
>
>
> -- Show final rows
> SELECT ProjectID,
> ProjectName1,
> Budget,
> Forecast,
> Booked,
> DiffPercent =
> CASE
> WHEN DiffType = 1 THEN
> CASE
> WHEN (Sum(Forecast) - Sum(Budget)) <> 0 THEN
> CASE
> WHEN Sum(Budget) <> 0 THEN
> Round((Sum(Forecast) - Sum(Budget)) / Sum(Budget) * 100, 1)
> ELSE 0
> END
> ELSE 0
> END
> WHEN DiffType = 2 THEN
> CASE
> WHEN (Sum(Booked) - Sum(Forecast)) <> 0 THEN
> CASE
> WHEN Sum(Forecast) <> 0 THEN
> Round((Sum(Booked) - Sum(Forecast)) / Sum(Forecast) * 100,
> 1)
> ELSE 0
> END
> ELSE 0
> END
> END,
> DiffText =
> CASE
> WHEN DiffType = 1 THEN 'PROJECT WHERE FORECAST EXCEEDS BUDGET'
> WHEN DiffType = 2 THEN 'PROJECT WHERE BOOKED EXCEEDS FORECAST'
> END
> FROM #tmpResult
> GROUP BY ProjectID, ProjectName1, Budget, Forecast, Booked,
> DashboardDifference, DiffType
> HAVING
> CASE
> WHEN DiffType = 1 THEN
> CASE
> WHEN (Sum(Forecast) - Sum(Budget)) <> 0 THEN
> CASE
> WHEN Sum(Budget) <> 0 THEN
> Round((Sum(Forecast) - Sum(Budget)) / Sum(Budget) * 100, 1)
> ELSE 0
> END
> ELSE 0
> END
> WHEN DiffType = 2 THEN
> CASE
> WHEN (Sum(Booked) - Sum(Forecast)) <> 0 THEN
> CASE
> WHEN Sum(Forecast) <> 0 THEN
> Round((Sum(Booked) - Sum(Forecast)) / Sum(Forecast) * 100, 1)
> ELSE 0
> END
> ELSE 0
> END
> END >= DashboardDifference
>
>
> -- Delete temporary table
> DROP TABLE #tmpResult
> END
>
>
>
> // S