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:

old query plan

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:

plan 1

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:

plan 2

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