 | | | strange mySQL speed problem over myODBC / ADO | strange mySQL speed problem over myODBC / ADO 2006-09-20 - By Wouter van der Lelij
Back
Hello,
I've been running a web site powered by mySQL 5.0.24a on a Windows 2003
Server, which is working rather well.
The largest query took about 1 second, which was reasonable, since all
the other results after this were "paged" and would only take 0.1
seconds.
The decision was made to invest in a faster new machine to prepare for
future growth.
After installing Windows 2003 Server and our website on the new machine,
we noticed good performance increases; our query now took 0.75 seconds -
before database optimization.
I was happy!
But....
After (foolishly?) installing several components on the server
(including Word and Access 2000, and several ASP components needed for
our web application) I now noticed our performance through the
application plunged dramatically....the same query now took more than 6
seconds with a CPU peaking!
The strange thing is that when I performed the exact query directly on
the mySQL server, the query was very fast (less than 1 second!). So the
issue was obviously not related to the database version nor the
Indexing, nor the application (which hasn't changed, but there is
somehow a problem with the myODBC / ADO interface.....
I uninstalled Access/Office 2000 aswell as the other applications, I
checked using the Component Checker that MDAC 2.8 SP 2 was still
installed, re-installed myODBC 3.51.12 and 13, ....nothing works.
Here is my connection string:
DataSource = DataSource & "DRIVER={MySQL ODBC 3.51 Driver};"
DataSource = DataSource & "server=localhost;"
DataSource = DataSource & "DATABASE=XXX;"
DataSource = DataSource & "UID=XXX;"
DataSource = DataSource & "PWD=XXXX;"
DataSource = DataSource & "Option=16386"
Cn.open DataSource
I tried to add the option 1048576 (do not cache results locally in the
driver, instead read from server).
This worked, the query now took slightly over 1 second.
But now the queries are not cached anymore so the results cannot be
"paged".......
Plus that this is slower than our current production server.
The easy idea would be to reinstall, but if anyone has any idea why this
strange problem is occurring and what we can do about it...any feedback
would be very much appreciated!
Best regards,
Wouter
This message has been scanned for viruses by BlackSpider MailControl.
|
|
|