1

I have a scalar function that returns a big XML, created over a bunch of invoices.

The exact list of invoices to feed to the function can be calculated using several different ways, but the function is same every time. For this reason, I declared a user-defined table type to contain primary keys from the eInvoice.Header table and pass it to the function. This way I can have several different functions to decide which invoices to process, and just one function that actually generates XML:

create function eInvoice.GetRelevantLinesInOneWay()
returns table ...

create function eInvoice.GetRelevantLinesInAnotherWay()
returns table ...

create function eInvoice.GetXML(@lines eInvoice.InvoicePrimaryKeys readonly)
returns xml
as
begin
    declare @x xml;

    with xmlnamespaces(N'important namespace' as pro)
    select @x = (
        select
            ...
        from
            eInvoice.Header h
            inner join @lines l on h.ST_PRIMARY = l.invoice_row_id
        for xml path(N'pro:Import'), type
    );

    return @x;
end;

Unfortunately this setup has proven to be very fragile.

Typically @lines contains about 150 rows (out of about 1m in eInvoce.Header). The correct execution plan is to use index seek on ST_PRIMARY, and that is what always happens when I execute the body of eInvoice.GetXML as an ad-hoc query.

However when I store it as a function, it works as expected for some little time, then something happens (too many rows in @lines, like about 300?), and it decides to change the execution plan to a full scan on eInvoice.Header and to keep it that way.

With the seek plan the function executes instantly, with the scan plan I don't know how much time it takes, I waited for 30 minutes and cancelled it.

I've tried various ways of forcing the seek plan on the function.

  • Adding with (forceseek) after eInvoce.Header seems to work when I click "Display estimated execution plan," but when I actually execute it, the hint is ignored and a scan is performed.
  • Adding option (recompile) inside the function or outside, in the calling code, does not seem to have an effect.
  • Capturing the execution plan and hardcoding it inside the function with option (use plan N'<plan>') does not work because @lines contains a primary key on its only column, and the name of that index is different for each instance of @lines, but the execution plan has to refer to the index by fixed name.
  • I tried deleting cached execution plans and recompiling the function several times. It sometimes helps for a very limited time and sometimes has no effect.

Is there a way to force the index seek?

  • Would OPTION(KEEPFIXED PLAN) help in this case? – spaghettidba Mar 10 '16 at 9:04
  • @spaghettidba If I clear cache, pass a @lines with one row to trick it into the seek plan, then add OPTION(KEEPFIXED PLAN) and recompile, only then it works a couple of times, but goes back to scan anyway. – GSerg Mar 10 '16 at 9:11
5

Summary

The question does not provide execution plans or a full reproduction script, but based on the information given, you should use a FAST 1 hint. If you can, you should also consider converting the scalar function to the inline table-valued type.

AdventureWorks-based examples

Download the Microsoft sample database here.

Table type

CREATE TYPE dbo.PrimaryKeys AS TABLE
(
    PK integer PRIMARY KEY
);

Scalar function

You could try various combinations of INNER LOOP JOIN (with the written join order reversed, and FORCE ORDER added to suppress the warning), FORCESEEK(index_name(columns)) (without schemabinding) and even QUERYTRACEON(8690) to prevent a performance spool; but in my experience, FAST 1 is the most reliable way to get the right plan, and simpler as well.

CREATE FUNCTION dbo.GetXML
(
    @Lines dbo.PrimaryKeys READONLY
)
RETURNS xml
WITH SCHEMABINDING -- If possible
AS
BEGIN
    DECLARE @x xml;

    WITH XMLNAMESPACES (N'important namespace' AS pro)
    SELECT @x =
    (
        SELECT
            SOH.SalesOrderID,
            SOH.OrderDate,
            SOH.DueDate,
            SOH.ShipDate,
            SOH.[Status],
            SOH.PurchaseOrderNumber,
            SOH.AccountNumber,
            SOH.CustomerID,
            SOH.TotalDue
        FROM @Lines AS L
        JOIN Sales.SalesOrderHeader AS SOH
            ON SOH.SalesOrderID = L.PK
        ORDER BY
            SOH.SalesOrderID
        FOR XML PATH(N'pro:Import'), TYPE
    )
    OPTION (FAST 1);

    RETURN @x;
