Are UDFs in queries a bad idea? (1 Viewer)

NauticalGent

Ignore List Poster Boy
Local time
Today, 02:25
Joined
Apr 27, 2015
Messages
6,286
From another Access Dev Forum:

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 UDF’s in a querydef a bad idea?
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 14:25
Joined
May 7, 2009
Messages
19,169
not bad for me.
only slow downs the query.
it's only a guideline, not ANSI standard.
 

NauticalGent

Ignore List Poster Boy
Local time
Today, 02:25
Joined
Apr 27, 2015
Messages
6,286
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.
 

Gasman

Enthusiastic Amateur
Local time
Today, 06:25
Joined
Sep 21, 2011
Messages
14,048
How can you not use UDFs if you need some sort of complex calculation?
Would those Access functions include Aggregate functions?
 

NauticalGent

Ignore List Poster Boy
Local time
Today, 02:25
Joined
Apr 27, 2015
Messages
6,286
How can you not use UDFs if you need some sort of complex calculation?
Would those Access functions include Aggregate functions?

Agreed and I’m 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.
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 23:25
Joined
Oct 29, 2018
Messages
21,358
"Do not" may be strong words. "Avoid" may be softer. It all depends on what the query needs.
 

isladogs

MVP / VIP
Local time
Today, 06:25
Joined
Jan 14, 2017
Messages
18,186
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
 

isladogs

MVP / VIP
Local time
Today, 06:25
Joined
Jan 14, 2017
Messages
18,186
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
 

isladogs

MVP / VIP
Local time
Today, 06:25
Joined
Jan 14, 2017
Messages
18,186
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?
 

NauticalGent

Ignore List Poster Boy
Local time
Today, 02:25
Joined
Apr 27, 2015
Messages
6,286
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...
 
Last edited:

Galaxiom

Super Moderator
Staff member
Local time
Today, 17:25
Joined
Jan 20, 2009
Messages
12,849
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.
 

sonic8

AWF VIP
Local time
Today, 07:25
Joined
Oct 27, 2015
Messages
998
From another Access Dev Forum:
Guidelines followed in constructing the query examples:

Do not use user-defined functions (VBA code).
Drawing attention to the bit in red, are UDF’s 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.
 

NauticalGent

Ignore List Poster Boy
Local time
Today, 02:25
Joined
Apr 27, 2015
Messages
6,286
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.
 

plog

Banishment Pending
Local time
Today, 01:25
Joined
May 11, 2011
Messages
11,613
I think you're propagating a spam post on another forum. Google the first words of the quoted posted ("111 Power Access Queries/SQL Examples"). The entire text is from a site selling the 111 queries for $40.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 01:25
Joined
Feb 28, 2001
Messages
27,001
A UDF is sometimes the fastest way to go for a mathematically oriented operation. It was a bizarre case, only happened once, but I had to compute some advanced statistical values for a Binomial Distribution and there wasn't one in any library I could find - so I had to roll my own as a UDF. Actually, despite the fact of it being interpreted rather than compiled, I was able to find "telescoped" functions that would help me speed up the computation.

I think that, like many other things we do, the ability to use a UDF should be a tool in the tool-kit, used when appropriate. We have to be mindful of the fact that if you only have hammers in your tool-kit, everything looks like nails.
 

Users who are viewing this thread

Top Bottom