mlachak
05-14-2009, 08:12 AM
is there a row limit when writing a query in SQL design of Access 2003?
|
View Full Version : row limit in SQL Query Design mlachak 05-14-2009, 08:12 AM is there a row limit when writing a query in SQL design of Access 2003? ByteMyzer 05-14-2009, 09:48 AM How big a query are you attempting to write? MSAccessRookie 05-14-2009, 09:53 AM is there a row limit when writing a query in SQL design of Access 2003? 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 05-14-2009, 01:52 PM 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 |