AllExperts > Ingres 
Search      
Ingres
Volunteer
Answers to thousands of questions
 Home · More Ingres Questions · Answer Library  · Encyclopedia ·
More Ingres Answers
Question Library

Ask a question about Ingres
Volunteer
Experts of the Month
Expert Login

Awards

About Us
Tell friends
Link to Us
Disclaimer

 
 
 
 
About Jean-Pierre Zuate
Expertise
Any questions about database Ingres (owned by Computer Associates then Ingres Corp) : - Ingres 6.4 - OpenINGRES from 1.0 to 2.0 - IngresII from 2.0 to 2.6 - Ingres R3, Ingres 2006 (Open Source version) - All tool around Ingres : ABF, Report Writer, Replicator, OpenROAD (3.5 to 2006), Ingres/NET Ingres/STAR, ...

Experience
16 years of computing experience as :
- AS400 programmer
- AIX / Ingres administrator and developer (OpenROAD and korn shell)
- Ingres DataBase Administrator
- Ingres expert - Data modelisation - ETL - Reporting - Many of Computer Associates sofwares - ITIL / CMDB / Change Management

Organizations
http://lafageconseil.fr

 
   

You are here:  Experts > Computing/Technology > Databases > Ingres > 10 tables query

Ingres - 10 tables query


Expert: Jean-Pierre Zuate - 2/28/2005

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

Add to this Answer   Ask a Question


 
User Agreement | Privacy Policy | Kids' Privacy Policy | Help
Copyright  © 2008 About, Inc. AllExperts, AllExperts.com, and About.com are registered trademarks of About, Inc. All rights reserved.