Anyone see any inefficiencies with this stored proc?
-
This stored proc below is causing major probs w/ our prod system to the point where the handheld cannot download data. Any help would be great!
USE
[IFTDB]
GO
/****** Object: StoredProcedure [dbo].[abanco_FindFlightsByRegionAndDateRange] Script Date: 05/16/2007 13:14:36 ******/
SET
ANSI_NULLS ON
GO
SET
QUOTED_IDENTIFIER ON
GO
CREATE
PROCEDURE [dbo].[abanco_FindFlightsByRegionAndDateRange]
@CarrierCompanyID
int,
@Region
nvarchar(50),
@StartDepartureDate
datetime,
@EndDepartureDate
datetime,
@ReturnLinkedFlights
bit
AS
-- Get midnight for the range
DECLARE @StartDateNum int
DECLARE @EndDateNum int
SET @StartDateNum = FLOOR(CAST(@StartDepartureDate AS decimal(38, 19)))
SET @EndDateNum = FLOOR(CAST(@EndDepartureDate AS decimal(38, 19)))
DECLARE @RegionID int
SELECT @RegionID = RegionID FROM Regions
WHERE CompanyID = @CarrierCompanyID and
[Name]
= @Region
SELECT *
FROM FlightsView fv (nolock)
join ScheduledFlightsRegions sfr on
sfr
.ScheduledFlightID=fv.ScheduledFlightID
AND
sfr
.IsActive = 1
join Regions r (nolock) on
r
.RegionID=sfr.RegionID
WHERE
fv
.CarrierCompanyID = @CarrierCompanyID
AND
(
(
(
r
.RegionID = @RegionID
)
AND
(
FLOOR(CAST(fv.DepartureDateTime AS decimal(38, 19)))
BETWEEN @StartDateNum AND @EndDateNum
)
)
OR
(
@ReturnLinkedFlights
= 1
AND fv.FlightID IN
(
SELECT fl.FlightID
FROM FlightChains fc (nolock)
INNER JOIN FlightLinks fl (nolock)
ON fc.FlightChainID = fl.FlightChainID
WHERE fc.IsActive = 1
AND fl.IsActive = 1
AND fc.FlightChainID IN
(
SELECT fl.FlightChainID
FROM FlightLinks fl (nolock)
INNER JOIN Flights f (nolock)
ON fl.FlightID = f.FlightID
WHERE
(
r
.RegionID = @RegionID
) AND
FLOOR(CAST(f.DepartureDateTime AS decimal(38, 19)))
BETWEEN @StartDateNum AND @EndDateNum
AND f.CarrierCompanyID = @CarrierCompanyID
AND fl.IsActive = 1
)
)
)
)
ORDER BY fv.FlightNum, fv.OriginatingAirport, fv.DestinationAirport, fv.DepartureDateTime, fv.FlightID, fv.EnglishName
-
Hard to tell really without seeing the table and index structure. Your best be would be to run the query in query analyser with the execution plan enabled, and see where the high percentages are.
That said, there are a few things.
SET @StartDateNum = FLOOR(CAST(@StartDepartureDate AS decimal(38, 19))) - That's a really large decimal for what you're trying to do. FLOAT here is perfectly adequate. It's what I usually use. Cast them back to dates and your where clause will be easier to write.
SELECT
@RegionID = RegionID FROM Regions WHERE CompanyID = @CarrierCompanyID and[Name] = @Region - Is Name a nvarchar? The parameter is nvarchar, so if the column is varchar you're forcing a tablescan here, even if there is an index.
SELECT * - Is better to specify the fields needed, rather than using SELECT *. You're more likely to use a covering index, and less data will be transmitted.
FLOOR
(CAST(f.DepartureDateTime AS decimal(38, 19))) BETWEEN @StartDateNum AND @EndDateNum - The function on the column will prevent any inddex usage on this column, if you have one. There are other ways to do time range. Like this... (assuming that startdate and end date are your rounded off dates converted back to date time)
WHERE
f.DepartureDateTime BETWEEN @StartDate AND DATEADD(ms,-3,DATEADD(dd,1,@EndDate))
If you're using SQL 2000, it may be faster to remove the OR, convert the query into two queries, one for each branch of the OR and union the two together. SQL 2000 was not very efficient with OR statements. give it a try and see if either way is notable faster.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
In addition to all those recommendations, I'd suggest taking the IN clause and turning that into a join, even if you keep it as a derived table. In terms of processing the query, JOINS are processed prior to WHERE clauses which means that data is filtered down to fewer rows sooner. Except in rare cases, the IN is almost always slower than a JOIN.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore RooseveltAuthor of:
SQL Server Execution Plans
SQL Server Query Performance Tuning -
Gail,
Thanks so much for your wealth of input! I will try your recommedations.
-
Grant,
Thanks for your input as well! I'll try your suggestion about the "IN" and using a "JOIN" instead.
-
Check out the data distributions (sp_spaceused) on each table. You may find <horror> that a join hint might sort things out a bit...
-
You guys left me hanging... What was the final result of the changes? Did it work, or did it work better?
Inquiring minds want to know!!!
Kurt
Kurt W. Zimmerman
SR DBA
Lefrak Organization
New York, NYhttp://www.linkedin.com/in/kurtwzimmerman
-
Also (to add to what's already been said), you have a large number of implicit conversions that may slow things down... 1 (in code) is an implicit INT but you're columns are probably BIT... you convert a bunch of dates to decimal in variables and then compare them to datetime columns...
Look at the execution plan... see any index useage???
--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) -
And please, post DDL too so that we are not blindly guessing ...
* Noel
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply