Go Back   Access World Forums > Microsoft Access Discussion > Queries

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 08-29-2019, 09:36 AM   #1
NauticalGent
Pristine Curmudgeon
Gold Supporter
 
NauticalGent's Avatar
 
Join Date: Apr 2015
Location: Naples, Italy (EXPAT from Virginia)
Posts: 2,019
Thanks: 450
Thanked 300 Times in 259 Posts
NauticalGent has a spectacular aura about NauticalGent has a spectacular aura about
Are UDFs in queries a bad idea?

From another Access Dev Forum:

Quote:
111 Power Access Queries/SQL Examples

Are you mining all the nuggets out of your goldmine? Are you asking your database questions tough enough to extract all the pertinent information for your business? Do you want to push the envelope and create some esoteric yet powerful queries? Here are 70+ challenging query examples all employing advanced techniques based mostly on the tables in the Northwind database which comes with Microsoft Access.

Built-in Access functions, query parameters, derived tables, self-joins, unequal joins, cross joins and correlated subqueries can make synergistic combinations, enabling you to build compact, powerful, and reusable queries.

Take your query building skills to the next level and impress your boss and/or colleagues. You will also find the query examples suitable to use as teaching material for an advanced Access class. Profit from these expert query techniques now. This product requires Microsoft Access 2000 or higher.

Guidelines followed in constructing the query examples:

Do not use user-defined functions (VBA code).
Use nested queries and sub-queries sparingly.
Introduce as many Access's built-in functions as possible.
Go outside the box and exclude run-of-the mill type of queries.
Use parameters wherever appropriate.
Cover as many types of query as possible.
Download: 111 Power Access Query/SQL Examples
Drawing attention to the bit in red, are UDFs in a querydef a bad idea?

__________________
It follows then as certain that night succeeds the day, that without normalization, we can do nothing definitive, and with it, everything honorable and glorious. - with apologies to George Washington
NauticalGent is offline   Reply With Quote
Old 08-29-2019, 09:39 AM   #2
arnelgp
error reading drive A:
 
arnelgp's Avatar
 
Join Date: May 2009
Location: somewhere out there
Posts: 8,568
Thanks: 68
Thanked 2,744 Times in 2,629 Posts
arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice
Re: Are UDFs in queries a bad idea?

not bad for me.
only slow downs the query.
it's only a guideline, not ANSI standard.
__________________
"Never stop learning, because life never stops teaching"
arnelgp is offline   Reply With Quote
Old 08-29-2019, 09:42 AM   #3
NauticalGent
Pristine Curmudgeon
Gold Supporter
 
NauticalGent's Avatar
 
Join Date: Apr 2015
Location: Naples, Italy (EXPAT from Virginia)
Posts: 2,019
Thanks: 450
Thanked 300 Times in 259 Posts
NauticalGent has a spectacular aura about NauticalGent has a spectacular aura about
Re: Are UDFs in queries a bad idea?

Thanks ArnelGp. I KNEW when I posted this which side of the fence you would be on! I use them a lot too and have not noticed any inconsistencies.

__________________
It follows then as certain that night succeeds the day, that without normalization, we can do nothing definitive, and with it, everything honorable and glorious. - with apologies to George Washington
NauticalGent is offline   Reply With Quote
Old 08-29-2019, 09:43 AM   #4
Gasman
Enthusiastic Amateur
 
Gasman's Avatar
 
Join Date: Sep 2011
Location: Swansea, South Wales,UK
Posts: 4,383
Thanks: 434
Thanked 804 Times in 779 Posts
Gasman is a jewel in the rough Gasman is a jewel in the rough Gasman is a jewel in the rough
Re: Are UDFs in queries a bad idea?

How can you not use UDFs if you need some sort of complex calculation?
Would those Access functions include Aggregate functions?
__________________
Access novice. Sometimes trying to give something back.
Access 2007

Please, please use code tag # when posting code snippets

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Gasman is offline   Reply With Quote
Old 08-29-2019, 09:45 AM   #5
NauticalGent
Pristine Curmudgeon
Gold Supporter
 
NauticalGent's Avatar
 
Join Date: Apr 2015
Location: Naples, Italy (EXPAT from Virginia)
Posts: 2,019
Thanks: 450
Thanked 300 Times in 259 Posts
NauticalGent has a spectacular aura about NauticalGent has a spectacular aura about
Re: Are UDFs in queries a bad idea?

Quote:
Originally Posted by Gasman View Post
How can you not use UDFs if you need some sort of complex calculation?
Would those Access functions include Aggregate functions?
Agreed and Im not sure what the motivation for advising against them would be. It really set me back on my heels because the site is quite reputable.
__________________
It follows then as certain that night succeeds the day, that without normalization, we can do nothing definitive, and with it, everything honorable and glorious. - with apologies to George Washington
NauticalGent is offline   Reply With Quote
Old 08-29-2019, 09:48 AM   #6
theDBguy
Im here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 5,896
Thanks: 57
Thanked 1,288 Times in 1,269 Posts
theDBguy has a spectacular aura about theDBguy has a spectacular aura about
Re: Are UDFs in queries a bad idea?

