July 19, 2011 at 2:13 pm
I have a multi-statement table valued function that, when certain parameters are passed in for one user, runs incredibly slowly - on an unburdened SQL Server 2008 installation, it ran for over an hour. When I run the exact same set of statements in Management Studio (that is, not invoking the UDF but invoking all the statements within the UDF, with the same variable values), it takes under ten seconds. When I modified the UDF to be an inline UDF, it took under a minute. Clearly there's a slowdown related to running the statement as a multi-statement TVF rather than inline or as a query, but I'm mystified as to how.
Can anyone shed some light on why the inline version would be *that* much more efficient than the multi-statement version?
Functions used:
fnCanUserViewRestrictedAccountsOnReports - 1 if a user has a permission set, 0 otherwise. Simple lookup.
fnUnitsUserCanSee - a list of unit IDs that a given user is allowed to view. Simple lookup.
fnAccountDataFX - returns the entire (large) table AccountData, with all monetary values translated into a specified currency. Includes subfunctions.
BEFORE (slow):
-- Return converted annual total for all units user can see.
-- Return sums in local currency as well as in the designated currency.
CREATE FUNCTION [dbo].[fnUnitTotalByUserAndAccountClass](@VersionID int, @user-id INT, @ToCurrencyID int)
RETURNS @MyTable TABLE (UnitID int, AccountClassID smallint, UnitTotalLocal money, UnitTotalFX money)
AS
BEGIN
DECLARE @ExcludeRestrictedAccounts bit
SET @ExcludeRestrictedAccounts = CASE WHEN dbo.fnCanUserViewRestrictedAccountsOnReports(@UserID)=1 THEN 0 ELSE 1 END
INSERT INTO @MyTable
SELECT UnitIDs.UnitID,
AccountSummary.AccountClassID,
ISNULL(AccountSummary.UnitTotalLocal,0) AS UnitTotalLocal,
ISNULL(AccountSummary.UnitTotalFX,0) AS UnitTotalFX
FROM dbo.fnUnitsUserCanSee(@UserID) UnitIDs
LEFT JOIN (
SELECT adfx.UnitID,
asi.AccountClassID,
SUM(adfx.AccountValue) AS UnitTotalLocal,
SUM(adfx.AccountValueFX) AS UnitTotalFX
FROM dbo.fnAccountDataFX(@ToCurrencyID) AS adfx
INNER JOIN dbo.AccountSetInfo asi
ON adfx.VersionID = asi.VersionID
AND adfx.UnitID = asi.UnitID
AND adfx.AccountID = asi.AccountID
AND adfx.SourceID = asi.SourceID
AND adfx.ProjectID = asi.ProjectID
INNER JOIN dbo.fnUnitsUserCanSee(@UserID) usee
ON usee.UnitID = asi.UnitID
WHERE asi.VersionID=@VersionID
AND asi.IsLocalAccount=1
AND asi.IsCore=1
AND adfx.PeriodNumber<>0
AND (@ExcludeRestrictedAccounts=0 OR (asi.IsAccountRestricted=0))
GROUP BY adfx.UnitID, asi.AccountClassID
) AS AccountSummary
ON AccountSummary.UnitID = UnitIDs.UnitID
RETURN
END
AFTER (runs within acceptable parameters):
-- Return converted annual total for all units user can see.
-- Return sums in local currency as well as in the designated currency.
CREATE FUNCTION [dbo].[fnUnitTotalByUserAndAccountClass](@VersionID int, @user-id INT, @ToCurrencyID int)
RETURNS TABLE AS RETURN(
SELECT UnitIDs.UnitID,
AccountSummary.AccountClassID,
ISNULL(AccountSummary.UnitTotalLocal,0) AS UnitTotalLocal,
ISNULL(AccountSummary.UnitTotalFX,0) AS UnitTotalFX
FROM dbo.fnUnitsUserCanSee(@UserID) UnitIDs
LEFT JOIN (
SELECT adfx.UnitID,
asi.AccountClassID,
SUM(adfx.AccountValue) AS UnitTotalLocal,
SUM(adfx.AccountValueFX) AS UnitTotalFX
FROM dbo.fnAccountDataFX(@ToCurrencyID) AS adfx
INNER JOIN dbo.AccountSetInfo asi
ON adfx.VersionID = asi.VersionID
AND adfx.UnitID = asi.UnitID
AND adfx.AccountID = asi.AccountID
AND adfx.SourceID = asi.SourceID
AND adfx.ProjectID = asi.ProjectID
INNER JOIN dbo.fnUnitsUserCanSee(@UserID) usee
ON usee.UnitID = asi.UnitID
WHERE asi.VersionID=@VersionID
AND asi.IsLocalAccount=1
AND asi.IsCore=1
AND adfx.PeriodNumber<>0
AND (dbo.fnCanUserViewRestrictedAccountsOnReports(@UserID)=0 OR (asi.IsAccountRestricted=0))
GROUP BY adfx.UnitID, asi.AccountClassID
) AS AccountSummary
ON AccountSummary.UnitID = UnitIDs.UnitID
)
July 19, 2011 at 2:23 pm
SQL can more easily optimize inline functions. If you're running it against a table a multi-line function needs to get called once for each row. An inline function can get treated like a view and SQL can handle the data as a set instead of one row at a time.
July 19, 2011 at 2:39 pm
Yes, I know that part. This statement is only being called once, though - if I call the 'before' multi-statement UDF a single time from SSMS, it takes over an hour to run, just that one time, versus ten seconds for the 'after' inline UDF. It is not executing any UDF repeatedly in the multi-statement version that is not called repeatedly in the inline version. For a single execution, the difference could be that great?
July 19, 2011 at 2:47 pm
To know for sure we'll need the execution plan of both queries but my bet is that somehow this line is causing a table scan:
AND (@ExcludeRestrictedAccounts=0 OR (asi.IsAccountRestricted=0))
Why that would but calling the function wouldn't, I don't really know. Can you get the execution plan for each? I'll try and take a look at that tomorrow if no one else has answered yet.
July 19, 2011 at 3:06 pm
Well, turns out the 'after' query was only faster for a couple of executions on the test machine after we had been running queries for a while and the large AccountData table was entirely cached. After running some other queries, the performance of the 'after' query fell apart again.
I think I've tracked down the source of the issue, though. The 'fnAccountDataFX' function returns the FX-converted annual data. Each period can have a different FX rate, so the function does a sum over the full year of the account value for a period times the FX rate for that period. In this particular case we're working around the issue by bypassing the entire FX translation when no FX is needed (in this case, everything takes place in a single currency).
Thanks for your help!
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply