16 table limit in queries 
Author Message
 16 table limit in queries

I am getting an odd error and no help from ms.  Maybe someone out there
knows the answer.  I continue to run into a 16 table limit in a select
statement, or in the combination of views.  I know this cannot be the
intended limit, since Access 7.0 can do 32 tables, and a database with
the ability to contain 4 billion tables and x terabytes of data cannot
possibly limit you to 16 tables at a time.  Please help, before I fill
ms's coffers with any more $$.

Jon Clemens
VP Operations, Polymer Dynamics, Inc.



Tue, 10 Aug 1999 03:00:00 GMT
 16 table limit in queries

We were told by ms sales several times that the limit is 32,000 tables
in a query, because we were concerned about this in the evaluation copy.

The limit cannot really be 16 tables, since Access 7.0 can do 32!!!!
It defies logic that a db with 4 billion tables can only look at 16 at
a time. Please, anyone, any info???

Jon Clemens
VP Operations, Polymer Dynamics, Inc.



Tue, 10 Aug 1999 03:00:00 GMT
 16 table limit in queries

I believe the limit is based on how optimization and execution is handled.
SQLServer can only handle four tables at a time.  When you get to five, it
joins four tables together into a temporary table then joins that to the
remaining table.  This can be done up to 16 (four sets of four temp
tables).  At that point there is a wall.  I hope it makes more sense from
that perspective.  It is also a good reason to keep away from breaking
those even bounds (4 vs 5, 8 vs 9).



Quote:
> Chris,

> SQL Server has a limit of 16 tables in a command.  It has always had
> this limit - though it used to be even more restrictive and include
> system generated temporary tables.  The limit originated with the code
> that Sybase wrote before Microsoft started creating Access.

> Roy



Sat, 21 Aug 1999 03:00:00 GMT
 16 table limit in queries

Fred,

Quote:
>I believe the limit is based on how optimization and execution is handled.
>SQLServer can only handle four tables at a time.  When you get to five, it
>joins four tables together into a temporary table then joins that to the
>remaining table.  This can be done up to 16 (four sets of four temp
>tables).  At that point there is a wall.  I hope it makes more sense from
>that perspective.  It is also a good reason to keep away from breaking
>those even bounds (4 vs 5, 8 vs 9).

That is interesting.  It is NOT what I have been led to believe, but
that sure doesn't rule it out!

My understanding is that the change that occurs when you get the fifth
(or was it sixth?) table is that the optimizer no longer explores
every possible execution plan.  Since the number of possible plans
expands exponentially, this is understandable.  I have never heard
before that the execution plan generated would follow an approach of
four tables at a time.

Hopefully Kalen Delaney will join this thread.  Kalen helped re-write
the PT&O course and would have better insight than I.

Roy



Sat, 21 Aug 1999 03:00:00 GMT
 
 [ 4 post ] 

 Relevant Pages 

1. 16 Table limit in Query

2. 16 Tables involved in query Limit

3. Q: Limit of 16 tables in a query

4. 16-table Limit in SQL Queries

5. 16 table limit in a query

6. Query limit of 16 tables

7. 16 Table query limit

8. Check if query exceeds 16 table limit

9. 16 tables involved in a query limit

10. sybase limit of 16 on queries /tables

11. SQL Server 6.5 and 16 Table Limit

12. 16 table limit


 
Powered by phpBB® Forum Software