Relative Performance: Native SQL vs User Functions

Hi all,
I'm interested in knowing, for a fact, what the performance overhead of
using a functional-language based query e.g.
select * from customers where dbo.ufn_stringcontains(custname, 'ar')=1
is over a conventional SQL statement:
select * from addrb4 where custname like '%'+'ar'+'%'
From the execution plan in my SQL2000 query analyser, it shows the
functional query is slightly better costing the batch of 49.97%. However,
both queries take 28 seconds to execute. I was expecting the functional
query to be slower so I'm a bit surprised.
I know the perfomance of the functional query depends on how complex the
function is. In my applications, the functions will be quite simple and where
they will be complex, a convenctional equivalent will equally complex to
build.
Your opinions, experience and test runs are welcome.
--
NB: declaration for ufn_stringcontains:
create function ufn_stringontains (@.m varchar(8000) , @.s varchar(8000) )
returns bit
as
begin
declare @.ret bit
if (@.m like '%'+@.s+'%')
set @.ret=1
else
set @.ret=0
return @.ret
endBecause of the like you are using, a table scan in involved, resulting in
mostly IO.
Try running the same query and function, but using only like to the right of
the search string (select * from addrb4 where custname like 'ar'+'%') and
have an index on custname.
You will find that the difference becomes clearer as soon as more records
can be eliminated by a index seek or scan.
Functions are faster sometimes, and sometimes they destroy performance. It
varies and there is no hard and fast rule.
Regards
Mike
"Sienko" wrote:
> Hi all,
> I'm interested in knowing, for a fact, what the performance overhead of
> using a functional-language based query e.g.
> select * from customers where dbo.ufn_stringcontains(custname, 'ar')=1
> is over a conventional SQL statement:
> select * from addrb4 where custname like '%'+'ar'+'%'
> From the execution plan in my SQL2000 query analyser, it shows the
> functional query is slightly better costing the batch of 49.97%. However,
> both queries take 28 seconds to execute. I was expecting the functional
> query to be slower so I'm a bit surprised.
> I know the perfomance of the functional query depends on how complex the
> function is. In my applications, the functions will be quite simple and where
> they will be complex, a convenctional equivalent will equally complex to
> build.
> Your opinions, experience and test runs are welcome.
> --
> NB: declaration for ufn_stringcontains:
> create function ufn_stringontains (@.m varchar(8000) , @.s varchar(8000) )
> returns bit
> as
> begin
> declare @.ret bit
> if (@.m like '%'+@.s+'%')
> set @.ret=1
> else
> set @.ret=0
> return @.ret
> end
>|||"Sienko" <Sienko@.discussions.microsoft.com> wrote in message
news:0ABAE066-2E11-4679-9AAA-8E9B8A636DD2@.microsoft.com...
> create function ufn_stringontains (@.m varchar(8000) , @.s varchar(8000) )
> returns bit
> as
> begin
> declare @.ret bit
> if (@.m like '%'+@.s+'%')
> set @.ret=1
> else
> set @.ret=0
> return @.ret
> end
You could speed it up a tad by doing early returns:
if (@.m like '%'+@.s+'%' )
return 1
else
return 0
Personally, I find this style easier to understand, as well.

Relative Performance: Native SQL vs User Functions

Hi all,
I'm interested in knowing, for a fact, what the performance overhead of
using a functional-language based query e.g.
select * from customers where dbo.ufn_stringcontains(custname, 'ar')=1
is over a conventional SQL statement:
select * from addrb4 where custname like '%'+'ar'+'%'
From the execution plan in my SQL2000 query analyser, it shows the
functional query is slightly better costing the batch of 49.97%. However,
both queries take 28 seconds to execute. I was expecting the functional
query to be slower so I'm a bit surprised.
I know the perfomance of the functional query depends on how complex the
function is. In my applications, the functions will be quite simple and wher
e
they will be complex, a convenctional equivalent will equally complex to
build.
Your opinions, experience and test runs are welcome.
NB: declaration for ufn_stringcontains:
create function ufn_stringontains (@.m varchar(8000) , @.s varchar(8000) )
returns bit
as
begin
declare @.ret bit
if (@.m like '%'+@.s+'%')
set @.ret=1
else
set @.ret=0
return @.ret
endBecause of the like you are using, a table scan in involved, resulting in
mostly IO.
Try running the same query and function, but using only like to the right of
the search string (select * from addrb4 where custname like 'ar'+'%') and
have an index on custname.
You will find that the difference becomes clearer as soon as more records
can be eliminated by a index seek or scan.
Functions are faster sometimes, and sometimes they destroy performance. It
varies and there is no hard and fast rule.
Regards
Mike
"Sienko" wrote:

