Maximum number of table names in a query

Maximum number of table names in a query

Post by Alexy Timoni » Tue, 25 Jan 1994 02:48:20



Hello,

I started a quite large query and suddenly receive the following error message:

Quote:>   Too many table names in the query.  The maximum allowable is 16.
>   (Msg 106, Level 15, State 1).

Why there is so small limit ? Of course I can decrease the number of table names,
but I'd like to know is this constant limitation?

I use MS SQL Server 4.20.00.

               Thank you.

|-------------------------------------------------|
|   Alexy Timonin |  Phone      (095) 166-1931    |
|   Laid Co.      |  Fax        (095) 165-0254    |

|   Russia        |                               |
|-------------------------------------------------|

 
 
 

Maximum number of table names in a query

Post by Randy Jord » Wed, 26 Jan 1994 03:49:57


|>Newsgroups: comp.databases.sybase
|>From:  Alexy Timonin <tim>

|>Distribution: world
|>Subject: Maximum number of table names in a query
|>Organization: Laid Co.
|>Reply-To: tim
|>Date: Sun, 23 Jan 94 20:48:20 +0300
|>Path: pnl-bbs!pnl-oracle!netnews.nwnet.net!news.uoregon.edu!cs.uoregon.edu!sgiblab!swrinde!cs.utexas.edu!howland.reston.ans.net!pipex!sunic!news.funet.fi!news.eunet.fi!news.spb.su!KremlSun!kiae!relcom!laid!news

|>Lines: 25
|>
|>Hello,
|>
|>I started a quite large query and suddenly receive the following error message:
|>
|>>   Too many table names in the query.  The maximum allowable is 16.
|>>   (Msg 106, Level 15, State 1).
|>
|>Why there is so small limit ? Of course I can decrease the number of table names,
|>but I'd like to know is this constant limitation?
|>
|>I use MS SQL Server 4.20.00.
|>
|>               Thank you.
|>
|>|-------------------------------------------------|
|>|   Alexy Timonin |  Phone      (095) 166-1931    |
|>|   Laid Co.      |  Fax        (095) 165-0254    |

|>|   Russia        |                               |
|>|-------------------------------------------------|
|>
|>
|>
|>
|>
|>

        If you need to join more than 16 tables together, reexamine you
        query requirements... failing this create views of several tables
        and then join the views together.
RJ

--
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
        To Thyne Own Self be true...
                                     myne opinions are.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

 
 
 

Maximum number of table names in a query

Post by Pablo Sanch » Wed, 26 Jan 1994 14:35:16




> |>I started a quite large query and suddenly receive the following error
> |> message:
> |>
> |>>   Too many table names in the query.  The maximum allowable is 16.
> |>>   (Msg 106, Level 15, State 1).
> |>

>         If you need to join more than 16 tables together, reexamine you
>         query requirements...

you should do this because the optimizer *typically* cannot optimize as
*well* after four tables.  so help it along a bit...

Quote:>         failing this create views of several tables
>         and then join the views together.

unfortunately this is not the case, you still incur the cost of the tables
through the views.  much like a distinct clause eats up a table, an order
by, ... see ya!!!

Pablo Sanchez              | Ph # (415) 390-3812

===============================================================================
"The pain of death is the pain of birth,
and the pain of birth is the pain of death." -- M. Scott Peck

 
 
 

Maximum number of table names in a query

Post by Jon F. Ros » Thu, 27 Jan 1994 06:52:25


Randy Jordan writes

Quote:> Alexy Timonin writes

> > I started a quite large query and suddenly receive the
> > following error message:

> >>  Too many table names in the query.  The maximum
> >>  allowable is16.  (Msg 106, Level 15, State 1).

> > Why there is so small limit ? Of course I can decrease the
> > number of table names, but I'd like to know is this constant
> > limitation?

> If you need to join more than 16 tables together, reexamine you
> query requirements... failing this create views of several tables
> and then join the views together.

There is indeed a limit of 16 tables per query in Sybase 4 (I do  
not know about Sybase 10.

(Sidenote: What happened to Sybase 5, 6, 7, 8 and 9? :-)  Maybe  
this was just a marketing hype way for Sybase to leapfrog Oracle in  
its release number in a single bound :-)

