Connector/Net connection performance

[Pär Ågren]

I know this has been discussed previously, but I need som updates on the issue.

I'm starting a new project where I will be using ASP.Net with MySQL and (hopefully) Connector/Net. The ASP.Net application will in addition to handle the "business logic" produce XML which will be formatted into HTML with XSL transformations. In many cases, this will be achieved by merely using a naming convention in the SQL statements so that column names easily can be transformed into XML tags in one generic function. I know I can send raw datasets as XML by this construct simplifies the creation of stylesheets.

I have an old ASP/MyODBC application that achieved something similar and it returns data very fast (even larger result sets are transformed and returned within a second).

However, the new ASP.Net application always take at least 4-5 seconds. This is too long, in the new application these requests will be made very, very frequently.

Testing the application in a console application with the connection kept open between requests I have response times similar to the old ASP/MyODBC application so the bottleneck is not the code that transforms the result into XML (it uses the MySqlDataReader and XMLTextWriter).

It might be some strange ASP.Net behavior. I'm fairly new to it, but so far I have not been able to find out what it could be. Other compiled ASP.Net applications responds fairly quickly. The debug switch is set to false in the Web.config file.

It seems that although connection pooling is on, it takes a very long time to get an actual connection from the pool. In my experience it has always been very fast to establish connections with MySql (whether using MyODBC or libmysql.dll) so this feels weird and a bit discouraging.

I have worked with other types of database connections (z39.50 if any one outside the library world knows what that is ;-)) and created my own connection pool managers. I guess I could implement the IHttpHandler interface in my application and keep connections open myself, but it feels messy and it should not be needed.

Hope someone has any ideas on what's going on here.



/Pär