Are UDFs in queries a bad idea?

[NauticalGent]

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]

not bad for me.
only slow downs the query.
it's only a guideline, not ANSI standard.

 

[NauticalGent]

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]

How can you not use UDFs if you need some sort of complex calculation?
Would those Access functions include Aggregate functions?

 

[NauticalGent]

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]

"Do not" may be strong words. "Avoid" may be softer. It all depends on what the query needs.

 

[isladogs]

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

 

[NauticalGent]

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

 

[isladogs]

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

 

[NauticalGent]

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.

 

[isladogs]

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]

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

 

[Galaxiom]

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]

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]

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]

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]

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.