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.
@lines contains about 150 rows (out of about
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.
eInvoce.Headerseems to work when I click "Display estimated execution plan," but when I actually execute it, the hint is ignored and a scan is performed.
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
@linescontains 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?