ASE 12 and number of tables in a query

Has ASE 12 expanded the number of tables you can have in a query?  It was my
understanding that the limit was going to go up from 16.  I can in other
platforms including SQL Anywhere but I am still stuck at 16 in ASE.  Any
ideas when this will be enhanced?


0
EMichie
6/4/2002 4:08:07 PM
sybase.ase.general 8655 articles. 0 followers. Follow

6 Replies
854 Views

Similar Articles

[PageSpeed] 9
Get it on Google Play
Get it on Apple App Store

In searching Sybase.com I found a white paper that states the the number of
tables in a query is 50 (http://www.sybase.com/detail/1,6904,1013024,00.html
section1.3.1 Database Statistics).  But the Transact-SQL manual for 12.5
states that the maximum number of tables for query is 16 (Chapter 2
Specifiying the from clause section).  Which is it?  Are we to believe the
"marketing material" or the technical manuals?


"EMichie" <e_michie@msn.com> wrote in message
news:IfljvN#CCHA.272@forums.sybase.com...
> Has ASE 12 expanded the number of tables you can have in a query?  It was
my
> understanding that the limit was going to go up from 16.  I can in other
> platforms including SQL Anywhere but I am still stuck at 16 in ASE.  Any
> ideas when this will be enhanced?
>
>


0
EMichie
6/4/2002 4:48:56 PM
"EMichie" <e_michie@msn.com> wrote in message
news:Ke9Kjk#CCHA.201@forums.sybase.com...
> In searching Sybase.com I found a white paper that states the the
number of
> tables in a query is 50
(http://www.sybase.com/detail/1,6904,1013024,00.html
> section1.3.1 Database Statistics).  But the Transact-SQL manual for
12.5
> states that the maximum number of tables for query is 16 (Chapter 2
> Specifiying the from clause section).  Which is it?  Are we to
believe the
> "marketing material" or the technical manuals?
>

You are right and the doc isn't. The maximum number of user tables in
a join is 50 as of ASE 12.0; in previous versions it's 11.9 (note that
it's actually a limit per statement, not for a join per se).
Additionally, there may be 14 worktables per statement (vs. 12 in
pre-12.0).
Note that it is *very* unwise to use large numbers of table in a
single statement, as it may easily lead to sub-optimal query plans
being generated.

HTH,

Rob
----------------------------------------------------------------------
Rob Verschoor

Certified Sybase Professional DBA for ASE 12.0/11.5/11.0

Author of "The Complete Sybase ASE Quick Reference Guide"
Online orders accepted at http://www.sypron.nl/qr

email mailto:rob@DO.NOT.SPAM.sypron.nl.REMOVE.THIS.DECOY
WWW  http://www.sypron.nl
snail Sypron B.V., P.O.Box 10695, 2501HR Den Haag, The Netherlands
----------------------------------------------------------------------

0
Rob
6/4/2002 5:10:01 PM
The technical manuals do sometimes have errors in them, particularly
when changes are made, sometimes not all the references to the previous
state of things are caught and updated.  The "What's New" manual does
list the change in limits.

When I am in some doubt, I tend to give it a try and put my trust 
more in the documentation that agrees with the behavior I can verify
by experimentation.

-bret


EMichie wrote:
> 
> In searching Sybase.com I found a white paper that states the the number of
> tables in a query is 50 (http://www.sybase.com/detail/1,6904,1013024,00.html
> section1.3.1 Database Statistics).  But the Transact-SQL manual for 12.5
> states that the maximum number of tables for query is 16 (Chapter 2
> Specifiying the from clause section).  Which is it?  Are we to believe the
> "marketing material" or the technical manuals?
> 
> "EMichie" <e_michie@msn.com> wrote in message
> news:IfljvN#CCHA.272@forums.sybase.com...
> > Has ASE 12 expanded the number of tables you can have in a query?  It was
> my
> > understanding that the limit was going to go up from 16.  I can in other
> > platforms including SQL Anywhere but I am still stuck at 16 in ASE.  Any
> > ideas when this will be enhanced?
> >
> >
0
Bret
6/4/2002 5:15:40 PM
Yo stated that 12.0 can have 50.  I am using 12.0 and get an error that I am
over 16.  Is it 12.0 or 12.5 that the change is in?



