Re: Need help!! Sql to Informix Migration

Unholy said: > And what about the checkpoint, LRU_MAX_DIRTY and LRU_MIN_DIRTY Twiddling with those is unlikely to give you an order of magnitude performance improvement. Might be useful to see the schema of the tables and indexes involved and some of the SQL. -- Bye now, Obnoxio "C'est pas parce qu'on n'a rien ' dire qu'il faut fermer sa gueule" - Coluche "I'm trying to see things your way, but I can't get my head up my ass" - JCH "Ogni uomo mi guarda come se fossi una testa di cazzo" - Marco Travel broadens a person. You look as if you have been all over the world. I went to the airport to check in and they asked what I did because I looked like a terrorist. I said I was a comedian. They said, "Say something funny then." I told them I had just graduated from flying school. -- Ahmed Ahmed http://i2.photobucket.com/albums/y41/Obnoxio/thinkIfoundtheproblem.jpg sending to informix-list

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.