cancel
Showing results for 
Search instead for 
Did you mean: 

ASA 11 ODBC Drivers Slow

Former Member
4,780

We are in the process of upgrading from Sybase v9 to v11, and I am having issues with the v11 odbc driver for a couple connections. I am seeing performance taper after approx. 500MB of data is transferred.

I am performing all tests on the same windows 2008r2 server, using SQL Server Integration Services 2012 to connect and pull data. I have two 32bit (but am seeing same results with 64bit v11) system odbc connections, one for the v9 drivers and one for the v11 drivers. Both are configured for the same Sybase v11 database, with the same settings. I am pulling two different data sets in parallel that total roughly 1GB.

I am seeing a steady average of roughly 3MB/s with the v9 drivers. 1GB of data is taking approx. 350sec to transfer.

The v11 drivers start to taper around 500MB, going from 3MB/s to < 1MB/s. It ends up taking roughly 4000 seconds to finish transferring the 1GB of data.

Does anyone have any suggestions or information? I didn't find anything that seems to relate to this in the connection parameters or network protocol options of the documentation. Additionally, it doesn't seem there is a meaningful difference in the connection logs below.

Thanks!

ODBC Log for v9:

Mon Sep 30 2013 14:55:50
14:55:50 Application information:
14:55:50 "HOST=*;OS=Windows NT 6.1 Build 7601 Service Pack 1;PID=0x1d70;THREAD=0x7e0;EXE=C:\\Program Files (x86)\\Microsoft SQL Server\\110\\DTS\\Binn\\ISServerExec.exe;VERSION=9.0.2.3381;API=ODBC;TIMEZONEADJUSTMENT=-240"
14:55:50 Attempting to connect using:
UID=*;PWD=********;ENG=*;START=*;CON=SQL_DBC_135108e8;ASTOP=NO;INT=NO;DBG=YES;LOG=C:\\Sybase Drivers\\ODBC_ASA9.log;DMRF=NO;LINKS=SharedMemory,TCPIP{ip=10.90.6.43};COMP=NO
14:55:50 Attempting to connect to a running server...
14:55:50 Trying to start TCPIP link ...
14:55:50 Loading wsock32.dll
14:55:50 Loading ws2_32.dll
14:55:50 TCP using Winsock version 2.0
14:55:50 My IP address is 10.90.5.53
14:55:50 My IP address is 127.0.0.1
14:55:50     TCPIP link started successfully
14:55:50 Attempting TCPIP connection (no asasrv.ini cached address)
14:55:50 Looking for server with name *
14:55:50 Trying to find server at address 10.90.6.43
14:55:50 Found server, verifying server name
14:55:50 Found database server at address 10.90.6.43:2638
14:55:50 Found database server * on TCPIP link
14:55:50 Connected using client address 10.90.5.53:50246
14:55:50 Connected to server over TCPIP at address 10.90.6.43:2638
14:55:50 Writing server address 10.90.6.43:2638 to asasrv.ini cache
14:55:50 Connected to ASA Engine version 11.0.1.2584
14:55:50 Connected to the server, attempting to connect to a running database...
14:55:50 [  307] Connected to database successfully

**ODBC Log for v11:**
Mon Sep 30 2013 14:54:41
14:54:41 Attempting to connect using:
UID=*;PWD=********;DBN=*;ENG=*;CON=SQL_DBC_114a7948;ASTOP=NO;INT=NO;CBSIZE=1460;DBG=YES;LOG='C:\\Sybase Drivers\\ODBC_ASA11.log';DMRF=NO;LINKS='SharedMemory,TCPIP{ip=10.90.6.43}';COMP=NO
14:54:41 Attempting to connect to a running server...
14:54:41 Trying to start TCPIP link ...
14:54:41 TCP using Winsock version 2.2
14:54:41 My IP address is 10.90.5.53
14:54:41 My IP address is ::1
14:54:41 My IP address is 127.0.0.1
14:54:41     TCPIP link started successfully
14:54:41 Attempting TCPIP connection (address 10.90.6.43:2638 found in sasrv.ini cache)
14:54:41 Trying to find server at cached address 10.90.6.43:2638 without broadcasting
14:54:41 Found server, verifying server name
14:54:41 Found database server at address 10.90.6.43
14:54:41 Found database server * on TCPIP link
14:54:41 Connected using client address 10.90.5.53:50238
14:54:41 Connected to server over TCPIP at address 10.90.6.43:2638
14:54:41 Connected to SQL Anywhere Server version 11.0.1.2584
14:54:41 Application information:
14:54:41 IP=10.90.5.53;HOST=*;OSUSER=*;OS='Windows 2008R2 Build 7601 Service Pack 1';EXE='C:\\Program Files (x86)\\Microsoft SQL Server\\110\\DTS\\Binn\\ISServerExec.exe';PID=0x1f2c;THREAD=0x1f7c;VERSION=11.0.1.2584;API=ODBC;TIMEZONEADJUSTMENT=-240
14:54:41 Connected to the server, attempting to connect to a running database...
14:54:41 [  304] Connected to database successfully
Breck_Carter
Participant
0 Kudos

What operation is being used to "pull data"?

On the SQL Anywhere side, what operation is being used to satisfy the pull? Is there a problem with query plan?

Former Member
0 Kudos

Both are queries that result in table scans with a LOWER function on one column in the projection. I need a couple columns from every row, so this is expected.

However, the degradation only occurs with the v11 drivers. v9 drivers with the v11 database work as expected.

ian_mchardy
Advisor
Advisor
0 Kudos

While using CBSIZE=1460 should cause both 9 and 11 to use the same packet size, given you are transferring so much data, I would expect the 11 default of 7300 to be faster. I would also expect the maximum of 16000 to be faster yet.

Also, can you double check that ODBC tracing is disabled in the v11 case.

Other than ODBC tracing, I have no idea what could cause this slowdown.

Former Member
0 Kudos

I tested with three different CBSIZE settings (1460, 7300, 16000) and am seeing the slowdown at approx 500MB with all three. ODBC tracing is off. Thanks for the suggestions.

MCMartin
Participant
0 Kudos

Have you been able to solve this?

Accepted Solutions (0)

Answers (2)

Answers (2)

Former Member

I have a similar issue that I am dealing with. I suspect that the newer Sybase ODBC drivers have some type of negative interaction with windows tcp auto tuning. If you disable windows tcp auto tuning, you get some improvement. There is a known defect in Windows Server 2008 R2 (and on Win 7 machines) related to windows tcp auto tuning.

Breck_Carter
Participant

Try removing the CBSIZE=1460 since the default is 7300 for everything except Windows Mobile.

VolkerBarth
Contributor
0 Kudos

Well, that may be added for compatibility, because for v10 and below 1460 was the default value on all platforms...

Former Member

Sorry, I should have mentioned that I got the same performance issues with 7300 CBSIZE. It was at that initially, and I changed to 1460 since that was the only difference I saw in settings between the drivers. I am going to change both to the maximum setting since it's server to server, but I don't think this will eliminate the slowdown. I will verify. Thanks for the suggestion.

Edit: I am seeing the same results with buffer sizes of 1460, 7300, and 16000.