"Query Too Complex" Errors

Hello,

In my office, we have a complex Access file which we use to create our
monthly reports that we send to our clients. The file was initially created
two years ago, but since then the company has grown immensely and the file
was updated many times to meet our growing needs. Many of the calculation
that the files does nowadays were not part of the initial design and so they
are done in a very inefficient way. As a result of that, the file contains a
few dozens of queries, in the middle of which there's a long chain of queries,
each based on another query's (or queries') output.

As you can probably guess, we've been having a lot of those "Query Too
Complex" errors. We overcame this problem by adding a few intermediate tables,
which contain the output of some of the complex queries, and then basing the
subsequent queries on those intermediate tables, instead of the queries
themselves.

Because this solution is a temporary one, and in light of the fact that our
company is still growing and we may need to add more functions to this Access
file, my boss decided to build this file from scratch, only this time trying
to make it as efficient as possible and take into consideration the needs
that may arise in the future. This project felt into the hands of yours truly.


Now, I'm sure I can greatly improve this file's efficiency, but I don't think
there's a way to altogether avoid this chain of complex, subsequent queries.
This means that I, most likely, am going to encounter the "Query Too Complex"
error, times and again.

My questions are as following:

1. What are the general guidelines in avoiding the dreaded "Query Too
Complex" error?
2. Should I strive to write one huge query or should I just break it down to
a large number of very simple queries instead?
3. What's the best way to arrange the initial tables?
4. Should I avoid having too many columns in a query?
5. Does the number of lines in an input table/query has an effect on the
probability of getting the "Query Too Complex" error?
6. Would a significantly more powerful PC help avoid the error?

Thanks in advance,
Jake.

Answers in-line, following your questions.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Jake_G" u30146@uwe wrote in message
news:6a9ed3f1b7d83@uwe...

In my office, we have a complex Access file which we use to create our
monthly reports that we send to our clients. The file was initially
created
two years ago, but since then the company has grown immensely and the file
was updated many times to meet our growing needs. Many of the calculation
that the files does nowadays were not part of the initial design and so
they
are done in a very inefficient way. As a result of that, the file contains
a
few dozens of queries, in the middle of which there's a long chain of
queries,
each based on another query's (or queries') output.

As you can probably guess, we've been having a lot of those "Query Too
Complex" errors. We overcame this problem by adding a few intermediate
tables,
which contain the output of some of the complex queries, and then basing
the
subsequent queries on those intermediate tables, instead of the queries
themselves.

Because this solution is a temporary one, and in light of the fact that
our
company is still growing and we may need to add more functions to this
Access
file, my boss decided to build this file from scratch, only this time
trying
to make it as efficient as possible and take into consideration the needs
that may arise in the future. This project felt into the hands of yours
truly.


Now, I'm sure I can greatly improve this file's efficiency, but I don't
think
there's a way to altogether avoid this chain of complex, subsequent
queries.
This means that I, most likely, am going to encounter the "Query Too
Complex"
error, times and again.

My questions are as following:

1. What are the general guidelines in avoiding the dreaded "Query Too
Complex" error?

The message just means Access doesn't understand the query. Even a simple
query can fail with this message for many reasons. Examples:

a) The bracketing is invalid, or inadequate (ambiguous.)

b) Square brackets are needed around a name containing a space.

c) You confused Access by using a reserved word as a the name of a
table/field/alias.

d) Name AutoCorrect has Access confused about the name of something. More
info on the raft of problems this causes:
http://allenbrowne.com/bug-03.html

e) Access is confused about the data type of a calculated field, so the
calculation needs typecasting. More info:
http://allenbrowne.com/ser-45.html

f) There's an undeclared parameter that has Access confused. Declare it so
JET knows the name and its data type.

g) Delimiters are needed (or should be omitted) around a literal value.

Oh, and of course, it could also be that the query is too complex to do as a
single operation. :-)

2. Should I strive to write one huge query or should I just break it down
to a large number of very simple queries instead?

Your goal is the simplest solution that is totally reliable, as easy as
practical to maintain, reasonably efficient to execute, and interfaced so
any user is clear what's going on. These 4 requirements are often in
conflict.

In practice, I usually start out on a task assuming I will be able to build
the SQL string in code. That gives you great flexibility. For example, if
there are several optional parameters, some of the complexity in the SQL
statement is just handling each case where the user might leave the text box
blank. If you build the SQL string dynamically, you can entirely omit from
the WHERE clause those fields that are not used this time. Similarly, if the
criteria includes a date period (month/quarter/...), you can calculate start
and end dates in VBA, and then patch the literal dates into the SQL string
instead of the entire calculation. This also avoids all problems with data
typing and parameters.

