The forum will be down for scheduled maintenance on Saturday, March 4 beginning at 10am EST. Actual downtime is unknown but may be several hours.

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?

asked 14 Nov '11, 12:18

Martin's gravatar image

Martin
8.6k114149237
accept rate: 14%

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?

(14 Nov '11, 15:05) Tyson Lewis
Replies hidden

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...

(15 Nov '11, 03:04) Martin

Are you using connection pooling?

(15 Nov '11, 06:10) Volker Barth

no connection pooling is not used

(15 Nov '11, 06:51) Martin

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...)

(15 Nov '11, 08:50) Volker Barth

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.

permanent link

answered 15 Nov '11, 08:50

Ian%20McHardy's gravatar image

Ian McHardy
2.7k22946
accept rate: 38%

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?

(15 Nov '11, 10:04) Martin
Replies hidden
1

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.

(15 Nov '11, 10:10) Ian McHardy

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

(15 Nov '11, 10:47) Volker Barth

thanks, good to have this clarified in the end

(15 Nov '11, 11:34) Martin
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×60
×55
×22

question asked: 14 Nov '11, 12:18

question was seen: 1,428 times

last updated: 15 Nov '11, 11:34