Can I expect any speed improvement for SAConnection between 11.0.1 and 12.0.1?

[MCMartin]

In 11.0.1.2550 I have made the observation that SAConnection is slower than OdbcConnection for simple operations going through tcp/ip like:

OdbcConnection myConnection = new OdbcConnection("dsn=xxx");
myConnection.Open();
OdbcCommand myCommand = myConnection.CreateCommand();
myCommand.CommandText = "SELECT 1"; 
myCommand.ExecuteScalar();
myCommand.Dispose();

With Odbc it needs ~310 ms and uses 29 TCP/IP packets,

with SA it needs ~350 ms and uses 48 TCP/IP packets

Have there been any performance improvements regarding this situation in current versions of the iAnywhere.Data.SQLAnywhere?

[Former Member]

I'd suspect that there is a bit of variation in the connection phase of your code. Can you post your DSN settings as well as the connection string for the SAConnection?

Apart from that, I might be more concerned with the time it takes to execute statements (leaving the connection phase out of this test). Have you done tests where only the statement is timed?

[MCMartin]

connection string is identical for SAConnection and OdbcConnection, it contains just DSN, PWD and UID.

In .net you have by design the requirement to use for each concurrent statement its own connection, so the above program flow is very common for .net programs. Therefore I am asking about the connection overhead. The performance of SA... retrieving huge result sets is far superior to Odbc...

[VolkerBarth]

Are you using connection pooling?

[MCMartin]

no connection pooling is not used

[VolkerBarth]

I'm not that .Net aware - but for me, the situation seems to ask for pooling...isn't that .Net default behaviour?

(Note, I'm just wild-guessing...)

[ian_mchardy · Accepted solution]

I can think of two improvements that have been made to version 12 that may help.

One is a round trip during the connect sequence has been eliminated for both ODBC and ADO.Net.

The second is connection pooling is enabled by default. In order for connection pooling to be helpful, the same process must connect at least 5 times with the exact same connection string, disconnect and connect again with the same connection string.

[MCMartin · Accepted solution]

Thanks for the answer, the exact same connection string is in my opinion not a good choice, as the connection string by default includes the AppInfo which contains even the thread id, so the connection needs to be issued by the same thread otherwise no use for pooling. Or is the relevant connection string just the program provided data?

[ian_mchardy · Accepted solution]

The generated portion of the AppInfo string is ignored when determining if two connection strings are the same for connection pooling. If a user specified AppInfo parameter is specified, that is included in the comparison.

[VolkerBarth · Accepted solution]

@Martin: Based on that important (and senseful) detail, I've felt free to edit that answer 🙂

[MCMartin · Accepted solution]

thanks, good to have this clarified in the end