In building that SQL string, you can include several joins and a few
subqueries. If there are 3 or 4 subqueries, I'm starting to worry because
it's fairly easy to crash JET with subqueries. Or there may be obvious
places where pre-processing would be more efficient. If so, it's time to use
some pre-processing, i.e. lower level queries that are called by the main
SQL statement you are working on. These stacked queries may be static, or
you might need to give them criteria specific to this monster you are
writing. You can do that by generating a string for their SQL statement, and
assigning it to the SQL property of the QueryDef, e.g.:
strSql = "SELECT ...
CurrentDb.QueryDefs("Query1").SQL = strSql
I find that JET usually (but not always) executes stacked queries more
efficiently than subqueries.

If the dynamic SQL statement with its subqueries and stacked queries is
still becoming unweildy or inefficient, I start thinking about using
temporary tables. It's usually just a matter of storing primary key values
so you can limit your SQL statement with an INNER JOIN on the temp table. If
I use a temp table, I always set it up at design time and INSERT/DELETE
(i.e. never a Make Table query.) It usually goes into the front end, so
multiple users don't interfere with each other if they are both running the
same process, and I use a different table prefix name for these tables.

As you gain experience, there will be times when the need for pre-processing
is obvious, so you will start to build your query that way.

3. What's the best way to arrange the initial tables?

Always normalized.
Use a naming convention.
Don't use spaces or other characters that require you to add the square
brackets around names.
Make sure Name AutoCorrect is off.

4. Should I avoid having too many columns in a query?

The limits on your query a
- 255 fields;
- 4k characters in all fields (not counting BLOBs such as memos, hyperlinks,
OLE Objects, attachments.)

5. Does the number of lines in an input table/query has an effect on the
probability of getting the "Query Too Complex" error?

Not directly.

6. Would a significantly more powerful PC help avoid the error?

No. The problem is with the interpretation of the query statement, not the
processing power needed to execute it.

"Jake_G" u30146@uwe wrote in news:6a9ed3f1b7d83@uwe:

we've been having a lot of those "Query Too
Complex" errors.

This occasionally can happen because the compiled query plan somehow
gets out of synch with the SQL text. A compact does not fully remove
the erroneous compilation (though sometimes it does), so I've found
that sometimes you need to recreate the query by copying its SQL
into a new blank query. I have also encountered cases where this
newly created query would run without any problems at all until you
make and save one change to it, at which point the "too complex"
error returns. Then pasting the new SQL into a new query will work
fine.

I have never figured out what the boundary condition is that leads
to this problem, but it does seem to have to do with the number of
joins, the depth of joins, and is perhaps aggravated by partially or
fully circular joins.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

First of all, I thank both of you for your replies.

Allen Browne wrote:
Oh, and of course, it could also be that the query is too complex to do as a
single operation. :-)

Is there no way to overcome this problem? Would switching to SQL Server help
with those complex queries?

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...forms/200612/1

SQL Server is certainly a more powerful product, with considerably more
control over how the query will be executed.

You know what you are aiming to do in JET, but IME, the error message rarely
means the query is too complex for JET to execute. I write SQL statements
that contain thousands of words, dozens of tables, numerous subqueries, and
so on. Sometimes a statement can take hours to write, test, and debug. Some
of these monsters are generated programmatically, i.e. I write VBA code that
generates the SQL statements based combinations of hundreds of runtime user
input choices.

I strike lots of bugs in JET where it interprets the query wrongly.
Examples:
http://allenbrowne.com/tips.html#Bug%20Engine

But, if I get a "query too complex" error it is almost always my own fault,
i.e. I have an error in my SQL statement.

(Of course, a non-normalized data structure can quickly give rise to
unnecessary complexity also.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Jake_G via AccessMonster.com" u30146@uwe wrote in message
news:6ac2257e3846d@uwe...
First of all, I thank both of you for your replies.

Allen Browne wrote:
Oh, and of course, it could also be that the query is too complex to do as
a
single operation. :-)

Is there no way to overcome this problem? Would switching to SQL Server
help
with those complex queries?

But like I said, when we base the query on a table - rather than another
query - the error is not prompted. Do you believe that a flow in the queries'
design can casue this?

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...forms/200612/1