473,892 Members | 1,416 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

fastest way to call a function

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
Nov 13 '05 #1
12 5180

"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.


Nov 13 '05 #2
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... :)
Nov 13 '05 #3
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
Nov 13 '05 #4

"Stefan Kowalski" <a@b.com> 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.
Nov 13 '05 #5

"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
Nov 13 '05 #6

"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
Nov 13 '05 #7
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

Nov 13 '05 #8
> 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.
Nov 13 '05 #9

"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
Nov 13 '05 #10

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

17
2345
by: DraguVaso | last post by:
Hi, I need to find the FASTEST way to get a string in a Loop, that goes from "a" to "ZZZZZZZZZZZZZZZZZ". So it has to go like this: a b .... z
2
1425
by: microsoft | last post by:
I have a very "flat" doc structure like this <root> <one> <two> <three> ... n <=100 </root>
11
2120
by: Ignacio X. Domínguez | last post by:
Hi. I'm developing a desktop application that needs to store some data in a local file. Let's say for example that I want to have an address book with names and phone numbers in a file. I would like to be able to retrieve the name by searching for a given phone number the fastest I can. I have considered the posibility of using XmlTextReader with something like: <list> <item number="1234567"> <name>John Doe</name>
44
3250
by: Don Kim | last post by:
Ok, so I posted a rant earlier about the lack of marketing for C++/CLI, and it forked over into another rant about which was the faster compiler. Some said C# was just as fast as C++/CLI, whereas others said C++/CLI was more optimized. Anyway, I wrote up some very simple test code, and at least on my computer C++/CLI came out the fastest. Here's the sample code, and just for good measure I wrote one in java, and it was the slowest! ;-)...
6
50282
by: Klaas Vantournhout | last post by:
Hi, I have a question, which is just out of interest. What is the fastest way to do an odd/even check with c++ and if needed assembler. Assume n is an unsigned integer like type (unsigned int, unsigned long int), what is the fastest? using the modulo operator
12
5085
by: Godzilla | last post by:
Hello, I'm trying to find a way to convert an integer (8-bits long for starters) and converting them to a list, e.g.: num = 255 numList = with the first element of the list being the least significant, so that i can keep appending to that list without having to worry about
24
2309
by: ThunderMusic | last post by:
Hi, The subject says it all... I want to use a byte and use it as byte* so I can increment the pointer to iterate through it. What is the fastest way of doing so in C#? Thanks ThunderMusic
10
5181
by: BostonNole | last post by:
Using Visual Studio 2005, .NET 2.0 and VB.NET: I am looking for the fastest possible way to import a very large fixed width file (over 6 million records and over 1.2 GB file size) into a DataTable. Any suggestions?
4
1640
by: poojagupta | last post by:
If a routine has to be called 10,000 times then what will be the fastest and the optimised way of calling this routine as per my knowledge "for loop" is one way....inline function won't solve the problem.....will preprocessor "#typedef" will be a benifit?
0
9981
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
9830
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
11241
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
10836
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
0
10468
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
7172
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
1
4683
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
4279
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
3288
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.