> Hi all,
> I'm interested in knowing, for a fact, what the performance overhead of
> using a functional-language based query e.g.
> select * from customers where dbo.ufn_stringcontains(custname, 'ar')=1
> is over a conventional SQL statement:
> select * from addrb4 where custname like '%'+'ar'+'%'
> From the execution plan in my SQL2000 query analyser, it shows the
> functional query is slightly better costing the batch of 49.97%. However,
> both queries take 28 seconds to execute. I was expecting the functional
> query to be slower so I'm a bit surprised.
> I know the perfomance of the functional query depends on how complex the
> function is. In my applications, the functions will be quite simple and wh
ere
> they will be complex, a convenctional equivalent will equally complex to
> build.
> Your opinions, experience and test runs are welcome.
> --
> NB: declaration for ufn_stringcontains:
> create function ufn_stringontains (@.m varchar(8000) , @.s varchar(8000) )
> returns bit
> as
> begin
> declare @.ret bit
> if (@.m like '%'+@.s+'%')
> set @.ret=1
> else
> set @.ret=0
> return @.ret
> end
>|||"Sienko" <Sienko@.discussions.microsoft.com> wrote in message
news:0ABAE066-2E11-4679-9AAA-8E9B8A636DD2@.microsoft.com...

> create function ufn_stringontains (@.m varchar(8000) , @.s varchar(8000) )
> returns bit
> as
> begin
> declare @.ret bit
> if (@.m like '%'+@.s+'%')
> set @.ret=1
> else
> set @.ret=0
> return @.ret
> end
You could speed it up a tad by doing early returns:
if (@.m like '%'+@.s+'%' )
return 1
else
return 0
Personally, I find this style easier to understand, as well.|||The problem is mostly in how you use them. You could inappropriately use a
system function as well and in this case the wildcard prefix only makes the
problem worse.
For example:
SELECT <something>
FROM <some table> AS t1
WHERE DAY(DATEADD(day, 1, t1.<some date column> )) = 12
These are system supplied functions but this query is going to be costly reg
ardless if the date column is indexed or not. User defined functions are no
exception.
In your query, not only are you doing a lousy string search using the wildca
rd prefix, you are attempting to use a function to encapsulate a criteria.
You shouldn't see any difference in performance at all. In this case, the h
aystack is going to swamp in needle differences between straw.
Sincerely,
Anthony Thomas
--
"Sienko" <Sienko@.discussions.microsoft.com> wrote in message news:0ABAE066
-2E11-4679-9AAA-8E9B8A636DD2@.microsoft.com...
Hi all,
I'm interested in knowing, for a fact, what the performance overhead of
using a functional-language based query e.g.
select * from customers where dbo.ufn_stringcontains(custname, 'ar')=1
is over a conventional SQL statement:
select * from addrb4 where custname like '%'+'ar'+'%'
From the execution plan in my SQL2000 query analyser, it shows the
functional query is slightly better costing the batch of 49.97%. However,
both queries take 28 seconds to execute. I was expecting the functional
query to be slower so I'm a bit surprised.
I know the perfomance of the functional query depends on how complex the
function is. In my applications, the functions will be quite simple and wh
ere
they will be complex, a convenctional equivalent will equally complex to
build.
Your opinions, experience and test runs are welcome.
--
NB: declaration for ufn_stringcontains:
create function ufn_stringontains (@.m varchar(8000) , @.s varchar(8000) )
returns bit
as
begin
declare @.ret bit
if (@.m like '%'+@.s+'%')
set @.ret=1
else
set @.ret=0
return @.ret
endsql

