Insert/Update Statements very Slow on AIX using ExecuteArray

sebniepel · Post by **sebniepel** » Tue 13 Apr 2010 09:36

We are having serious performance problems using inserts or updates on AIX with ExecuteArray(). We did a lot of tests. All with the same code base. What we found out:

Mono 2.4.2.3; Execute() in Loop; AIX -> normal
Mono 2.4.2.3; Execute() in Loop; Linux -> normal
.Net; Execute() in Loop; Windows -> normal
Mono 2.4.2.3; ExecuteArray(); AIX -> slow
Mono 2.4.2.3; ExecuteArray(); Linux -> fast
.Net; ExecuteArray(); Windows -> fast

10,000 rows with 25 columns of Varchar2 and Integer
slow means more than 2 Minutes
normal means 30s
fast means 1,5s

All tests are done in direct mode.

The speed to build up the array is not the problem. We could clearly track the issue down to OracleCommand.ExecuteArray().
We could not see any bottlenecks on the maschine. CPU does not do very much while executing, Network is not very busy and memory is fine. It nearly looks like there is some kind of sleep() statment involved.

What additional information do you need to solve this issue?

StanislavK · Post by **StanislavK** » Wed 14 Apr 2010 10:18

Please check if the performance problem can be reproduced on tables with integer fields only. Also, please specify the charset you are using in your test database and the maximal length of varchar fields.

Tobi · Post by **Tobi** » Mon 19 Apr 2010 10:36

Hi,

I made a testcase with an integer-only table; the performance problem can be reproduced with it as well. We use Charset: WE8ISO8859P15.

Best regards,
Tobi

StanislavK · Post by **StanislavK** » Tue 20 Apr 2010 14:36

We did not test dotConnect for Oracle under AIX. However, the dotConnect for Oracle preformance should not depend on OS peculiarities. The possible performance loss on big-endian systems might be connected with inverting variables, but this should not be significant for integer types. We can only suggest that there may be some differences in Mono realizations for AIX and Linux.

ddeml · Post by **ddeml** » Wed 21 Apr 2010 14:32

Hi StanislavK,

this issue is still so important for us, that we would like a cooperation with you to track down the actual problem so we can find the core solution.

Because you don't have an AIX at your fingertips, and we dont have your source code at our fingertips, i propose the following apprach to find a solution as fast as possible:

You create a patched version of the DevArt component that creates extended debug information in the part of code where the problem seems to be
We reproduce the problem while using your patched version of the DevArt component
We send you the result of the extended Debug output so you can analyze it
If the cause is not sufficiently tracked down, proceed with 1.
If the cause is found, we can continue seeking to a solution.

If you agree wich such an approach for solving this problem, it would be helpful to negotiate a time window of 2-3 days where we both have a developer ready at each side to maintain a quick debug cycle.

Please don't hesitate to contact me or Tobi for extensive negotiations. I am confident that we can come to a conclusion.

What do you think?

Greetings - Richie

StanislavK · Post by **StanislavK** » Fri 23 Apr 2010 16:14

We will prepare a test project that should help to localize the problem. We will inform you here when it is ready.

schueler · Post by **schueler** » Thu 29 Apr 2010 10:14

Any updates for this issue?

StanislavK · Post by **StanislavK** » Fri 30 Apr 2010 14:24

We are working on the test project. We will notify you when it is ready.

Tobi · Post by **Tobi** » Mon 10 May 2010 11:25

Do you have a date for us when this project will be ready? This issue is very important to us, please let us know, whether we can help.
Regards,
Tobi

StanislavK · Post by **StanislavK** » Wed 12 May 2010 16:49

We are still working on the test project, but cannot provide any timeframe. I will let you know when it is ready.

Tobi · Post by **Tobi** » Wed 19 May 2010 07:20

How is the status on this in between. It is still very important to us.

StanislavK · Post by **StanislavK** » Thu 20 May 2010 14:12

The test project is ready. I will send it to the mail specified in your forum profile, please check that it was not blocked by your mail filter.

Please run the project, perform the test and save its results. The application will create an XML file (Bin\Debug\WindowsApplication1.xml). Please send us this file so that we are able to localize the problem.

Tobi · Post by **Tobi** » Tue 25 May 2010 07:30

Did the resulting XML help you to locate the problem? If not: how can we proceed with it? Does it help to provide you a login for an AIX machine?

StanislavK · Post by **StanislavK** » Wed 26 May 2010 16:21

I will send you another test project by mail, please check that it is not filtered. Please run both ExecuteArray and ExecuteNonQuery tests and check that the performance of ExecuteArray is lower. If it is not, please change the project in such way that the problem can be reproduced under AIX.

Also, please note that the assemblies used in the current test project are based on the ones from the latest 5.70.140 build. If the problem is not reproduced, please try using the assemblies from the previous test project.

After the problem is reproduced, please send us the resulting XML files for both ExecuteArray and ExecuteNonQuery. If these files are insufficient to localize the problem, it would be helpful to connect the AIX machine remotely.

StanislavK · Post by **StanislavK** » Tue 01 Jun 2010 13:31

I will send you another test project in the letter, please check that it isn't blocked.

According to the XML results, the problem may be caused by the size of packets sent by the ExecuteArray and ExecuteNonQuery methods. The first method sent larger packets, please try to limit their size manually as it is done in the test project. Please tell us if this helps.