strange myODBC / ADO speed problem....

Hello,

I've been running a web site powered by mySQL 4.0 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, mySQL 5.0.24a 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 the CPU peaking!

The strange thing is that when I performed the exact same 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

.

Relevant Pages

  • About ADAM and VB web application
    ... for do query about the values of 4 attribute. ... I have about 750 connect/sec on 6 different web server that point to my ... that every time that i try to connect a new socket was open so the nimber of ... At the moment i modified a number of socket that Windows 2003 maybe open ...
    (microsoft.public.windows.server.active_directory)
  • About ADAM and VB web application
    ... for do query about the values of 4 attribute. ... I have about 750 connect/sec on 6 different web server that point to my ... that every time that i try to connect a new socket was open so the nimber of ... At the moment i modified a number of socket that Windows 2003 maybe open ...
    (microsoft.public.vb.6.webdevelopment)
  • Re: Searching AD to determine account usage
    ... I haven't set it to Windows 2003 Server mode yet as I ... >> Is there a tool to query AD and report user login activity? ...
    (microsoft.public.windows.server.active_directory)
  • Re: Performance Benchmarks?
    ... Are attribute relationships defined properly on the dimensions? ... If you run Profiler against the server, how long is spent in "Query ... I have created aggregations (Partitions tab in cube design). ...
    (microsoft.public.sqlserver.olap)
  • strange ADO / myODBC speed problem
    ... Server, ... The largest query took about 1 second, which was reasonable, since all ... After installing Windows 2003 Server, mySQL 5.0.24a and our website on ...
    (microsoft.public.data.ado)