row limit in SQL Query Design
- Thread starter mlachak
- Start date
MSAccessRookie
AWF VIP
- Local time
- Today, 06:21
- Joined
- May 2, 2008
- Messages
- 3,428
Do you mean a limit to the number of characters/rows contained in the query text, or the number of rows that a query is able to return?
In the case of the number of characters/rows contained in the query text, I have created Union queries that Access said were too big. I have always assumed it was a character count.
In the case of the number of rows that a query is able to return, I have never encountered a limit so far, but more rows being sought and returned are usually accompanied by a slower response time.
just.a.guy
Registered User.
- Local time
- Today, 05:21
- Joined
- May 14, 2009
- Messages
- 14
I have encounter a situation where my query was too complicated (too big?)
to be saved in ACCESS.
My solution was to break the query into multiple query members.
The main issue to watch for is the use of "alias" labels for table
names.
It seems (I am guessing) that "labels" are global in scope, within nested SQL queries.
By this I mean, if you code the clause "FROM table_1 AS a" ("a" is the label),
you cannot use that label for some other table in any of the other query members.
ACCESS may get confused and produce the wrong results or an error.
I am guessing, but looks like ACCESS assembles all of the SQL before
it does an analysis to build the execution plan.
However, I am using a very old version of ACCESS. The newer versions may
actually build an execution plan for each piece as it is saved, and assemble the plans
as a final step.
I have not run into an output limit. A major limit is the "effective table size"
the the query builds. You can think of a JOIN type operation(s) as creating
a virtual table that is queried. The larger the virtual table the longer it
takes to run the query. If the virtual size gets too big, it can take too
much time to finish just one query. The solution is to avoid large complicated
queries. Do things in smaller "work" tables and assemble the result from these
work (intermediate) tables.
Hope this helps
to be saved in ACCESS.
My solution was to break the query into multiple query members.
The main issue to watch for is the use of "alias" labels for table
names.
It seems (I am guessing) that "labels" are global in scope, within nested SQL queries.
By this I mean, if you code the clause "FROM table_1 AS a" ("a" is the label),
you cannot use that label for some other table in any of the other query members.
ACCESS may get confused and produce the wrong results or an error.
I am guessing, but looks like ACCESS assembles all of the SQL before
it does an analysis to build the execution plan.
However, I am using a very old version of ACCESS. The newer versions may
actually build an execution plan for each piece as it is saved, and assemble the plans
as a final step.
I have not run into an output limit. A major limit is the "effective table size"
the the query builds. You can think of a JOIN type operation(s) as creating
a virtual table that is queried. The larger the virtual table the longer it
takes to run the query. If the virtual size gets too big, it can take too
much time to finish just one query. The solution is to avoid large complicated
queries. Do things in smaller "work" tables and assemble the result from these
work (intermediate) tables.
Hope this helps
Similar threads
