Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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
share|improve this question
    
What does dbo.GetCode look like? –  gbn Jun 24 '11 at 12:05
    
@gbn Added function. –  bernd_k Jun 24 '11 at 17:52
add comment

1 Answer

up vote 6 down vote accepted

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

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,
    A.Code,
    D.DTRefnr
from 
    ratings l
    left join 
    ratings r on l.lfd = r.lfd + 1
    JOIN 
    Ableitungen A ON A.Value = l.BBCode
    join
    DT d on A.code = d.DTCode and d.DTDTKRefnr = A.DTZ
where
    A.DTZ = @fDtz
    AND 
   (l.BBCode <> r.BBCode or r.BBCode is null)
order by
    SEDATE;
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.