SQL Server 2000 UDF Intermittent Slow Execution

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
.

Relevant Pages

  • RE: Executing Looping SP within DTS (SP provided)
    ... I haven't included the DTS Package. ... Package simply runs this procedure using execute sql task. ... INNER JOIN tbl_country cty ... --- Now load volume data by comparing the two months cumulative totals ...
    (microsoft.public.sqlserver.dts)
  • Re: access SQL query
    ... the records in a field from one of the tables and update/insert them ... from tblSharesSpreadsheet inner join tblShares on ... your join criteria in the Criteria box for LifetimeOfShare. ... eventually execute (via the red! ...
    (comp.databases.ms-access)
  • Re: Left Join woes
    ... That join is ambiguous, meaning if you execute the inner join first, then ... join you want, the second query use the first query, as table, and execute ...
    (microsoft.public.access.queries)
  • Re: STORED PROCEDURE is not working HELP
    ... > but right after i execute the sp and i check the table that was ... > FROM groupq g ... > INNER JOIN questionaires q ON q.groupid = g.groupid ...
    (microsoft.public.dotnet.framework.adonet)