Question Bon jour, Jean-Pierre,
Sorry, I don't understand the 'correct indices' part. Could you explain it a little more or, perhaps, add an example?
Another question. I've see that the optimizer timeout 'disappear' as I change the number of tables involved in the query (when I get off tables). What can be the cause of this?
Thanks.
-------------------------
Followup To
Question -
Have you any idea about how to improve the performance of a ten tables based query (with k-joins)?
Answer -
Hi Miguel,
Your question is in FoxPro folder, and I'am an Ingres expert. Hope this question is for Ingres ... :-)
You can use QEP (Query Execution Plan). Here is how you can turn it on :
sql your_db
set qep
\g
set optimizeonly
\g
your bif select here
\g
Performance can be improved by :
--------------------------------
- correct indices
- appropriates statistic data for the DBMS (taken by optimizedb command)
- reorganisation ad'hoc
Correct indices :
-----------------
When you join 2 or more tables, take care of the correct where clause. Columns are to be joined from the left to the right (ie if your key is col1, col2 and col3 the correct where clause will involve col1 and col2 but not col2 and col3 [miss col1 in that case)
Appropriate statistics :
------------------------
A good optimizedb command is :
optimizedb -zh -zk mydb
-zh to see histograms
-zk to ask optimizedb to take stats on key columns only
Reorganisation ad'hoc :
-----------------------
On regular basis, take care to pass a MODIFY on each table and perhaps a CREATE INDEX (because sometimes, depend on create index option, modify delete indices).
A good regular administrative task on Ingres can be :
1/ modify and create index (to eliminate hole in data pages)
2/ optimizedb to take correct stats
3/ sysmod to reoganize system data tables (destructured by optimizedb)
Hope this help,
Jean-Pierre Zuate
Answer Correct indices ...
When you access data through a SGBD/R, you "only" do join between columns. If you want your query to speed up, indices must exist when you run the query.
A short example ...
Suppose two tables, customer and bill.
Customer table look like this :
customer_number
customer_name,
customer_address
Bill table look like this :
bill_number
customer_number,
quantity_sum,
amount_sum
If, from the bill table, you need to know what are all the bill for one customer the query look like this :
select *
from bill
where customer_number = 'the requested number'
Correct indice mean if you don't have an object (an index in fact) well designed, you query can be very slow. In my example, the correct index is an index on the table bill for the column customer_number. In SQL something like :
CREATE INDEX bill_idx on bill (customer_number) with structure = hash
Performance depend on the pertinence of the columns involved in the index, the structure, and by all the parameter linked to the structure.
In Ingres, you have some differents structures, depend on the usage :
- heap : no sort here, no key. Fast for massive insert
- btree : good for partial key query. Very good if table have many update an few window for modify
- isam : same service as btree, but need to be reorganised (more) regulary
- hash : good for exact query (where each column of the key have an unique value in the query)
Now, my turn to have a question : what do you call "optimizer timeout" and how do you see it ?
