Optimizing table valued function SQL Server

Question

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](
   @IDVazRole INT,
   @IDUzivatel INT,
   @IDRole INT,
   @IDLokalita INT,
   @lCid INT
)

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,
       dcZbyvaMotohodin DECIMAL(15,2),
       lZbyvaDni INT,
       lDepozit INT,
       IDVazPlanServisAuto INT,
       IDPlanServisDefinice INT,
       lMaxTachograf INT,
       lKmPretaceni INT,
       dtOd DATE,
       lKmPosledniServis INT
    )

    DECLARE @IDCisAutoParametrKmPriZavadeni INT = 10012
    DECLARE @lKmPred INT = 30000
    DECLARE @lKmPredMensi INT = 15000
    DECLARE @lDniPred INT = 60
    DECLARE @lDniPredMensi INT = 30
    DECLARE @lMotohodinyPred INT = 100
    DECLARE @lMotohodinyPredMensi INT = 50
    DECLARE @IDBarvaBlizi INT = 1010 --Odkaz do CisTermBarva
    DECLARE @IDBarvaBliziMensi INT = 1016 --Odkaz do CisTermBarva
    DECLARE @IDBarvaPres INT  = 1017 --Odkaz do CisTermBarva
    --============ Koenc deklarace promennych ===========

    INSERT INTO @Auto (lIDAuto, szSPZ, szTyp, IDCisTypServis, szTypServisu, lKmStart, dtStart, lKmPriZavedeni, dcMotohodinyStart,[@Auto].lIntervalKm,[@Auto].dcIntervalMotohodiny,[@Auto].lIntervalDatum,szServisniPlan,[@Auto].IDVazPlanServisAuto,[@Auto].IDPlanServisDefinice)
    SELECT Auto.lIDAuto,
           Auto.szSpz,
           CASE WHEN Auto.lTyp = 0 THEN 'Taha?'  WHEN Auto.lTyp =  1 THEN 'N?v?s' ELSE '' END,
           PlanServisDefinice.IDCisTypServis,
           dbo.GetLocalText('CisTypServis','szNazev',CisTypServis.lIDCisTypServis,@lCid,CisTypServis.szNazev,''),
           PlanServisDefinice.lStartKM,
           PlanServisDefinice.dtStartDatum,
           CONVERT(INT,VazAutoParametr.varHodnota),
           PlanServisDefinice.dcMotohodinyStart,
           PlanServisDefinice.lIntervalKM,
           PlanServisDefinice.dcIntervalMotohodin,
           PlanServisDefinice.lIntervalDatum,
           PlanServis.szNazev,
           PlanServisDefinice.IDVazPlanServisAuto,
           PlanServisDefinice.lIDPlanServisDefinice
    FROM Auto INNER JOIN VazPlanServisAuto ON Auto.lIDAuto = VazPlanServisAuto.IDAuto
             INNER JOIN PlanServisDefinice ON VazPlanServisAuto.lIDVazPlanServisAuto = PlanServisDefinice.IDVazPlanServisAuto   
             INNER JOIN CisTypServis ON PlanServisDefinice.IDCisTypServis = CisTypServis.lIDCisTypServis
             LEFT OUTER JOIN VazAutoParametr ON VazAutoParametr.IDAuto = Auto.lIDAuto AND VazAutoParametr.IDCisAutoParametr = @IDCisAutoParametrKmPriZavadeni
             INNER JOIN PlanServis ON VazPlanServisAuto.IDPlanServisu = PlanServis.lIDPlanServis


    UPDATE @Auto SET lUjeto = Km.lKm
        FROM @Auto INNER JOIN 
        (SELECT
                    SUM(JizdaTachograf.lkmDo - JizdaTachograf.lkmOd) AS lKm, JizdaTachograf.IDAuto,JizdaTachograf.IDNaves
        FROM Jizda
            INNER JOIN JizdaTachograf ON JizdaTachograf.IDJizda = Jizda.lIDJizda
        WHERE JizdaTachograf.lkmOd IS NOT NULL
            AND JizdaTachograf.lkmDo IS NOT NULL
            AND Jizda.lProvozne = 1
        GROUP BY 
            JizdaTachograf.IDAuto,JizdaTachograf.IDNaves    
        ) as Km 
            ON Km.IDAuto = [@Auto].lIDAuto OR Km.IDNaves = [@Auto].lIDAuto
            OPTION  (RECOMPILE)


    UPDATE @Auto SET lMaxTachograf = ISNULL(ISNULL(Km.lKm, [@Auto].lKmServis),[@Auto].lKmStart)
    FROM @Auto 
         OUTER APPLY 
    (SELECT TOP 1 JizdaTachograf.lkmDo lKm 
     FROM Jizda INNER JOIN 
     JizdaTachograf 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 
           OR JizdaTachograf.IDNaves = [@Auto].lIDAuto) 
           AND Jizda.dtZacatek > ISNULL(ISNULL([@Auto].dtServis, 
           [@Auto].dtStart),DATEADD(YEAR,-100,GETDATE())) 
     ORDER BY 
           Jizda.dtZacatek DESC, JizdaTachograf.lkmDo desc) Km



    INSERT INTO @PlanServis (lIDAuto, 
                             szSPZ, 
                             lDepozit, 
                             szTypVozidla, 
                             szTypServisu, 
                             szServisniPlan, 
                             lZbyvaDni, 
                             lZbyvaKm, 
                             lNajetoKm, 
                             dtServis, 
                             dcZbyvaMotohodin, 
                             dcNajetoMotohodin, 
                             IDVazPlanServisAuto, 
                             IDPlanServisDefinice,
                             lBarva)
    SELECT lIDAuto, 
            szSPZ, 
            lDepozit, 
            szTyp, 
            szTypServisu, 
            szServisniPlan, 
            lZbyvaDni, 
            lZbyvaKm, 
            lUjeto,--lNajetoKm, 
            dtServis, 
            dcZbyvaMotohodin, 
            dcMotohodiny,--dcNajetoMotohodin, 
            IDVazPlanServisAuto, 
            IDPlanServisDefinice,
            lBarva
      FROM @Auto 
    RETURN
