Re: select statement slow on 1 machine and quick on another machine(ASA9.0.2 different builds)

#1

The problem in the slow plan

Quote:

PLAN> Ord<seq> JNLO OrdPFr<Place> JNLO OrdPTo<Place> Equ<Equipment> JNL
AC<seq> JNL OM<OrdModality> JNLO M<Modality> JNLO Dep<Address> JNLO
OMP<R_OrdModality> JNLO PTo<Place> JNLO PFr<Place> JNLO OP<OrdProduct
JNLO Cus<Address> JNL Pro<Product> JNLO CouFr<Country> JNLO
CouTo<Country> JNLO TU<TransporTUnit> JNLO Plb<Planboard> : GrByS[
OrdProduct<R_Orders> ]

is likely the sequential scan of the temp table AC on the right-hand
side of the nested-loop join

..... Equ<Equipment> JNL AC<seq> ....

but without additional detail, such as a graphical plan with statistics,
it is impossible to explain why the optimizer picked that strategy.

Glenn

Frank Vestjens wrote:

Quote:

Hi,

I'm running a select statement on a view joined with a temp table.
In 2 databases it's running very fast and in 1 database it's slow.
I've found that the difference is in the planused on the different
machines.

The 2 machines running fast use ASA 9.0.2 build 3456 and 9.0.2 build
2451. The one that is slow uses 9.0.2 build 3420

The select statement looks like:

select A.*
from View A join TempTable AC on A.Id = AC.Id

The machines where the database is quick is use the following plan:

PLAN> AC<seq> JNL OM<OrdModality> JNLO M<Modality> JNLO Dep<Address
JNLO OMP<R_OrdModality> JNLO PTo<Place> JNLO Pfr<Place> JNLO
OP<OrdProduct> JNLO Cus<Address> JNLO Pro<Product> JNLO CouFr<Country
JNLO CouTo<Country> JNLO TU<TransportUnit> JNLO Plb<Planboard> JNL
Ord<Orders> JNLO OrdPFr<Place> JNLO OrdPTo<Place> JNLO Equ<Equipment> :
GrByS[ OrdProduct<R_Orders> ]

AC is the temp table and the view contains all the other tables.

The machine where the database is slow uses the following plan:

PLAN> Ord<seq> JNLO OrdPFr<Place> JNLO OrdPTo<Place> Equ<Equipment> JNL
AC<seq> JNL OM<OrdModality> JNLO M<Modality> JNLO Dep<Address> JNLO
OMP<R_OrdModality> JNLO PTo<Place> JNLO PFr<Place> JNLO OP<OrdProduct
JNLO Cus<Address> JNL Pro<Product> JNLO CouFr<Country> JNLO
CouTo<Country> JNLO TU<TransporTUnit> JNLO Plb<Planboard> : GrByS[
OrdProduct<R_Orders> ]

--
Glenn Paulley
Director, Engineering (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://case-express.sybase.com

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

Whitepapers, TechDocs, and bug fixes are all available through the iAnywhere
Developer Community at www.ianywhere.com/developer

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Re: select statement slow on 1 machine and quick on another machine(ASA9.0.2 different builds)

sybase.public.sqlanywhere.general sybase.public.sqlanywhere.general

Re: select statement slow on 1 machine and quick on another machine(ASA9.0.2 different builds) - 11-20-2007 , 08:43 AM