I've got an ASP web page that spits out sales order data using ODBC etc to get it direct from informix.

The query ...

SELECT * FROM ttdsls040003 WHERE t_orno=340101

works fine. but I don't know how to select based on an index (which I would expect to be a lot quicker?).

I've tried ...

SELECT * FROM ttdsls040003 WHERE t_index1=340101

but this doesn't work.

How do you select by an index or doesn't it matter?

Hi,

If you are using Informix, then your chances are, that you use the level 1 driver of Baan.

in this case, the indexes are not defined on the actual columns, but on some "hidden" hash columns, which are no good for you.

If this is so, then

a) avoid any inserts via ODBC

b) be very carefull with updates

c) for speedup define indexes manually in informix!

Regards

Markus

In the Baan SQL you seen indeed a lot of

WHERE table_index1 = {:X}

but when you look to the database SQL you will see that the index code is split up to the particular index fields. The database will (in most cases) be smart enough to select the right index when using a Level 2 database. But when using a Level 1 database it is advised to create the needed indexes manually so the database can retrieve the data the easiest.

Some tips when using Level 1:
- Only create indexes were needed (large tables with selects on a small range)
- Recreate indexes after a rebuild of the table (bdbreconfig, export - import)

Hope this helps,
Dick