END
GO

Answer 1

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 times. 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) 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

Now there isn't any sorting:

However, this is a bit of a dangerous optimization to do. Now Jizda is the outer table for the nested loop join. Consider a row in @Auto with NULL for [@Auto].dtServis, NULL for [@Auto].dtStart, and no matches against JizdaTachograf by IDNaves and IDAuto. SQL Server will read through all 180k rows in Jizda and do 180k index seeks against JizdaTachograf to ultimately return no rows. I don't know how likely this is to happen, but it could happen.

Based on the information provided in the question, my advice is to try the first query and see if that makes it fast enough. If not, implement both queries with filters on them. Scanning an 845 row table variable takes no time at all, so you may be able to get the best of both queries with two separate UPDATE` statements that operate on different parts of the table. The first query may be more efficient when there isn't a non-NULL date column:

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

The second query may be more efficient when there is a non-NULL date column (I'm assuming that the column is somewhat selective):

WHERE [@Auto].dtServis IS NOT NULL OR [@Auto].dtStart IS NOT NULL

Answer 2

I think part of query which is updating @Auto table can inserted further in table variable.

So that, again same query is not repeated.so it will boost performance.

Please understand the idea and do the correct where require.

declare @table TABLE(lkmDo int, lkmOd int,IDAuto int, IDNaves int,dtZacatek datetime )
insert into @table
SELECT JizdaTachograf.lkmDo ,JizdaTachograf.lkmOd ,IDAuto,IDNaves,dtZacatek
     FROM Jizda INNER JOIN 
     JizdaTachograf 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 
           OR JizdaTachograf.IDNaves = [@Auto].lIDAuto) 
-- OPTION  (RECOMPILE)

    UPDATE @Auto SET lUjeto = Km.lKm
        FROM @Auto INNER JOIN 
        (SELECT
                    SUM(lkmDo - lkmOd) AS lKm, IDAuto,IDNaves
        FROM @table
        GROUP BY 
            JizdaTachograf.IDAuto,JizdaTachograf.IDNaves    
        ) as Km 
            ON Km.IDAuto = [@Auto].lIDAuto OR Km.IDNaves = [@Auto].lIDAuto



    UPDATE @Auto SET lMaxTachograf = ISNULL(ISNULL(Km.lKm, [@Auto].lKmServis),[@Auto].lKmStart)
    FROM @Auto 
         OUTER APPLY 
    (SELECT TOP 1 JizdaTachograf.lkmDo lKm 
     FROM @table
     WHERE
           Jizda.dtZacatek > ISNULL(ISNULL([@Auto].dtServis, 
           [@Auto].dtStart),DATEADD(YEAR,-100,GETDATE())) 
     ORDER BY 
           Jizda.dtZacatek DESC, JizdaTachograf.lkmDo desc) Km