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 (@[EMAIL PROTECTED]
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 = @[EMAIL PROTECTED]
) AND
(S.sch_pmID IN (3, 5)) AND --Manual or Drip Feed
(B.baDeductionDate >= E.empLastRlvrDate) --Since last rollover
)
)
END
|
