DEFAULT keyword performance

[Jason]

I have a function which performs a query and returns a table. The one
parameter that can get passed in is a date which defaults to NULL.
There is an IF statement in the function that will set the paramter to
an actual date if null. If I call the function while passing in a date
the function comes back a second or 2 later. But if I pass in DEFAULT
to the function, the same query takes 8 minutes. See code below and
sample call below.

CREATE FUNCTION fCalculateProfitLossFromClearing (
@TradeDate DATETIME = NULL
)
RETURNS @t TABLE (
[TradeDate] DATETIME,
[Symbol] VARCHAR(15),
[Identity] VARCHAR(15),
[Exchange] VARCHAR(5),
[Account] VARCHAR(10),
[Value] DECIMAL(18, 6)
)
AS
BEGIN
-- Use previous trading date if none specified
IF @TradeDate IS NULL
SET @TradeDate = Supporting.dbo.GetPreviousTradeDate()

-- Make the query
INSERT @t
SELECT
@TradeDate,
tblTrade.[Symbol],
tblTrade.[Identity],
tblTrade.[Exchange],
tblTrade.[Account],
SUM((CASE tblTrade.[Side] WHEN 'B' THEN -ABS(tblTrade.[Quantity])
ELSE ABS(tblTrade.[Quantity]) END) * (tblPos.[ClosingPrice] -
tblTrade.[Price])) AS [Value]
FROM
Historical.dbo.ClearingTrade tblTrade
LEFT JOIN Historical.dbo.ClearingPosition tblPos ON (@TradeDate =
tblPos.[TradeDate] AND tblTrade.[Symbol] = tblPos.[Symbol] AND
tblTrade.[Identity] = tblPos.[Identity])
WHERE
([TradeTimestamp] >= @TradeDate AND [TradeTimestamp] < DATEADD(DAY,
1, @TradeDate))
GROUP BY tblTrade.[Symbol],tblTrade.[Identity],tblTrade.[Exchange],tblTrade.[Account]

RETURN
END

If I call the function as

SELECT * FROM fCalculateProfitLossFromClearing('09/25/2003')

it returns in 2 seconds.

If I call the function as

SELECT * FROM fCalculateProfitLossFromClearing(DEFAULT)

in which GetPreviousTradeDate() will set @TradeDate to 09/25/2003 it
returns in 8 minutes.

[Erland Sommarskog]

[posted and mailed, please reply in news]

Jason (Ja*******@hotmail.com) writes:

I have a function which performs a query and returns a table. The one
parameter that can get passed in is a date which defaults to NULL.
There is an IF statement in the function that will set the paramter to
an actual date if null. If I call the function while passing in a date
the function comes back a second or 2 later. But if I pass in DEFAULT
to the function, the same query takes 8 minutes. See code below and
sample call below.

The query seems familiar. :-)

The reason for this is that when SQL Server builds the query plan,
it considers the value of the input parameter. When you provide an
explicit date, SQL Server can consult the statistics for the table
and see that the value you provided is very selective, and use the
index.

But if you provide NULL, SQL Server will build the query plan on that
assumption. Obviously a NULL value would return no rows, but SQL Server
never makes any assumptions that could yield incorrect results. Since
you WHERE condition is for a range, SQL Server appears to prefer to
scan the table, than using a non-clustered index.
--
Erland Sommarskog, SQL Server MVP, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Jason]

>

The query seems familiar. :-)

The reason for this is that when SQL Server builds the query plan,
it considers the value of the input parameter. When you provide an
explicit date, SQL Server can consult the statistics for the table
and see that the value you provided is very selective, and use the
index.

But if you provide NULL, SQL Server will build the query plan on that
assumption. Obviously a NULL value would return no rows, but SQL Server
never makes any assumptions that could yield incorrect results. Since
you WHERE condition is for a range, SQL Server appears to prefer to
scan the table, than using a non-clustered index.

I hate the restart this thread but I have hit a brick wall...

I am at a lose of whether to creat functions or stored procedures (or
even views).

The below questions/issues are based on a need to return information
on a particular date for one to many symbols.

For my example lets say 15 symbols. You could do a query like Symbol =
'a' OR Symbol = 'b' OR Symbol... but it would make more sense to do
Symbol IN ('a','b',...))

I would also like to give my functions and stored procedures to use a
default date if none is specified. I created a function named
GetPreviousTradeDate() which does this based on a calendar.

SO here is how I see it.

Stored procedures seem to be the fastest in terms of returning data
back. But they seem to be limited in the sense that they can return
ONE row or ALL the rows since you cannot pass in a variable list of
symbols. You also cannot use the SP as part of a query. You could just
return all the rows back to the client and do filter or searching on
that end but that does not seem efficient or professional.