> You are right and the doc isn't. The maximum number of user tables in
> a join is 50 as of ASE 12.0; in previous versions it's 11.9 (note that
> it's actually a limit per statement, not for a join per se).
> Additionally, there may be 14 worktables per statement (vs. 12 in
> pre-12.0).
> Note that it is *very* unwise to use large numbers of table in a
> single statement, as it may easily lead to sub-optimal query plans
> being generated.
>
> HTH,
>
> Rob
> ----------------------------------------------------------------------
> Rob Verschoor
>
> Certified Sybase Professional DBA for ASE 12.0/11.5/11.0
>
> Author of "The Complete Sybase ASE Quick Reference Guide"
> Online orders accepted at http://www.sypron.nl/qr
>
> email mailto:rob@DO.NOT.SPAM.sypron.nl.REMOVE.THIS.DECOY
> WWW  http://www.sypron.nl
> snail Sypron B.V., P.O.Box 10695, 2501HR Den Haag, The Netherlands
> ----------------------------------------------------------------------
>


0
EMichie
6/4/2002 5:47:29 PM
12.0.  From the 12.5 "What's New" chapter on changes from 12.0:

Number of tables in a query

The number of user tables and worktables that can
be used for a query has been increased from
earlier versions of Adaptive Server. In previous
versions, a query and any views used in a query
could reference at most 16 user tables, and the
number of worktables that could be created was
12. The limits are now 50 user tables and 14
worktables. Limits on the number of referential
integrity checks allowed (192) and the number of
subqueries allowed in a query (16) have not
changed.

You may need to reset the configuration parameter
number of aux scan descriptors if you increase the
number of tables referenced in queries.

For information on how queries are optimized
when large numbers of tables are included, see
Chapter 7, "Access Methods and Query Costing
for Joins and Subqueries," in the Performance
and Tuning Guide.

For information on resetting scan descriptors, see
number of aux scan descriptors in Chapter 17,
"Setting Configuration Parameters," in the System
Administration Guide

EMichie wrote:
> 
> Yo stated that 12.0 can have 50.  I am using 12.0 and get an error that I am
> over 16.  Is it 12.0 or 12.5 that the change is in?
> 
> > You are right and the doc isn't. The maximum number of user tables in
> > a join is 50 as of ASE 12.0; in previous versions it's 11.9 (note that
> > it's actually a limit per statement, not for a join per se).
> > Additionally, there may be 14 worktables per statement (vs. 12 in
> > pre-12.0).
> > Note that it is *very* unwise to use large numbers of table in a
> > single statement, as it may easily lead to sub-optimal query plans
> > being generated.
> >
> > HTH,
> >
> > Rob
> > ----------------------------------------------------------------------
> > Rob Verschoor
> >
> > Certified Sybase Professional DBA for ASE 12.0/11.5/11.0
> >
> > Author of "The Complete Sybase ASE Quick Reference Guide"
> > Online orders accepted at http://www.sypron.nl/qr
> >
> > email mailto:rob@DO.NOT.SPAM.sypron.nl.REMOVE.THIS.DECOY
> > WWW  http://www.sypron.nl
> > snail Sypron B.V., P.O.Box 10695, 2501HR Den Haag, The Netherlands
> > ----------------------------------------------------------------------
> >
0
Bret
6/4/2002 5:56:23 PM
That's weird. Can you post the complete query, the complete error
message and your @@version string ?

Rob V.

"EMichie" <e_michie@msn.com> wrote in message
news:$h85QF$CCHA.70@forums.sybase.com...
> Yo stated that 12.0 can have 50.  I am using 12.0 and get an error
that I am
> over 16.  Is it 12.0 or 12.5 that the change is in?
>
>
>
> > You are right and the doc isn't. The maximum number of user tables
in
> > a join is 50 as of ASE 12.0; in previous versions it's 11.9 (note
that
> > it's actually a limit per statement, not for a join per se).
> > Additionally, there may be 14 worktables per statement (vs. 12 in
> > pre-12.0).
> > Note that it is *very* unwise to use large numbers of table in a
> > single statement, as it may easily lead to sub-optimal query plans
> > being generated.
> >
> > HTH,
> >
> > Rob
>
> --------------------------------------------------------------------
--
> > Rob Verschoor
> >
> > Certified Sybase Professional DBA for ASE 12.0/11.5/11.0
> >
> > Author of "The Complete Sybase ASE Quick Reference Guide"
> > Online orders accepted at http://www.sypron.nl/qr
> >
> > email mailto:rob@DO.NOT.SPAM.sypron.nl.REMOVE.THIS.DECOY
> > WWW  http://www.sypron.nl
> > snail Sypron B.V., P.O.Box 10695, 2501HR Den Haag, The Netherlands
>
> --------------------------------------------------------------------
--
> >
>
>


0
Rob
6/4/2002 6:50:02 PM
Reply: