Hello,

I am using ODBC driver Sybase System 11 3.11.00.01 to access an ASE
12.5.0.3.
Using this driver, all queries (like regular SELECTs, UPDATEs) are
10times slower than with isql (from my tuning).

I would have expected a difference, but not so big.

Does anyone have any suggestions about where this could come from?

Thanks in advance,

SerGioGio

Hello,

That driver is so old I'm not sure how the setup/configuration
screen looks. But under one of the configuration tabs, you
should be able to switch the "Select Method". This generally
has a big effect on performance.

This is a listing of some of the options that are available on
the newer ODBC drivers (and some of the older ones too I think)
that can affect performance:

Select Method: Select a value of 0 or 1 that determines whether database cursors are used for Select statements. When set to 0, the
default, database cursors are used; when set to 1, Select statements are run directly without using database cursors. A setting of 1
limits the data source to one active statement.

Prepare Method: Select a value of 0, 1, 2, or 3 that determines whether stored procedures are created on the server for calls to
SQLPrepare.
When set to 0, stored procedures are created for every call to SQLPrepare. This setting can result in decreased performance when
processing statements that do not contain parameters.
When set to 1 (the initial default), the driver creates stored procedures only if the statement contains parameters. Otherwise, the
statement is cached and run directly at the time of SQLExecute.

When set to 2, stored procedures are never created. The driver caches the statement, executes it directly at the time of
SQLExecute, and reports any syntax or similar errors at the time of SQLExecute.
When set to 3, stored procedures are never created. This is identical to value 2 except that any syntax or similar errors are
returned at the time of SQLPrepare instead of SQLExecute. Use this setting only if you must have syntax errors reported at the time
of SQLPrepare.

Fetch Array Size: Type the number of rows the driver retrieves when fetching from the server. This is not the number of rows given
to the user. The default is 50 rows.
Packet Size: Type a value of -1, 0, or x that determines the number of bytes per network packet transferred from the database server
to the client. The correct setting of this attribute can improve performance.
When set to -1, the driver computes the maximum allowable packet size on the first connect to the data source and saves the value in
the system information.

When set to 0, the default, the driver uses the default packet size as specified in the Sybase server configuration.
When set to x, an integer from 1 to 1024, the driver uses a packet size represented by x times 512 bytes. For example, "6" means to
set the packet size to 6 * 512 bytes (3072 bytes).
To take advantage of this connection attribute, you must configure the Sybase server for a maximum network packet size greater than
or equal to the value you specified for PacketSize. For example:

sp_configure "maximum network packet size", 5120
reconfigure
Restart Sybase Server

NOTE: The ODBC specification identifies a connect option, SQL_PACKET_SIZE, that offers this same functionality. To avoid conflicts
with applications that may set both the connection string attribute and the ODBC connect option, they have been defined as mutually
exclusive. If PacketSize is specified, you will receive a message "Driver Not Capable" if you attempt to call SQL_PACKET_SIZE. If
you do not set PacketSize, then application calls to SQL_PACKET_SIZE are accepted by the driver.


TomTom wrote:

Can you use the OLEDB provider? If so it's significantly F A S T E R.

"TomTom" <sergiogiogio (AT) yahoo (DOT) fr> wrote