you may update your BUFFERS, how much memory do you have? you may send your
machine configuration for tuning onconfig file.
----- Original Message -----
From: "Unholy" <dcalleja@gmail.com>
To: <informix-list@iiug.org>
Sent: Monday, April 18, 2005 11:10 AM
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 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)
>
> 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
> 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 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)
>
> # 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 worker
> threads
> ON_RECVRY_THREADS 1 # Default number of online worker
> threads
>
> # 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 dbspace (default same as
> catalog)
> CDR_QDATA_SBSPACE # CDR queue smart blob space
> CDR_NIFCOMPRESS 0 # Link level compression (-1 never, 0
> none, 9 max)
>
> # Backup/Restore variables
> BAR_ACT_LOG C:\PROGRA~1\Informix\bar_nomina.log
> #Path of log file for onbar.exe
> BAR_MAX_BACKUP 0
> BAR_RETRY 1
> BAR_NB_XPORT_COUNT 10
> BAR_XFER_BUF_SIZE 15
> BAR_BSALIB_PATH C:\ISM\2.20\bin\libbsa.dll # Location of ISM XBSA DLL
>
> RESTARTABLE_RESTORE on #To support restartable
> restore..values on/off
>
> # Informix Storage Manager variables
> ISM_DATA_POOL ISMData
> ISM_LOG_POOL ISMLogs
>
> # Read Ahead Variables
> RA_PAGES 4 # Number of pages to attempt to read
> ahead
> RA_THRESHOLD 2 # Number of pages left before next
> group
>
> # DBSPACETEMP:
> # Dynamic Server equivalent of DBTEMP for SE. This is the list of
> dbspaces
> # that the Dynamic Server SQL Engine will use to create temp tables
> etc.
> # If specified it must be a colon separated list of dbspaces that exist
> # when the Dynamic Server system is brought online. If not specified,
> or if
> # all dbspaces specified are invalid, various ad hoc queries will
> create
> # temporary files in /tmp instead.
>
> DBSPACETEMP # Default temp dbspaces
>
> # DUMP*:
> # The following parameters control the type of diagnostics information
> which
> # is preserved when an unanticipated error condition (assertion
> failure) occurs
> # during Dynamic Server operations.
> # For DUMPSHMEM, DUMPGCORE and DUMPCORE 1 means Yes, 0 means No.
>
> DUMPDIR C:\tmp # Preserve diagnostics in this
> directory
> DUMPSHMEM 1 # Dump a copy of shared memory
> DUMPGCORE 0 # Dump a core image using 'gcore'
> DUMPCORE 0 # Dump a core image (Warning:this
> aborts Dynamic
> DUMPCNT 1 # Number of shared memory or gcore
> dumps for
> # a single user's session
>
> FILLFACTOR 90 # Fill factor for building indexes
>
> # method for Dynamic Server to use when determining current time
> USEOSTIME 0 # 0: use internal time(fast), 1: get
> time from OS(slow)
>
> # Parallel Database Queries (pdq)
> MAX_PDQPRIORITY 100 # Maximum allowed pdqpriority
> DS_MAX_QUERIES 32 # Maximum number of decision support
> queries
> DS_TOTAL_MEMORY 4096 # Decision support memory (Kbytes)
> DS_MAX_SCANS 1048576 # Maximum number of decision support
> scans
> DATASKIP off # List of dbspaces to skip
>
> # OPTCOMPIND
> # 0 => Nested loop joins will be preferred (where
> # possible) over sortmerge joins and hash joins.
> # 1 => If the transaction isolation mode is not
> # "repeatable read", optimizer behaves as in (2)
> # below. Otherwise it behaves as in (0) above.
> # 2 => Use costs regardless of the transaction isolation
> # mode. Nested loop joins are not necessarily
> # preferred. Optimizer bases its decision purely
> # on costs.
> OPTCOMPIND 0 # To hint the optimizer
>
> DIRECTIVES 1 # Optimizer DIRECTIVES ON (1/Default)
> or OFF (0)
>
> ONDBSPACEDOWN 2 # Dbspace down option: 0 = CONTINUE, 1
> = ABORT, 2 = WAIT
> OPCACHEMAX 0 # Maximum optical cache size (Kbytes)
>
> # HETERO_COMMIT (Gateway participation in distributed transactions)
> # 1 => Heterogeneous Commit is enabled
> # 0 (or any other value) => Heterogeneous Commit is disabled
> HETERO_COMMIT 0
>
> SBSPACENAME sbspace # Default sbspace
>
> BLOCKTIMEOUT 3600 # Default timeout for system block
>
> # Optimization goal: -1 = ALL_ROWS(Default), 0 = FIRST_ROWS
> OPT_GOAL -1
>
> ALLOW_NEWLINE 0 # embedded newlines(Yes = 1, No = 0 or
> anything but 1)
> SYSSBSPACENAME sbspace # Default System sbspace
>
> #
> # The following are default settings for enabling Java in the database.
> #
> #VPCLASS jvp,num=1 # Number of JVPs to start with
> JVPJAVAHOME C:\PROGRA~1\Informix\extend\krakatoa\jre
> # JDK installation root directory
> JVPHOME C:\PROGRA~1\Informix\extend\krakatoa
> # Krakatoa installation directory
> JVPLOGFILE C:\PROGRA~1\Informix\extend\krakatoa\nomina_jvp.lo g
> # VP log file
> JVPPROPFILE C:\PROGRA~1\Informix\extend\krakatoa\.jvpprops_nom ina
> # JVP property file
> JDKVERSION 1.3 # JDK version supported by this server
>
> # The path to the JRE libraries relative to JVPJAVAHOME
> JVPJAVALIB \bin\
> JVPJAVAVM hpi;server;verify;java;net;zip;jpeg
> # Classpath to use upon Java VM start-up (use _g version for debugging)
> JVPCLASSPATH
> C:\PROGRA~1\Informix\extend\krakatoa\krakatoa.jar; C:\PROGRA~1\Informix\extend\krakatoa\jdbc.jar
>
>
> Thanks a lot.
>
>


sending to informix-list
sending to informix-list