I have executed these two queries which is smilar to each other, time and again. I have obtain same unexpected result. My queries and questions is at below.
Thank in Advance
My Questions
-------------
1. 2.query is slower than 1.query. Why ?
2. Rushmore optimization of second query is executed wrongly. Why ?
Note that: A join between Kimlik and mhsb is created before mhsb and hplan table in second query. This prevent the rushmore engine to operate wrongly. To reach maxsimum query speed like in first query (0.01 seconds), the join between hplan and mhsb must be created before mhsb and kimlik.
3. How can I solve rushmore optimization problem ?
Note :
-----------
1: recods of table : mhsb: 131.171; kimlik: 18.104 ; hplan:5
2: Each table has primary index on tag "id" and in addition to hplan has a index on tag "exp"
3: Due to small file size of resulted cussor of query is not affected on query execuation time.
4: set("dele") is on where query execute
5: there isnt any index related to deleted records
---------------------
1. QUERY
---------------------
select * ;
from AtpNet!mhsb, ;
AtpNet!hplan ,;
atpnet!kimlik ;
where mhsb.hplan = hplan.id ;
and mhsb.kimlik = kimlik.id ;
and hplan.exp like 'Abone%'
Query Result :
Selected 5 records in 0.01 seconds
result of SYS(3054 12, "cMemvar") is :
1.Rushmore optimization level for table mhsb: none
2.Using index tag Exp to rushmore optimize table hplan
3.Rushmore optimization level for table hplan: partial
4.Rushmore optimization level for table kimlik: none
5.Joining table hplan and table mhsb using index tag Hplan
6.Joining intermediate result and table kimlik using index tag Id
---------------------
2. QUERY
---------------------
select mhsb.id ;
from atpNet!mhsb, ;
atpNet!hplan ,;
atpnet!kimlik ;
where mhsb.hplan = hplan.id ;
and mhsb.kimlik = kimlik.id ;
and hplan.exp like 'Abone%'
Query Result :
Selected 5 records in 3.17 seconds
result of SYS(3054 12, "cMemvar") is :
1.Rushmore optimization level for table mhsb: none
2.Using index tag Exp to rushmore optimize table hplan
3.Rushmore optimization level for table hplan: partial
4.Rushmore optimization level for table kimlik: none
5.Joining table kimlik and table mhsb using index tag Kimlik
6.Joining table hplan and intermediate result using temp index
<\PRE>