I have this query and I want to improve performance:
SELECT
OrarioA,
OrarioB,
IDOrario,
IDDettaglioOrarioA,
IDDettaglioOrarioB
FROM
(
SELECT
Tb_01.Orario AS OrarioA,
Tb_02.Orario AS OrarioB,
Tb_01.IDDettaglioOrariLinee AS IDDettaglioOrarioA,
Tb_02.IDDettaglioOrariLinee AS IDDettaglioOrarioB,
Tb_01.IDOrario,
ROW_NUMBER() OVER (PARTITION BY Tb_01.Orario, Tb_02.Orario ORDER BY Tb_01.IDOrario DESC) AS Row
FROM
(
SELECT Orario, IDDettaglioOrariLinee, IDOrario
FROM DettaglioOrariLinee
WHERE IDRelLineeStazionamenti = @IDRelA
) AS Tb_01
INNER JOIN
(
SELECT Orario, IDDettaglioOrariLinee, IDOrario
FROM DettaglioOrariLinee
WHERE IDRelLineeStazionamenti = @IDRelB
) AS Tb_02
ON Tb_01.IDOrario = Tb_02.IDOrario
INNER JOIN
(
SELECT IDOrario
FROM Periodi
WHERE
(
@Data = 0 OR
(
@Data >= CAST(CAST(DATEPART(DAY, PeriodoDal) AS VARCHAR)+'/'+ CAST(DATEPART(MONTH, PeriodoDal)AS VARCHAR) +'/'+ CAST(DATEPART(YEAR,@Data)AS VARCHAR) AS DATETIME)
AND
@Data <= CAST(CAST(DATEPART(DAY, PeriodoAl) AS VARCHAR)+'/'+ CAST(DATEPART(MONTH, PeriodoAl)AS VARCHAR) +'/'+ CAST(DATEPART(YEAR,@Data)AS VARCHAR) AS DATETIME)
)
)
) Tb_Periodi
ON Tb_01.IDOrario = Tb_Periodi.IDOrario --dbo.periodi ON Tb_01.IDOrario = dbo.periodi.IDOrario
INNER JOIN --dbo.relgiornisettimanaorarilinee ON Tb_01.IDOrario = dbo.relgiornisettimanaorarilinee.IDOrario
(
SELECT IDOrario
FROM relgiornisettimanaorarilinee
WHERE @IDGiorno = 0 OR IDGiorno = @IDGiorno
) Tb_Giorni
ON Tb_01.IDOrario = Tb_Giorni.IDOrario
WHERE
(
@Orario = '' OR DATEDIFF(minute, CAST(@ORARIO AS DATETIME), CAST(Tb_01.Orario AS DATETIME)) >=0
) AND (
DATEDIFF(minute, CAST(Tb_01.Orario AS DATETIME), CAST(Tb_02.Orario AS DATETIME)) >=0
) AND (
@IDOrari = '' OR Tb_01.IDOrario NOT IN (SELECT CAST(s AS INT) AS IDOrario FROM dbo.Split(',', @IDOrari) AS Split_1)
)
/*
AND
(
@Data = 0
OR
(
@Data >= CAST(CAST(DATEPART(DAY, PeriodoDal) AS VARCHAR)+'/'+ CAST(DATEPART(MONTH, PeriodoDal)AS VARCHAR) +'/'+ CAST(DATEPART(YEAR,@Data)AS VARCHAR) AS DATETIME)
AND
@Data <= CAST(CAST(DATEPART(DAY, PeriodoAl) AS VARCHAR)+'/'+ CAST(DATEPART(MONTH, PeriodoAl)AS VARCHAR) +'/'+ CAST(DATEPART(YEAR,@Data)AS VARCHAR) AS DATETIME)
)
)
AND
(@IDGiorno = 0 OR IDGiorno = @IDGiorno)
*/
) As Tb_New
WHERE ROW = 1
OPTION (MAXRECURSION 0);
I want to filter Tb_Periodi And Tb_Giorni with IDOrario.
How can I improve this query?