fastest way to call a function

[windandwaves]

Hi Gurus

When I have a query in which I use a small function, e.g.:

SELECT A03_FILES.ID, A03_FILES.D, hasvt([ID]) AS hsvVT
FROM A03_FILES;

where HasVT is defined below:

--------------------
Public Function hasVT(ID As Long) As Boolean

Dim rst As Recordset

Set rst = CurrentDb.OpenRecordset("SELECT A03_FILES.ID FROM A03_FILES INNER JOIN A65_vts ON A03_FILES.D = A65_vts.D WHERE
A03_FILES.ID= " & ID & ";")

hasVT = rst.RecordCount

Set rst = Nothing

End Function
--------------------

Then it seems to really slow down the query. Is there a faster way to do this without changing the concept of the query (i.e. I
know that I can make it all into SQL which would definitely make it faster).

It seems to me that the reason it slows down is that for each row in the query, it has to open and close the function (running it is
actually very fast of course).

Thank you
- Nicolaas

[Stefan Kowalski]

"windandwaves" <wi*********@coldmail.com> wrote in message
news:Ir*******************@news.xtra.co.nz...

Hi Gurus

When I have a query in which I use a small function, e.g.:

SELECT A03_FILES.ID, A03_FILES.D, hasvt([ID]) AS hsvVT
FROM A03_FILES;

where HasVT is defined below:

--------------------
Public Function hasVT(ID As Long) As Boolean

Dim rst As Recordset

Set rst = CurrentDb.OpenRecordset("SELECT A03_FILES.ID FROM A03_FILES
INNER JOIN A65_vts ON A03_FILES.D = A65_vts.D WHERE A03_FILES.ID= " & ID &
";")

hasVT = rst.RecordCount

Set rst = Nothing

End Function
--------------------

Then it seems to really slow down the query. Is there a faster way to do
this without changing the concept of the query (i.e. I know that I can
make it all into SQL which would definitely make it faster).

It seems to me that the reason it slows down is that for each row in the
query, it has to open and close the function (running it is actually very
fast of course).

Thank you
- Nicolaas

You seem understand very well what is causing the problem and how to fix it.
In this particular example, you would be mad to call an external function,
when you could get the data you need by using standard SQL like Count(*). I
suppose you must have some reason for asking such a question, so perhaps if
we cannot change the 'concept of the query' perhaps you should look at the
sort of recordset you are opening - if you simply use:
Set rst=dbs.OpenRecordset(strSQL) you will be opening a recordset which you
can edit.
Set rst=dbs.OpenRecordset(strSQL,dbOpenForwardOnly) opens a more efficient,
read-only recordset.
Also, change the SQL to
SELECT COUNT(*) FROM A03_FILES INNER JOIN A65_vts ON A03_FILES.D = A65_vts.D
WHERE...
But best of all, do it all using standard SQL.

[deko]

Then it seems to really slow down the query. Is there a faster way to do this without changing the concept of the query (i.e. I know that I can make it all into SQL which would definitely make it

faster).

Why keep the function? Why not just use SQL? Sounds like you already know
what needs to be done... :)

[Trevor Best]

It's not the function call that slows it down, it's what you do in the
function.

