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 flushes (in sec) DRTIMEOUT 30 # DR network timeout (in sec) DRLOSTFOUND \\tmp # DR lost+found file path # CDR Variables CDR_EVALTHREADS 1,2 # evaluator threads (per-cpu-vp,additional) CDR_DSLOCKWAIT 5 # DS lockwait timeout (seconds) CDR_QUEUEMEM 4096 # Maximum memory for any CDR queue (Kbytes) CDR_QHDR_DBSPACE # CDR queue db

did you run update statistics?

Yes,. I ran update statistics.... The PC in wich the database is , is a PIV , just one CPU, 1 GB RAM, and 200 GB HD

Not a lot of buffers for starters, this looks like an 'out-of-box' config, grab the performance guide, have a quick read and the use pf most the parameters will 'as if by magic' become clearer [cutting] > > SERVERNUM 1 # Unique id corresponding to a server> instance > 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 in> distributed env. > RESIDENT 0 # Forced residency flag (Yes = 1, No = > 0) Probaly should be 1 or -1 > > MULTIPROCESSOR 0 # 0 for single-processor, 1 for> multi-processor > NUMCPUVPS 1 # Number of user (cpu) vps > SINGLE_CPU_VP 0 # If non-zero, limit number of cpu vps> to 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 A little low > 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 I tend to set cleaners = lrus = 128 > SHMBASE 0xc000000 # Shared memory base address > SHMVIRTSIZE 8192 # initial virtual shared memory segment> size > 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 cleaning> limit > LRU_MIN_DIRTY 50.000000 # LRU percent dirty end cleaning limit > TXTIMEOUT 0x12c # Transaction timeout (in sec) > STACKSIZE 64 # Stack size (Kbytes)> [cutting] -- Paul Watson # Oninit Ltd # Growing old is mandatory Tel: +44 1436 672201 # Growing up is optional Fax: +44 1436 678693 # Mob: +44 7818 003457 # www.oninit.com #

no idea, do the same indexes exist? is it using the indexes in the same way it does in sql server