RE: Need help!! Sql to Informix Migration

you may need to capture the query plan by adding the {+ explain } optimizer hints in the queries, and then analyze the plan for any changes to the queries or to make sure if an index is needed. -----Original Message----- From: owner-informix-list@iiug.org [mailto:owner-informix-list@iiug.org]On Behalf Of Unholy Sent: Monday, April 18, 2005 3:10 AM To: informix-list@iiug.org Subject: Need help!! Sql to Informix Migration Hi, I have developed a program that runs under sql Server, and it runs really fast...... The problem is that now, I have to change the DBMS to Informix. I installed Informix under W2000, and also under W2003, and with it the program is really slow, as much as 10 times slower than when it was running under SQl. I think that Informix should be faster, and I think that i hace to condigure it, but i do not have so much experience as with sql... can you help me?? This is my ONCONFIG file #************************************************************************** # # INFORMIX SOFTWARE, INC. # # Title: onconfig.std # Description: Informix Dynamic Server Configuration Parameters # #************************************************************************** # Root Dbspace Configuration ROOTNAME rootdbs # Root dbspace name ROOTPATH C:\\IFMXDATA\\nomina\\rootdbs_dat.000 # Path for device containing root dbspace ROOTOFFSET 0 # Offset of root dbspace into device (Kbytes) ROOTSIZE 51200 # Size of root dbspace (Kbytes) # Disk Mirroring Configuration Parameters MIRROR 1 # Mirroring flag (Yes = 1, No = 0) MIRRORPATH D:\\IFMXDATA\\nomina\\rootdbs_mirr.000 # Path for device containing mirrored root MIRROROFFSET 0 # Offset into mirrored device (Kbytes) # Physical Log Configuration PHYSDBS rootdbs # Location (dbspace) of physical log PHYSFILE 2000 # Physical log file size (Kbytes) # Logical Log Configuration LOGFILES 7 # Number of logical log files LOGSIZE 2000 # Logical log size (Kbytes)LOG_BACKUP_MODE MANUAL # Logical log backup mode (MANUAL, CONT) # Diagnostics MSGPATH C:\\PROGRA~1\\Informix\\nomina.log # System message log file path CONSOLE C:\\PROGRA~1\\Informix\\connomina.log # System console message path # To automatically backup logical logs, edit alarmprogram.bat and set # BACKUPLOGS=Y ALARMPROGRAM C:\\PROGRA~1\\Informix\\etc\\alarmprogram.bat # Alarm program path TBLSPACE_STATS 1 # Maintain tblspace statistics # System Diagnostic Script. # SYSALARMPROGRAM - Full path of the system diagnostic script (e.g. # c:\\informix\\etc\\evidence.bat.) Set this parameter # if you want a different Diagnostic Script than # {INFORMIXDIR}\\etc\\evidence.bat, which is default. # System Archive Tape Device TAPEDEV \\\\.\\TAPE0 # Tape device path TAPEBLK 16 # Tape block size (Kbytes) TAPESIZE 10240 # Maximum amount of data to put on tape (Kbytes) # Log Archive Tape Device LTAPEDEV NUL # Log tape device path LTAPEBLK 16 # Log tape block size (Kbytes) LTAPESIZE 10240 # Max amount of data to put on log tape (Kbytes) # Optical STAGEBLOB # Informix Dynamic Server/Optical staging area OPTICAL_LIB_PATH # Location of Optical Subsystem driver DLL # System Configuration SERVERNUM 1 # Unique id corresponding to a serverinstance DBSERVERNAME nomina # Name of default Dynamic Server DBSERVERALIASES # List of alternate dbservernames NETTYPE soctcp,1,,NET # Override sqlhosts nettype parameters DEADLOCK_TIMEOUT 10 # Max time to wait of lock indistributed env. RESIDENT 0 # Forced residency flag (Yes = 1, No = 0) MULTIPROCESSOR 0 # 0 for single-processor, 1 formulti-processor NUMCPUVPS 1 # Number of user (cpu) vps SINGLE_CPU_VP 0 # If non-zero, limit number of cpu vpsto one NOAGE 0 # Process aging AFF_SPROC 0 # Affinity start processor AFF_NPROCS 0 # Affinity number of processors # Shared Memory Parameters LOCKS 2000 # Maximum number of locks BUFFERS 2000 # Maximum number of shared buffers NUMAIOVPS 1 # Number of IO vps PHYSBUFF 32 # Physical log buffer size (Kbytes) LOGBUFF 32 # Logical log buffer size (Kbytes) CLEANERS 1 # Number of buffer cleaner processes SHMBASE 0xc000000 # Shared memory base address SHMVIRTSIZE 8192 # initial virtual shared memory segmentsize SHMADD 8192 # Size of new shared memory segments (Kbytes) SHMTOTAL 0 # Total shared memory (Kbytes). 0=>unlimited CKPTINTVL 300 # Check point interval (in sec) LRUS 8 # Number of LRU queues LRU_MAX_DIRTY 60.000000 # LRU percent dirty begin cleaninglimit LRU_MIN_DIRTY 50.000000 # LRU percent dirty end cleaning limit TXTIMEOUT 0x12c # Transaction timeout (in sec) STACKSIZE 64 # Stack size (Kbytes) # Dynamic Logging # DYNAMIC_LOGS: # 2 : server automatically add a new logical log when necessary. (ON) # 1 : notify DBA to add new logical logs when necessary. (ON) # 0 : cannot add logical log on the fly. (OFF) # # When dynamic logging is on, we can have higher values for LTXHWM/LTXEHWM, # because the server can add new logical logs during long transaction rollback. # However, to limit the number of new logical logs being added, LTXHWM/LTXEHWM # can be set to smaller values. # # If dynamic logging is off, LTXHWM/LTXEHWM need to be set to smaller values # to avoid long transaction rollback hanging the server due to lack of logical # log space, i.e. 50/60 or lower. DYNAMIC_LOGS 2 LTXHWM 70 LTXEHWM 80 # System Page Size # BUFFSIZE - Dynamic Server no longer supports this configuration parameter. # To determine the page size used by Dynamic Server on your platform # see the last line of output from the command, 'onstat -b'. # Recovery Variables # OFF_RECVRY_THREADS: # Number of parallel worker threads during fast recovery or an offline restore. # ON_RECVRY_THREADS: # Number of parallel worker threads during an online restore. OFF_RECVRY_THREADS 10 # Default number of offline workerthreads ON_RECVRY_THREADS 1 # Default number of online workerthreads # Data Replication Variables DRINTERVAL 30 # DR max time between DR buffer f