MS SQL Server - my first function

Author

my first function

Dion

2005-04-29, 8:23 pm

I am looking for some insight into performance issues with a query:

SELECT bv.fld1, bct.fld2
FROM bct LEFT JOIN bv on BCT.VisitID=bv.VisitID
WHERE bct.BatchDate>='9-1-2004' --bct has a clustered index on BatchDate

this query takes about 8 sec
If I change the select to:
SELECT fn_LG(bv.fld1,bct.fld2) --fn_LG is my first function
the query takes about 54 sec.
If I add GROUP BY fn_LG(bv.fld1,bct.fld2) the query takes about 90 sec.
This is ultimately what I want to do, but I'm not sure why I am going from 8
sec to 90 sec, and how I can get things back to the 8 sec range.

Since all this is still fairly new to me, I am looking more for insight on
what things to consider, rather than a quick answer to my problem.

Thanks
Dion

Jens Süßmeyer

2005-04-29, 8:23 pm

Would be great if you could show us your function to see wheter there is
potential to tune it, it wouldn´t group by using the function. Make an outer
query to group the result by the vales returned from the function.

HTH, Jens Suessmeyer.

---
http://www.sqlserver2005.de
---

"Dion" <Dion@discussions.microsoft.com> schrieb im Newsbeitrag
news:C3BCCA44-02E4-4751-A691- 9592077E594D@microso
ft.com...
>I am looking for some insight into performance issues with a query:
>
> SELECT bv.fld1, bct.fld2
> FROM bct LEFT JOIN bv on BCT.VisitID=bv.VisitID
> WHERE bct.BatchDate>='9-1-2004' --bct has a clustered index on BatchDate
>
> this query takes about 8 sec
> If I change the select to:
> SELECT fn_LG(bv.fld1,bct.fld2) --fn_LG is my first function
> the query takes about 54 sec.
> If I add GROUP BY fn_LG(bv.fld1,bct.fld2) the query takes about 90 sec.
> This is ultimately what I want to do, but I'm not sure why I am going from
> 8
> sec to 90 sec, and how I can get things back to the 8 sec range.
>
> Since all this is still fairly new to me, I am looking more for insight on
> what things to consider, rather than a quick answer to my problem.
>
> Thanks
> Dion

David Gugick

2005-04-30, 3:23 am

Dion wrote:
> I am looking for some insight into performance issues with a query:
>
> SELECT bv.fld1, bct.fld2
> FROM bct LEFT JOIN bv on BCT.VisitID=bv.VisitID
> WHERE bct.BatchDate>='9-1-2004' --bct has a clustered index on
> BatchDate
>
> this query takes about 8 sec
> If I change the select to:
> SELECT fn_LG(bv.fld1,bct.fld2) --fn_LG is my first function
> the query takes about 54 sec.
> If I add GROUP BY fn_LG(bv.fld1,bct.fld2) the query takes about 90
> sec. This is ultimately what I want to do, but I'm not sure why I am
> going from 8 sec to 90 sec, and how I can get things back to the 8
> sec range.
>
> Since all this is still fairly new to me, I am looking more for
> insight on what things to consider, rather than a quick answer to my
> problem.
>
> Thanks
> Dion

Functions are terrible for performance, even scalar ones. Simple tests
I've run on a function that adds 1 day to a date parameter causes about
a 10-fold increase in CPU. If you're only dealing with a few rows, then
you might not notice. Once the row count gets high, the performanc
degradation is pretty profound. BTW, date comparisons should always be
done using 'YYYYMMDD' format.

--
David Gugick
Imceda Software
www.imceda.com

Sponsored Links