patternsqlMinor

Optimizing table valued function SQL Server

Submitted by: @import:stackexchange-dba·Mar 10, 2026·

Viewed 0 times

sqlvaluedfunctionoptimizingservertable

Problem

I am trying to optimize this table-valued function. If I could, I would change it to the procedure but I cant. The problem is with two update statements. I kept only those two in the function because they are causing main performance issues.
I rewrote the first one from outer apply to inner join and I looked at the statistics and they were wrong so I added an option(recompile) and it significantly helped.
The problem is within the second update. The statistics are wrong and I do not know how to make an appropriate execution plan and optimize it with hints.
Do you please have any idea how to get the time down? I tried to index table variable but with no result.

Here is an execution plan https://www.brentozar.com/pastetheplan/?id=B1EdBo5e4

Thanks.

```
CREATE FUNCTION [dbo].cfn_PlanServis_Seznam

RETURNS @PlanServis TABLE(
lIDAuto INT,
szSPZ VARCHAR(100),
lDepozit INT,
szTypVozidla varchar(100),
szTypServisu NVARCHAR(300),
szServisniPlan NVARCHAR(300),
lZbyvaDni INT,
lZbyvaKm INT,
lNajetoKm INT,
dtServis DATETIME,
dcZbyvaMotohodin DECIMAL(15,1),
dcNajetoMotohodin DECIMAL(15,1),
IDVazPlanServisAuto INT,
IDPlanServisDefinice INT,
lBarva INT

)

AS

BEGIN
DECLARE @Auto TABLE(
lIDAuto INT,
szSPZ VARCHAR(100),
szTyp VARCHAR(100),
IDCisTypServis INT,
szTypServisu NVARCHAR(500),
szServisniPlan NVARCHAR(500),
lKmStart INT,
dtStart DATETIME,
lKmPriZavedeni INT,
lUjetoPredZavedenim INT,
dcMotohodinyStart DECIMAL(15,1),
lIntervalKm INT,
dcIntervalMotohodiny DECIMAL(15,1),
lUjeto INT,
dcMotohodiny DECIMAL(15,1),
IDServis INT,
lKmServis INT,
dcMotohodinyServis DECIMAL(15,1),
dtServis DATETIME,
lIntervalDatum INT,
lDniUbehlo INT,
lBarva INT,
lZbyvaKm INT,
dcZbyvaMotohod

Solution

For a question like this it's very helpful to provide an MCVE. As is I had to make a lot of guesses about table structure and data distribution. You say that this part of the query plans is too slow without any further elaboration:

I can see three reasons why that part might be slow. The first issue is that both tables only have 176k total rows in them, yet index seeks pull over 800k rows from both tables. The second issue is that the index seek on JizdaTachograf only has the following seek predicate: [Lori_MDL].[dbo].[JizdaTachograf].lkmOd IS NOT NULL. I suppose that could be selective, but if not then you're effectively scanning most of the index 845 tables. The third issue is a total of 800k rows are sorted, although the sorts are split into 846 iterations.

There might be a way to get a plan that just does a single scan of both tables, but without understanding the data distributions I don't know if that would be worth it. The requirements of your query (inequalities, sorting, OR logic) make it hard for a merge join or hash join to work.

One issue that you can solve is the second one. If you define the right indexes and split up (JizdaTachograf.IDAuto = [@Auto].lIDAuto OR JizdaTachograf.IDNaves = [@Auto].lIDAuto) into two subqueries then you can get more effective index seeks on JizdaTachograf that seek directly to the relevant rows. That could save a lot of time if most of the rows in the table have non-NULL values for lkmOd. There are many different index definitions that could work. Two are below:

CREATE INDEX IX2 ON JizdaTachograf (IDAuto, IDJizda, lkmDo) INCLUDE (lkmOd)
WHERE lkmOd IS NOT NULL AND lkmDo IS NOT NULL;

CREATE INDEX IX3 ON JizdaTachograf (IDNaves, IDJizda, lkmDo) INCLUDE (lkmOd)
WHERE lkmOd IS NOT NULL AND lkmDo IS NOT NULL;

I then split up the query so that SQL Server can take advantage of the indexes.

UPDATE @Auto SET lMaxTachograf = ISNULL(ISNULL(Km.lKm, [@Auto].lKmServis),[@Auto].lKmStart)
    FROM @Auto 
    OUTER APPLY 
    (
    SELECT TOP (1) lKm
    FROM
    (

    SELECT TOP (1) Jizda.dtZacatek, JizdaTachograf.lkmDo lKm 
     FROM JizdaTachograf
     INNER JOIN Jizda WITH (INDEX(1)) ON JizdaTachograf.IDJizda = Jizda.lIDJizda 
     WHERE JizdaTachograf.lkmOd IS NOT NULL AND 
           JizdaTachograf.lkmDo IS NOT NULL AND Jizda.lProvozne = 1 
           AND JizdaTachograf.IDAuto = [@Auto].lIDAuto -- first half
           AND Jizda.dtZacatek > ISNULL(ISNULL([@Auto].dtServis, [@Auto].dtStart),DATEADD(YEAR,-100,GETDATE())) 
     ORDER BY  Jizda.dtZacatek DESC, JizdaTachograf.lkmDo desc

     UNION ALL

     SELECT TOP (1) Jizda.dtZacatek, JizdaTachograf.lkmDo lKm 
     FROM JizdaTachograf
     INNER JOIN Jizda WITH (INDEX(1)) ON JizdaTachograf.IDJizda = Jizda.lIDJizda 
     WHERE JizdaTachograf.lkmOd IS NOT NULL AND 
           JizdaTachograf.lkmDo IS NOT NULL AND Jizda.lProvozne = 1 
           AND JizdaTachograf.IDNaves = [@Auto].lIDAuto -- second half
           AND Jizda.dtZacatek > ISNULL(ISNULL([@Auto].dtServis, [@Auto].dtStart),DATEADD(YEAR,-100,GETDATE())) 
     ORDER BY Jizda.dtZacatek DESC, JizdaTachograf.lkmDo desc
    ) IDNaves_IDAuto
    ORDER BY dtZacatek DESC, lKm DESC   
    ) Km;

I'm working with empty tables, but I can show that it's at least possible to get the desired plan shape:

The advantage of this plan is that it will do less IO on JizdaTachograf and that the sorts are split up even further. However, you're still pulling the same number of rows from both indexes and sorting the same total number of rows.

It is possible to write this query so that there's no sorting. The IO pattern is different which could result in less reads overall. You'll need another index. Below is one that works:

CREATE INDEX IX1 ON Jizda (dtZacatek) INCLUDE (lIDJizda, lProvozne)
WHERE lProvozne = 1;

The optimizer can't always make the same inferences that we can about sorted data, so I changed the query to make it understand that the sort isn't needed:

```
UPDATE @Auto SET lMaxTachograf = ISNULL(ISNULL(Km.lKm, [@Auto].lKmServis),[@Auto].lKmStart)
FROM @Auto
OUTER APPLY
(
SELECT TOP (1) lkmDo lKm
FROM
(
SELECT TOP (1) Jizda.dtZacatek, ca.lkmDo
FROM Jizda
CROSS APPLY (
SELECT TOP (1) JizdaTachograf.lkmDo
FROM JizdaTachograf
WHERE JizdaTachograf.IDJizda = Jizda.lIDJizda
AND JizdaTachograf.lkmOd IS NOT NULL AND
JizdaTachograf.lkmDo IS NOT NULL
AND JizdaTachograf.IDNaves = [@Auto].lIDAuto -- this line is different
ORDER BY JizdaTachograf.lkmDo DESC
) ca
WHERE Jizda.lProvozne = 1
AND Jizda.dtZacatek > ISNULL(ISNULL([@Auto].dtServis,[@Auto].dtStart),DATEADD(YEAR,-100,GETDATE()))
ORDER BY Jizda.dtZacatek DESC

UNION ALL

SELECT TOP (1) Jizda.dtZacatek, ca.lkmDo
FROM Jizda
CROSS APPLY (
SELECT TOP (1) JizdaTacho

Code Snippets

CREATE INDEX IX2 ON JizdaTachograf (IDAuto, IDJizda, lkmDo) INCLUDE (lkmOd)
WHERE lkmOd IS NOT NULL AND lkmDo IS NOT NULL;

CREATE INDEX IX3 ON JizdaTachograf (IDNaves, IDJizda, lkmDo) INCLUDE (lkmOd)
WHERE lkmOd IS NOT NULL AND lkmDo IS NOT NULL;

UPDATE @Auto SET lMaxTachograf = ISNULL(ISNULL(Km.lKm, [@Auto].lKmServis),[@Auto].lKmStart)
    FROM @Auto 
    OUTER APPLY 
    (
    SELECT TOP (1) lKm
    FROM
    (

    SELECT TOP (1) Jizda.dtZacatek, JizdaTachograf.lkmDo lKm 
     FROM JizdaTachograf
     INNER JOIN Jizda WITH (INDEX(1)) ON JizdaTachograf.IDJizda = Jizda.lIDJizda 
     WHERE JizdaTachograf.lkmOd IS NOT NULL AND 
           JizdaTachograf.lkmDo IS NOT NULL AND Jizda.lProvozne = 1 
           AND JizdaTachograf.IDAuto = [@Auto].lIDAuto -- first half
           AND Jizda.dtZacatek > ISNULL(ISNULL([@Auto].dtServis, [@Auto].dtStart),DATEADD(YEAR,-100,GETDATE())) 
     ORDER BY  Jizda.dtZacatek DESC, JizdaTachograf.lkmDo desc

     UNION ALL

     SELECT TOP (1) Jizda.dtZacatek, JizdaTachograf.lkmDo lKm 
     FROM JizdaTachograf
     INNER JOIN Jizda WITH (INDEX(1)) ON JizdaTachograf.IDJizda = Jizda.lIDJizda 
     WHERE JizdaTachograf.lkmOd IS NOT NULL AND 
           JizdaTachograf.lkmDo IS NOT NULL AND Jizda.lProvozne = 1 
           AND JizdaTachograf.IDNaves = [@Auto].lIDAuto -- second half
           AND Jizda.dtZacatek > ISNULL(ISNULL([@Auto].dtServis, [@Auto].dtStart),DATEADD(YEAR,-100,GETDATE())) 
     ORDER BY Jizda.dtZacatek DESC, JizdaTachograf.lkmDo desc
    ) IDNaves_IDAuto
    ORDER BY dtZacatek DESC, lKm DESC   
    ) Km;

CREATE INDEX IX1 ON Jizda (dtZacatek) INCLUDE (lIDJizda, lProvozne)
WHERE lProvozne = 1;

UPDATE @Auto SET lMaxTachograf = ISNULL(ISNULL(Km.lKm, [@Auto].lKmServis),[@Auto].lKmStart)
    FROM @Auto 
    OUTER APPLY 
    (
    SELECT TOP (1) lkmDo lKm
    FROM
    (
        SELECT TOP (1) Jizda.dtZacatek, ca.lkmDo
        FROM Jizda 
        CROSS APPLY (
             SELECT TOP (1) JizdaTachograf.lkmDo
             FROM JizdaTachograf
             WHERE JizdaTachograf.IDJizda = Jizda.lIDJizda 
             AND JizdaTachograf.lkmOd IS NOT NULL AND 
             JizdaTachograf.lkmDo IS NOT NULL
             AND JizdaTachograf.IDNaves = [@Auto].lIDAuto  -- this line is different    
             ORDER BY JizdaTachograf.lkmDo DESC          
        ) ca
        WHERE Jizda.lProvozne = 1 
        AND Jizda.dtZacatek > ISNULL(ISNULL([@Auto].dtServis,[@Auto].dtStart),DATEADD(YEAR,-100,GETDATE())) 
        ORDER BY Jizda.dtZacatek DESC

        UNION ALL

        SELECT TOP (1) Jizda.dtZacatek, ca.lkmDo
        FROM Jizda 
        CROSS APPLY (
             SELECT TOP (1) JizdaTachograf.lkmDo
             FROM JizdaTachograf
             WHERE JizdaTachograf.IDJizda = Jizda.lIDJizda 
             AND JizdaTachograf.lkmOd IS NOT NULL AND 
             JizdaTachograf.lkmDo IS NOT NULL
             AND JizdaTachograf.IDAuto = [@Auto].lIDAuto -- this line is different  
             ORDER BY JizdaTachograf.lkmDo DESC          
        ) ca
        WHERE Jizda.lProvozne = 1 
        AND Jizda.dtZacatek > ISNULL(ISNULL([@Auto].dtServis,[@Auto].dtStart),DATEADD(YEAR,-100,GETDATE())) 
        ORDER BY Jizda.dtZacatek DESC
    )  IDNaves_IDAuto

    ORDER BY dtZacatek DESC, lkmDo DESC
    ) Km

WHERE [@Auto].dtServis IS NULL AND [@Auto].dtStart IS NULL;

Context

StackExchange Database Administrators Q#225584, answer score: 4

Revisions (0)

No revisions yet.