 | |
select statement slow on 1 machine and quick on another machine (ASA9.0.2 different builds)
sybase.public.sqlanywhere.general sybase.public.sqlanywhere.general
 |
| | Thread Tools | Display Modes |
#1
 
| |||
| |||
|
select statement slow on 1 machine and quick on another machine (ASA9.0.2 different builds) -
11-20-2007
, 06:47 AM
#2
| |||
| |||
|
|
The problem in the slow 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> ] 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: 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> ] |
#3
| |||
| |||
|
|
I know the problem is in the slow plan, but all 3 databases have the same view and stored procedure. Because the select statement is created in a stored procedure it is not possible to generate a graphical plan in I-SQL. Is there another way to generate a graphical plan? Currently I'm working on a change on one of the tables in the view. I added an additional field and a new index on that field to improve the speed of the view so the new index is used in stead of the current one Glenn Paulley wrote: The problem in the slow 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> ] 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: 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
|
« Previous Thread
|
Next Thread »
| Thread Tools | |
| Display Modes | |
Linear Mode
| |
Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.