Relationship between t-Sql and "Set Theory"

I have always heard that much of t-sql is based on "set theory". I had Set theory in high school and I remember it as being simple Unions, Intersections, Differences of Sets. By a Set I mean a collection such as {2,5,7,8,9, ...) That could well described a single row in a table. By unioning several of these rows we could end up with a table.

But how does that relate to t-sql such as

select * from <table name> Where <condition 1> ?

Is it simply that the result returned by the query is a Set? (if so, a Set is simply being used as a synonym for a Collection. No set theory involved.)

TIA,

barkingdog

This is quite a large subject =;o)

Perhaps the best way is to point you to some reading, to get you started.
http://en.wikipedia.org/wiki/Relational_model

/Kenneth

Relathionship for two databases

Is it possible to create a relationship between two tables in different
databases or between a table and a view which is based on a table in another
database?
If this is not possible, is it possible to setup RI between two databases
using the triggers?
Thanks.No there no such mechanisam to do this, what you could do is to work with
Views and design them to update the databases on change, another way would
be to trigger the changes / the integrity directly on the tables.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Vik" <viktorum@.==hotmail.com==> schrieb im Newsbeitrag
news:%23s6W54DRFHA.3296@.TK2MSFTNGP15.phx.gbl...
> Is it possible to create a relationship between two tables in different
> databases or between a table and a view which is based on a table in
> another database?
> If this is not possible, is it possible to setup RI between two databases
> using the triggers?
> Thanks.
>
>|||Yes, you have to force the RI using triggers.
AMB
"Vik" wrote:

> Is it possible to create a relationship between two tables in different
> databases or between a table and a view which is based on a table in anoth
er
> database?
> If this is not possible, is it possible to setup RI between two databases
> using the triggers?
> Thanks.
>
>

relate two tables based on an 'active' column

So I have a question and I will give a related example. Say I have a list of records in a table:

Product Cost Active

hat 1.00 false

coat 2.00 true

I have a DataGridView that shows purchases from a purchase table: Date, ProductID, Buyer

Is there a way to have a DataGridViewComboBoxColumn show only active items so that a user can only select active items in the puchases datagridview, but when they click a purchase from long ago when an item was active they have the ability to pick that old(unactive) item or a new one for an update.

Basically if I just have the ComboBoxColumn bound to a dataset with just active items, the datagrid will fail to load cause old purchases are not in its item list, but I also don't want all the items to be in the list.

So... you want a union of active items and inactive previously-bought items?

select *
from Products
where Active = 'true' --Should you be using a bit/tinyint/smallint here?
UNION ALL
select distinct p.*
from
Purchases pu
join

Products p
on p.ProductID = pu.ProductID
where p.Active = 'false'
and pu.Buyer = @.Buyer

Maybe?

Rob|||Hi,

well this is more a front-end related question. You can capture the SelectedIndexchangedEvent of the Combobox and select upon the value or the column where the row is marked as new / old if it can be choosen or not. Depending on your control you could also use another color for each combobox value (and therefore hrey out the non selectable). If you want to filter the combobox values in the first place without showing them to the user, you might want to hook into the ItemDatabound event. That highly depends on your used language / controls (which you did not mentioned)

HTH; Jens K. Suessmeyer.

http://www.sqlserver2005.de|||I got it hammered out. I set the combobox data source on a filtered binding source when the OnBeginEdit event fired. The filtered string simply included and "or combobox.Value = sourceTable.ID". Then on the OnEndEdit event, I set the combobox datasource back to an unfiltered view of the items.

Registration of SQL with WEB Server

i need to know how can i register my SQL server with WEb based SQL server

i have the address MSSQL2.MINUTESUAE.COM now how can i register my SQL Server with them.

when i m going to conect with this server it register but after that Database not show it take to much time.

Hi,

If you are trying to connect a shared database supplied by a hosting firm, to see your database in your Enterprise Manager will take really so long time.

I can advise you to connect your database by using Query Analyzer not Enterprise Manager.

You will connect your database directly if you use Query Analyzer.

Eralper

http://www.kodyaz.com