Slow queries when using myODBC with ASP pages - MySQL

[Fred]

Hi,

I have a website hosted on two servers bi-xeon(Win2003, 1GB RAM) : one
for the website, one for MySQL 4.

I access MySQL from my ASP Pages with myOBDC.

I noticed a strange problem :
When I execute a quite complex query using MySQL control center directly
on the MySQL server, I have the following execution times :

- With ODBC turned off (ie the website is stopped, so I have no
connections from the website to MySQL) : 0.1 sec
- With MyODBC on (15 connections simultaneously) : 1,5sec

Do you have any idea of where the problem can come from and how I can
solve it ?

Thanks for your help,

Fred

[Giuseppe Maxia]

Fred wrote:

> Hi,
>
> I have a website hosted on two servers bi-xeon(Win2003, 1GB RAM) : one
> for the website, one for MySQL 4.
>
> I access MySQL from my ASP Pages with myOBDC.
>
> I noticed a strange problem :
> When I execute a quite complex query using MySQL control center directly
> on the MySQL server, I have the following execution times :
>
> - With ODBC turned off (ie the website is stopped, so I have no
> connections from the website to MySQL) : 0.1 sec
> - With MyODBC on (15 connections simultaneously) : 1,5sec
>
> Do you have any idea of where the problem can come from and how I can
> solve it ?
>
> Thanks for your help,
>
> Fred

MySQL Control Center uses direct connections (i.e. through calls to
MySQL C API library) while MyODBC has a few additional layers on top
of taht. Thus, connections through ODBC are always slower than direct
connections.

Instead of MyODBC, you may try Connector/.NET, which does not have any
intermediate libraries to slow down the data flow.
[url]http://dev.mysql.com/doc/refman/5.0/en/connector-net.html[/url]

Some tutorials here:
[url]http://www.vbmysql.com/articles[/url]

ciao
gmax

--
_ _ _ _
(_|| | |(_|>< The Data Charmer
_|
[url]http://datacharmer.blogspot.com/[/url]

[Fred]

Giuseppe Maxia a écrit :

>
> MySQL Control Center uses direct connections (i.e. through calls to
> MySQL C API library) while MyODBC has a few additional layers on top
> of taht. Thus, connections through ODBC are always slower than direct
> connections.

Thanks for your answer.

In fact, the execution times was both measured executing the query in
MySQL CC, the difference is just that in one case, Mysql was also
sharing connections with MyOBDC.

>
> Instead of MyODBC, you may try Connector/.NET,

My website is developped in ASP, not in ASP.NET.
Can it work (ASP.NET works on my web server) ?

[Giuseppe Maxia]

Fred wrote:

> Giuseppe Maxia a écrit :
>

>>
>> MySQL Control Center uses direct connections (i.e. through calls to
>> MySQL C API library) while MyODBC has a few additional layers on top
>> of taht. Thus, connections through ODBC are always slower than direct
>> connections.

>
> Thanks for your answer.
>
> In fact, the execution times was both measured executing the query in
> MySQL CC, the difference is just that in one case, Mysql was also
> sharing connections with MyOBDC.
>

>>
>> Instead of MyODBC, you may try Connector/.NET,

>
> My website is developped in ASP, not in ASP.NET.
> Can it work (ASP.NET works on my web server) ?

I am not very much knowledgeable on ASP programming.
You should check the docs I already mentioned, or browse the archives in the relevant forum:
[url]http://forums.mysql.com/list.php?38[/url]

ciao
gmax

--
_ _ _ _
(_|| | |(_|>< The Data Charmer
_|
[url]http://datacharmer.blogspot.com/[/url]

[Fred]

Fred a écrit :

> Hi,
>
> I have a website hosted on two servers bi-xeon(Win2003, 1GB RAM) : one
> for the website, one for MySQL 4.
>
> I access MySQL from my ASP Pages with myOBDC.

I made som other tests :

- On the dataserver, with MyODBC, the query runs in 1.5 sec
- On the web server (connected directly with an ip 192.168.0.1) : it
takes between 3 and 18 sec
- On an other server connected via a switch : 1.5 sec

It seems that there is a communication problem between the database
server and the webserver (the TCP/IP is working fine with file copying,
for example), but i don't know what happens.

MyODBC configuration is the same on the three servers.

I tried the skip-name-resolve option, but the problem persists.

[Fred]

Fred a écrit :

> It seems that there is a communication problem between the database
> server and the webserver (the TCP/IP is working fine with file copying,
> for example), but i don't know what happens.

I finally solved my problem : I changed my network adapter configuration
from full-duplex to half-duplex.

Fred