strange mySQL speed problem over myODBC / ADO

------_=_NextPart_001_01C6DC3A.31264130
Content-Type: text/plain;
charset="us-ascii"
Content-Transfer-Encoding: quoted-printable

Hello,=20

=20

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.

=20

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.

=20

I was happy!

=20

But....

=20

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!

=20

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

=20

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.

=20

Here is my connection string:=20

=20

DataSource =3D DataSource & "DRIVER=3D{MySQL ODBC 3.51 Driver};"

DataSource =3D DataSource & "server=3Dlocalhost;"

DataSource =3D DataSource & "DATABASE=3DXXX;"

DataSource =3D DataSource & "UID=3DXXX;"

DataSource =3D DataSource & "PWD=3DXXXX;"

=20

DataSource =3D DataSource & "Option=3D16386"=20

=20

Cn.open DataSource=20

=20

I tried to add the option 1048576 (do not cache results locally in the
driver, instead read from server).=20

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.

=20

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!

=20

=20

Best regards,=20

=20

Wouter

=20



This message has been scanned for viruses by BlackSpider MailControl.

------_=_NextPart_001_01C6DC3A.31264130--

Re: strange mySQL speed problem over myODBC / ADO

--------------040700010808020607020702
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: quoted-printable


Hello Wouter:

If you can, I suggest you to install MySQL in other machine, do a backup =

of the databases, restore in the new machine, and try what happened. As=20
you know, in your program only you need to change the connection string.

One question: only you connect directly to Server by connection string,=20
or perhaps you've used a DSN connection....?.

Best regards,

Josemi

---------------------------------

Wouter van der Lelij escribi=F3:
> Hello,=20
>
> =20
>
> 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.
>
> =20
>
> 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.
>
> =20
>
> I was happy!
>
> =20
>
> But....
>
> =20
>
> 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!
>
> =20
>
> 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.....
>
> =20
>
> 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.
>
> =20
>
> Here is my connection string:=20
>
> =20
>
> DataSource =3D DataSource & "DRIVER=3D{MySQL ODBC 3.51 Driver};"
>
> DataSource =3D DataSource & "server=3Dlocalhost;"
>
> DataSource =3D DataSource & "DATABASE=3DXXX;"
>
> DataSource =3D DataSource & "UID=3DXXX;"
>
> DataSource =3D DataSource & "PWD=3DXXXX;"
>
> =20
>
> DataSource =3D DataSource & "Option=3D16386"=20
>
> =20
>
> Cn.open DataSource=20
>
> =20
>
> I tried to add the option 1048576 (do not cache results locally in the
> driver, instead read from server).=20
>
> 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.
>
> =20
>
> The easy idea would be to reinstall, but if anyone has any idea why thi=
s
> strange problem is occurring and what we can do about it...any feedback=

> would be very much appreciated!
>
> =20
>
> =20
>
> Best regards,=20
>
> =20
>
> Wouter
>
> =20
>
>
>
> This message has been scanned for viruses by BlackSpider MailControl.
>
> =20

--------------040700010808020607020702--

Re: strange mySQL speed problem over myODBC / ADO

Wouter van der Lelij wrote:
> 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.
>

Maybe something's turned on ODBC tracing, or logging, or whatever they
call it. There are driver-specific places to set this, and there is also
a generic 'trace all ODBC stuff' setting ... also somewhere. Sorry -
it's been a quite while since I had to muck around with Windows ODBC
stuff, thankfully. Look in the ODBC Administrator section in the control
panel.

Dan

--
Daniel Kasak
IT Developer
NUS Consulting Group
Level 5, 77 Pacific Highway
North Sydney, NSW, Australia 2060
T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989
email: dkasak@nusconsulting.com.au
website: http://www.nusconsulting.com.au

--
MySQL ODBC Mailing List
For list archives: http://lists.mysql.com/myodbc
To unsubscribe: http://lists.mysql.com/myodbc?unsub=gcdmo-myodbc@m.gmane.org