A function also does not let you pass in a variable list of symbols
but at least you can use it in a query. You could do something like
SELECT * FROM TheFunction() WHERE Symbol IN ('a','b',...). All this
happens at the server side and only the needed rows gets sent back.

But functions seem to have MAJOR performance problems when default
values are passed in. When I pass in a specific date the query takes a
few seconds but when I pass in DEFAULT and set the date to the results
of the GetPreviousTradeDate() function the query takes anywhere from 8
minutes to 15 minutes. (This even happens if I do not use the
GetPreviousTradeDate() function and set my parameter to a hard-coded
value)

Any thoughts or comments would be appreciated.

[Erland Sommarskog]

Jason (Ja*******@hotmail.com) writes:

A function also does not let you pass in a variable list of symbols
but at least you can use it in a query. You could do something like
SELECT * FROM TheFunction() WHERE Symbol IN ('a','b',...). All this
happens at the server side and only the needed rows gets sent back.
I happen to have an article on my web site that discusses this case
in detail. You don't have to read all of it, but you can just get
the function you need at
http://www.algonet.se/~sommar/arrays...html#iterative.
But functions seem to have MAJOR performance problems when default
values are passed in. When I pass in a specific date the query takes a
few seconds but when I pass in DEFAULT and set the date to the results
of the GetPreviousTradeDate() function the query takes anywhere from 8
minutes to 15 minutes. (This even happens if I do not use the
GetPreviousTradeDate() function and set my parameter to a hard-coded
value)

The difference is not always that big, but apparently your query is
sensitive for this. I suggest that you split up the procedure in two:
EXEC outer_sp @date = NULL datetime
IF @date IS NULL
SELECT @date = dbo.yourfunctionfordefault()
EXEC inner_sp @date

And then inner_sp includes the actual query.

For a long treatise on this subject, search on Google news for articles
by Bart Duncan (a escalation engineer at Microsoft) and the phrase
"parameter sniffing".

--
Erland Sommarskog, SQL Server MVP, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Jason]

Ja*******@hotmail.com (Jason) wrote in message news:<f0**************************@posting.google. com>...

I hate the restart this thread but I have hit a brick wall...

I am at a lose of whether to creat functions or stored procedures (or
even views).

The below questions/issues are based on a need to return information
on a particular date for one to many symbols.

For my example lets say 15 symbols. You could do a query like Symbol =
'a' OR Symbol = 'b' OR Symbol... but it would make more sense to do
Symbol IN ('a','b',...))

I would also like to give my functions and stored procedures to use a
default date if none is specified. I created a function named
GetPreviousTradeDate() which does this based on a calendar.

SO here is how I see it.

Stored procedures seem to be the fastest in terms of returning data
back. But they seem to be limited in the sense that they can return
ONE row or ALL the rows since you cannot pass in a variable list of
symbols. You also cannot use the SP as part of a query. You could just
return all the rows back to the client and do filter or searching on
that end but that does not seem efficient or professional.

A function also does not let you pass in a variable list of symbols
but at least you can use it in a query. You could do something like
SELECT * FROM TheFunction() WHERE Symbol IN ('a','b',...). All this
happens at the server side and only the needed rows gets sent back.

But functions seem to have MAJOR performance problems when default
values are passed in. When I pass in a specific date the query takes a
few seconds but when I pass in DEFAULT and set the date to the results
of the GetPreviousTradeDate() function the query takes anywhere from 8
minutes to 15 minutes. (This even happens if I do not use the
GetPreviousTradeDate() function and set my parameter to a hard-coded
value)

Any thoughts or comments would be appreciated.

Since the stored procedure has both the speed and the ability to use
default values without performance hit, would it be normal practice or
efficient to send separate queries for each symbol to the stored
procedure? This could result in anywhere from a few to several hundred
calls at a time.

[Erland Sommarskog]

Jason (Ja*******@hotmail.com) writes:

Since the stored procedure has both the speed and the ability to use
default values without performance hit, would it be normal practice or
efficient to send separate queries for each symbol to the stored
procedure? This could result in anywhere from a few to several hundred
calls at a time.

That does not seem like a good idea. Certainly more efficient to get
data for all symbols at once. See my previous post for suggestions.
--
Erland Sommarskog, SQL Server MVP, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[--CELKO--]

>> A function also does not let you pass in a variable list of symbols
but at least you can use it in a query ... Any thoughts or comments
would be appreciated. <<

Ever try putting the list of symbols into a one column table and using
an "IN (SELECT parm FROM Parmlist)" instead?