Custom Row Functions: Access vs. SQL Server

[TC]

I have an Access database application with a lot of custom row
functions written in VBA. In other words, a lot of queries contain
calculated fields which use functions defined in the modules.

I am now thinking about upgrading the database from Access to SQL
Server. If I do, how can I implement the custom row functions? Is
Visual Basic integrated with SQL Server just as it is with Access? Or
does T-SQL in SQL Server offer the functionality VBA gives me in
Access? Or perhaps there is no way to do custom row functions in the
back-end, and they have to be implemented in the front-end?

Can anyone explain to me how it works?
-TC

[Steve Jorgensen]

On 16 Nov 2005 08:05:23 -0800, "TC" <go*********@yahoo.com> wrote:

I have an Access database application with a lot of custom row
functions written in VBA. In other words, a lot of queries contain
calculated fields which use functions defined in the modules.

I am now thinking about upgrading the database from Access to SQL
Server. If I do, how can I implement the custom row functions? Is
Visual Basic integrated with SQL Server just as it is with Access? Or
does T-SQL in SQL Server offer the functionality VBA gives me in
Access? Or perhaps there is no way to do custom row functions in the
back-end, and they have to be implemented in the front-end?

Can anyone explain to me how it works?
-TC

Custom functions can be used in SQL Server 2000 and above queries, but they
must be written in T-SQL and contained on the server. Furthermore, the
performance of these functions is absolutely dismal. You can slow a query
down by a factor of 10,000 by putting a few simple function calls in it (note
that tabluar functions don't have this performance problem).

Fortunately, this just isn't a problem with most Access/SQL Server
applications because it turns out the functions are usually used at a point in
the query where Access/JET can apply it to the results coming back from the
server. If I do a query of a linked table, and I format a field as a string
using Format(), Access first queries the fields it needs from the back-end,
then applies the Format function locally as the rows are received.

The trouble comes in when you use Access functions somewhere that they prevent
Access/JET from being able to delegate processing to the back-end, such as
when the function is used in a WHERE clause or inside and aggregate function
such as SUM(Round([Foo])). You just need to be aware of these cases, and
avoid them.

[lylefair]

IMO T-SQL in SQL Server offers the functionality VBA gives in Access.
But, it might be helpful if we are sure we are talking about the same
thing. If you could post one of the queries that calls perhaps two of
the VBA functions, and the code of the functions, then we might see how
this could be done as T-SQL only.

(I have been wondering whether VBA is redundant when one uses MS-SQL
[that is a application without modules might be efficient] but that is
another story.)

[TC]

Steve,

Thank you for the reply.

In fact, I expressed my question badly -- I'm really interested in
moving the functions to the back-end, so your comments on T-SQL are
most relevant to me. Is T-SQL really that slow? Suppose I choose Oracle
instead; does PL-SQL have the required capabilities, and is it faster
than T-SQL?

Also, what is a "tabular function"?
-TC

[TC]

Lyle,

Thank you for the answer. After reflection, I realize I asked the wrong
question. What I really want to know is whether SQL Server allows me to
implement, in the back-end, row functions as powerful as those Access
allows me to implement, in the front-end, using VBA. In other words,
the issue you mentioned in your footnote is really the question I meant
to raise.

I can't provide any real-code examples, but here is an illustrative
sample:

Query:
SELECT ContactID, FirstName, LastName, FullName(FirstName, LastName) AS
FullName
FROM Contact;

Module:
Function FullName(FirstName, LastName)
If IsNull(FirstName) Then
If IsNull(LastName) Then
FullName = Null
Else
FullName = LastName
End If
Else
If IsNull(LastName) Then
FullName = FirstName
Else
FullName = FirstName & " " & LastName
End If
End If
End Function

This is really a trivial example. A realistic example would use much
more complicated program logic. In order to implement all the functions
in my application, I need a procedural programming language with
variables, arrays, looping, branching, error-handling, etc. Does T-SQL
qualify?

I should mention that one thing I love about Access is the way it lets
me organize the program logic. I create one module for every entity in
my ERD diagram. In that module, I have a section called "Row
Functions". I can be confident that the logic required for every row
function will be implemented in a modular format in that section (as
opposed to somewhere among the SQL of my queries). This is an
under-appreciated benefit to MS Access which I hope has some equivalent
in SQL Server.

By the way, what do you say to Steve's claim that T-SQL is a
performance killer?

Also, which term is current, "T-SQL" or "MS-SQL"?
-TC

[lylefair]

TC wrote:

Lyle,

Thank you for the answer. After reflection, I realize I asked the wrong
question. What I really want to know is whether SQL Server allows me to
implement, in the back-end, row functions as powerful as those Access
allows me to implement, in the front-end, using VBA. In other words,
the issue you mentioned in your footnote is really the question I meant
to raise.

I can't provide any real-code examples, but here is an illustrative
sample:

Query:
SELECT ContactID, FirstName, LastName, FullName(FirstName, LastName) AS
FullName
FROM Contact;
CREATE PROCEDURE QueryTemp
AS
SELECT DISTINCT(dbo.FullName(Supplier,Article)) FROM Fabrics
RETURN

(I had to munge a fabrics table to test this)
Module:
Function FullName(FirstName, LastName)
If IsNull(FirstName) Then
If IsNull(LastName) Then
FullName = Null
Else
FullName = LastName
End If
Else
If IsNull(LastName) Then
FullName = FirstName
Else
FullName = FirstName & " " & LastName
End If
End If
End Function
CREATE FUNCTION dbo.FullName
(
@FirstName varchar(255),
@LastName varchar(255)
)
RETURNS varchar(255)
AS
BEGIN
DECLARE @FullName varchar(255)
SET @FullName = LTRIM(RTRIM(ISNULL(@FirstName,'') + ' ' +
ISNULL(@LastName,'')))
IF LEN(@FullName) = 0
SET @FullName = NULL
RETURN @FullName
END

(I don't know if all the NULL testing is required; it depends I suppose
on your requirements)
This is really a trivial example. A realistic example would use much
more complicated program logic. In order to implement all the functions
in my application, I need a procedural programming language with
variables, arrays, looping, branching, error-handling, etc. Does T-SQL
qualify?
I think so; it does not have array but it does have cursors ("sort of"
recordsets)

I should mention that one thing I love about Access is the way it lets
me organize the program logic. I create one module for every entity in
my ERD diagram. In that module, I have a section called "Row
Functions". I can be confident that the logic required for every row
function will be implemented in a modular format in that section (as
opposed to somewhere among the SQL of my queries). This is an
under-appreciated benefit to MS Access which I hope has some equivalent
in SQL Server.
Yes, I can understand that.
By the way, what do you say to Steve's claim that T-SQL is a
performance killer?
I'm unaware that it's a performance killer and have not found it to be
so, but typically my applications are calculation intensive rather than
data extensive. Perhaps, Steve will expand on that.
Also, which term is current, "T-SQL" or "MS-SQL"?

T-SQL -> Transact SQL ... the "programming" language or script of
MS-SQL server.

******
As an example this is a Procedure on which I am currently working; I
have done this previously in VBA, VB (compiled to COM) and
ASP/Javascript. (Lest anyone want to point out that its incomplete,
please note the "on which I am currently working").
ALTER PROCEDURE dbo.CreateTimeTable
@SchoolID int = 0
AS
/* delete current time table entrie dor school */
DELETE Timetable WHERE Teacher IN
(SELECT TeacherID FROM Teachers WHERE SchoolID = @SchoolId)

/* Declare Variables */
Declare @DaysPerCycle int, @PeriodsPerDay int, @PeriodsPerCycle int
Declare @Class int, @Subject int, @Teacher int,
@ClassSubjectTeacherPeriods int
Declare @SumClassTeacherPeriods tinyint,
@SumClassSubjectTeacherPeriods tinyint
Declare @ClassTeacherOrdinal tinyint, @ClassSubjectTeacherOrdinal
tinyint
Declare @Required varchar(255), @Forbidden varchar(255),
@MaximumConcurrent tinyint

/* get Parameters of Cycle */
SELECT @DaysPerCycle=DaysPerCycle, @PeriodsPerDay=PeriodsPerDay
FROM Schools WHERE SchoolID = @SchoolID
SET @PeriodsPerCycle = @DaysPerCycle * @PeriodsPerDay

/* get Assignments Cursor */
DECLARE AssignmentsCursor CURSOR
FOR
SELECT a.ClassID, a.SubjectID, a.TeacherID, a.Periods,
a2.SumClassSubjectTeacherPeriods, a4.SumClassTeacherPeriods,
a.Manual, t.UnavailablePeriods, s.MaximumConcurrentPeriods
FROM Assignments a
JOIN
Teachers t
ON a.TeacherID=t.TeacherID
JOIN
Subjects s
ON a.SubjectID=s.SubjectID
LEFT JOIN
(SELECT a1.ClassID, a1.SubjectID, a1.TeacherID, Sum(a1.Periods) AS
SumClassSubjectTeacherPeriods FROM Assignments a1
JOIN
Teachers t
ON a1.TeacherID=t.TeacherID
WHERE t.SchoolID = @SchoolID
GROUP BY a1.ClassID, a1.SubjectID, a1.TeacherID) a2
ON a.ClassID = a2.ClassID AND a.SubjectID = a2.SubjectID AND
a.TeacherID = a2.TeacherID
LEFT JOIN
(SELECT a3.ClassID, a3.TeacherID, Sum(a3.Periods) AS
SumClassTeacherPeriods FROM Assignments a3
JOIN
Teachers t
ON a3.TeacherID=t.TeacherID
WHERE t.SchoolID = @SchoolID
GROUP BY a3.ClassID, a3.TeacherID) a4
ON a.ClassID = a4.ClassID AND a.TeacherID = a4.TeacherID
WHERE t.SchoolID = @SchoolID
ORDER BY a4.SumClassTeacherPeriods desc, a.ClassID, a.TeacherID,
a.SubjectID

/* open AssignmentsCursor */
OPEN AssignmentsCursor

/* Get first record from Assignments Cursor */
FETCH NEXT FROM AssignmentsCursor
INTO @Class, @Subject, @Teacher, @ClassSubjectTeacherPeriods,
@SumClassSubjectTeacherPeriods, @SumClassTeacherPeriods,
@Required, @Forbidden, @MaximumConcurrent

/* Loop through Assignments Cursor */
WHILE @@FETCH_STATUS != -1

BEGIN

/* Insert number of periods required */
/* by looping while decremented @ClassSubjectTeacherPeriods > 0 */
WHILE @ClassSubjectTeacherPeriods > 0

BEGIN

IF ISNULL(@ClassTeacherOrdinal,0) = 0
SET @ClassTeacherOrdinal = @SumClassTeacherPeriods

/* do the insert */
INSERT INTO Timetable
(Class, Subject, Teacher,
SumClassSubjectTeacherPeriods, SumClassTeacherPeriods,
ClassSubjectTeacherOrdinal, ClassTeacherOrdinal,
Required, Forbidden, MaximumConcurrent)
VALUES
(@Class, @Subject, @Teacher,
@SumClassSubjectTeacherPeriods, @SumClassTeacherPeriods,
@ClassSubjectTeacherPeriods, @ClassTeacherOrdinal,
@Required, @Forbidden, @MaximumConcurrent)

/* decrement @ClassSubjectTeacherPeriods */
SET @ClassSubjectTeacherPeriods = @ClassSubjectTeacherPeriods - 1
/* decrement @ClassTeacherOrdinal */
SET @ClassTeacherOrdinal = @ClassTeacherOrdinal - 1

END

FETCH NEXT FROM AssignmentsCursor
INTO @Class, @Subject, @Teacher, @ClassSubjectTeacherPeriods,
@SumClassSubjectTeacherPeriods, @SumClassTeacherPeriods,
@Required, @Forbidden, @MaximumConcurrent

END

CLOSE AssignmentsCursor
DEALLOCATE AssignmentsCursor

RETURN

[Bernard Peek]

In message <11*********************@f14g2000cwb.googlegroups. com>, TC
<go*********@yahoo.com> writes

This is really a trivial example. A realistic example would use much
more complicated program logic. In order to implement all the functions
in my application, I need a procedural programming language with
variables, arrays, looping, branching, error-handling, etc. Does T-SQL
qualify?

Possibly not. But if you use SQL Server 2005 you should be able to code
the functions you require in any of the .NET languages and call them
from T-SQL.

--
Bernard Peek
London, UK. DBA, Manager, Trainer & Author.

[lylefair]

Bernard Peek wrote:

But if you use SQL Server 2005 you should be able to code
the functions you require in any of the .NET languages and call them
from T-SQL.

Perhaps, you could expand on this with references to the appropriate
Micorsoft articles?

[lylefair]

Bernard Peek wrote:

But if you use SQL Server 2005 you should be able to code
the functions you require in any of the .NET languages and call them
from T-SQL.

Perhaps, you could expand on this with references to the appropriate
Microsoft articles?

[rkc]

lylefair wrote:

Bernard Peek wrote:

But if you use SQL Server 2005 you should be able to code
the functions you require in any of the .NET languages and call them
from T-SQL.

Perhaps, you could expand on this with references to the appropriate
Micorsoft articles?

Here's a start.

http://www.microsoft.com/sql/prodinf...erver2005.mspx

[lylefair]

rkc wrote:

Here's a start.

http://www.microsoft.com/sql/prodinf...erver2005.mspx

Cool! So this means the end of Access, JET, DAO, VBA (and civlization)
as we know them?

[rkc]

lylefair wrote:

rkc wrote:

Here's a start.

http://www.microsoft.com/sql/prodinf...erver2005.mspx

Cool! So this means the end of Access, JET, DAO, VBA (and civlization)
as we know them?

I'm pretty sure civlization is fucked regardless.

[Steve Jorgensen]

On 16 Nov 2005 10:31:56 -0800, "TC" <go*********@yahoo.com> wrote:

Steve,

Thank you for the reply.

In fact, I expressed my question badly -- I'm really interested in
moving the functions to the back-end, so your comments on T-SQL are
most relevant to me. Is T-SQL really that slow? Suppose I choose Oracle
T-SQL is not slow in general, however scalar functions really are that slow.
It basically means you don't use scalar functions much, but other than that,
MS SQL Server preforms quite well.
instead; does PL-SQL have the required capabilities, and is it faster
than T-SQL?
PL-SQL does have the capabilities, and its scalar function performance is
great, but Access works slightly better with the MS SQL Server back-end, and
you have to make sure your ODBC settings are just right, for PostgreSQL to
work correctly with Access on the front-end.
Also, what is a "tabular function"?

That is a function that returns rows - essentially like an Access select query
that can take parameters.

[David W. Fenton]

Steve Jorgensen <no****@nospam.nospam> wrote in
news:oa********************************@4ax.com:

On 16 Nov 2005 08:05:23 -0800, "TC" <go*********@yahoo.com> wrote:

I have an Access database application with a lot of custom row
functions written in VBA. In other words, a lot of queries containcalculated fields which use functions defined in the modules.

I am now thinking about upgrading the database from Access to SQL
Server. If I do, how can I implement the custom row functions? Is
Visual Basic integrated with SQL Server just as it is with Access?Or does T-SQL in SQL Server offer the functionality VBA gives me
in Access? Or perhaps there is no way to do custom row functions
in the back-end, and they have to be implemented in the front- end?
Can anyone explain to me how it works?
Custom functions can be used in SQL Server 2000 and above

queries, but they must be written in T-SQL and contained on the server.
Er, doesn't that depend entirely on what the VBA functions/UDFs do
and how they are used? If, for instance, you are sorting or
filtering on a UDF, then you should port it to some T-SQL
equivalent, because otherwise, you're going to have a mess.

But if you're just calculating, where's the harm in leaving them in
VBA? Assuming that you are filitering the recordset based on
selections on some of the non-calculated columns, it should be
perfectly fast, as Jet will hand off the filtering part to the back
end, and then apply the functions only to the matching records that
are returned.
Furthermore, the performance of these functions is absolutely
dismal. You can slow a query down by a factor of 10,000 by
putting a few simple function calls in it (note that tabluar
functions don't have this performance problem).
It entirely depends on what the functions do, and how they are
used,
no?
Fortunately, this just isn't a problem with most Access/SQL Server applications because it turns out the functions are usually used
at a point in the query where Access/JET can apply it to the
results coming back from the server. If I do a query of a linked
table, and I format a field as a string using Format(), Access
first queries the fields it needs from the back-end, then applies
the Format function locally as the rows are received.
Oops. I should read the whole post before replying, I guess.
The trouble comes in when you use Access functions somewhere that
they prevent Access/JET from being able to delegate processing to
the back-end, such as when the function is used in a WHERE clause
or inside and aggregate function such as SUM(Round([Foo])). You
just need to be aware of these cases, and avoid them.

That may still be manageable if the number of records returned is
limited by some other criteria. It entirely depends on how you're
using the VBA function.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[Tom van Stiphout]

On 16 Nov 2005 17:33:34 -0800, "lylefair" <ly***********@aim.com>
wrote:

It's cool that you can use any .Net language in your sprocs.
Performance-wise, I don't know where that leaves us. And even if I did
know, I would not be able to share that information (see Eula).
Personally I think we should not discard T-Sql just yet. If it can be
done set-based, procedural is always going to be slower.

-Tom.

rkc wrote:

Here's a start.

http://www.microsoft.com/sql/prodinf...erver2005.mspx

Cool! So this means the end of Access, JET, DAO, VBA (and civlization)
as we know them?

[Larry Linson]

"lylefair" <ly***********@aim.com> wrote

Here's a start.

http://www.microsoft.com/sql/prodinf...erver2005.mspx
Cool! So this means the end of Access, JET, DAO, VBA (and civlization)
as we know them?

So Chicken Little _was_ correct?

[lylefair]

TC wrote:

By the way, what do you say to Steve's claim that T-SQL is a
performance killer?

I was interested in this statement, never having noted any particular
slowness, but then again, never really having considered the issue, so,
having woken up I decided to do a little test as below:

Option Explicit

Private Declare Function GetTickCount Lib "kernel32" () As Long

Sub TestUDFFullNameforSpeed()
Dim c As ADODB.Connection
Dim r As ADODB.Recordset
Dim t As Single
Dim z As Long

Set c = New ADODB.Connection
c.Open CurrentProject.AccessConnection.ConnectionString &
";Password=Password"

t = GetTickCount
For z = 1 To 100
' SELECT DISTINCT(dbo.FullName(Supplier,Article)) FROM Fabrics
Set r = c.Execute("QueryTemp")
Next z
Debug.Print r.RecordCount, GetTickCount - t
' with the UDF
' returns 16 distinct records 100 times in 9.654 seconds

t = GetTickCount
For z = 1 To 100
' SELECT DISTINCT Supplier,Article FROM Fabrics
Set r = c.Execute("QueryTemp2")
Next z
Debug.Print r.RecordCount, GetTickCount - t
' without a UDF
' returns 16 distinct records 100 times in 9.604 seconds

' function call seems to require 0.00003125 seconds per record
' how does that compare with using a VBA function
' who knows?

' note second call of function resulted in FullName function being
FASTER
' 16 9485
' 16 9917

' server is in California
' workstation is in Ontario, Canada about 4000 km distant

' connection is Cable Modem - Internet

' Access Version is 2003
' ADO is 2.5

End Sub

As you can see I did not find that the UDF was slow; in fact it may
have had no appreciable slowdown at all [may even no slowdown at all].
When compared with other "time" issues like the speed of data over the
wire, what other tasks the server might be performing when you ask it
to call the function etc, I think I am not going to worry about it,
barring Steve coming up with something I have overlooked.

I would add that I have found CurrentProject.Connection to be amazingly
slow in some situations and I try never to use it except in trivial
situations. (That's why I have created a separate connection here In a
project I generally have a public function that returns an independent
connection; Access typically opens more connections than one might
think it needs; empowers them way beyond what your code might need, and
could be doing who knows what with your connection while you are trying
to do something else.

[Steve Jorgensen]

On Wed, 16 Nov 2005 21:50:57 -0600, "David W. Fenton"
<dX********@bway.net.invalid> wrote:

....

The trouble comes in when you use Access functions somewhere that
they prevent Access/JET from being able to delegate processing to
the back-end, such as when the function is used in a WHERE clause
or inside and aggregate function such as SUM(Round([Foo])). You
just need to be aware of these cases, and avoid them.

That may still be manageable if the number of records returned is
limited by some other criteria. It entirely depends on how you're
using the VBA function.

Absolutely true - so long as future conditions/requirements are given
appropriate consideration, of course.

[Bernard Peek]

In message <11*********************@z14g2000cwz.googlegroups. com>,
lylefair <ly***********@aim.com> writes

rkc wrote:

Here's a start.
http://www.microsoft.com/sql/prodinf...in-sqlserver20
05.mspx

Cool! So this means the end of Access, JET, DAO, VBA (and civlization)
as we know them?

Nobody knows. I'm pretty sure that nobody at Microsoft is certain. As I
understand it the next version of Office includes a new version of
Access that uses VBA. But Microsoft wants to get rid of VBA from all of
their products and replace it with the CLR system. That means being able
to use any .NET language.

The problem I see is that all of the .NET languages are too complicated
for at least part of the market for Access. So either Microsoft will
have to find a way of making writing .NET applications much simpler, or
it will have to retain VBA and support two different language families.
That is a possibility because there is a lot of pressure from VB6
programmers to continue support for VB as opposed to VB.NET. So we could
see VB.Classic and VBA surviving. Or not.

It's a problem that Microsoft has to resolve. The easy route from Access
to SQL Server is too valuable to break. They have to produce a version
of Access that non-programmers can use. Failure is not an option.

--
Bernard Peek
London, UK. DBA, Manager, Trainer & Author.

[Bri]

lylefair wrote:

TC wrote:

By the way, what do you say to Steve's claim that T-SQL is a
performance killer?
As you can see I did not find that the UDF was slow; in fact it may
have had no appreciable slowdown at all [may even no slowdown at
all]. When compared with other "time" issues like the speed of data
over the wire, what other tasks the server might be performing when
you ask it to call the function etc, I think I am not going to worry
about it, barring Steve coming up with something I have overlooked.

Since Steve hasn't yet replied to this, I thought I might insert a quote
of his original assurtion to see if this helps calrify his point:

Steve Jorgensen wrote: On 16 Nov 2005 10:31:56 -0800, "TC" <go*********@yahoo.com> wrote:

Steve,

Thank you for the reply.

In fact, I expressed my question badly -- I'm really interested in
moving the functions to the back-end, so your comments on T-SQL are
most relevant to me. Is T-SQL really that slow? Suppose I choose
Oracle

T-SQL is not slow in general, however scalar functions really are
that slow. It basically means you don't use scalar functions much,
but other than that, MS SQL Server preforms quite well.

instead; does PL-SQL have the required capabilities, and is it
faster than T-SQL?

PL-SQL does have the capabilities, and its scalar function
performance is great, but Access works slightly better with the MS
SQL Server back-end, and
you have to make sure your ODBC settings are just right, for
PostgreSQL to work correctly with Access on the front-end.

--
Bri

[Steve Jorgensen]

On 16 Nov 2005 21:30:40 -0800, "lylefair" <ly***********@aim.com> wrote:

TC wrote:

By the way, what do you say to Steve's claim that T-SQL is a
performance killer?

I was interested in this statement, never having noted any particular
slowness, but then again, never really having considered the issue, so,
having woken up I decided to do a little test as below:

Option Explicit

Private Declare Function GetTickCount Lib "kernel32" () As Long

Sub TestUDFFullNameforSpeed()
Dim c As ADODB.Connection
Dim r As ADODB.Recordset
Dim t As Single
Dim z As Long

Set c = New ADODB.Connection
c.Open CurrentProject.AccessConnection.ConnectionString &
";Password=Password"

t = GetTickCount
For z = 1 To 100
' SELECT DISTINCT(dbo.FullName(Supplier,Article)) FROM Fabrics
Set r = c.Execute("QueryTemp")
Next z
Debug.Print r.RecordCount, GetTickCount - t
' with the UDF
' returns 16 distinct records 100 times in 9.654 seconds

t = GetTickCount
For z = 1 To 100
' SELECT DISTINCT Supplier,Article FROM Fabrics
Set r = c.Execute("QueryTemp2")
Next z
Debug.Print r.RecordCount, GetTickCount - t
' without a UDF
' returns 16 distinct records 100 times in 9.604 seconds

' function call seems to require 0.00003125 seconds per record
' how does that compare with using a VBA function
' who knows?

' note second call of function resulted in FullName function being
FASTER
' 16 9485
' 16 9917

' server is in California
' workstation is in Ontario, Canada about 4000 km distant

' connection is Cable Modem - Internet

' Access Version is 2003
' ADO is 2.5

End Sub

As you can see I did not find that the UDF was slow; in fact it may
have had no appreciable slowdown at all [may even no slowdown at all].
When compared with other "time" issues like the speed of data over the
wire, what other tasks the server might be performing when you ask it
to call the function etc, I think I am not going to worry about it,
barring Steve coming up with something I have overlooked.

I would add that I have found CurrentProject.Connection to be amazingly
slow in some situations and I try never to use it except in trivial
situations. (That's why I have created a separate connection here In a
project I generally have a public function that returns an independent
connection; Access typically opens more connections than one might
think it needs; empowers them way beyond what your code might need, and
could be doing who knows what with your connection while you are trying
to do something else.

As Bri pointed out, I was not making a negative statement about T-SQL
performance in general, just scalar functions. If I understand your post
correctly, you are testing the performance of a tabular function, and I have
not found those to have a performance problem.

To test what I'm talking about, try writing, say, a string formatting function
in T-SQL, and call that from within a SELECT statement.

[lylefair]

Steve Jorgensen wrote:

As Bri pointed out, I was not making a negative statement about T-SQL
performance in general, just scalar functions. If I understand your post
correctly, you are testing the performance of a tabular function, and I have
not found those to have a performance problem.

To test what I'm talking about, try writing, say, a string formatting function
in T-SQL, and call that from within a SELECT statement.

I think it would be more efficient were you to post a function that is
slow.

[Steve Jorgensen]

On 20 Nov 2005 14:54:32 -0800, "lylefair" <ly***********@aim.com> wrote:

Steve Jorgensen wrote:

As Bri pointed out, I was not making a negative statement about T-SQL
performance in general, just scalar functions. If I understand your post
correctly, you are testing the performance of a tabular function, and I have
not found those to have a performance problem.

To test what I'm talking about, try writing, say, a string formatting function
in T-SQL, and call that from within a SELECT statement.

I think it would be more efficient were you to post a function that is
slow.

It would if I had easy access to that code right now, but I'll see if I can
make time to assemble and post a test to demonstrate the issue.