A well-tuned Sybase IQ insert operation exhibits certain characteristics. You can observe these characteristics from the Windows Task Manager and Windows Performance Monitor.
Insert operations are generally CPU-bound. All CPUs within the system should be running at close to 100%, with 95% or higher of the CPU being executed in user mode. You can see this easily by clicking on the Performance tab of the Windows Task Manager with the View-Show Kernel Times option set.
Physical memory should not be overallocated and in particular, the virtual address space for the Sybase IQ process should be less than the physical memory (RAM) for the machine.
Hard page faults (I/O to the volume containing pagefile.sys) should be low and ideally close to 0 (zero).
I/O operations to the IQ Store should be steady and within the I/O capacity of the disk subsystem.
Sybase IQ uses the Windows CreateFile option (for both creating and opening a file) that specifies a file is to be read for sequential access. This option is used on the files specified in the LOAD TABLE command. As a result, load performance is improved through read ahead and reduced NTFS Cache memory utilization.
Load performance can be further improved, sometimes significantly, by setting the size of the main Sybase IQ buffer cache considerably smaller than the calculated recommended values in “Sybase IQ main and temp buffer caches”. The reasons for this performance improvement are described in “Using the NTFS cache”. You can set the main Sybase IQ buffer cache as much as 50% smaller than the calculated recommended values.
