SQL statement performance

11 Nov 2005 6:03 AM

Atenza

Hi all,

I have the following sql:

SELECT
    a.fieldA, b.fieldB, c.filedC,
    dbo.fn_A(1, a.fieldA, b.fieldB, c.filedC) as testField
FROM tableA a
    INNER JOIN tableB b ON ...
    INNER JOIN tableC c ON ...
WHERE
    dbo.fn_A(1, a.fieldA, b.fieldB, c.filedC) as testField > 0

which require to execute around 1 min

However, if i change to the following structure, remove the condition and
use a loop to replace it, it only requires few seconds:

SELECT
    a.fieldA, b.fieldB, c.filedC,
    dbo.fn_A(1, a.fieldA, b.fieldB, c.filedC) as testField
FROM tableA a
    INNER JOIN tableB b ON ...
    INNER JOIN tableC c ON ...

OPEN cur
FETCH cur INTO @fieldA, @fieldB, @fieldC, @testField

WHILE (@@fetch_status = 0)
BEGIN
    if @testField > 0
    BEGIN
    END
END

Would you advise me what can i do to improve performance of the select
statement?

Thanks,
Martin

11 Nov 2005 6:31 AM

Vadivel

Might be you want to read this ..
http://sql-server-performance.com/tuning_joins.asp

Best Regards
Vadivel

http://vadivel.blogspot.com
http://thinkingms.com/vadivel

Show quote

"Atenza" wrote:

> Hi all,
>
> I have the following sql:
>
> SELECT
>     a.fieldA, b.fieldB, c.filedC,
>     dbo.fn_A(1, a.fieldA, b.fieldB, c.filedC) as testField
> FROM tableA a
>     INNER JOIN tableB b ON ...
>     INNER JOIN tableC c ON ...
> WHERE
>     dbo.fn_A(1, a.fieldA, b.fieldB, c.filedC) as testField > 0
>
> which require to execute around 1 min
>
> However, if i change to the following structure, remove the condition and
> use a loop to replace it, it only requires few seconds:
>
> SELECT
>     a.fieldA, b.fieldB, c.filedC,
>     dbo.fn_A(1, a.fieldA, b.fieldB, c.filedC) as testField
> FROM tableA a
>     INNER JOIN tableB b ON ...
>     INNER JOIN tableC c ON ...
>
> OPEN cur
> FETCH cur INTO @fieldA, @fieldB, @fieldC, @testField
>
> WHILE (@@fetch_status = 0)
> BEGIN
>     if @testField > 0
>     BEGIN
>     END
> END
>
> Would you advise me what can i do to improve performance of the select
> statement?
>
> Thanks,
> Martin
>
>
>

11 Nov 2005 7:36 AM

Jens

Hi martin,

The calling of a function is a rowbased table scan work, so you should
avopid calling the function twice with perhaps:

SELECT
    fieldA, fieldB, filedC,testField
FROM
(
SELECT
    a.fieldA, b.fieldB, c.filedC,
    dbo.fn_A(1, a.fieldA, b.fieldB, c.filedC) as testField
FROM tableA a
    INNER JOIN tableB b ON ...
    INNER JOIN tableC c ON ...
) SubQuery
Where testfield > 0

HTH, Jens Suessmeyer.

11 Nov 2005 2:44 PM

David Gugick

Atenza wrote:

> Hi all,
>
> I have the following sql:
>
> SNIP

Sclarar functions can cause severe performance problems if you use them
on large intermediate result sets. As Jens mentions, if you absolutely
must use one, then make sure you do not call it more than once per row.
If you examine the behavior of a scalr function in Profiler what you see
is something akin to making a stored procedure call for every row in the
query and that can cause an order of magnitude increase in performance
time (CPU and Duration). If the function is simple enough, I prefer
coding it in-line (if possible). If not try and limit the number of rows
that must be accessed.

--
David Gugick
Quest Software
www.imceda.com
www.quest.com

12 Nov 2005 1:50 AM

Atenza

I have tried Jens approach, got the same result :(
Anyway, i am studying Vadivel's suggestion and thx supporting from all of
you

Show quote

"David Gugick" <david.gugick-nospam@quest.com> wrote in message
news:%23BsId5s5FHA.744@TK2MSFTNGP10.phx.gbl...
> Atenza wrote:
> > Hi all,
> >
> > I have the following sql:
> >
> > SNIP
>
> Sclarar functions can cause severe performance problems if you use them
> on large intermediate result sets. As Jens mentions, if you absolutely
> must use one, then make sure you do not call it more than once per row.
> If you examine the behavior of a scalr function in Profiler what you see
> is something akin to making a stored procedure call for every row in the
> query and that can cause an order of magnitude increase in performance
> time (CPU and Duration). If the function is simple enough, I prefer
> coding it in-line (if possible). If not try and limit the number of rows
> that must be accessed.
>
>
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com
>