"Do not" may be strong words. "Avoid" may be softer. It all depends on what the query needs.
__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is offline   Reply With Quote
Old 08-29-2019, 10:36 AM   #7
isladogs
High Noon Moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 11,011
Thanks: 114
Thanked 3,014 Times in 2,741 Posts
isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold
Re: Are UDFs in queries a bad idea?

Agree with the consensus here.
Use built in functions where possible rather than UDFs but if appropriate for the query then go ahead.
I think the reason why this was stated is related to the query execution plan.
Its probable that JET/ACE will need to do more processing if a UDF is involved compared to a built in function. Easy to test....

Anyway NG, you can't give a long quote from a reputable source without telling us where it came from. Besides which I'd like to see what these "70+ challenging query examples" are

__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Website links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Colin (Mendip Data Systems)
Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


"As we know, there are known knowns; there are things we know we know. We also know there are known unknowns; that is to say we know there are some things we do not know. But there are also unknown unknowns the ones we don't know we don't know. It is the latter category that tend to be the difficult ones" Donald Rumsfeld
isladogs is offline   Reply With Quote
Old 08-29-2019, 10:41 AM   #8
NauticalGent
Pristine Curmudgeon
Gold Supporter
 
NauticalGent's Avatar
 
Join Date: Apr 2015
Location: Naples, Italy (EXPAT from Virginia)
Posts: 2,019
Thanks: 450
Thanked 300 Times in 259 Posts
NauticalGent has a spectacular aura about NauticalGent has a spectacular aura about
Re: Are UDFs in queries a bad idea?

Quote:
Originally Posted by isladogs View Post
Anyway NG, you can't give a long quote from a reputable source without telling us where it came from. Besides which I'd like to see what these "70+ challenging query examples" are
But of course, http://www.databasedev.co.uk/query_using_listbox.html
__________________
It follows then as certain that night succeeds the day, that without normalization, we can do nothing definitive, and with it, everything honorable and glorious. - with apologies to George Washington
NauticalGent is offline   Reply With Quote
Old 08-29-2019, 11:47 AM   #9
isladogs
High Noon Moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 11,011
Thanks: 114
Thanked 3,014 Times in 2,741 Posts
isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold
Re: Are UDFs in queries a bad idea?

Agree its a reputable site but can't find the extended quote or the challenging queries on that page. Please could you recheck the link
__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Website links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Colin (Mendip Data Systems)
Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


"As we know, there are known knowns; there are things we know we know. We also know there are known unknowns; that is to say we know there are some things we do not know. But there are also unknown unknowns the ones we don't know we don't know. It is the latter category that tend to be the difficult ones" Donald Rumsfeld
isladogs is offline   Reply With Quote
Old 08-29-2019, 11:54 AM   #10
NauticalGent
Pristine Curmudgeon
Gold Supporter
 
NauticalGent's Avatar
 
Join Date: Apr 2015
Location: Naples, Italy (EXPAT from Virginia)
Posts: 2,019
Thanks: 450
Thanked 300 Times in 259 Posts
NauticalGent has a spectacular aura about NauticalGent has a spectacular aura about
Re: Are UDFs in queries a bad idea?

Try this one, (the one I posted was some listbox code) : http://www.databasedev.co.uk/microsoft-access-sql.html

The link is at the bottom of the page - but - apparently these 70+ examples were not free and they are no longer available.
__________________
It follows then as certain that night succeeds the day, that without normalization, we can do nothing definitive, and with it, everything honorable and glorious. - with apologies to George Washington
NauticalGent is offline   Reply With Quote
Old 08-29-2019, 11:58 AM   #11
isladogs
High Noon Moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 11,011
Thanks: 114
Thanked 3,014 Times in 2,741 Posts
isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold
Re: Are UDFs in queries a bad idea?

Rats! Foiled again.
As I said before my guess is that the comment is related to the query execution plan. Is there anything elsewhere on the site to provide any context for the comment?
__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Website links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Colin (Mendip Data Systems)
Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


"As we know, there are known knowns; there are things we know we know. We also know there are known unknowns; that is to say we know there are some things we do not know. But there are also unknown unknowns the ones we don't know we don't know. It is the latter category that tend to be the difficult ones" Donald Rumsfeld
isladogs is offline   Reply With Quote
Old 08-29-2019, 12:03 PM   #12
NauticalGent
Pristine Curmudgeon
Gold Supporter
 
NauticalGent's Avatar
 
