If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
"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. |
#2
|
|||
|
|||
"Query Too Complex" Errors
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. |
#3
|
|||
|
|||
"Query Too Complex" Errors
"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/ |
#4
|
|||
|
|||
"Query Too Complex" Errors
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 |
#5
|
|||
|
|||
"Query Too Complex" Errors
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? |
#6
|
|||
|
|||
"Query Too Complex" Errors
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 |
Thread Tools | |
Display Modes | |
|
|