Performance optimization

Hi guys.
I'm trying to solve a performance problem I'm having with SQL Server.
I narrowed the cause of the deficiency to SQL Server's dynamic memory
allocation. ( I ran mmc.exe and used SQL Server: Memory Manager: Total
Server Memory )
This is the stored procedure that is causing the dynamic memory
allocation problem. There is a helper stored procedure that runs this
procedure while looping through a table.
My eyes have been trained to this code and I'm probably missing
something really simple. Can anyone lend me a fresh pair of eyes?
Thanks,
Ben
CREATE PROCEDURE CalculateCycleCount (@.currentEntity float(20),
@.startDate char(20), @.endDate char(20)) as
SET NOCOUNT ON
DECLARE @.level int
DECLARE @.ErrorCode int
Set @.ErrorCode = 0
DECLARE @.startDateDT datetime
DECLARE @.endDateDT datetime
Set @.startDateDT = convert(datetime, @.startDate)
Set @.endDateDT = convert(datetime, @.endDate)
CREATE TABLE #stack (entityID float(20), level int, plant char(255),
names char(100), Posted int, Accurate int )
/* this creates a temp table called #stack that recursively moves
through the plant table pushing and popping */
/* until it gets the entire tree calculated */
INSERT #stack
SELECT P.ID, 1, P.Plant, P.PlantName, C.Posted, C.Accurate
FROM tbl_Plant P Left Outer Join tbl_Cycle_Count_Temp C ON P.Plant
= C.Plnt
WHERE P.ID = @.currentEntity AND ((C.Date >= @.startDateDT AND C.Date
< @.endDateDT) OR C.Posted Is NULL)
SELECT @.level = 1
CREATE TABLE #tbl_temp_sum (Sls_Office char(255), Posted int, Accurate
int)
WHILE @.level > 0
BEGIN
IF EXISTS (SELECT * FROM #stack WHERE level = @.level)
BEGIN
SELECT @.currentEntity = entityID
FROM #stack
WHERE level = @.level
INSERT #tbl_temp_sum
SELECT S.plant, S.Posted, S.Accurate
FROM #stack S
WHERE S.entityID = @.currentEntity
DELETE FROM #stack
WHERE level = @.level
AND entityID = @.currentEntity
INSERT #stack
SELECT P.ID, @.level + 1, P.Plant, P.PlantName, C.Posted,
C.Accurate
FROM tbl_Plant P Left Outer Join tbl_Cycle_Count_Temp C ON
P.Plant = C.Plnt
WHERE P.parent = @.currentEntity AND ((C.Date >=
@.startDateDT AND C.Date < @.endDateDT) OR C.Posted Is NULL)
IF @.@.ROWCOUNT > 0
SELECT @.level = @.level + 1
END
ELSE
SELECT @.level = @.level - 1
END -- WHILE
DECLARE @.count int;
DECLARE @.plantName nvarchar(50);
SELECT @.plantName = P.Plant FROM tbl_Plant P WHERE P.ID =
@.currentEntity;
SELECT @.count = COUNT(*) FROM #tbl_temp_sum;
if ( @.count > 0 )
BEGIN
INSERT INTO tbl_Cycle_Count SELECT @.plantName, SUM(Posted),
SUM(Accurate), @.endDate FROM #tbl_temp_sum;
END
--SELECT COUNT(*) AS Cnt, SUM(Posted) AS Posted, SUM(Accurate) as
Accurate FROM #tbl_temp_sum
SET @.ErrorCode = @.@.Error
Return @.ErrorCode
GO
Hi
Without knowing the table structure and data it is harder to diagnose your
issue. There is a lack of error handling see
http://www.sommarskog.se/error-handling-II.html and your temporary tables
should probably be created at the start (if they are really needed!)
Try using the debug option in Query Analyser (open the object browser(F8),
select the procedure, right click and choose debug), to step through the code
and see the values.
John
"benis.ong@.gmail.com" wrote:

> Hi guys.
> I'm trying to solve a performance problem I'm having with SQL Server.
> I narrowed the cause of the deficiency to SQL Server's dynamic memory
> allocation. ( I ran mmc.exe and used SQL Server: Memory Manager: Total
> Server Memory )
> This is the stored procedure that is causing the dynamic memory
> allocation problem. There is a helper stored procedure that runs this
> procedure while looping through a table.
> My eyes have been trained to this code and I'm probably missing
> something really simple. Can anyone lend me a fresh pair of eyes?
>
> Thanks,
> Ben
> CREATE PROCEDURE CalculateCycleCount (@.currentEntity float(20),
> @.startDate char(20), @.endDate char(20)) as
> SET NOCOUNT ON
> DECLARE @.level int
> DECLARE @.ErrorCode int
> Set @.ErrorCode = 0
> DECLARE @.startDateDT datetime
> DECLARE @.endDateDT datetime
> Set @.startDateDT = convert(datetime, @.startDate)
> Set @.endDateDT = convert(datetime, @.endDate)
> CREATE TABLE #stack (entityID float(20), level int, plant char(255),
> names char(100), Posted int, Accurate int )
> /* this creates a temp table called #stack that recursively moves
> through the plant table pushing and popping */
> /* until it gets the entire tree calculated */
> INSERT #stack
> SELECT P.ID, 1, P.Plant, P.PlantName, C.Posted, C.Accurate
> FROM tbl_Plant P Left Outer Join tbl_Cycle_Count_Temp C ON P.Plant
> = C.Plnt
> WHERE P.ID = @.currentEntity AND ((C.Date >= @.startDateDT AND C.Date
> < @.endDateDT) OR C.Posted Is NULL)
> SELECT @.level = 1
> CREATE TABLE #tbl_temp_sum (Sls_Office char(255), Posted int, Accurate
> int)
> WHILE @.level > 0
> BEGIN
> IF EXISTS (SELECT * FROM #stack WHERE level = @.level)
> BEGIN
> SELECT @.currentEntity = entityID
> FROM #stack
> WHERE level = @.level
> INSERT #tbl_temp_sum
> SELECT S.plant, S.Posted, S.Accurate
> FROM #stack S
> WHERE S.entityID = @.currentEntity
> DELETE FROM #stack
> WHERE level = @.level
> AND entityID = @.currentEntity
> INSERT #stack
> SELECT P.ID, @.level + 1, P.Plant, P.PlantName, C.Posted,
> C.Accurate
> FROM tbl_Plant P Left Outer Join tbl_Cycle_Count_Temp C ON
> P.Plant = C.Plnt
> WHERE P.parent = @.currentEntity AND ((C.Date >=
> @.startDateDT AND C.Date < @.endDateDT) OR C.Posted Is NULL)
> IF @.@.ROWCOUNT > 0
> SELECT @.level = @.level + 1
> END
> ELSE
> SELECT @.level = @.level - 1
> END -- WHILE
> DECLARE @.count int;
> DECLARE @.plantName nvarchar(50);
> SELECT @.plantName = P.Plant FROM tbl_Plant P WHERE P.ID =
> @.currentEntity;
> SELECT @.count = COUNT(*) FROM #tbl_temp_sum;
> if ( @.count > 0 )
> BEGIN
> INSERT INTO tbl_Cycle_Count SELECT @.plantName, SUM(Posted),
> SUM(Accurate), @.endDate FROM #tbl_temp_sum;
> END
> --SELECT COUNT(*) AS Cnt, SUM(Posted) AS Posted, SUM(Accurate) as
> Accurate FROM #tbl_temp_sum
> SET @.ErrorCode = @.@.Error
> Return @.ErrorCode
> GO
>