Join Date: Apr 2015
Location: Naples, Italy (EXPAT from Virginia)
Posts: 2,019
Thanks: 450
Thanked 300 Times in 259 Posts
NauticalGent has a spectacular aura about NauticalGent has a spectacular aura about
Re: Are UDFs in queries a bad idea?

None. I did a google search on the concept and found a pretty in-depth conversation on SO. But the consensus was that UDFs have no adverse effects/performance hits, etc..

But...it was SQL Server/T-SQL so it really isn’t the same thing...
__________________
It follows then as certain that night succeeds the day, that without normalization, we can do nothing definitive, and with it, everything honorable and glorious. - with apologies to George Washington

Last edited by NauticalGent; 08-29-2019 at 12:13 PM.
NauticalGent is offline   Reply With Quote
Old 08-30-2019, 12:38 AM   #13
Galaxiom
Super Moderator
 
Join Date: Jan 2009
Location: NSW Australia
Posts: 11,657
Thanks: 99
Thanked 1,500 Times in 1,415 Posts
Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold
Re: Are UDFs in queries a bad idea?

There are definite advantages to built in functions but the convenience of a UDF often outweighs the disadvantages. However developers should avoid putting trivial tasks into UDFs if they care about performance.

Built in functions are compiled in a library while a user defined function is interpreted VBA. The performance of UDF is unlikely to match the built in functions unless there are inefficiencies avoided in structure the UDF.

If the back end is ODBC connected to an RDMS, some queries can be passed to the back end for processing. Some built in functions can also be ODBC translated to an equivalent in the RDMS, but UDFs definitely cannot, so require records to be brought down to the FE before applying the function. Consequently sticking to built in functions can be a significant performance advantage in this situation.

If performance really matters, enterprising users can write functions in .NET languages, compile them into a class library and reference them in Access to gain the performance advantages seen with built in functions.

However it is an unlikely scenario for someone seeking such advantage to be working in Access. More likely, they would use the library in an Assembly in SQL Server to provide a CLR function.

I have recently started employing CLR functions in SQL Server and confirmed their spectacular performance. On one test, converting a scalar function written in SQL to a CLR function resulted in a three fold improvement. That is a huge factor on a busy server.
Galaxiom is offline   Reply With Quote
The Following User Says Thank You to Galaxiom For This Useful Post:
lpapad (08-31-2019)
Old 08-30-2019, 06:27 AM   #14
sonic8
AWF VIP
 
Join Date: Oct 2015
Posts: 269
Thanks: 44
Thanked 76 Times in 73 Posts
sonic8 is on a distinguished road
Re: Are UDFs in queries a bad idea?

Quote:
Originally Posted by NauticalGent View Post
From another Access Dev Forum:
Quote:
Guidelines followed in constructing the query examples:

Do not use user-defined functions (VBA code).
Drawing attention to the bit in red, are UDFs in a querydef a bad idea?
I'd say you need to see this in its context. If you want to provide a wide variety of sample queries for different purposes it is certainly advisable not to use custom VBA functions. These would be an additional dependency for the query sample and add complexity.

So, in my opinion the above is not general advice but just an arbitrary rule imposed by the author of the original text and sample queries to limit their complexity and broaden the applicability of the samples.
__________________
New article:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
sonic8 is offline   Reply With Quote
The Following User Says Thank You to sonic8 For This Useful Post:
NauticalGent (08-30-2019)
Old 08-30-2019, 06:42 AM   #15
NauticalGent
Pristine Curmudgeon
Gold Supporter
 
NauticalGent's Avatar
 
Join Date: Apr 2015
Location: Naples, Italy (EXPAT from Virginia)
Posts: 2,019
Thanks: 450
Thanked 300 Times in 259 Posts
NauticalGent has a spectacular aura about NauticalGent has a spectacular aura about
Re: Are UDFs in queries a bad idea?

Quote:
Originally Posted by sonic8 View Post
So, in my opinion the above is not general advice but just an arbitrary rule imposed by the author of the original text and sample queries to limit their complexity and broaden the applicability of the samples.
Hadn't thought about it along those lines, but given the experienced responses and my own experience, I'd say you are probably spot-on.

Thanks for the input.

__________________
It follows then as certain that night succeeds the day, that without normalization, we can do nothing definitive, and with it, everything honorable and glorious. - with apologies to George Washington
NauticalGent is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
How can fix it? any better idea please meenctg Forms 2 08-02-2014 03:30 PM
I have an idea but I have no idea where to start isapple General 7 06-26-2014 04:58 AM
No idea how to do this kamamakazii Queries 2 04-13-2008 01:00 PM
UDFs that accept arguments to process UPDATE/INSERT INTO SQL stmts? steve728 Queries 1 03-19-2006 05:08 PM
any one an idea Traden General 2 03-10-2003 02:38 AM




All times are GMT -8. The time now is 10:49 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World