END;

In addition to all the usual reasons to avoid scalar functions, they do make plan analysis harder. First, you need to capture actual plans using Profiler or Extended Events - they do not appear in query tools like SSMS. Second, not all hints work as you would expect, for example RECOMPILE due to scoping and the separate plans.

DECLARE @Lines dbo.PrimaryKeys;

INSERT @Lines (PK)
SELECT TOP (300) SOH.SalesOrderID
FROM Sales.SalesOrderHeader AS SOH;

SELECT dbo.GetXML(@Lines);

The code above reliably produces the desired plan for me regardless of the number of rows in the table variable.

Without the FAST 1 hint in the function, the optimizer chooses a merge join and scan of both tables. This plan is cached and reused for a later function execution where TOP (300)is changed to TOP (1), reproducing the core of the issue.

Inline table-valued function

Unless there are absolutely compelling reasons to stick with the scalar function, you should probably rewrite it to inline table-valued form as follows:

CREATE FUNCTION dbo.GetXMLTable
(
    @Lines dbo.PrimaryKeys READONLY
)
RETURNS TABLE
AS
RETURN
    WITH XMLNAMESPACES (N'important namespace' AS pro)
    SELECT
        X.xml_result 
    FROM 
    (
        SELECT
            SOH.SalesOrderID,
            SOH.OrderDate,
            SOH.DueDate,
            SOH.ShipDate,
            SOH.[Status],
            SOH.PurchaseOrderNumber,
            SOH.AccountNumber,
            SOH.CustomerID,
            SOH.TotalDue
        FROM @Lines AS L
        CROSS APPLY
        (
            SELECT TOP (1) SOH.*
            FROM Sales.SalesOrderHeader AS SOH
                WITH (FORCESEEK(PK_SalesOrderHeader_SalesOrderID(SalesOrderID)))
            WHERE
                SOH.SalesOrderID = L.PK
            ORDER BY
                SOH.SalesOrderID
        ) AS SOH
        ORDER BY
            SOH.SalesOrderID
        FOR XML PATH(N'pro:Import'), TYPE
    ) AS X (xml_result);

The TOP (1) in the cross apply prevents the optimizer choosing anything other than nested loops join. The FORCESEEK hint is not required; I have included it to illustrate how it could be used if necessary.

DECLARE @Lines dbo.PrimaryKeys;

INSERT @Lines (PK)
SELECT TOP (300) SOH.SalesOrderID
FROM Sales.SalesOrderHeader AS SOH;

SELECT GXT.xml_result 
FROM dbo.GetXMLTable(@Lines) AS GXT;

The actual execution plan (without using Profiler/XE) is:

iTVF Execution Plan

  • Unless there are absolutely compelling reasons to stick with the scalar function - I cannot reliably answer that because some time passed since that function was written, but I'm pretty sure it was because it does not compile with xmlnamespaces as is, and the solution that was found on stackoverflow was to convert it to scalar function to make it compile. with schemabinding was not an option because several databases are involved, and enforcing hint usage on external callers was no good either. – GSerg Apr 23 '16 at 19:59
  • What we did in the end, we moved the XML selection logic to another function that accepts l.invoice_row_id as an int and only calculates XML for one invoice, then we cross apply that to the @lines table. Seems to work very well in all situations. – GSerg Apr 23 '16 at 20:02
  • @PaulWhite Funnily enough, the nested functions are inline table valued ones. As opposed to the outermost function, they properly use the name assignment trick to make them inline. Which only confuses me more about why the outer function is scalar. Not sure if I should post my solution as the solution, happy to accept your answer if you do (or don't, as a matter of fact). However I must admit that my personal preference is to make the query work without hints. – GSerg Apr 24 '16 at 9:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.