I won't repeat what Stefan and Deko have stated as I think that's the
best method but out of interest, if you used my domain function
replacements
(http://easyweb.easynet.co.uk/~trevor.../baslookup.zip) you could
achieve the same result, e.g.

Select a,b,c,tCount("*","A03_FILES INNER JOIN A65_vts ON A03_FILES.D =
A65_vts.D","A03_FILES.ID= " & ID)

Not sure that the standard DCount() would support that but as mine just
creates a SQL statement from the bits you send it...

--
This sig left intentionally blank

[windandwaves]

"Stefan Kowalski" <[e-mail]> wrote in message news:cv**********@titan.btinternet.com...

"windandwaves" <wi*********@coldmail.com> wrote in message news:Ir*******************@news.xtra.co.nz...

Hi Gurus

When I have a query in which I use a small function, e.g.:

SELECT A03_FILES.ID, A03_FILES.D, hasvt([ID]) AS hsvVT
FROM A03_FILES;

where HasVT is defined below:

--------------------
Public Function hasVT(ID As Long) As Boolean

Dim rst As Recordset

Set rst = CurrentDb.OpenRecordset("SELECT A03_FILES.ID FROM A03_FILES INNER JOIN A65_vts ON A03_FILES.D = A65_vts.D WHERE
A03_FILES.ID= " & ID & ";")

hasVT = rst.RecordCount

Set rst = Nothing

End Function
--------------------

Then it seems to really slow down the query. Is there a faster way to do this without changing the concept of the query (i.e. I
know that I can make it all into SQL which would definitely make it faster).

It seems to me that the reason it slows down is that for each row in the query, it has to open and close the function (running it
is actually very fast of course).

Thank you
- Nicolaas

You seem understand very well what is causing the problem and how to fix it. In this particular example, you would be mad to call
an external function, when you could get the data you need by using standard SQL like Count(*). I suppose you must have some
reason for asking such a question, so perhaps if we cannot change the 'concept of the query' perhaps you should look at the sort
of recordset you are opening - if you simply use:
Set rst=dbs.OpenRecordset(strSQL) you will be opening a recordset which you can edit.
Set rst=dbs.OpenRecordset(strSQL,dbOpenForwardOnly) opens a more efficient, read-only recordset.
Also, change the SQL to
SELECT COUNT(*) FROM A03_FILES INNER JOIN A65_vts ON A03_FILES.D = A65_vts.D WHERE...
But best of all, do it all using standard SQL.

Thank you very much for your reply, basically you are saying: fine tune your function... and I should.

[windandwaves]

"deko" <de**@hotmail.com> wrote in message news:BQ***************@newssvr13.news.prodigy.com. ..

Then it seems to really slow down the query. Is there a faster way to do

this without changing the concept of the query (i.e. I

know that I can make it all into SQL which would definitely make it

faster).

Why keep the function? Why not just use SQL? Sounds like you already know
what needs to be done... :)

I do, but i was just using this as an example. In order to keep my project simple and manage the development process, it is much
more efficient to use a function then to customise every query.

For example, if this is a recurring function then you may call it in fifty queries, then, later if you wanted to change the
function, you either had to change fifty queries or just one function.

That is why I prefer to use a function.

Thank you for your reply

Nicolaas

[windandwaves]

"Trevor Best" <no****@besty.org.uk> wrote in message news:42***********************@news.zen.co.uk...

It's not the function call that slows it down, it's what you do in the function.

I won't repeat what Stefan and Deko have stated as I think that's the best method but out of interest, if you used my domain
function replacements (http://easyweb.easynet.co.uk/~trevor.../baslookup.zip) you could achieve the same result, e.g.

Select a,b,c,tCount("*","A03_FILES INNER JOIN A65_vts ON A03_FILES.D = A65_vts.D","A03_FILES.ID= " & ID)

Not sure that the standard DCount() would support that but as mine just creates a SQL statement from the bits you send it...

--
This sig left intentionally blank

Thank you Trevor

I have already used this function. it is brilliant. It is also good to know that it is not the function call that slows it down.

I just gave the code as an example, as described in my other replies. It is more about the concept then the example. it seems that
no matter how fast the function is, it always seems slower than SQL.

Thank you once more for your reply.

Nicolaas

[Darryl Kerkeslager]

On Thu, 17 Feb 2005 14:46:58 +1300, windandwaves
<wi*********@coldmail.com> wrote:

"Trevor Best" <no****@besty.org.uk> wrote in message
news:42***********************@news.zen.co.uk...

It's not the function call that slows it down, it's what you do in the
function.

I have already used this function. it is brilliant. It is also good to
know that it is not the function call that slows it down.

I just gave the code as an example, as described in my other replies.
It is more about the concept then the example. it seems that
no matter how fast the function is, it always seems slower than SQL.

What is slowing down your query is the opening and closing of a recordset,
I presume many, many times. If you can eliminate that one line of code,
calling the function will run no slower than coding it without the
function.

IME, it is *much* more efficient to open one recordset, and rs.Find
through it, than to pass a table_id to a function that opens the specific
record in a recordset, does stuff, then closes the recordset.
Darryl Kerkeslager

[deko]

> For example, if this is a recurring function then you may call it in fifty
queries, then, later if you wanted to change the

function, you either had to change fifty queries or just one function.

I have a sophisticated search/sort/export function in one of my Access apps.
I too was faced with the prospect of having to maintain multiple queries to
generate the views required (at least 50). What I did was create a separate
module with the necessary logic (quite a bit, actually) to create a string
that I then assign to the SQL property of a single QueryDef. IMHO, this is
the best way to go if you're concerned about maintainablitlity.

Set db = CurrentDb
Set qdfs = db.QueryDefs
Set qdf = qdfs("qrySearch")
strSql = modBuildQry.[whatever]([parameters])
qdf.SQL = strSql
db.Execute "qrySearch"

This way I create anything I want - multiple joins, selects from other
queries, creation of temp tables, etc.

[windandwaves]

"Darryl Kerkeslager" <ke*********@comcast.net> wrote in message news:op**************@tigger.cnorth01.va.comcast.n et...

On Thu, 17 Feb 2005 14:46:58 +1300, windandwaves <wi*********@coldmail.com> wrote:

"Trevor Best" <no****@besty.org.uk> wrote in message news:42***********************@news.zen.co.uk...

It's not the function call that slows it down, it's what you do in the function.

I have already used this function. it is brilliant. It is also good to know that it is not the function call that slows it
down.

I just gave the code as an example, as described in my other replies. It is more about the concept then the example. it seems
that
no matter how fast the function is, it always seems slower than SQL.

What is slowing down your query is the opening and closing of a recordset, I presume many, many times. If you can eliminate that
one line of code, calling the function will run no slower than coding it without the function.

IME, it is *much* more efficient to open one recordset, and rs.Find through it, than to pass a table_id to a function that opens
the specific record in a recordset, does stuff, then closes the recordset.
Darryl Kerkeslager

That is a nice idea... I just have to get my head around it a little. Are you saying that the recordset should stay open all the
time?

Thank you for your note

Nicolaas

[Darryl Kerkeslager]

On Thu, 17 Feb 2005 19:41:04 +1300, windandwaves
<wi*********@coldmail.com> wrote:

"Darryl Kerkeslager" <ke*********@comcast.net> wrote in message
news:op**************@tigger.cnorth01.va.comcast.n et...

On Thu, 17 Feb 2005 14:46:58 +1300, windandwaves
<wi*********@coldmail.com> wrote:

"Trevor Best" <no****@besty.org.uk> wrote in message
news:42***********************@news.zen.co.uk...
It's not the function call that slows it down, it's what you do in
the function.
I have already used this function. it is brilliant. It is also good
to know that it is not the function call that slows it
down.

I just gave the code as an example, as described in my other
replies. It is more about the concept then the example. it seems
that
no matter how fast the function is, it always seems slower than SQL.

What is slowing down your query is the opening and closing of a
recordset, I presume many, many times. If you can eliminate that
one line of code, calling the function will run no slower than coding
it without the function.

IME, it is *much* more efficient to open one recordset, and rs.Find
through it, than to pass a table_id to a function that opens
the specific record in a recordset, does stuff, then closes the
recordset.
Darryl Kerkeslager

That is a nice idea... I just have to get my head around it a little.
Are you saying that the recordset should stay open all the
time?

I'm just saying that it is more efficient to open one recordset and do 200
operations, than to open 200 recordsets and do one operation, especially
if it is remote data. Of course, if you can open one recordset, and do
all your stuff in one SQL statement, that would be most efficient.

Darryl Kerkeslager

[Trevor Best]

Darryl Kerkeslager wrote:

I'm just saying that it is more efficient to open one recordset and do
200 operations, than to open 200 recordsets and do one operation,
especially if it is remote data. Of course, if you can open one
recordset, and do all your stuff in one SQL statement, that would be
most efficient.

I wouldn't take that a rule of thumb, .FindFirst on a recordset can be
very slow on linked jet tables, on SQL Server OTOH .Findfirst actually
generates another query (i.e opens another recordset anyway).

But I think I'm splitting hairs here as we're dicsussing the
efficiencies of doing something inefficient in the first place :-)

--
This sig left intentionally blank

[windandwaves]

"Trevor Best" <no****@besty.org.uk> wrote in message news:42***********************@news.zen.co.uk...

[....]

But I think I'm splitting hairs here as we're dicsussing the efficiencies of doing something inefficient in the first place :-)

--
This sig left intentionally blank

After a while, I have realised though that doing something efficient in the big picture means that you develop it in such a way that
it is easy to alter and adjust (i.e. easy to develop) rather than fast or what have you. Only when your application is proven to
work and the like you can start tweaking, before that you should write your code in such a way that it is easy to edit - well, just
my two cents worth....

Thank you for you knowledgeable answers.