Optimiser and UDF's

I came across something today that hopefully if my explanation is
wrong someone could correct me.
I was trying to speed up a stored proc that took an integer as a
parameter and then ran around a dozen SQL statements to populate a
table variable.
1st query was :-
select <some columns> from A where A.id = @.id
The others were along those lines but joined to several lookup tables
and other combinations. Basically though it was hitting table A with
the same search conditions each time (id = @.id).
Thinking this was a waste I wrote a UDF that took the id as a
parameter and returned a table with just the relevant records from
table A in it. The SQL in the stored proc was then changed as follows
:-
select <some columns> from myUDF(@.id)
The new stored proc now runs twice as fast, uses half the cpu time and
a third of the disk io.
Would I be right in saying that SQL2k has realised that the UDF had
been called a dozen times with the same parameters, ran it once and
cached the results?
Its the only reason I can see for such a big performance improvement.
cheers.No, UDF results are not cached in SQL 2k, but that might be a nice feature
for Yukon... Sybase, for instance, does cache udf results...If your efforts
lowered the number of joins that were being done, I suspect the improvement
lies there.
"Mike Watson" <mike@.prog99.com> wrote in message
news:q1h7lvoluabdh7ulaigatf1ndn05gaofo8@.4ax.com...
> I came across something today that hopefully if my explanation is
> wrong someone could correct me.
> I was trying to speed up a stored proc that took an integer as a
> parameter and then ran around a dozen SQL statements to populate a
> table variable.
> 1st query was :-
> select <some columns> from A where A.id = @.id
> The others were along those lines but joined to several lookup tables
> and other combinations. Basically though it was hitting table A with
> the same search conditions each time (id = @.id).
> Thinking this was a waste I wrote a UDF that took the id as a
> parameter and returned a table with just the relevant records from
> table A in it. The SQL in the stored proc was then changed as follows
> :-
> select <some columns> from myUDF(@.id)
> The new stored proc now runs twice as fast, uses half the cpu time and
> a third of the disk io.
> Would I be right in saying that SQL2k has realised that the UDF had
> been called a dozen times with the same parameters, ran it once and
> cached the results?
> Its the only reason I can see for such a big performance improvement.
> cheers.
>|||On Tue, 2 Sep 2003 07:45:12 -0400, "Wayne Snyder"
<wsnyder@.computeredservices.com> wrote:
>No, UDF results are not cached in SQL 2k, but that might be a nice feature
>for Yukon... Sybase, for instance, does cache udf results...If your efforts
>lowered the number of joins that were being done, I suspect the improvement
>lies there.
>
Thanks for the reply, I'm surprised that the overhead of calling the
UDF around a dozen times is so much cheaper than doing a striaght join
on the table.

Operational and Analytical CRM??

Hi,
While going through an article I came across the subject terms and
wanted to ask the experts if these are "basically" the same thing as
"BI" or are these totally different?
I'll appreciate any pointers/help from anyone.
Thanks.
Dont' think they are the same. Here's my "guess"...
* CRM = Customer Relationsship Management - something about customers...but
in BI you can have lots of areas such as finance, sales, inventory and so
forth...
* however - i would guess that data mining could be relevant a lot in CRM on
websites and for that i would think an AS engine could be used
* In CRM there a many more processes related to customers - that could be
the operatonal CRM above and perhaps the analytical could be solved by
BI...?
"Learner" <wantnospam@.email.com> skrev i en meddelelse
news:MPG.1b1714e36d0ad7aa989703@.msnews.microsoft.c om...
> Hi,
> While going through an article I came across the subject terms and
> wanted to ask the experts if these are "basically" the same thing as
> "BI" or are these totally different?
> I'll appreciate any pointers/help from anyone.
> --
> Thanks.

Operational and Analytical CRM??

Hi,
While going through an article I came across the subject terms and
wanted to ask the experts if these are "basically" the same thing as
"BI" or are these totally different?
I'll appreciate any pointers/help from anyone.
Thanks.Dont' think they are the same. Here's my "guess"...
* CRM = Customer Relationsship Management - something about customers...but
in BI you can have lots of areas such as finance, sales, inventory and so
forth...
* however - i would guess that data mining could be relevant a lot in CRM on
websites and for that i would think an AS engine could be used
* In CRM there a many more processes related to customers - that could be
the operatonal CRM above and perhaps the analytical could be solved by
BI...?
"Learner" <wantnospam@.email.com> skrev i en meddelelse
news:MPG.1b1714e36d0ad7aa989703@.msnews.microsoft.com...
> Hi,
> While going through an article I came across the subject terms and
> wanted to ask the experts if these are "basically" the same thing as
> "BI" or are these totally different?
> I'll appreciate any pointers/help from anyone.
> --
> Thanks.