SQL Anywhere database - SQL question...

Author

SQL question...

Troy

2005-10-27, 7:40 am

We are building an enormous SELECT that will be used to create a View. It
gathers a ton of information from numerous tables.

Currently we have a table used 4 times using alias names. When we add the
fifth alias for the table and run the Select through ISQL the query runs
forever. Remove the one of the earlier alias' and the query runs fine even
with the 5th alias included.

Is therea limit to the size of the SQL statment or the number of alias' you
can use for a table?

Using ASA 8.0.2.

Troy

Breck Carter [TeamSybase]

2005-10-27, 7:40 am

There are effectively no limits.

Check to make sure you don't have a cartesian product; check that all
the join predicates are correct.

Have a look at the graphical plan for the query.

Breck

On 24 Oct 2005 07:34:22 -0700, "Troy" < coombst@NOSPAMquorum
is.com>
wrote:

>We are building an enormous SELECT that will be used to create a View. It
>gathers a ton of information from numerous tables.
>
>Currently we have a table used 4 times using alias names. When we add the
>fifth alias for the table and run the Select through ISQL the query runs
>forever. Remove the one of the earlier alias' and the query runs fine even
>with the 5th alias included.
>
>Is therea limit to the size of the SQL statment or the number of alias' you
>can use for a table?
>
>
>Using ASA 8.0.2.
>
>Troy
>

--
SQL Anywhere Studio 9 Developer's Guide
Buy the book: http://www.amazon.com/exec/obidos/A...7/risingroad-20
bcarter@risingroad.com
RisingRoad SQL Anywhere and MobiLink Professional Services
www.risingroad.com

Glenn Paulley

2005-10-27, 7:40 am

Currnetly, there is no hard limit for the size of an SQL statement or the
number of tables in a query. Keep in mind that using multiple aliases on
a table in a query results in a query with the same complexity as if all
of the tables were separate base tables.

Without seeing the query, I would guess that your performance problem is
due to the nature of the query itself - particularly the availability of
indexes and the nature of the join conditions. Occassionally we see a
"performance problem" come to the lab where the issue is actually a
missing join condition in the SQL statement, which results in a Cartesian
product between two (or more) tables and can increase the number of rows
in an intermediate or final result by several orders of magnitude.

Glenn

"Troy" < coombst@NOSPAMquorum
is.com> wrote in
news:435cf0ee$1@foru
ms-2-dub:

> We are building an enormous SELECT that will be used to create a View.
> It gathers a ton of information from numerous tables.
>
> Currently we have a table used 4 times using alias names. When we add
> the fifth alias for the table and run the Select through ISQL the
> query runs forever. Remove the one of the earlier alias' and the
> query runs fine even with the 5th alias included.
>
> Is therea limit to the size of the SQL statment or the number of
> alias' you can use for a table?
>
>
> Using ASA 8.0.2.
>
> Troy
>
>
>

--
Glenn Paulley
Research and Development Manager, Query Processing
iAnywhere Solutions Engineering

EBF's and Patches: http://downloads.sybase.com
choose SQL Anywhere Studio >> change 'time frame' to all

To Submit Bug Reports: http://casexpress.sybase.com/cx/cx.stm

SQL Anywhere Studio Supported Platforms and Support Status
http://my.sybase.com/detail?id=1002288

Sponsored Links