Query Issue

[isladogs]

OK ...so who is going to do a speed comparison test on all of these variations and see whether there really is any noticeable difference between them?
In this case, not me!

 

[The_Doc_Man]

In this case, a speed test on arnelgp's #17 would be on what is PROBABLY the fastest choice, but with the understanding that the general case might be content-sensitive i.e. speed will depend on the number of nulls involved. The ampersand will try to concatenate something but it has to test first whether there IS anything to concatenate. Therefore there is a two-step process, the second step of which will be skipped when there IS a null in a field. (Actually, three two-step processes, one for each field.) Therefore the speed test would be expected to show a wider range of timing.

 

[Pat Hartman]

#17 is the OPPOSITE of what the OP requested.
According to old documentation (which I can't find at the moment), there is overhead with using functions. Therefore, If you can do something in SQL without using a function, it would incur less overhead and thus be faster.

The document showed the relative "weight" of a variety of commands. Perhaps someone else has a copy of it.

 

[GPGeorge]

I can verify that functions in SQL can be a tremendous performance hit, but it probably does vary with context, so an absolute statement is hard to pin down. The particular place where this came home to me was when we migrated the back end to SQL Server and queries that called Access functions ground to a halt. We moved most of those functions to stored procedures or functions in SQL Server, and others we revised the SQL in the Access queries. I recall that situation with clarity because most of the project budget that should have gone to enhancements went into remediating that performance problem.