In the following example the performance of the first version using only one SQL query is very bad. The problem is the last join.
When I store the result of the left side into a temporary table and join this, it runs about 50 times quicker.
It is intended to convert this to Oracle, therefore I try to avoid the use of temporary tables.
Any explanations, why the first query behaves so bad or hint to improve it?
Declare @HO int = 2866;
Declare @Dtz int = 35;
---- version 1 takes about 60 seconds
With ratings as
(
select
ROW_NUMBER() Over ( ORDER BY SEDate) lfd,
SEDate,
BBCode
from PPV
join BB on BBRefnr = SEBBRefnr
where SEHORefnr = @HORefnr
and SEBBRefnr > 0
)
Select
SEDATE,
BBCODE,
Code,
DTRefnr
FROM (
Select
l.SEDate,
l.BBCode,
dbo.GetCode(@Dtz, l.BBCode) Code
from ratings l left join ratings r on l.lfd = r.lfd + 1
where l.BBCode <> r.BBCode
or r.BBCode is null
) as t
join DT d on code = d.DTCode and d.DTDTKRefnr = @Dtz
order by 1;
------- version 2 using a temp table takes less than 1 second
With ratings as
(
select
ROW_NUMBER() Over ( ORDER BY SEDate) lfd,
SEDate,
BBCode
from PPV
join BB on BBRefnr = SEBBRefnr
where SEHORefnr = @HORefnr
and SEBBRefnr > 0
)
Select
l.SEDate,
l.BBCode,
dbo.GetCode(@Dtz, l.BBCode) Code
into #tmp
from ratings l left join ratings r on l.lfd = r.lfd + 1
where l.BBCode <> r.BBCode
or r.BBCode is null
Select
SEDATE,
BBCODE,
Code,
DTRefnr
FROM #tmp
join DT d on code = d.DTCode and d.DTDTKRefnr = @Dtz
order by 1;
EDIT:
Here the function:
Create function dbo.GetCode (
@fDtz int,
@Value varchar(10)
) returns varchar(10)
as
begin
declare @CODE varchar(10)
SET @Code =
(SELECT Code FROM Ableitungen
WHERE DTZ = @fDtz
AND Value = @Value )
return @CODE
end
Best Answer
Exactly as expected.
A scalar UDF is a black box to the optimiser: no indexes can be used, the cost can't be worked out correctly.
And if the scalar UDF has table access, then you have running a CURSOR (bad in SQL Server) to do a lookup per row: it isn't a set based operation
To fix it, don't use a UDF this way. It can be written as a JOIN. There is no hint or magic
Edit: remove the UDF. For each row in ratings your query Ableitungen. It's exponential
And I've move the filters into the JOINs