Poor ADO.net Driver Performance

[Former Member]

Hi all,

I wondered whether anyone might be able to help me with a problem. We are using iAnywhere.Data.SQLAnywhere.v3.5.dll versions 11 & 12 to connect to a Sybase database over a LAN which is fast and reliable. However we have recently been using this over a WAN with both Sybase and other drivers. We have noticed a significant decrease in performance when using the Sybase ADO.net driver in comparison to other database platforms and drivers (such as SQL Server's ADO.net driver). The performance issue has been tracked to the Fill method of the Data Adapter. Even small retrieve statements can take seconds compared to near instant retrieves when connected over the LAN or indeed those of other drivers over a WAN. The performance difference is substantial between the Sybase and SQL Server drivers, with SQL being much much quicker. Is there something we are missing, or is this a scenario not supported by Sybase?

For reference we have also confirmed this issue to be the same with the latest version of Sybase v16. Any advice or comments much appreciated.

thanks

Ach

[jeff_albion]

Hi Ach,

Which version and builds of the SQL Anywhere ADO.NET driver have you tested specifically for this behaviour? Which version and builds of the database server have you used for your testing?

From a .NET perspective, how are you actually going about "Fill"ing the data adapter exactly? Can you provide a short code sample of this? Do you need to create a new connection in order to perform the Fill operation?

When you're comparing SQL Anywhere against other databases, is the time to execute the queries otherwise the "same" and you're just looking at the time to transfer the results over the network...? (e.g. is the time "expected" if everything is executed locally?)

Regards,

Jeff Albion

SAP Active Global Support

[Former Member]

Thanks for the reply Jeff. Some answers for you:

Which version and builds of the SQL Anywhere ADO.NET driver have you tested specifically for this behaviour? Which version and builds of the database server have you used for your testing?

iAnywhere.Data.SQLAnywhere.v3.5.dll

  versions

11 (build 11.0.1.24363)

12 (build 12.0.1.36053)
 

16 (build 16.0.0.13243)

From a .NET perspective, how are you actually going about "Fill"ing the data adapter exactly? Can you provide a short code sample of this? Do you need to create a new connection in order to perform the Fill operation?

We do create a new connection if required yes, with that connection we then create our DataAdapter:

        public TDataAdapter CreateDataAdapter(String commandText)

        {

            var command = this.CreateCommand(commandText);

            var dataAdapter = new TDataAdapter();

            dataAdapter.SelectCommand = command;

            return dataAdapter;

        }

Both of which result in something along the lines of:

     using (var conn = connectionFactory.CreateConnection())

     {

            using (var dataAdapter = conn.CreateDataAdapter(_selectSQL))

            {

                    dataAdapter.Fill(dataSet);
 

             }

       }

  

When you're comparing SQL Anywhere against other databases, is the time to execute the queries otherwise the "same" and you're just looking at the time to transfer the results over the network...? (e.g. is the time "expected" if everything is executed locally?)

The time is largely the same to execute queries. The time difference is in adding the data to the data set. limiting the number of columns and the number of rows returned from a select statement does improve the time slightly although performance is still poor compared with SQL Server. When running locally over the LAN times are roughly comparable.

Does that help?

Ach    

[JasonHinsperger]

Ach,

     How many records are in the result set?

Also, if you are able, can you check the server property "RequestsReceived" before and after the dataAdapter.Fill() call (assuming nothing else is running at the same time)?  You can check this property by running SELECT property( 'RequestsReceived') FROM DUMMY in a DBISQL session.

This will tell us how many round trips we are doing to the server during the "Fill()" call.  As far as I can tell, based on your responses to Jeff's questions, an increased number of roundtrips should be the only reason you see such a performance difference in WAN vs LAN.

--Jason

[Former Member]

Thanks for the reply Jason. Between 1 and 1500 records are returned and both before and after the result of RequestsReceived is null.

[jeff_albion]

Hi Ach,

Does that help?

Yes, your responses have been very helpful.

We do create a new connection if required yes

Have you been able to separate out any increase in connection times in WAN vs. LAN situations from the time taken by the .Fill() operation? (Are you measuring the total time for all of this code to execute, or have you been able to profile it down to just the .Fill() operation?)

What does your connection string look like? Does it change from the WAN vs. LAN situation? If the connection is already guaranteed to be established, does the time to complete the .Fill() change?

12 (build 12.0.1.36053)
16 (build 16.0.0.13243)

You may want to try later builds of 12.0.1 and 16.0.0 to solve CR #743048, which improve .Open() times for pooled connections.

Between 1 and 1500 records are returned

If you stabilize the size of the result set for testing purposes (pick '1500' ?), what is the time difference we're talking about between LAN and WAN conditions? Is it a 'seconds' difference or 'milliseconds'...? On Microsoft SQL Server when comparing WAN vs. LAN conditions, is there a difference also in execution times and if so, by how much?

Also, if you execute the .Fill() command multiple times in sequence, does the time change or stay the same to complete the operation on the next execution?

What are the qualities for a 'LAN' vs. 'WAN' to your environment - LAN vs. Internet? What are the network speed differences in the two connections?

Regards,

Jeff Albion

SAP Active Global Support

[Former Member]

Thanks for the reply Jeff. We have profiled the issue down to the .fill() operation in our testing. Our connection string is as follows:

<add key="SAConnectionString" value="servername=ABC123;uid={0};pwd={1};CommLinks=tcpip(HOST=xxx.xxx.xxx.xxx;port=1234);dbn=DSN1"/>

We will need a bit of time to setup our environments to obtain the answer to the other questions you mentioned. I will let you know the results shortly.

thanks

Paul

P

[Former Member]

Hi Jeff,

Please find below a comparison by database version and dll build of timings, which as you can see shows significant degradation in performance for Sybase over the WAN. As you will see we are using builds which resolve issue CR743048.

We have also include the order in which the queries are run so you can see differences in subsequent execution time after the .Fill command is executed. In our situation we are running the database locally for the LAN and connecting to database(s) hosted in a data center over the internet for the WAN situation.

I hope that this answers your questions if not please just let me know.

thanks

Ach

[jeff_albion]

Hi Ach,

That's a very interesting result - I would certainly agree that something does not look right.

In our situation we are running the database locally for the LAN and connecting to database(s) hosted in a data center over the internet

Admittedly, this is not the same SQL Anywhere database server: you're sure that if you execute queries locally on the data center that you see the same response times as the LAN configuration? (e.g. the server performance always looks the "same" for these queries, from the database server's perspective?

Is the Microsoft SQL Server machine on the same virtual instance in the data center or a different instance? If not, is it the same network routing in the data center to both database servers?

Have you attempted to capture a network trace to help and decide what's happening at the raw network level? (i.e. are there just simply more packets, more delays, any errors, resend requests, unexpected traffic, etc.?) It would be interesting to compare the network traces to see what's happening between the different cases.

Regards,

Jeff Albion

SAP Active Global Support

[Former Member]

Hello,

Has there been any progress on the ADO.Net performance issue reported here?

I've done some testing and running a simple select statement on the server takes around 25ms. The same query running over a 15MBit connection takes around 500ms.

The query returns 29 rows. When the query is modified to return 0 results it still takes around 350ms. Locally this takes 15ms.

In another test, I return a 29 rows with a single integer - locally 12ms but over the internet 350ms.

I appreciate that it will take longer to return data over the internet vs local or even a local LAN but this difference is far larger than I would expect.

It looks like the internet request is taking 350ms with the full data payload being 150ms.

Thanks

Chris

[Former Member]

For Internet scenarios the bandwidth is mostly not the bottleneck, but the latency is.

E.g. if you have a ping time of 50ms it means, that exchaning 10 messages to perform a task like a database query will lead to a minimum time of 500ms needed...

[Former Member]

Hi Martin,

Ping time is around 38ms.

Performing a select that returns a single integer still takes between 330ms and 450ms.

I have a lot of experience with SQL Server and MySQL - I will put together a test to to compare the three databases.

Does a simple select statement really require 10 messages to be exchanged?

In the meantime, what connection string options could I use to improve network performance. I have been through the manual - none of the settings I tried make any significant difference (and I tried all the obvious ones).

Thanks

Chris

[jeff_albion]

Hi Chris,

>> Has there been any progress on the ADO.Net performance issue reported here?

Just to confirm, are we still referring to Ach's original ADO.NET .Fill() code with the same ADO.NET driver versions?

>> Does a simple select statement really require 10 messages to be exchanged?

The answer to his question is really: "it depends" -  the programming interface used, the preparation type of the statement, and the MTU size of your TCP network.

Have you been able to use WireShark or similar to trace out the two database client TCP conversations (WAN vs. LAN) to the database server in order to determine the difference in latency between the packets and if there are any errors associated with transmitting the packets over the WAN?

Also (and I'm not sure why ), we never actually asked about the SQL query being used - what is it and over what types of objects? What happens if you use a 'fake' query instead, does it change the behaviour? i.e.

  SELECT row_num FROM sa_rowgenerator( 1, 4000 );

Regards,

Jeff Albion

SAP Active Global Support

[Former Member]

Hi Jeff,

Thank you for your reply.

I have to say I don't actually know the internals of how the data is being processed. For testing I've used both Entity Framework and NPoco (modified with a Sybase SQL Anywhere factory) converting the data into a strongly typed object. Perhaps I should have started a separate thread?

The simplest select statement I've been testing with is a parametrised query like this:

SELECT top 1 OrderID FROM DBA.Orders WHERE CustomerID=@0 order by OrderID

I have a saved a Wireshark capture - I'm happy to send this to you along with the demo application I have created, please let know where to send it.

To get a single integer from the database took 24 messages to the server and 22 from the server. Running on the local network the number of requests was the same but obviously it was much quicker as latency wasn't an issue.

I haven't got a proper test set up for MySQL at this stage but a similar request took 120ms to complete with 49 messages captured in Wireshark. The MySQL server is less powerful than the Sybase server but has a similar latency.

The actual query doesn't matter. I haven't seen a response from any query (even SELECT 1) take less than 316ms. If a lot of data is being returned it obviously takes longer which is to be expected.

If you let me know where to send the data and application I'll happily forward it on to you.

Chris