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)
