Hi Everybody :

We had an external In-House Application, this application needs to "read" some tables (tdinv700,ticpr300) from our ERP (BaanIVC4). But the performance its really bad!!! . We made some versions of Store Procedures, looking for improve performance of querys, without better results...
Were not using indexes... if we create indexes over this tables (i'm sure about it...) the performance will be increased!??...

Here the questions :

Somebody has created indexes over baan tables??
This represent a risk for the ERP operation ??
Do you have a "Case of study" about this ??

Our DB :Informix IDS 9.4/Level 1

Thanks for your help...

I have never tried it but I would think it would be ok with a level 2 driver but I am not sure if it would be safe with a level 1 probably best to check with baan support.
There is one way to do it using the existing baan features
don't know if this will help with performance as it might transfer the performance hit to baan but one option would be to use exchange with audit on the tables then have a scheduled stored procedure that imports the exchange files into a new set of tables then have the external In-House Application use the new tables and you can put what ever index you want.

We're also on informix, also using the level 1 driver. We probably will not move to level 2, because our tables are too big to get them converted in a reasonable time.

Converting from level 1 to 2 - or - adding an index to an existing table normally means an automated process of 1. dump the data to a file, 2. do the conversion and 3. read it in again (to build the indexes, or to remove the hash fields). That might be a big act for your machine or lead to a long down-time.

After adding an index, you possible have better performance for a specific way to read. But keep in mind that - for level 1 - it means that your data tables becom bigger (an additional hash field), so the total performance on that table might go down.

Also, be careful with the design of your new (unique) indext; use "enough" fields (at least all fields of index 1), otherwise you will loose data when loading in the table.

Hi,

We had the same problem when we used MS-Word to generate serial letters based on Baan Customer master tccom010 using an ODBC connection to Informix. After adding an Index in Informix for customer field the whole thing was speeded up to an acceptable level. This was a level 1 driver with database indexes on hash fields.

Creating indexes is too easy in my opinion. The problem is that you are likely to end up with a lot of them.

I would be interested to know how many records the base tables have and just how long it takes to extract data. In our environment with several million records in tdinv700 the run time is generally acceptable.

The tables you have mentioned all have item number as high level key whereas you probably want to select by data range. If data up to prior day was what you are interested in the one approach would be to run an overnight job to build/maintain a date-item cross reference table that could be used by all sessions that need to to access any of the tables whose keys are not date ordered

tables inv700 19.5 Millions of records.
cpr300 5.6 Millions of records.

I'll try your solution with the cross reference table...

Thks tjbyfield for your advise...