Re: 7.x -> 5.x joins

> Ron M. Flannery wrote: > > > I'm currently migrating a bunch of 5x databases to 7x. We're migrating > > one database at a time. The old databases are running on a > > Compaq Sypro running SCO and Online version 5.00.UD6. The new databases [SNIP] There are actually two problems. One is the network communication protocol used by 5.00. The other is the limited capabilities of 5.00 to participate in remote joins. The network communications were streamlined in 5.05 (and fixed in 5.06) and again in 7.10 or 7.11. The 5.00 engine you are using is the oldest and slowest of the Informix I-Net protocols. Although all Informix versions (ignoring such a bug in 5.05) are backward compatible and able to talk to older I-Net versions such as 5.00, they do so by reverting to the older protocols. The 5.00 protocol had a great deal of redundant communications going on and eliminating all that extraneous handshaking was the biggest change in 5.05/5.06. Second, as long as the join only includes data in tables on 5.00 the join and filtering is performed there and only results are returned to 7.24. In this case the slower network protocol has less effect. Once you include a 7.24 table the engine requests the data from 5.00 and performs the join locally. The 5.xx protocols did permit filtering on the remote host for remote joins but could not perform any joining on the remote host so all of the data for the join has to be transmitted to the 7.24 engine for joining together and with the 7.24 local table results. A native 7.xx remote join involving a join between tables on the remote system performs as much processing as possible on the remote system and sends only results and that with the faster 7.xx I-Net protocols. You just need to restructure the request to take this into consideration, towit: You should be able to speed things up by performing the join on all of the native 5.00 tables only and saving the results in a temp table locally on the 7.24 instance. This will move all processing onto the 5.00 engine and only return results to the local temp table. Then join the temp table to the local table. If you package this query as a stored procedure you can modify it as the tables/databases migrate from one engine to the other to optimize performance and hide the conversion. Art S. Kagel