By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,290 Members | 1,253 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,290 IT Pros & Developers. It's quick & easy.

SQL Server 2000 UDF Intermittent Slow Execution

P: n/a
Two (almost) identical SQL Server databases (DB1 backed up and
restored to DB2 yesterday). DB2.dbo.GetSchPaymentsTD took 1.5 seconds
(!) to execute DB1.dbo.GetSchPaymentsTD took less than a millisecond
with identical code and data.

I'm guessing this is some sort of indexing issue and the code is below
but I'm not sure it's relevant because . . .

.. . . I dropped DB2.dbo.GetSchPaymentsTD and then recreated it (with
identical code) after which the execution was lightning fast (just
like DB1). This exact behaviour was duplicated with another similar
function.

So . . . I've fixed the problem for the moment but why did do uyou
think this happened and how can I ensure that it doesn't happen again?

Thanks for your help!

The code is below - the sizes of the relevant tables are:-
- tblPayment 5 million records,
- tblPaymentTemplate 170 K records,
- tblSchedule 140 K records,
- tblEmployee 50 K records,
- tblBatch 30 K records.

The database may well not be optimally indexed but if this function
does reliably run in less than 1 ms who's going to complain?

ALTER FUNCTION dbo.GetSchPaymentsTD (@schID INT)
RETURNS DECIMAL(19, 2)
AS
BEGIN
RETURN
(
SELECT SUM (ISNULL (P.pmntAmountPerPay, 0))
FROM dbo.tblPayment P
INNER JOIN dbo.tblPaymentTemplate PT
ON PT.ptID = P.pmnt_ptID
INNER JOIN dbo.tblSchedule S
ON S.schID = PT.pt_schID
INNER JOIN dbo.tblEmployee E
ON E.empID = S.sch_empID
INNER JOIN dbo.tblBatch B
ON B.baID = P.pmnt_baID
WHERE
(
(S.schID = @schID) AND
(S.sch_pmID IN (3, 5)) AND --Manual or Drip Feed
(B.baDeductionDate >= E.empLastRlvrDate) --Since last rollover
)
)
END
Jun 27 '08 #1
Share this Question
Share on Google+
7 Replies


P: n/a
This is most likely a case of parameter sniffing. When the UDF is first
executed SQL Server looks at the input parameter and uses this as guidance
to build the query plan. This is good as long as the input parameter for the
first invocation is typical for future invocations. But if that is not the
case this will cause performance problems. For example, assuming you have an
index on the column, if the first invocation has to process a large set of
data it may be most efficient to scan the table. Further invocations may be
for a single row, but the first cached plan with table scan will be used
resulting in poor performance.

You see the improved performance after recreating the UDF because it was
recompiled and a new plan was generated on first execution that was good for
the parameter you used.

To solve this problem you can assign the parameter to a local variable and
then use the local variable in the query. The value of the local variable is
not known at compilation time because it is not set until the UDF is
executed. This in effect disables parameter sniffing.

HTH,

Plamen Ratchev
http://www.SQLStudio.com

Jun 27 '08 #2

P: n/a
On May 30, 12:37*pm, "Plamen Ratchev" <Pla...@SQLStudio.comwrote:
This is most likely a case of parameter sniffing.
Thank you, Plamen, that's a brilliant suggestion which is most
consistent with what I'm seeing.

I'll use the cunning idea of an intermediate variable, but is there a
more direct (and obvious) way to force the query plan to use a seek?

ITH (It did help!)
Jun 27 '08 #3

P: n/a
SQL Server 2000 has limited options. On SQL Server 2005 you can use query
hints to optimize for particular value, or recompile the query, even force a
query plan. SQL Server 2008 adds the FORCESEEK hint.

If you have a particular value that you would like to optimize for (and you
expect that value to produce a plan with seek), then you can add one more
parameter to the UDF and assign that value as default. Then use that second
parameter in the query, but just before the SELECT assign the real value
from the first parameter. Since the assignment does not take effect until
the plan is actually executed, the optimizer can not take it into account
during plan compilation. As far as the optimizer is concerned it optimizes
the query plan for the default value (assuming you never really pass a value
for it but use the default).

To illustrate the steps:

1) Adding the second parameter with default:
ALTER FUNCTION dbo.GetSchPaymentsTD (@schID INT, @schID_def INT = 100)

2) Assign the real value:
SET @schID_def = @schID

3) In the query:
WHERE
(
(S.schID = @schID_def) ...

HTH,

Plamen Ratchev
http://www.SQLStudio.com

Jun 27 '08 #4

P: n/a
On May 30, 1:29*pm, "Plamen Ratchev" <Pla...@SQLStudio.comwrote:
SQL Server 2000 has limited options. . . . you can add one more
parameter to the UDF and assign that value as default . . .
Thank you for that. After seeing your post, I found Ken Henderson's
Weblog at http://blogs.msdn.com/khen1234/archi...02/424228.aspx
which recommends the same thing.
Jun 27 '08 #5

P: n/a
Plamen Ratchev (Pl****@SQLStudio.com) writes:
To solve this problem you can assign the parameter to a local variable
and then use the local variable in the query. The value of the local
variable is not known at compilation time because it is not set until
the UDF is executed. This in effect disables parameter sniffing.
This may or may not work. There are two ways why this could backfire:

1) The query is compiled for an unknown value, and the distribution is
such that for an unknown value a scan may be best. Say for instance
that of the rows in tblPaymentTemplate, most are for schedules
that are no longer used, but there are many rows for these schedules.
Whereas active schedules are more selective.

2) When recompilation occurs because of changed statistics, this could
happen on statement level, in which case the variable may be sniffed.

Copying a parameter to a local variable mainly makes sense, in my
opinion, if the most common parameter value is actually never used in
the query. The typical case is a date parameter where NULL means "today".
Since the NULL value is not used in the query, it's better to copy to
local variable.
SQL Server 2008 adds the FORCESEEK hint.
It also adds the hint OPTIMIZE FOR @var UNKNOWN, so that you don't
need that extra variable.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Jun 27 '08 #6

P: n/a
"Erland Sommarskog" <es****@sommarskog.sewrote in message
news:Xn**********************@127.0.0.1...
>
This may or may not work. There are two ways why this could backfire:

1) The query is compiled for an unknown value, and the distribution is
such that for an unknown value a scan may be best. Say for instance
that of the rows in tblPaymentTemplate, most are for schedules
that are no longer used, but there are many rows for these schedules.
Whereas active schedules are more selective.
Yes, but this is why you use the second method I posted to set another
parameter with the value you would expect to produce the desired plan.
>
2) When recompilation occurs because of changed statistics, this could
happen on statement level, in which case the variable may be sniffed.
But since this refers to SQL Server 2000, there is no statement level
recompilation, correct? It was added in SQL Server 2005. SQL Server 2000 has
batch level recompilation only.

Plamen Ratchev
http://www.SQLStudio.com

Jun 27 '08 #7

P: n/a
Plamen Ratchev (Pl****@SQLStudio.com) writes:
But since this refers to SQL Server 2000, there is no statement level
recompilation, correct? It was added in SQL Server 2005. SQL Server 2000
has batch level recompilation only.
Correct. But maybe one day, Peter will migrate to a newer version of SQL
Server.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Jun 27 '08 #8

This discussion thread is closed

Replies have been disabled for this discussion.