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.OpenR ecordset("SELEC T 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]

"windandwav es" <wi*********@co ldmail.com> wrote in message
news:Ir******** ***********@new s.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.OpenR ecordset("SELEC T 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.OpenRec ordset(strSQL) you will be opening a recordset which you
can edit.
Set rst=dbs.OpenRec ordset(strSQL,d bOpenForwardOnl y) 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_FILE S 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.btinte rnet.com...

"windandwav es" <wi*********@co ldmail.com> wrote in message news:Ir******** ***********@new s.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.OpenR ecordset("SELEC T 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.OpenRec ordset(strSQL) you will be opening a recordset which you can edit.
Set rst=dbs.OpenRec ordset(strSQL,d bOpenForwardOnl y) 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.c om> wrote in message news:BQ******** *******@newssvr 13.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.o rg.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_FILE S 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*********@co ldmail.com> wrote:

"Trevor Best" <no****@besty.o rg.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 maintainablitli ty.

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*********@co mcast.net> wrote in message news:op******** ******@tigger.c north01.va.comc ast.net...

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

"Trevor Best" <no****@besty.o rg.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