SEO

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

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

Hi Thomas,

Thanks for your message....
I just spent 30 minutes on the internet to search try to understand
your suggestion...
How do I define a passthrough query in ASP with mySQL?

However, even if the passthrough query works, and this will be very
interesting for me to learn, it still doesn't explain how all of a
sudden the speed of the application plunged; and what I can do about it
to fix it....

Thanks!

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

On Wed, 20 Sep 2006 00:13:12 -0700, vanwout wrote:

> Hi Thomas,
>
> Thanks for your message....
> I just spent 30 minutes on the internet to search try to understand your
> suggestion...
> How do I define a passthrough query in ASP with mySQL?

I know nothing of ASP. You must be using the Microsoft DAO or ADODB
libaralibraries. When you initiate your database or connection object,
you have the opportunity to add a dbSQLPassthrough switch that indicates
you want the system to pass the raw query through and let the the server
interpret the SQL and return the result set.

One trick I have resorted to is to use MS Access (.mdb) files. You can
create stored queries tagged as PassThrough. From there you can just
treat it like an ordinary Access database. The SQL in the query will
simply be sent to the server without interpretation by Microsoft and you
get the results back in a recordset. You can even dynamically alter the
SQL inside the querydef objects (in the Access .mdb file!), throwing
different MySQL legal queries into it. This is *mucho mas* faster that
letting ADO interact with MySQL directly.

> However, even if the passthrough query works, and this will be very
> interesting for me to learn, it still doesn't explain how all of a
> sudden the speed of the application plunged;

That's a question for Microsoft.

> and what I can do about it
> to fix it....

In my case, I have found the solution in falling back to older ADO library
..dll(s) and blocking automated Microsoft updates.

This is not wonderful. The more viable, long term solution for me is to
abandon attempts to mix Microsoft with non-Microsoft technologies (such as
MySQL). For me this means Microsoft gets the boot. I can't deal with
building my stuff on foundations of loose sand.

Sour grapes aside. You would probably get better results and better
responsiveness from Microsoft if you stick with all Microsoft technology
like SQL server or whatever the heck it is they push nowadays.

Thomas Bartkus



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