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
Best Answer
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 onJizdaTachograf
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 forlkmOd
. There are many different index definitions that could work. Two are below:I then split up the query so that SQL Server can take advantage of the indexes.
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:
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:
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 againstJizdaTachograf
byIDNaves
andIDAuto
. SQL Server will read through all 180k rows inJizda
and do 180k index seeks againstJizdaTachograf
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:
The second query may be more efficient when there is a non-NULL date column (I'm assuming that the column is somewhat selective):