Sybase9, not using full system capacity The SSD I/O capacity is around 450MB. When I copy files from one folder to another I can see in the Windows Resource Monitor that the sum of read/write speed gets around 450MB. Why the same wont happen with dbeng9 or dbsrv9, while I am executing a read, transform and insert procedure?

These are the parameters:

C:\Program Files\Sybase\SQL Anywhere 9\x64\dbsrv9.exe
C:\Tributos\Dados\Asa9\Almirante\Integra.db -xtcpip -gl all -c 5000M -cl 5000M -ch 5000M -gp 8K -gb high -gt 4 -cs -n cnv_tributos


The process is not using more than 25% from each one of the 4 cores, so I don't know where is the bottleneck.

The Sybase manual informs that the per-seat license(the one i am using), is suposed to use all cores for processing requests. In adiction the "-gt 4" purpose was to force the use of 4 cores.
It looks like a default behavior as if the server process was expecting more conections or more querys to be executed, but that wont happen, only i am using the database during this process.

I need to use the maximum capacity from the machine, because I work with a ETL (Extract Transform and Load) scenario all day.
These procedures runs for hours, if i could make then run 4 times faster it would make my day.

Copying Files ->  
Procedure Running -> 

SSD -> Samsung SSD 840 EVO 500GB ATA Device



Sybase document reference.

Function (-gn)
Set the maximum number of active requests (both user and system) that the database server can handle concurrently.

Syntax 
{ dbsrv9 | dbeng9 } -gn integer ...

Applies to 
All operating systems and servers.

Description 
Set the number of active user and system requests that the database server can handle concurrently. If the maximum number of active requests are already in use when the database server receives an additional request, the new request must wait until another request is completed.

Each connection uses a thread for each request, and when the request is completed the thread is returned to the pool for use by other connections. As no connection can have more than one request in progress at one time, no connection uses more than one thread at a time. An exception to this rule is if a Java application uses threads. Each thread in the Java application is a database server execution thread.

The default is 20 threads for the network database server and for the personal database server, except on Windows CE where the default is 3 threads.

On Windows NT/2000/XP, you may want investigate the Performance Monitor readings for Requests: Active and Requests: Unscheduled. If the number of active requests is always less than -gn, you can lower -gn. If the number of total requests (active + unscheduled) is often larger than -gn, then you might want to increase -gn. The Performance Monitor is not available for UNIX or Linux platforms.



Function (-gt)
Set the maximum number of requests that can be run simultaneously by the database server. This option is only useful on multiprocessor systems.

Syntax 
{ dbsrv9 | dbeng9 } -gt integer ...

Applies to 
All operating systems and servers except NetWare.

Description 
With per-seat licensing, the network database server uses all CPUs available on the machine (the default). With CPU-based licensing, the network database server uses only the number of processors you are licensed for. In addition, the personal database server and runtime database server are both limited to a single processor.



I tried to set both parameters to 4 and to 1 , the results had no change.
Two things sprint to mind:


The process could be partly CPU bound on a single thread. You don't state what you CPU resource is, but if you have a 4-core arrangement then it coudl be that the ~25% use you are seeing is a single thread bouncing between the cores. Similarly with 8 cores it could be using 2x parallelism so two cores worth of CPU resource which is 25% of the total available. Hyperthreading may confuse this further as the virtual cores exposed by HT do not perform completely independently.
In this case your CPU is the bottleneck and trying to improve the concurrency of your operation may be the solution.
The process may not be streaming effectively so during the "read something, do something, write something" process there will be no disk activity during the "so something" phase. Often processes do not try to buffer I/O to improve this situation because adding the extra buffering layer is extra complication for (sometimes) minimal gain, it requires more memory, and it is less friendly to other concurrent processes. In comparison to your file copy test: there is almost nothing by way of "do something" stage there.
Again increasing the concurrency of your process may help a lot here.


A lot of processes like this are still optimised for spinning-metal in mind, so they will avoid concurrency where it would create I/O contention because with disc+head drives this can harm performance a lot more than it helps - addding the extra processing threads woudl increase CPU utilisation but can make the discs much more of a bottleneck due to head thrashing. I/O contention is much less of a problem with solid state drives (particularly for read operations) with SSDs because you don't have the flapping heads problem so you can increase the concurrency of I/O operations for benefit rather than detriment.

If your ETL tool can't be told to increase concurrency, or the dependencies in your overall process mean further concurrency is not even possible, the if you have several such processes to run try running more than one instance of your ETL tool at once.