linked table performance issues? (1 Viewer)

[songofsolon]

Greetings all,

I have been working over the past year on developing a single-user access app based on data imported three or four times a year from an Informix db. Ideally, this data would be live, and as of this week I finally have the tools at my disposal to make that happen. However, the performance hit has been so great as to make it almost not worthwhile. I'm having the devil's own time figuring out the precise cause.

In order to get the data I need, I have to link 8 different informix tables (some quite large) and query the results. This query includes some calculated fields, though no aggregates, and a subquery that uses a couple of outer joins.

The main form related to this data is based on a query that futher restricts returns from the above query. The main functionality of this form depends on adding and deleting records from a local table through vb.

Using the linked tables and queries instead of the old static table and queries has slowed down the form load to nearly 1 minute, and any actions within the form will then take between 10 to 30 seconds, especially adding or deleting records to the local table.

I have compiled, analyzed, etc. using the optimization suggestions on this forum, but it hasn't seemed to improve any. I haven't yet tried the dummy table/hidden form tip to force the db to stay open, since there seems to be such reluctance on this one and I'm not sure how it would affect other users of the informix data in the company. Would it even help in my situation, since the performance issues are still so great even when the form is open?

Any thoughts on what in particular might be causing the bottleneck? Does it seem like there might be a way to resolve the performance issues and keep the live data, or should I try a semi-live method instead? Thanks very much for any advice you can give.

 

[namliam]

Try using pass-through querys instead of linked tables to get your data from informix. This way the query is executed on the informix server, which is bound to be faster than doing the processing in you own local machine.

Greetz

 

[Pat Hartman]

I have no experience with linking to Informix databases. Jet normally would parse your queries and send them to the server for processing rather than importing the data and processing locally - a pass-through query is slightly more efficient but not enough to justify converting everything. The only savings is that Jet does less work before sending off the query and the server does less work because the stored procedure has already been pre-processed.

Possible problems:
1. Joining an Informix table to a local Jet table. Although this can be done and will usually produce an updatable recordset, it can slow down processing if it forces Jet to request all the data from the server table and do the joining locally. A possible solution is to break the one query into two. Make a query that selects only server-side data and applies selection criteria to reduce the number of rows selected. Create a second query that joins the first query to the local table. Jet should send the embedded query to the server and finish the process locally by joining to the Jet table. If you can't apply the selection criteria to the linked table because the join is what is doing the limiting, you should move the local table to the server so that the join can be performed on the server.
2. Sub queries are inefficient in Jet and may be in Informix also. The only solution here is a stored procedure since you can't use the embedded query trick to get Jet to do things your way. The ODBC driver will attempt to create a single query and so it will create a sub-select even if you use embedded queries.

 

[songofsolon]

Brilliant, folks, thanks so much. I ended up using bits of both suggestions: broke up the queries, per Pat, and used pass-through to access the informix data, per namliam. So far, it seems to work beautifully. I can't thank you both enough.