Sybase NNTP forums - End Of Life (EOL)

The NNTP forums from Sybase - forums.sybase.com - are now closed.

All new questions should be directed to the appropriate forum at the SAP Community Network (SCN).

Individual products have links to the respective forums on SCN, or you can go to SCN and search for your product in the search box (upper right corner) to find your specific developer center.

maximum practical joins?

3 posts in Windows NT Last posting was on 2000-02-02 01:11:09.0Z
Vanessa Sikkema Posted on 2000-01-31 20:20:45.0Z
From: "Vanessa Sikkema" <sikkema@bsr.com>
Subject: maximum practical joins?
Date: Mon, 31 Jan 2000 15:20:45 -0500
Lines: 9
X-Newsreader: Microsoft Outlook Express 4.72.3110.5
X-MimeOLE: Produced By Microsoft MimeOLE V4.72.3110.3
Message-ID: <MBKG2jCb$GA.85@forums.sybase.com>
Newsgroups: sybase.public.sqlserver.nt
NNTP-Posting-Host: 198.17.85.136
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.sqlserver.nt:2603
Article PK: 1090058

I have been told that although Sybase can handle up to 16 joins in a select
statement, that the practical limit is 6-8 and any more than that and the
optimizer becomes inefficient. Has anyone out there run into a similar
practical limit? Assume large amounts of data in each table.

TIA
-Vanessa


Joseph Campbell Posted on 2000-02-01 14:06:37.0Z
Message-ID: <3896E86D.412EB693@voicenet.com>
Date: Tue, 01 Feb 2000 09:06:37 -0500
From: Joseph Campbell <moandjo@voicenet.com>
Organization: XtendTech Inc.
X-Mailer: Mozilla 4.7 [en] (WinNT; U)
X-Accept-Language: en
MIME-Version: 1.0
Subject: Re: maximum practical joins?
References: <MBKG2jCb$GA.85@forums.sybase.com>
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Newsgroups: sybase.public.sqlserver.nt
Lines: 16
NNTP-Posting-Host: 209.71.19.15
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.sqlserver.nt:2599
Article PK: 1090052

No - In fact for some time while working on a project we had joins and groups
that were one less then this limit on millions of rows of data per table and
had no problem what so ever. 11.5.1 Server with Par query on.

Joe Campbell
Xtendtech Inc.

Vanessa Sikkema wrote:

> I have been told that although Sybase can handle up to 16 joins in a select
> statement, that the practical limit is 6-8 and any more than that and the
> optimizer becomes inefficient. Has anyone out there run into a similar
> practical limit? Assume large amounts of data in each table.
>
> TIA
> -Vanessa


Bret Halford Posted on 2000-02-02 01:11:09.0Z
Message-ID: <3897842D.4B17B2F5@sybase.com>
Date: Tue, 01 Feb 2000 18:11:09 -0700
From: Bret Halford <bret@sybase.com>
Organization: Sybase, Inc.
X-Mailer: Mozilla 4.5 [en]C-CCK-MCD (WinNT; I)
X-Accept-Language: en
MIME-Version: 1.0
Subject: Re: maximum practical joins?
References: <MBKG2jCb$GA.85@forums.sybase.com> <3896E86D.412EB693@voicenet.com>
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Newsgroups: sybase.public.sqlserver.nt
Lines: 35
NNTP-Posting-Host: bret-pc.sybase.com 157.133.80.211
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.sqlserver.nt:2594
Article PK: 1090047

It all depends on what you mean by "inefficient". I believe the optimizer's
efficiency is fairly constant: it can analyze so many combinations per unit of
time. As you increase the number of tables, the combinations increase rapidly.
As you increase the table's size, the more important good optimization becomes.

ASE 12.0 allows you to join up to 50 tables. That might take hours or days or
weeks for the optimizer to process. However, another 12.0 feature called
abstract plans could mean that you only have to optimize the query once and
capture the plan for it, and use that plan for all similar queries in the future,
bypassing the optimizer - which is going to be pretty efficient!

-bret

Joseph Campbell wrote:

> No - In fact for some time while working on a project we had joins and groups
> that were one less then this limit on millions of rows of data per table and
> had no problem what so ever. 11.5.1 Server with Par query on.
>
> Joe Campbell
> Xtendtech Inc.
>
> Vanessa Sikkema wrote:
>
> > I have been told that although Sybase can handle up to 16 joins in a select
> > statement, that the practical limit is 6-8 and any more than that and the
> > optimizer becomes inefficient. Has anyone out there run into a similar
> > practical limit? Assume large amounts of data in each table.
> >
> > TIA
> > -Vanessa