[Solved] Is there a performance disadvantage if an in-line function calls a scalar function?
Looking to automatically optimize YOUR SQL query? Start for free.

EverSQL Database Performance Knowledge Base

Is there a performance disadvantage if an in-line function calls a scalar function?

I've been getting to grips with APPLY, and at the same time reading about the performance improvement of in-line functions over scalar functions (see Rob Farley's post here).

I have an application that manages Requests. One Request might apply to a single Person on the system, or to many Persons. Transactions (not SQL TRAN!!) are created against Requests.

So, I've written a little function that returns the latest Transaction of a certain type, given the PersonID and RequestID

ALTER FUNCTION [dbo].[fnReturnPersonCollectionTime] 
(   
    -- Add the parameters for the function here
    @PersonID int, 
    @RequestID int
)
RETURNS TABLE 
AS
RETURN 
(

    SELECT
        TOP 1 Transactions.DateCreated AS TransactionDateTime,
        Transactions.FKPersonID,
        Transactions.FKRequestID
    FROM
        Transactions
    WHERE
        ((Transactions.FKPersonID = @PersonID) AND
        (Transactions.FKRequestID = @RequestID) AND
        (Transactions.FKTransxTypeID = 169))
    ORDER BY
        Transactions.DateTime DESC
)

Here it is in action.

SELECT 
    Tx.TransactionDateTime
FROM 
    Request INNER JOIN 
    RequestPersons ON Request.ID = RequestPersons.FKRequestID INNER JOIN 
    Person ON RequestPersons.FKPersonID = Person.ID 
    CROSS APPLY dbo.fnReturnPersonCollectionTime(RequestPersons.FKPersonID, RequestPersons.FKRequestID) Tx

However. My colleagues and I don't like the Magic Number 169 in the WHERE clause of the function - in other places in the application we have used scalar functions that return singleton values like these (this is, I believe, generally regarded as a Good Thing, since all such references are centralised to a single location). This is what it would look like with a call to a scalar function.

ALTER FUNCTION [dbo].[fnReturnPersonCollectionTime] 
(   
    -- Add the parameters for the function here
    @PersonID int, 
    @RequestID int
)
RETURNS TABLE 
AS
RETURN 
(

    SELECT
        TOP 1 Transactions.DateCreated AS TransactionDateTime,
        Transactions.FKPersonID,
        Transactions.FKRequestID
    FROM
        Transactions
    WHERE
        ((Transactions.FKPersonID = @PersonID) AND
        (Transactions.FKRequestID = @RequestID) AND
        (Transactions.FKTransxTypeID = dbo.fnReturnCollectionTransactionID()))
    ORDER BY
        Transactions.DateTime DESC
)

So my question is - if my in-line function requires a call to a scalar function, does this negate any good that accrues from writing an in-line function in the first place?

Many thanks

Edward

How to optimize this SQL query?

The following recommendations will help you in your SQL tuning process.
You'll find 3 sections below:

  1. Description of the steps you can take to speed up the query.
  2. The optimal indexes for this query, which you can copy and create in your database.
  3. An automatically re-written query you can copy and execute in your database.
The optimization process and recommendations:
  1. Create Optimal Indexes (modified query below): The recommended indexes are an integral part of this optimization effort and should be created before testing the execution duration of the optimized query.
Optimal indexes for this query:
ALTER TABLE `Person` ADD INDEX `person_idx_id` (`ID`);
ALTER TABLE `RequestPersons` ADD INDEX `requestpersons_idx_fkrequestid` (`FKRequestID`);
The optimized query:
SELECT
        Tx.TransactionDateTime 
    FROM
        Request 
    INNER JOIN
        RequestPersons 
            ON Request.ID = RequestPersons.FKRequestID 
    INNER JOIN
        Person 
            ON RequestPersons.FKPersonID = Person.ID CROSS APPLY dbo.fnReturnPersonCollectionTime(RequestPersons.FKPersonID,
        RequestPersons.FKRequestID) Tx

Related Articles



* original question posted on StackOverflow here.