Can this function be Tuned
-
CREATE FUNCTION [dbo].[DeriveRate](@Date datetime) returns varchar(10)
AS
BEGIN
return
(
select case when (datepart(dw, @date ) in (1,7)) then 'Discount'
when '2002-01-01' + ' ' + convert(varchar(8), @date, 108) between '2002-01-01 08:00:00' and '2002-01-01 18:59:59' then 'FullFare'
else 'Off FullFare'
end
)
END
I have been trying to tune my query, but I have just found out that this is where the bottleneck is.
Can anyone advice whether this can be tuned up ?
-
That function looks fairly straightforward, and by itself I bet it runs fine.
My guess is how your using it in your select statement.
Can you post that too?
-
If you're running this function many times, you may be taking a processor hit for the extra conversions in the second case evaluation.
Try:
CREATE
FUNCTION dbo.DeriveRate(@Date
datetime)
RETURNS varchar(10)AS
BEGIN
RETURN (SELECT CASE WHEN datepart(dw, @date ) IN (1,7) THEN 'Discount' WHEN datepart(hour, @date) BETWEEN 8 AND 18 THEN 'FullFare' ELSE 'Off FullFare' END )END
Eddie Wuerch
MCM: SQL -
The function will run on a group by which can return over a thousand rows, meaning that the function will be called thousands of times.
At the moment, If I run the simple groupby using the function, it takes 30 minutes, without the function, it takes less than 2 minutes, so this is where the problem is.
-
Hello,
functions don't work well when used in a select across a large number of rows. Maybe you could post your entire query that's using this function, plus DDL of respective tables and a few sample rows?
Did you try putting the CASE directly into your query instead of calling a function? It looks pretty straightforward and I don't think a function is necessary.
-
There is no calendar table on the database, what exactly do you mean here ?
Also, I am thinking of using a Cursor, as I reckon that might be faster instead of the function.
I agree that the function cannot fit into this query , there are over 80m rows to deal with. This will KILL the server hands down.
What do you guys think about the cursor method ?
-
Why not just add the case statement to you select statement?
select
a.SomeDate, -- a date field
case
when (datepart(dw, a.SomeDate) in (1,7)) then 'Discount'
when '2002-01-01' + ' ' + convert(varchar(8), a.SomeDate, 108) between '2002-01-01 08:00:00' and '2002-01-01 18:59:59' then 'FullFare'
else 'Off FullFare'
end as DeriveRate
from
SomeTable a
where
your criteria (if any)
hth
(PS Joe Celk, how about getting off your purist horse and try helping. )
-
If u are using the function in Select statement then use what Lynn suggested. Cos u don't need to have a calendar table to do what SQL does best. And more over companies can have different holiday schedule for different loaction so u might have to have "Company_Location" calender table and this will keep going on.
But one thing to be noted with DatePart(Dw..) is check ur DateFirst..1 and 7 assumes Datefirst is sunday but some company can have weeke begining at Monday so u will have to set DateFirst to Monday.
Thanks
Sreejith
-
And ultimately it is the application that dictates what needs to be done. If a calendar table is not required, why build it. No where in the posts is there even anything to indicate that there is a requirement to determine if it is a three, or for that matter a four, day weekend. The original query is simply looking to determine if it is the weekend (Saturday and Sunday) or a weekday (Monday through Friday).
Why add complexity when it is not required?
-
That's right, John didn't say anything about special holidays and long weekends - but Calendar table is a universal solution that can be used on many places, not just in this one procedure. And it often helps a lot, so it is a good thing to mention this idea... if nothing, then at least those who read this thread (and don't know) will realise there is such possibility. Although I didn't mention it myself in my reply, I'm glad Joe Celko did. John can try different solutions and choose the one that suits his needs.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply