On Tue, 19 Sep 2006 15:26:40 -0700, vanwout wrote:

> 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!

This is just an experiment.

Try defining a PassThrough query that will bypass all the MS libraries and
speak directly to the MySQL server. All you want is to have the server
execute a sql statement and deliver the results to an ADO or DAO recordset
object. PassThrough queries bypass most of ADO to achieve this.

Microsoft libraries are now so larded over with anti virus patches that
performance is definitely deteriorating. This is particularly true for
anything that needs to talk to a server over TCP/IP. It is no surprise
anymore to have MS replace a few .DLLs via an automated update and have
things mysteriously tank from one day to the next.

Thomas Bartkus