Need help in Query Tunning - 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

SQL Server 2000 Forums

SQL Server Administration (2000)

Need help in Query Tunning

Author

Topic

pokhara
Starting Member

1 Post

Posted - 2007-09-04 : 20:57:21

Hi all,
The following query is taking more time (40sec) than it should take (10 sec). Please can somebody help me in tunning this query?
Thanks in advance.

use xxx
go
CHECKPOINT
DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE
EXEC DBRefreshViews
go
Declare @dbname sysname; select @dbname = db_name()
Declare @p_enddate datetime;select @p_enddate = open_date from system_Parameters
Declare @p_startdate datetime; set @p_startdate= @p_endDate - 35
Declare @pInterval smallint; set @pInterval = 60
declare @p_type smallint; set @p_type = 0

print @p_StartDate
print @p_EndDate

create table #log (dbname sysname, message varchar(50), timer bigint)

declare @TotalTimer datetime;set @TotalTimer = getdate()
declare @timer datetime;set @timer = getdate()

--select * from fDates (@p_StartDate, @p_EndDate)
--insert #log values(@dbname, 'fDates: ' , (datediff(millisecond,@timer,getdate())));set @timer = getdate()
--
--select * from fHours (@p_StartDate, @p_EndDate)
--insert #log values(@dbname, 'fHours: ' , (datediff(millisecond,@timer,getdate())));set @timer = getdate()

select * from fLaborHoursEarned (@p_StartDate, @p_EndDate, 0)
insert #log values(@dbname, 'fLaborHoursEarned-0: ' , (datediff(millisecond,@timer,getdate())));set @timer = getdate()
select * from fLaborHoursEarned (@p_StartDate, @p_EndDate, 1)
insert #log values(@dbname, 'fLaborHoursEarned-1: ' , (datediff(millisecond,@timer,getdate())));set @timer = getdate()
select * from fLaborHoursEarned (@p_StartDate, @p_EndDate, 2)
insert #log values(@dbname, 'fLaborHoursEarned-2: ' , (datediff(millisecond,@timer,getdate())));set @timer = getdate()

select * from fLaborHoursEarnedHourly (@p_StartDate, @p_EndDate)
insert #log values(@dbname, 'fLaborHoursEarnedHourly: ' , (datediff(millisecond,@timer,getdate())));set @timer = getdate()

--select * from fLaborHoursScheduled (@p_StartDate, @p_EndDate, 0) order by businessDate, LaborCat
--insert #log values(@dbname, 'fLaborHoursScheduled-0: ' , (datediff(millisecond,@timer,getdate())));set @timer = getdate()
--select * from fLaborHoursScheduled (@p_StartDate, @p_EndDate, 1) order by businessDate, LaborCat
--insert #log values(@dbname, 'fLaborHoursScheduled-1: ' , (datediff(millisecond,@timer,getdate())));set @timer = getdate()
--select * from fLaborHoursScheduled (@p_StartDate, @p_EndDate, 2) order by businessDate, LaborCat
--insert #log values(@dbname, 'fLaborHoursScheduled-2: ' , (datediff(millisecond,@timer,getdate())));set @timer = getdate()
--
--select * from fLaborHoursScheduledHourly (@p_StartDate, @p_EndDate)
--insert #log values(@dbname, 'fLaborHoursScheduledHourly: ' , (datediff(millisecond,@timer,getdate())));set @timer = getdate()

select * from fLaborHoursForecast (@p_StartDate, @p_EndDate, 0)
insert #log values(@dbname, 'fLaborHoursForecast-0: ' , (datediff(millisecond,@timer,getdate())));set @timer = getdate()
select * from fLaborHoursForecast (@p_StartDate, @p_EndDate, 1)
insert #log values(@dbname, 'fLaborHoursForecast-1: ' , (datediff(millisecond,@timer,getdate())));set @timer = getdate()
select * from fLaborHoursForecast (@p_StartDate, @p_EndDate, 2)
insert #log values(@dbname, 'fLaborHoursForecast-2: ' , (datediff(millisecond,@timer,getdate())));set @timer = getdate()

select * from fLaborHoursForecastHourly (@p_StartDate, @p_EndDate)
insert #log values(@dbname, 'fLaborHoursForecastHourly: ' , (datediff(millisecond,@timer,getdate())));set @timer = getdate()

insert #log values(@dbname, 'TOTAL: ' , str(datediff(millisecond,@TotalTimer,getdate())));set @TotalTimer = getdate()

theEnd:
select * from #log
drop table #log

ka

Kristen
Test

22859 Posts

Posted - 2007-09-05 : 04:07:58

Your timer includes the time to "display" the data, which will depend on a number of things outside of just selecting the data itself.

All your values appear to be coming from functions? which you haven;t provided any code for at all, so its a bit hard to guess whether there are performance improvements that could be made, or not

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources