help with slow query - 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 Development (2000)

help with slow query

Author

Topic

matrixr
Starting Member

26 Posts

Posted - 2006-05-31 : 22:51:41

here is a stripped down sample of the current query, this is slow takes around 3-4 seconds to run with only 53 rows.

-- create a temporary table to hold the results
create table #results (xID int identity, [Id] uniqueidentifier, [Name] varchar(50), FullRate money, Discount decimal(18,5), D1...D14 money)

insert into #results select [Id], [Name], Rate, Discount, ...etc from Rooms where Facility =@facilityId

select @totalRows = count(*) from #results

while (@totalRows > 0)
begin
select @roomId = [Id] from #results where xID =@totalRows

while (@start <= @end) -- @start and @end are datetime
begin
if (exists(select [Id] from DefaultRates where RoomId =@roomId AND [Date] =@start))
begin
select @discountLevel =Price from DefaultRates where RoomId =@roomId AND [Date] =@start
-- do some calculations here
end

if (exists(select [Id] from SpecificDayRates where RoomId =@roomId AND [Date] =@start))
begin
select @price = Price from SpecificDayRates ...etc
if (@price != @rate) begin ... end
else begin ... end -- i dont see how i can move these to a case statement, and i need to be able to loop through @start and @end
end
end

update #results set D1...D14 = ...etc where xID =@totalRows

select @totalRows = @totalRows - 1
end

any ideas on how i can improve the speed of the query?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-05-31 : 23:33:02

Make sure you have index created for your table and also the temp table.

for best result, try to perform using set based and don't use while loop.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources