March 27, 2002 at 2:45 pm
Hello,
In a new stored procedure I am creating a global temporary table ##Adjust with Create.
After this I run a number of different Inserts to this table. The very first insert takes about 20-25 seconds to run, then the rest of them run immediate. The entire process will take about 35 seconds to run in the SP. If I take this same code and copy it into Query Analyzer it will run in 8 seconds. It almost seems as though in the SP it's spending time on that first query either creating or looking for that ## table.
Any ideas on what I might be missing in the SP?
Thanks,
Dan
March 27, 2002 at 3:29 pm
Execute the SP with Execution Plan output to see if it does anything odd.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
March 27, 2002 at 3:51 pm
The plan did not show me anything out of the norm... So what I did was Create a table named Adjust and took the ## off of the 1st insert into, and it ran immediate. As soon as it hit the next Insert which was to the ##Adjust it had the pause, then the rest of them were quick. I am testing this in the debugger so I can see where the delay is.
Not sure if this gives any clues...
Dan
March 27, 2002 at 5:15 pm
you could always just do the inserts into a regular table or table variable and then do a select into ##Adjust. Depending on the amount of inserts you are doing on ##Adjust, you could see a performance benefit - less traffic to the tempdb.
March 27, 2002 at 5:27 pm
Yes... that's what I am doing now is going to a regular table. Much faster.
I was just curious of what was going on with the delay to the ## table on that 1st insert.
Dan
March 28, 2002 at 4:35 am
seems like the procedure might be being recompiled every time its run..can you post the SQL
March 28, 2002 at 5:50 am
Attached is a downsized version of the code... I also tried moving the commit's in different places with no difference as well as without Commit's. Sorry about the naming, but I was asked to alter it before posting.
CREATE PROCEDURE usp_Adjust @Y as int, @Q as int, @G as int, @SG as int AS
DECLARE @YQTR as int
Set @YQ = Convert(int, Convert(char(4), @Y) + Convert(Char(1), @Q))
Begin Tran
Create Table ##Adjust (Man [int], RN [int], RB [money], Type [int])
Commit Tran
if @Q = 1
BEGIN
Begin Tran
--****** Adjustment Type 1 *****
-- Copy Man
Insert Into ##Adjust (Man, RN, RB, Type)
(Select T.Man, T.RN, T.RB, 1 as Type
From dbo.Trans_1 as T INNER JOIN MA_1 as A ON
T.Man = A.Man
Where A.G = @G and A.SG = @SG and A.Y = @Y and A.Q = @YQ
and A.Type = 1 and A.Est is NULL and A.Dist is NULL
and A.Prod is NULL and A.Reg is NULL and A.L is NULL and RN NOT IN
(Select RN From ##Adjust
Where Type = 1))
-- Copy Man, Dist
Insert Into ##Adjust (Man, RN, RB, Type)
(Select T.Man, T.RN, T.RB, 1 as Type
From dbo.Trans_1 as T INNER JOIN MA_1 as A ON
T.Man = A.Man
Where A.G = @G and A.SG = @SG and A.Y = @Y and A.Q = @YQ
and A.Type = 1 and A.Est is NULL and A.Dist = T.Dist
and A.Prod is NULL and A.Reg is NULL and A.L is NULL and RN NOT IN
(Select RN From ##Adjust
Where Type = 1))
Commit Tran
END
Select M.Man,
Sum(Case T.Type When 1 Then T.RB Else 0 END) as A1_Total,
Sum(Case T.Type When 2 Then T.RB Else 0 END) as A2_Total,
Sum(Case T.Type When 3 Then T.RB Else 0 END) as A3_Total
From [MTable] as M LEFT JOIN ##Adjust as T ON
M.Man = T.Manuf
Group By M.Man, T.Type
Order By M.Man, T.Type
Drop Table ##Adjust
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply