Re: Need help!! Sql to Informix Migration

The best that can be expected from this sort of cry for help is a handful of things to check. There is no onconfig file that will provide the best performance for all occasions, platforms, and applications. Our app needs different configurations for different tasks, and that is with the same data! Let me illustrate with a recent example. I am not a SQL Server expert by any means. However, we recently had a group of contractors who claim to be develop an application for use with our IDS-based app. Their original design was for SQL Server and they planned to replicate the information in our IDS database, but, when that fell through, they eventually put virtually the same schema into IDS. We ran into a few problems with stored procedures where they were trying to do things the wrong way for IDS. However, the kicker came when they got everything working and tried it with a customer's data. Performance was awful. They tried a lot of things before I got involved, wasted a lot of time trying to tweak onconfig parameters, etc., to no gain. It was kind of like what is going on with Unholy's issue, here. The first thing I did was to turn SET EXPLAIN ON and run the query. To my surprise, I found they were selecting from a view into a view into a view into a very large table. This was causing the optimiser to build temp tables for the views and perform sequential scans on a large number of records in order to return one row. We then began a discussion of views which surprised them, as SQL Server pros, and myself, as an IDS head. They insist, and I have no reason to doubt them, that their method of using views is the recommended way in SQL Server to boost performance. However, in IDS, it was a huge drag on the system. Making their top-level view directly onto the table instead of on intermediary views dropped run time from minutes to seconds as the optimiser used the indexes to find the one row to return. The point of this story is that some of the performance enhancing tricks they were using were completely inappropriate in IDS. What made things worse were the assumptions being made and the difficulty in communicating between our worlds. Unholy needs a lot more information to diagnose where the performance lag is. SET EXPLAIN is a good place to start. If you are on IDS 9, you can set it dynamically with onmode. Use onstats to check your system performance before you try to monkey with the onconfig. Read the Performance Guide. Not only will it help you fix the problems, it will also help you diagnose them. Don't assume that what works in SQL Server is the best way to work in IDS. http://publib.boulder.ibm.com/epubs/html/25122960/25122960tfrm.htm When you find a more specific problem, then comp.databases.informix will be a much more helpful resource. Sincerely, Christopher Coleman President Kansas City Informix Users Group www.iiug.org/kciug Database Analyst Medication Management Mediware Information Systems, Inc.