Why Sybase chose this number eludes me.  Moreover, I have not done  
any experimentation to determine whether this is in a single FROM  
clause or in the entire query.   Randy's comment about reexamining  
query requirements if you need to use more than 16 tables seems  
somewhat kneejerk.  In a well-designed relational database, there  
will often be a number of smaller "satellite" tables that surround  
the main tables.  If you have several major tables, the need to do  
a join between several major tables and most or all of the  
satellite tables often comes up.  If in addition, you have some  
subqueries that look at data in other tables, this 16 table limit  
can indeed be a severe restriction.

I would agree that accessing lots of tables can cause performance  
hits, but saying that someone should reevaluate their performance  
expectations in light of using more than 16 tables is far different  
from suggesting that someone reevaluate their query requirements.  
What I *want* and *need* to do is much different than what I might  
expect in terms of performance from trying to do it.  

I have always believed that, with the exception of true physical  
constraints (like available memory, real or virtual, and the size  
of atomic data values, etc), programs should be written with no  
restrictions or at least none that can't be removed with by setting  
the appropriate parameters.  16 is WAY TO LOW for any reasonable  
program.  If the number was 32,768, I might understand (they are  
using a small int as an identifier for objects used in a query).  
But there seems to be no justifiable reason for a restriction of 16  
except lazy architecture and implementation.  This sounds something  
like a fixed array somewhere in the code (like "int tables[16]")  
which simply should not be done in good system software (this  
should be a dynamically expandable array or list or something that  
doesn't impose an artifical limit).  

BTW, I do not believe the view approach will help, either, because  
most databases effectively merge views and select statements  
together, resulting in a single execution plan which includes all  
the tables from the view (with some minor differences).  

Jon Rosen

 
 
 

Maximum number of table names in a query

Post by Wild » Thu, 27 Jan 1994 00:31:55



> Hello,

> I started a quite large query and suddenly receive the following error message:

> >   Too many table names in the query.  The maximum allowable is 16.
> >   (Msg 106, Level 15, State 1).

> Why there is so small limit ? Of course I can decrease the number of table names,
> but I'd like to know is this constant limitation?

> I use MS SQL Server 4.20.00.

>                Thank you.

> |-------------------------------------------------|
> |   Alexy Timonin |  Phone      (095) 166-1931    |
> |   Laid Co.      |  Fax        (095) 165-0254    |

> |   Russia        |                               |
> |-------------------------------------------------|

This - 16 - is a hard rule within the SQL Server.  There is no parameter
within server which will allow you to change this.
 
 
 

Maximum number of table names in a query

Post by Randy Jord » Fri, 28 Jan 1994 08:47:40


|>Why Sybase chose this number eludes me.  Moreover, I have not done  
|>any experimentation to determine whether this is in a single FROM  
|>clause or in the entire query.   Randy's comment about reexamining  
|>query requirements if you need to use more than 16 tables seems  
|>somewhat kneejerk.  In a well-designed relational database, there  
|>will often be a number of smaller "satellite" tables that surround  
|>the main tables.  If you have several major tables, the need to do  

        Ok, My Knee has been jerked, (is that the same as having my leg pulled?)

        In almost every database I have observed two things are clear:

        1) the tables need to be MORE normalized...too much replicated data.

        2) The tables are OVER normalized and too complex to manage.

        In complex databases the limit of six* table joins IS too small
        but hey! I was only giving a work around to the limit. If I were
        building the schema for my users I would create Stored Procedures, or
        create commonly used VIEWS of the data FOR my user so complex table
        joins were more user friendly. Then examine the type of querys that
        my users needed and flatten the schema to produce a more pratical
        schema.

RJ
--
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
        To Thyne Own Self be true...
                                     myne opinions are.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

 
 
 

Maximum number of table names in a query

Post by Rob Sarge » Fri, 28 Jan 1994 08:58:46


   I started a quite large query and suddenly receive the following error message:

   >   Too many table names in the query.  The maximum allowable is 16.
   >   (Msg 106, Level 15, State 1).

   Why there is so small limit ? Of course I can decrease the number of table names,
   but I'd like to know is this constant limitation?

I would think the performance would suck anyway.
rjs

--
Rob Sargent                      s-mail: Dept. of Human Genetics    

phone: (801) 585-3388                    Eccles Genetics Bldg
phax :          -3833                    Salt Lake City, Utah  84112

 
 
 

Maximum number of table names in a query

Post by Bill Hodgson SYSTEMS PROGRAMMER LDN X24 » Thu, 27 Jan 1994 22:31:43



Quote:>>   Too many table names in the query.  The maximum allowable is 16.
>>   (Msg 106, Level 15, State 1).

that's the limit. rtfm, as they say.