About tuning store procedure
-
i am trying to optimize this store procedure and in some part i have created temporary table, and i know store procedure with temp table cannot be pre compiled. so how do i use table variable instead, please help me out, or if anyone has better idea appreciate it if you could share.
here is portion of my store procedure.
if not exists (select * from sysobjects where id = object_id('#ScorecardTemp') )
BEGIN
CREATE TABLE #ScorecardTemp (
businessDate datetime null,
Cycle char (5) NULL ,
daypart smallint NULL ,
EarnedHours money NULL ,
ActualHours money NULL ,
ForecastHours money NULL ,
ScheduleHours money NULL ,
ActualDayParts smallint NULL ,
ScheduleDayparts smallint NULL ,
ActualRedDayparts smallint NULL ,
ScheduleRedDayparts smallint NULL
)
END
Insert into #ScorecardTemp
select
c.enddate BusinessDate,
eventType Cycle,
daypart,
sum(EarnedHours) EarnedHours,
sum(ActualHours) ActualHours,
sum(ForecastHours) ForecastHours,
sum(ScheduleHours) ScheduleHours,
count(ActualRed) ActualDayParts,
count(ScheduleRed) ScheduleDayparts,
sum(ActualRed) ActualRedDayparts,
sum(ScheduleRed) ScheduleRedDayparts
from fSystemCalendarRollups(@closedate, @closeDate,'DWP') c
left OUter join fLaborRedDayparts(@startPeriod, @closeDate,0)
on businessDate between c.startDate and c.endDate
--where (c.eventtype <> 'D' or c.enddate = @closeDate)
group by c.enddate, eventtype, daypart
-- Now load the data from the temp table
insert into ScorecardLaborDayparts (BusinessDate, value, cycle,daypart,hours)
select businessDate, 'Earned', cycle, daypart, EarnedHours hours
from #ScorecardTemp
-
You could do:
DECLARE @ScorecardTemp table (
businessDate datetime null,
Cycle char (5) NULL ,
daypart smallint NULL ,
EarnedHours money NULL ,
ActualHours money NULL ,
ForecastHours money NULL ,
ScheduleHours money NULL ,
ActualDayParts smallint NULL ,
ScheduleDayparts smallint NULL ,
ActualRedDayparts smallint NULL ,
ScheduleRedDayparts smallint NULL
)
Insert into @ScorecardTemp
select
c.enddate BusinessDate,
eventType Cycle,
daypart,
sum(EarnedHours) EarnedHours,
sum(ActualHours) ActualHours,
sum(ForecastHours) ForecastHours,
sum(ScheduleHours) ScheduleHours,
count(ActualRed) ActualDayParts,
count(ScheduleRed) ScheduleDayparts,
sum(ActualRed) ActualRedDayparts,
sum(ScheduleRed) ScheduleRedDayparts
from fSystemCalendarRollups(@closedate, @closeDate,'DWP') c
left OUter join fLaborRedDayparts(@startPeriod, @closeDate,0)
on businessDate between c.startDate and c.endDate
--where (c.eventtype 'D' or c.enddate = @closeDate)
group by c.enddate, eventtype, daypart
-- Now load the data from the temp table
insert into ScorecardLaborDayparts (BusinessDate, value, cycle,daypart,hours)
select businessDate, 'Earned', cycle, daypart, EarnedHours hours
from @ScorecardTemp
Procs with table variables can be pre-compiled. They are also not logged in the transaction log
Regards,
Andras
Andras Belokosztolszki, MCPD, PhD
GoldenGate Software -
I mentioned that table variables are not logged in the transaction log, and that unlike temp tables, procs that contain them can be precompiled. However, this does not necessarily improve performance. I've just found http://www.sql-server-performance.com/articles/per/temp_tables_vs_variables_p1.aspx which suggests that while on 2000 it is a good idea to use table variables, on 2k5 it may be a bad idea (depends on many things though, so it is important to check this with your own db/data)
Regards,
Andras
Andras Belokosztolszki, MCPD, PhD
GoldenGate Software -
Best if you keep the content of table variables pretty short... they do not use nor can they be made to use Statistics... there are other disadvantages to table variables, as well. Pay particular attention to Q3/A3 and Q4/A4... and note that both table variables and temp tables are built in the same place
http://support.microsoft.com/default.aspx?scid=kb;en-us;305977&Product=sql2k
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions -
Is there a case for not using a temp table or table variable at all? If you wrap your temp table select statement in the from clause of your insert statement you achieve the same result. I am unsure what locking issues may arise if you select statement is a long running query.
-- Now load the data from the temp table
insert into ScorecardLaborDayparts
(
BusinessDate,
value,
cycle,
daypart,
hours
)
select businessDate,
'Earned',
cycle,
daypart,
EarnedHours hours
from (
select c.enddate BusinessDate,
eventType Cycle,
daypart,
sum(EarnedHours) EarnedHours,
sum(ActualHours) ActualHours,
sum(ForecastHours) ForecastHours,
sum(ScheduleHours) ScheduleHours,
count(ActualRed) ActualDayParts,
count(ScheduleRed) ScheduleDayparts,
sum(ActualRed) ActualRedDayparts,
sum(ScheduleRed) ScheduleRedDayparts
from fSystemCalendarRollups(@closedate, @closeDate, 'DWP') c
left OUter join fLaborRedDayparts(@startPeriod, @closeDate, 0)
on businessDate between c.startDate and c.endDate
--where (c.eventtype 'D' or c.enddate = @closeDate)
group by c.enddate,
eventtype,
daypart
) AS ToInsert
-- JP
-
I realise I'm drifting off-topic here, but I'm struggling to understand the SQL. In what circumstances would one adopt this particular LEFT JOIN construction, with an ambiguous ON join-criterion, particularly without checking on NULLs in the WHERE clause.
Best wishes,
Phil Factor -
The outer join will ensure that all records from fSystemCalendarRollups are grouped and returned in the result set.
Where the outer join was unsuccessful the values in the fLabourRedDayParts will be returned as null for that row. Perhaps it is important for every record from the left table to be returned regardless of the joins success or failure.
In the query the JOIN statement is effectively doing what a WHERE clause cannot which is preserving rows and appending them to the result set. If the between statement was used in the WHERE clause then there would be no way to return all rows from the fSystemCalendarRollups table.
This example below shows the difference.
CREATE TABLE #Tester1 (low int, high int)
CREATE TABLE #Tester2 (value int)
INSERT INTO #Tester1 (low, high) VALUES (1, 10)
INSERT INTO #Tester1 (low, high) VALUES (2, 10)
INSERT INTO #Tester1 (low, high) VALUES (3, 10)
INSERT INTO #Tester1 (low, high) VALUES (4, 10)
INSERT INTO #Tester1 (low, high) VALUES (5, 10)
INSERT INTO #Tester1 (low, high) VALUES (6, 10)
INSERT INTO #Tester1 (low, high) VALUES (7, 10)
INSERT INTO #Tester1 (low, high) VALUES (8, 10)
INSERT INTO #Tester1 (low, high) VALUES (9, 10)
INSERT INTO #Tester1 (low, high) VALUES (10, 10)
INSERT INTO #Tester2 (value) VALUES (5)
-- Shows where join was successful
SELECT *
FROM #Tester1 AS t1
LEFT OUTER JOIN #Tester2 AS t2
ON value between t1.low and t1.high
-- This shows how the group by will return null
SELECT value, sum(low)
FROM #Tester1 AS t1
LEFT OUTER JOIN #Tester2 AS t2
ON value between t1.low and t1.high
GROUP BY value
-- A cartesian join using a where clause
SELECT value, sum(low)
FROM #Tester1 AS t1, #Tester2 AS t2
WHERE t2.value between t1.low and t1.high
GROUP BY value
-- Group by using where clause
SELECT *
FROM #Tester1 AS t1, #Tester2 AS t2
WHERE t2.value between t1.low and t1.high
DROP TABLE #Tester1
DROP TABLE #Tester2
-- JP
-
Thanks Jonathan.
As far as I can see, this is an aggregation of data into arbitrary date/time slices (defined by a start and end date) in such a way that each time-slice is represented by a row in the result: in other words, no time slice is left without aggregated data, even if it is a null. It would rely on its accuracy on there being no overlap in the calendar table containing the start and end dates.
I was just wondering if there was any, more immediate, performance gains we could suggest for this stored procedure than changing from a temp table to a table variable. In this case, the join is unindexed as it is between two table functions. This wouldn't matter if there wasn't much data but...
Best wishes,
Phil Factor -
The following does a much better job explaining when/why to use table variables.
Also, be careful not to get into optimization overload. I still suggest that you spend time optimizing the procedure from the beginning, but make sure that the time and effort is worth it.
FYI< I am making these numbers up!!!>
For example: Each recomple that the procedure experiences causes say .01 seconds delay... You have 10 recompiles so to total .1 seconds in a .15 second procedure.
Well, each re-compile will reduce the procedure run time by a noticable amount. If you can cut them out by 1/2, you can improve the procedure by 33%.
If the procedure is run 5 Milliion times per day that would result in roughly 7 hours of CPU TIME!... That would be significant.
However, if that procedure is only run 5x per day, then we are talking about less than a second a day 
Spend your time where you get the the most results. Another example is say for a procedure that runs for 2 min. Removing the 5 recompiles, really doesn't do much for you.
-
I agree with the post that states that if there isn't a huge concern for locking the best thing is to simply remove the temp table altogether and simply do a straight insert/select.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service -
Bob Fazio (9/12/2007)
The following does a much better job explaining when/why to use table variables.
Also, be careful not to get into optimization overload. I still suggest that you spend time optimizing the procedure from the beginning, but make sure that the time and effort is worth it.
FYI< I am making these numbers up!!!>
For example: Each recomple that the procedure experiences causes say .01 seconds delay... You have 10 recompiles so to total .1 seconds in a .15 second procedure.
Well, each re-compile will reduce the procedure run time by a noticable amount. If you can cut them out by 1/2, you can improve the procedure by 33%.
If the procedure is run 5 Milliion times per day that would result in roughly7 hours of CPU TIME!... That would be significant. Spend your time where you get the the most results.
Another example is say for a procedure that runs for 2 min. Removing the 5 recompiles, really doesn't do much for you.
5 million times a day? Sure, if it's RBAR for a GUI... the "other" world is batch and 1 recompile isn't going to amount to a hill of beans.
The other thing is, if you put the DDL for the temp table at the beginning of the proc (or whatever) and don't mix it in with the DML, you won't cause a recompile... even for GUI code.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions -
I don't have anythng to add to the variable vs. temp table discussion, but I have a correction for the original script.
The line "if not exists (select * from sysobjects where id = object_id('#ScorecardTemp') )" only works if tempdb is the current database. I always use "if object_id('tempdb..#ScorecardTemp') is null".
-
Thanks scott,
i'll surely look on to that and at the moment i am also doing some readings on optimizing queries.
appreciate it.
Derek
-
Derek,
From looking at your stored procedure, I would have to agree with Jonathon Prosper. I do not think you would really need a temp table or a table variable; this would induce more READ costs. The data that you want is already in the SELECT statement, for which you are inserting into the temp table.
I'd recommend checking the indexes for each table associated with the columns in your WHERE clause. I would also double check the function that you are calling within your stored procedure to ensure the proper performance threshold.
Regards,
Wameng Vang
MCTS
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply