slow access to MySql Database

[Jim Lawton]

I'm not sure where my problem lies, but someone here might have a suggestion.

I'm developing a .NET web application, (but I don't really) think that's
relevant.

I'm running MySQL 4.1.7-nt on my laptop, and I'm accessing it through ODBC
(MyODBC-3.51.10-x86-win-32bit).

I already had the application running OK on my desktop, and when I created the
same environment on the laptop, retrieving trivial amounts of data (5 rows) from
a table is taking 6 seconds. Updates the same. IN otherwords any table access
takes 5 or six seconds. (Table only has about twenty rows in it).

I can connect to the database on the laptop from the desktop (i.e - application
runs on desktop, retrieves data from laptop - works fine, not noticeably slower
than local on desktop.

If I connect the other way - application on laptop, database on desktop, it runs
just as slowly as locally.

The effect is the same whether I connect to 127.0.0.1 or via the machine name.

I don't really know where to look. Any suggestions would be gratefully
received...

Jim

[Chris R. Timmons]

Jim Lawton <uc**@use.your.initiative> wrote in
news:tj********************************@4ax.com:

I'm not sure where my problem lies, but someone here might have
a suggestion.

I'm developing a .NET web application, (but I don't really)
think that's relevant.

I'm running MySQL 4.1.7-nt on my laptop, and I'm accessing it
through ODBC (MyODBC-3.51.10-x86-win-32bit).

I already had the application running OK on my desktop, and when
I created the same environment on the laptop, retrieving trivial
amounts of data (5 rows) from a table is taking 6 seconds.
Updates the same. IN otherwords any table access takes 5 or six
seconds. (Table only has about twenty rows in it).

I can connect to the database on the laptop from the desktop
(i.e - application runs on desktop, retrieves data from laptop -
works fine, not noticeably slower than local on desktop.

If I connect the other way - application on laptop, database on
desktop, it runs just as slowly as locally.

The effect is the same whether I connect to 127.0.0.1 or via the
machine name.

I don't really know where to look. Any suggestions would be
gratefully received...

Jim,

I'm not sure what the exact problem could be, but here are some
ideas to try on your laptop:

- Turn on ODBC Trace.

Go to Start / Programs / Administrative Tools / Data Sources (ODBC).

Click on the Tracing tab.

- I'm sure MySql also has some kind of tracing or logging facility.
You might want to check into that.

- Turn off System Restore.

I encountered a similar problem with Interbase database files that
have a GDB extension. System Restore will backup files with a
certain extension every time they are modified, which can slow
things down a lot if the file is frequently modified.

The file extensions that System Restore backs up are listed in
FileList.xml, located somewhere under c:\windows. Note that this
file cannot be modified. If it is, Windows will simply overwrite
the changes by refreshing it from the system cache.
--
Hope this helps.

Chris.
-------------
C.R. Timmons Consulting, Inc.
http://www.crtimmonsinc.com/

[Cristian Lupu]

Hi,
Why use ODBC (the old 3.51) in C#. You can find a free managed
provider (URL http://umn.dl.sourceforge.
net/sourceforge/mysqldrivercs/MySQLDriverCS-n-EasyQueryTools-3.0.16b.
exe) and it's fast.
Best regards
ubik
11/25/2004 9:51:21 AM
Jim Lawton <uc**@use.your.initiative> wrote in message
<tj********************************@4ax.com>

I'm not sure where my problem lies, but someone here might have a suggestion.
I'm developing a .NET web application, (but I don't really) think that's relevant.

I'm running MySQL 4.1.7-nt on my laptop, and I'm accessing it through ODBC (MyODBC-3.51.10-x86-win-32bit).

I already had the application running OK on my desktop, and when I created the same environment on the laptop, retrieving trivial amounts of data (5 rows) from a table is taking 6 seconds. Updates the same. IN otherwords any table access takes 5 or six seconds. (Table only has about twenty rows in it).

I can connect to the database on the laptop from the desktop (i.e - application runs on desktop, retrieves data from laptop - works fine, not noticeably slower than local on desktop.

If I connect the other way - application on laptop, database on desktop, it runs just as slowly as locally.

The effect is the same whether I connect to 127.0.0.1 or via the machine name.
I don't really know where to look. Any suggestions would be gratefully received...

Jim

[Jim Lawton]

On Thu, 25 Nov 2004 06:42:46 -0800, "Chris R. Timmons"
<crtimmons@X_NOSPAM_Xcrtimmonsinc.com> wrote:

Jim Lawton <uc**@use.your.initiative> wrote in
news:tj********************************@4ax.com :

I'm not sure where my problem lies, but someone here might have
a suggestion.

I'm developing a .NET web application, (but I don't really)
think that's relevant.

I'm running MySQL 4.1.7-nt on my laptop, and I'm accessing it
through ODBC (MyODBC-3.51.10-x86-win-32bit).

I already had the application running OK on my desktop, and when
I created the same environment on the laptop, retrieving trivial
amounts of data (5 rows) from a table is taking 6 seconds.
Updates the same. IN otherwords any table access takes 5 or six
seconds. (Table only has about twenty rows in it).

I can connect to the database on the laptop from the desktop
(i.e - application runs on desktop, retrieves data from laptop -
works fine, not noticeably slower than local on desktop.

If I connect the other way - application on laptop, database on
desktop, it runs just as slowly as locally.

The effect is the same whether I connect to 127.0.0.1 or via the
machine name.

I don't really know where to look. Any suggestions would be
gratefully received...

Jim,

I'm not sure what the exact problem could be, but here are some
ideas to try on your laptop:

- Turn on ODBC Trace.

Go to Start / Programs / Administrative Tools / Data Sources (ODBC).

Click on the Tracing tab.

- I'm sure MySql also has some kind of tracing or logging facility.
You might want to check into that.

- Turn off System Restore.

I encountered a similar problem with Interbase database files that
have a GDB extension. System Restore will backup files with a
certain extension every time they are modified, which can slow
things down a lot if the file is frequently modified.

The file extensions that System Restore backs up are listed in
FileList.xml, located somewhere under c:\windows. Note that this
file cannot be modified. If it is, Windows will simply overwrite
the changes by refreshing it from the system cache.

Thanks Chris, I'll give these a go - I think the problem does lie with the OS,
because I changes to a native provider, and the symptoms remain the same.

J

[Jim Lawton]

On Thu, 25 Nov 2004 21:21:00 +0200, "Cristian Lupu" <ci********@yahoo.com>
wrote:

Hi,
Why use ODBC (the old 3.51) in C#. You can find a free managed
provider (URL http://umn.dl.sourceforge.
net/sourceforge/mysqldrivercs/MySQLDriverCS-n-EasyQueryTools-3.0.16b.
exe) and it's fast.

Thanks ubik - I tried the managed provider from bytefx - but that had its own
problems. In so far as it worked, it displayed the same symptoms as ODBC.
I'll try your suggestion though, thanks for the tip.

Jim

11/25/2004 9:51:21 AM
Jim Lawton <uc**@use.your.initiative> wrote in message
<tj********************************@4ax.com>

I'm not sure where my problem lies, but someone here might have a

suggestion.

I'm developing a .NET web application, (but I don't really) think

that's

relevant.

I'm running MySQL 4.1.7-nt on my laptop, and I'm accessing it

through ODBC

(MyODBC-3.51.10-x86-win-32bit).

I already had the application running OK on my desktop, and when I

created the

same environment on the laptop, retrieving trivial amounts of data

(5 rows) from

a table is taking 6 seconds. Updates the same. IN otherwords any

table access

takes 5 or six seconds. (Table only has about twenty rows in it).

I can connect to the database on the laptop from the desktop (i.e -

application

runs on desktop, retrieves data from laptop - works fine, not

noticeably slower

than local on desktop.

If I connect the other way - application on laptop, database on

desktop, it runs

just as slowly as locally.

The effect is the same whether I connect to 127.0.0.1 or via the

machine name.

I don't really know where to look. Any suggestions would be

gratefully

received...

Jim

[Jim Lawton]

On Fri, 26 Nov 2004 08:09:19 GMT, Jim Lawton <uc**@use.your.initiative> wrote:

On Thu, 25 Nov 2004 21:21:00 +0200, "Cristian Lupu" <ci********@yahoo.com>
wrote:

Hi,
Why use ODBC (the old 3.51) in C#. You can find a free managed
provider (URL http://umn.dl.sourceforge.
net/sourceforge/mysqldrivercs/MySQLDriverCS-n-EasyQueryTools-3.0.16b.
exe) and it's fast.

I'm no further forward with my basic problem, but I can now say that ByteFX
seems much superior to MySQLDRiverCS better BLOB handling, and more intuitive
dates.

I know this is OT here, but completes the thread for the archive ...


Thanks ubik - I tried the managed provider from bytefx - but that had its own
problems. In so far as it worked, it displayed the same symptoms as ODBC.
I'll try your suggestion though, thanks for the tip.

[Jim Lawton]

On Fri, 26 Nov 2004 08:09:19 GMT, Jim Lawton <uc**@use.your.initiative> wrote:

On Thu, 25 Nov 2004 21:21:00 +0200, "Cristian Lupu" <ci********@yahoo.com>
wrote:

Hi,
Why use ODBC (the old 3.51) in C#. You can find a free managed
provider (URL http://umn.dl.sourceforge.
net/sourceforge/mysqldrivercs/MySQLDriverCS-n-EasyQueryTools-3.0.16b.
exe) and it's fast.

I'm no further forward with my basic problem, but I can now say that ByteFX
seems much superior to MySQLDRiverCS better BLOB handling, and more intuitive
dates.

I know this is OT here, but completes the thread for the archive ...


Thanks ubik - I tried the managed provider from bytefx - but that had its own
problems. In so far as it worked, it displayed the same symptoms as ODBC.
I'll try your suggestion though, thanks for the tip.

[Frans Bouma [C# MVP]]

Jim Lawton wrote:

On Fri, 26 Nov 2004 08:09:19 GMT, Jim Lawton <uc**@use.your.initiative> wrote:

On Thu, 25 Nov 2004 21:21:00 +0200, "Cristian Lupu" <ci********@yahoo.com>
wrote:

Why use ODBC (the old 3.51) in C#. You can find a free managed
provider (URL http://umn.dl.sourceforge.
net/sourceforge/mysqldrivercs/MySQLDriverCS-n-EasyQueryTools-3.0.16b.
exe) and it's fast.

I'm no further forward with my basic problem, but I can now say that ByteFX
seems much superior to MySQLDRiverCS better BLOB handling, and more intuitive
dates.

... though it is discontinued as ByteFX and is now the MySql .NET
provider (downloadable from MySql.org). The problem with the old ByteFX
driver is that it apparently can't connect with the latest mysql 4.1
version (I couldn't get it to work). DId you manage to get it to connect
to a default install of MySql 4.1.1g (the latest I think) with InnoDB?
It kept complaining that my client was out of date, which is an error
which is displayed when you use new password storage settings and an old
client (which I didn't I downloaded the latest stable client/server from
the website)

FB