Connection Performance problem

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for universal data access
Post Reply
jorgesv13
Posts: 7
Joined: Thu 09 Oct 2008 00:54

Connection Performance problem

Post by jorgesv13 » Thu 23 Oct 2008 21:33

Hello,

I'm evaluating UniDirect for migrating a .Net 2.0 Windows Application that previously used SQLServer.
Now I want it to support multiple databases, so I changed all my code with the UniDirect DataProvider.

I'm having a performance problem when running several queries:
The program runs about 300 queries fine (and fast). For each query a new connection must be opened, as they run in different functions.

Then the program halts for about 10 seconds, and then it starts running again fast, for another 300 or so queries, when it stops again.

I've pause the debugging in the waiting-time, and I see that the delay is in the function open() of the UniConnection. It takes a LOT of time to get a connection after several queries.

I looked on the FAQ and thought the problem was about the Connection Pooling, but I disabled it in the Connection String ("Pooling=false"), but the problem persists.

Does somebody knows about this problem?

jorgesv13
Posts: 7
Joined: Thu 09 Oct 2008 00:54

Post by jorgesv13 » Fri 24 Oct 2008 14:34

Okay, I've run some more tests, and I'm quite sure the problem is about the Connection Pool.
I got to this conclusion because after about a minute or two I get dozens of messages like this:

"UniDirect.NET: ProviderConnection (35017241) - Disconnect"

in the Output window of Visual Studio.
However, I don't understand, because I explicitely added to the connectionstring: "Pooling=false", but that makes no difference at all

I also tried clearing the pool with
CoreLab.UniDirect.UniConnection.ClearAllPools()
but same thing happened

Any ideas why the Connection Pool is not getting cleared?

Shalex
Site Admin
Posts: 9543
Joined: Thu 14 Aug 2008 12:44

Post by Shalex » Mon 27 Oct 2008 08:13

We are investigating this issue. You will be notified on the results as soon as possible.

jorgesv13
Posts: 7
Joined: Thu 09 Oct 2008 00:54

Post by jorgesv13 » Mon 27 Oct 2008 21:53

Thank you,

I also tried to solve the problem by increasing the Max Pool Size to 200 connections.
It did improve a little, but still have the problem after about 500 queries.

The problems I have now can be sumed up as follow:
1. The "Pooling=false" do not actually turn off the Connection Pooling
2. When set to "Pooling=true" I think that the DataProvider still opens a new connection instead of taking one from the pool. Because I still see messages like:
"UniDirect.NET: ProviderConnection (35017241) - Connect"
"UniDirect.NET: ProviderConnection (35017241) - Open"
in the output window, for each query!

jorgesv13
Posts: 7
Joined: Thu 09 Oct 2008 00:54

Post by jorgesv13 » Wed 29 Oct 2008 00:15

Ok, I found the problem.
It seems that when executing a Reader with the Behaviour CloseConnection, it doesn't actually close the connection when the DataReader is closed:

Code: Select all

cmd.ExecuteReader(CommandBehavior.CloseConnection) //this does not work!
If I explicitely close the connection after finishing the reader, it works fine.

However, I use this in a lot of places in my code, so I don't want to change it all.
Is this bug will be fixed anytime soon?

Post Reply