Very Computer

by **Arbajra** » Thu, 04 Dec 2003 16:34:07

Hi,

I have one "master" table and one "child" table. Each record of the master
table is connected to mutiple records of the child table by field
"masterid".

Question is - is the query with user-defined function faster than without
it?

1. select m.*,
(select count(*) from child c1 where c1.masterid = m.masterid and postind =
'Y') ,
(select count(*) from child c2 where c2.masterid = m.masterid and postind =
'X')
from master m where m.masterid = 235423

2. select m.*, GetNrOfChild(m.masterid, 'Y'), GetNrOfChild(m.masterid, 'X')
from master m where m.masterid = 235423

Of course, "GetNrOfChild" is user-defined function that is basically (select
count(*) from child c2 where c2.masterid = <something> and postind =
<something_else>)

Thx in advance
Armin

by **Uri Diman** » Thu, 04 Dec 2003 16:41:54

Hi,
Have you tested it by yourself?
i'd go with subquery rather than udf

Quote:> Hi,

> I have one "master" table and one "child" table. Each record of the master
> table is connected to mutiple records of the child table by field
> "masterid".

> Question is - is the query with user-defined function faster than without
> it?

> 1. select m.*,
> (select count(*) from child c1 where c1.masterid = m.masterid and postind
=
> 'Y') ,
> (select count(*) from child c2 where c2.masterid = m.masterid and postind
=
> 'X')
> from master m where m.masterid = 235423

> 2. select m.*, GetNrOfChild(m.masterid, 'Y'), GetNrOfChild(m.masterid,
'X')
> from master m where m.masterid = 235423

> Of course, "GetNrOfChild" is user-defined function that is basically
(select
> count(*) from child c2 where c2.masterid = <something> and postind =
> <something_else>)

> Thx in advance
> Armin

by **Tom Hage** » Thu, 04 Dec 2003 17:02:31

Hi,

I was considering the same just a while before. I have to say that SUBQUERY
IS FASTER then the function.
I was selecting something like 2000 records and joining with select from
another 2000 records. With subquery I got to 2 sec, with the function nearly
a minute!!!

This is my experience. But I am also interested WHY? Anyone have answer?

Thanks

Tom

Quote:> Hi,

> I have one "master" table and one "child" table. Each record of the master
> table is connected to mutiple records of the child table by field
> "masterid".

> Question is - is the query with user-defined function faster than without
> it?

> 1. select m.*,
> (select count(*) from child c1 where c1.masterid = m.masterid and postind
=
> 'Y') ,
> (select count(*) from child c2 where c2.masterid = m.masterid and postind
=
> 'X')
> from master m where m.masterid = 235423

> 2. select m.*, GetNrOfChild(m.masterid, 'Y'), GetNrOfChild(m.masterid,
'X')
> from master m where m.masterid = 235423

> Of course, "GetNrOfChild" is user-defined function that is basically
(select
> count(*) from child c2 where c2.masterid = <something> and postind =
> <something_else>)

> Thx in advance
> Armin

by **Aaron Bertrand - MV** » Thu, 04 Dec 2003 17:02:51

Quote:> Question is - is the query with user-defined function faster than without
> it?

You are in a much better position to answer that question than we are, since
you have your table structure, indexes, dataset, hardware, etc. all right
there in front of you. As Uri suggested, my money would be on the subquery.
But it makes no sense to ask us which is faster when you have the answer
right in front of you. Turn statistics and execution plan on when you run
the two queries in Query Analyzer, and you should have more than enough
information...

--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/

by **Aaron Bertrand - MV** » Thu, 04 Dec 2003 17:04:15

Quote:> This is my experience. But I am also interested WHY? Anyone have answer?

A subquery is a set, which is the kind of thing SQL Server is optimized to
work against. A function is fired once for every single row.

--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/

by **Uri Diman** » Thu, 04 Dec 2003 17:06:34

Hi, Tom
Here you will find some info about UDF's perfromace.
http://www.sql-server-performance.com/user_defined_functions.asp

> Hi,

> I was considering the same just a while before. I have to say that
SUBQUERY
> IS FASTER then the function.
> I was selecting something like 2000 records and joining with select from
> another 2000 records. With subquery I got to 2 sec, with the function
nearly
> a minute!!!

> This is my experience. But I am also interested WHY? Anyone have answer?

> Thanks

> Tom

> > Hi,

> > I have one "master" table and one "child" table. Each record of the
master
> > table is connected to mutiple records of the child table by field
> > "masterid".

> > Question is - is the query with user-defined function faster than
without
> > it?

> > 1. select m.*,
> > (select count(*) from child c1 where c1.masterid = m.masterid and
postind
> =
> > 'Y') ,
> > (select count(*) from child c2 where c2.masterid = m.masterid and
postind
> =
> > 'X')
> > from master m where m.masterid = 235423

> > 2. select m.*, GetNrOfChild(m.masterid, 'Y'), GetNrOfChild(m.masterid,
> 'X')
> > from master m where m.masterid = 235423

> > Of course, "GetNrOfChild" is user-defined function that is basically
> (select
> > count(*) from child c2 where c2.masterid = <something> and postind =
> > <something_else>)

> > Thx in advance
> > Armin

by **Arbajra** » Fri, 05 Dec 2003 08:57:56

The fact is - I did turn statistics (+ execution plan) on and didn't get any
significant difference between two cases.

Anyway, thx for your answers :)
Armin

> Hi, Tom
> Here you will find some info about UDF's perfromace.
> http://www.sql-server-performance.com/user_defined_functions.asp

> > Hi,

> > I was considering the same just a while before. I have to say that
> SUBQUERY
> > IS FASTER then the function.
> > I was selecting something like 2000 records and joining with select from
> > another 2000 records. With subquery I got to 2 sec, with the function
> nearly
> > a minute!!!

> > This is my experience. But I am also interested WHY? Anyone have answer?

> > Thanks

> > Tom

> > > Hi,

> > > I have one "master" table and one "child" table. Each record of the
> master
> > > table is connected to mutiple records of the child table by field
> > > "masterid".

> > > Question is - is the query with user-defined function faster than
> without
> > > it?

> > > 1. select m.*,
> > > (select count(*) from child c1 where c1.masterid = m.masterid and
> postind
> > =
> > > 'Y') ,
> > > (select count(*) from child c2 where c2.masterid = m.masterid and
> postind
> > =
> > > 'X')
> > > from master m where m.masterid = 235423

> > > 2. select m.*, GetNrOfChild(m.masterid, 'Y'), GetNrOfChild(m.masterid,
> > 'X')
> > > from master m where m.masterid = 235423

> > > Of course, "GetNrOfChild" is user-defined function that is basically
> > (select
> > > count(*) from child c2 where c2.masterid = <something> and postind =
> > > <something_else>)

> > > Thx in advance
> > > Armin

by **Aaron Bertrand - MV** » Fri, 05 Dec 2003 16:20:15

Quote:> The fact is - I did turn statistics (+ execution plan) on and didn't get
any
> significant difference between two cases.

Perhaps your dataset wasn't large enough.

--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/

Very Computer

User-defined functions vs. subqueries

User-defined functions vs. subqueries

User-defined functions vs. subqueries

User-defined functions vs. subqueries

User-defined functions vs. subqueries

User-defined functions vs. subqueries

User-defined functions vs. subqueries

User-defined functions vs. subqueries

User-defined functions vs. subqueries