MySQL ODBC 3.51 driver Very Slow!

I am using the MySQL ODBC 3.51 driver to link three relatively small MySQL
tables to a Microsoft Access 2003 database. I am finding that the data from
the MySQL tables takes a hell of a long time to load making any kind linkage
with my Access data virtually useless.



I have the MySQL driver setup in as a USER DSN. The MySQL data is sitting
out on a server and the Access database is running locally. The network
connection is very good.



Is there anyway to tweak the DSN so it pulls the data faster?



Thanks...



David J

re: MySQL ODBC 3.51 driver Very Slow!

DJJ wrote:
[color=blue]
> I am using the MySQL ODBC 3.51 driver to link three relatively small MySQL
> tables to a Microsoft Access 2003 database. I am finding that the data from
> the MySQL tables takes a hell of a long time to load making any kind linkage
> with my Access data virtually useless.[/color]

I never had problems with the speed of access and MySQL, did you already
check the option 'return only matching rows' This is needed for Access
(see http://dev.mysql.com/doc/mysql/en/msaccess-setup.html)

You can also try and log what is happening, maybe the log will give you
some information (I 've never used it thought). It is possible to trace
driver calls and it is possible to log queries.

http://dev.mysql.com/doc/mysql/en/myodbc-trace.html

Also have al ook here:
http://dev.mysql.com/doc/mysql/en/optimize-access.html

It contains tips to optimize Access for the use of mySQL .

Jonathan

--
A: Because it messes up the order in which people normally read text.
Q: Why is it such a bad thing?
A: Top-posting.
Q: What is the most annoying thing on usenet and in e-mail?

re: MySQL ODBC 3.51 driver Very Slow!

"DJJ" <gemdjj@writme.com> wrote in message
news:RD3me.2483$2u1.1248@newssvr19.news.prodigy.co m...[color=blue]
> I am using the MySQL ODBC 3.51 driver to link three relatively small MySQL
> tables to a Microsoft Access 2003 database. I am finding that the data[/color]
from[color=blue]
> the MySQL tables takes a hell of a long time to load making any kind[/color]
linkage[color=blue]
> with my Access data virtually useless.
>
> I have the MySQL driver setup in as a USER DSN. The MySQL data is sitting
> out on a server and the Access database is running locally. The network
> connection is very good.
>
> Is there anyway to tweak the DSN so it pulls the data faster?[/color]

That driver is not slow!
[color=blue]
> I am finding that the data from
> the MySQL tables takes a hell of a long time to load ...[/color]

You mention that your (3) tables are relatively small. If you are linking
to small MySQL tables, they will typically load very quickly. You need to
give us some more details about your setup. Something is not quite right
here!

Large (linked) table may indeed be slow. Access itself is probably
requireing an immense amount of data to xfer into a recordset upfront. I
preferentially use passthrough queries, bypassing the JET software. "SELECT
* FROM SomeTable;" sent directly to MySQL will tend to load an ADO recordset
quite a bit faster than a linked table. Particularly if you can use a
ForwardOnly recordset. Of course this is a one way load. You will need a
separate passthrough query to write back to the table.

Pass through queries are a very efficient way to bind a MySQL server to the
MS Office system. In any case, it is doubtful that the ODBC 3.51 driver is
the problem. Perhaps you could provide a bit more detail?

Thomas Bartkus

re: MySQL ODBC 3.51 driver Very Slow!

Thanks very much for the info.



I did setup the following parameters but with little or no difference.



Don't Optimize Column Width

Return Matching Rows

Allow Big Results

Use Compressed Protocol



It takes 16 minutes to load and scroll to the last record of a table in
datasheet mode with 1319 records, 40 fields including a primary key.



My network speed is 100 mpbs



I also tried connecting to a copy of the same MySQL table I have locally on
my machine on a separately configured DNS and it was still not much better.
So it is definitely not the network



DJJ



"Jonathan" <jonathan@heelal.nl> wrote in message
news:d7c4hc$sgj$1@reader10.wxs.nl...[color=blue]
> DJJ wrote:
>[color=green]
> > I am using the MySQL ODBC 3.51 driver to link three relatively small[/color][/color]
MySQL[color=blue][color=green]
> > tables to a Microsoft Access 2003 database. I am finding that the data[/color][/color]
from[color=blue][color=green]
> > the MySQL tables takes a hell of a long time to load making any kind[/color][/color]
linkage[color=blue][color=green]
> > with my Access data virtually useless.[/color]
>
> I never had problems with the speed of access and MySQL, did you already
> check the option 'return only matching rows' This is needed for Access
> (see http://dev.mysql.com/doc/mysql/en/msaccess-setup.html)
>
> You can also try and log what is happening, maybe the log will give you
> some information (I 've never used it thought). It is possible to trace
> driver calls and it is possible to log queries.
>
> http://dev.mysql.com/doc/mysql/en/myodbc-trace.html
>
> Also have al ook here:
> http://dev.mysql.com/doc/mysql/en/optimize-access.html
>
> It contains tips to optimize Access for the use of mySQL .
>
> Jonathan
>
> --
> A: Because it messes up the order in which people normally read text.
> Q: Why is it such a bad thing?
> A: Top-posting.
> Q: What is the most annoying thing on usenet and in e-mail?[/color]

re: MySQL ODBC 3.51 driver Very Slow!

What kind of info do you need?



I've tried placing an initial select statement under the odbc options to
reduce the number of fields and records but it doesn't work. This odbc is
unbelievably slow.



"Thomas Bartkus" <tom@dtsam.com> wrote in message
news:5JSdncSWy_108wHfRVn-ig@telcove.net...[color=blue]
> "DJJ" <gemdjj@writme.com> wrote in message
> news:RD3me.2483$2u1.1248@newssvr19.news.prodigy.co m...[color=green]
> > I am using the MySQL ODBC 3.51 driver to link three relatively small[/color][/color]
MySQL[color=blue][color=green]
> > tables to a Microsoft Access 2003 database. I am finding that the data[/color]
> from[color=green]
> > the MySQL tables takes a hell of a long time to load making any kind[/color]
> linkage[color=green]
> > with my Access data virtually useless.
> >
> > I have the MySQL driver setup in as a USER DSN. The MySQL data is[/color][/color]
sitting[color=blue][color=green]
> > out on a server and the Access database is running locally. The network
> > connection is very good.
> >
> > Is there anyway to tweak the DSN so it pulls the data faster?[/color]
>
> That driver is not slow!
>[color=green]
> > I am finding that the data from
> > the MySQL tables takes a hell of a long time to load ...[/color]
>
> You mention that your (3) tables are relatively small. If you are linking
> to small MySQL tables, they will typically load very quickly. You need to
> give us some more details about your setup. Something is not quite right
> here!
>
> Large (linked) table may indeed be slow. Access itself is probably
> requireing an immense amount of data to xfer into a recordset upfront. I
> preferentially use passthrough queries, bypassing the JET software.[/color]
"SELECT[color=blue]
> * FROM SomeTable;" sent directly to MySQL will tend to load an ADO[/color]
recordset[color=blue]
> quite a bit faster than a linked table. Particularly if you can use a
> ForwardOnly recordset. Of course this is a one way load. You will need a
> separate passthrough query to write back to the table.
>
> Pass through queries are a very efficient way to bind a MySQL server to[/color]
the[color=blue]
> MS Office system. In any case, it is doubtful that the ODBC 3.51 driver[/color]
is[color=blue]
> the problem. Perhaps you could provide a bit more detail?
>
> Thomas Bartkus
>
>
>[/color]

re: MySQL ODBC 3.51 driver Very Slow!

"DJJ" <gemdjj@writme.com> wrote in message
news:dynne.11346$iA6.1360@newssvr19.news.prodigy.c om...[color=blue]
> What kind of info do you need?
>[/color]

How big is the table?
"relatively small" doesn't tell us too much. Linked tables that contain
more than >100000 records are going to be slow. I generally work around
this by using pass through queries instead of linked tables.

Does the linked table have a primary key that DAO/ADO recognizes?
BigInt type fields won't cut it here because it is alien to these Microsoft
libraries.

Have you tried the [Test Data Source] button on the ODBC dialog box?
If it takes more than a split second to snap back with a "Success" dialog
box, then you have probably have some kind of interface parameter problem.

Where is MySQL running? On a seperate server (Linux? Windows?) or is it
running locally on the same Windows machine you are running Access?
[color=blue]
> I've tried placing an initial select statement under the odbc options to
> reduce the number of fields and records but it doesn't work.[/color]

You might be better off looking inside Access for the [Advanced] tab of
Tools_Options. where you will find several ODBC settings to tweak. I
confess I haven't played much with these, opting instead to work with pass
through queries. Note that you are working with an *extra* software layer
with Microsoft JET. Linked tables are going to be slower than working
directly with MySQL for reasons that have nothing to do with ODBC. But the
good news is that you can work around this by using pass through queries.
Filling a DAO or ADO recordset with data coming from a MySQL server should
be quite fast. The speed limitation should be the pipes between your server
and the cpu where ADO is running. The overhead caused by the MySQL/ODBC
driver shouldn't even enter the picture.
[color=blue]
> This odbc is unbelievably slow.[/color]
Again I would suggest that blaming odbc will yield little in terms of
speeding up your queries. It is quite doubtful that the V3.51 MySQL driver
is the bottleneck. I have too many examples of joining tables from a
separate MySQL server with local Access tables with excellent results. They
are *fast*!

Thomas Bartkus
[color=blue]
> "Thomas Bartkus" <tom@dtsam.com> wrote in message
> news:5JSdncSWy_108wHfRVn-ig@telcove.net...[color=green]
> > "DJJ" <gemdjj@writme.com> wrote in message
> > news:RD3me.2483$2u1.1248@newssvr19.news.prodigy.co m...[color=darkred]
> > > I am using the MySQL ODBC 3.51 driver to link three relatively small[/color][/color]
> MySQL[color=green][color=darkred]
> > > tables to a Microsoft Access 2003 database. I am finding that the[/color][/color][/color]
data[color=blue][color=green]
> > from[color=darkred]
> > > the MySQL tables takes a hell of a long time to load making any kind[/color]
> > linkage[color=darkred]
> > > with my Access data virtually useless.
> > >
> > > I have the MySQL driver setup in as a USER DSN. The MySQL data is[/color][/color]
> sitting[color=green][color=darkred]
> > > out on a server and the Access database is running locally. The[/color][/color][/color]
network[color=blue][color=green][color=darkred]
> > > connection is very good.
> > >
> > > Is there anyway to tweak the DSN so it pulls the data faster?[/color]
> >
> > That driver is not slow!
> >[color=darkred]
> > > I am finding that the data from
> > > the MySQL tables takes a hell of a long time to load ...[/color]
> >
> > You mention that your (3) tables are relatively small. If you are[/color][/color]
linking[color=blue][color=green]
> > to small MySQL tables, they will typically load very quickly. You need[/color][/color]
to[color=blue][color=green]
> > give us some more details about your setup. Something is not quite right
> > here!
> >
> > Large (linked) table may indeed be slow. Access itself is probably
> > requireing an immense amount of data to xfer into a recordset upfront.[/color][/color]
I[color=blue][color=green]
> > preferentially use passthrough queries, bypassing the JET software.[/color]
> "SELECT[color=green]
> > * FROM SomeTable;" sent directly to MySQL will tend to load an ADO[/color]
> recordset[color=green]
> > quite a bit faster than a linked table. Particularly if you can use a
> > ForwardOnly recordset. Of course this is a one way load. You will need[/color][/color]
a[color=blue][color=green]
> > separate passthrough query to write back to the table.
> >
> > Pass through queries are a very efficient way to bind a MySQL server to[/color]
> the[color=green]
> > MS Office system. In any case, it is doubtful that the ODBC 3.51 driver[/color]
> is[color=green]
> > the problem. Perhaps you could provide a bit more detail?
> >
> > Thomas Bartkus
> >
> >
> >[/color]
>
>[/color]

re: MySQL ODBC 3.51 driver Very Slow!

The table is small. The database is on a Windows 2K Server.

I tried an ADO recordset to populate a single text box on a form as per your
suggestion. It seems to run faster but the text box shows no data (#Name?)

The SQL query was tested on using MySQL query browser. The DSN connection
tests ok. Here's the code I used:

Option Compare Database
Dim Conn As ADODB.Connection
Dim rsInventory As ADODB.Recordset
Dim strConn As String
Dim strSQLInventory As String

Private Sub Form_Open(Cancel As Integer)

strConn = "Provider=MSDASQL.1;Persist Security Info=False;Data
Source=BusinessMind"

Set Conn = New ADODB.Connection
Conn.Open strConn

Set rsInventory = New ADODB.Recordset
rsInventory.Open "SELECT stock_id FROM inventory", Conn, adOpenStatic,
adLockOptimistic

Me!txtStockNum.ControlSource = "stock_id"

End Sub

Private Sub Form_Close()

rsInventory.Close
Conn.Close
Set rsInventory = Nothing
Set Conn = Nothing

End Sub


"Thomas Bartkus" <tom@dtsam.com> wrote in message
news:5uSdnRzHFs2_hAPfRVn-rA@telcove.net...[color=blue]
> "DJJ" <gemdjj@writme.com> wrote in message
> news:dynne.11346$iA6.1360@newssvr19.news.prodigy.c om...[color=green]
> > What kind of info do you need?
> >[/color]
>
> How big is the table?
> "relatively small" doesn't tell us too much. Linked tables that[/color]
contain[color=blue]
> more than >100000 records are going to be slow. I generally work around
> this by using pass through queries instead of linked tables.
>
> Does the linked table have a primary key that DAO/ADO recognizes?
> BigInt type fields won't cut it here because it is alien to these[/color]
Microsoft[color=blue]
> libraries.
>
> Have you tried the [Test Data Source] button on the ODBC dialog box?
> If it takes more than a split second to snap back with a "Success" dialog
> box, then you have probably have some kind of interface parameter problem.
>
> Where is MySQL running? On a seperate server (Linux? Windows?) or is it
> running locally on the same Windows machine you are running Access?
>[color=green]
> > I've tried placing an initial select statement under the odbc options to
> > reduce the number of fields and records but it doesn't work.[/color]
>
> You might be better off looking inside Access for the [Advanced] tab of
> Tools_Options. where you will find several ODBC settings to tweak. I
> confess I haven't played much with these, opting instead to work with pass
> through queries. Note that you are working with an *extra* software layer
> with Microsoft JET. Linked tables are going to be slower than working
> directly with MySQL for reasons that have nothing to do with ODBC. But[/color]
the[color=blue]
> good news is that you can work around this by using pass through queries.
> Filling a DAO or ADO recordset with data coming from a MySQL server should
> be quite fast. The speed limitation should be the pipes between your[/color]
server[color=blue]
> and the cpu where ADO is running. The overhead caused by the MySQL/ODBC
> driver shouldn't even enter the picture.
>[color=green]
> > This odbc is unbelievably slow.[/color]
> Again I would suggest that blaming odbc will yield little in terms of
> speeding up your queries. It is quite doubtful that the V3.51 MySQL[/color]
driver[color=blue]
> is the bottleneck. I have too many examples of joining tables from a
> separate MySQL server with local Access tables with excellent results.[/color]
They[color=blue]
> are *fast*!
>
> Thomas Bartkus
>[color=green]
> > "Thomas Bartkus" <tom@dtsam.com> wrote in message
> > news:5JSdncSWy_108wHfRVn-ig@telcove.net...[color=darkred]
> > > "DJJ" <gemdjj@writme.com> wrote in message
> > > news:RD3me.2483$2u1.1248@newssvr19.news.prodigy.co m...
> > > > I am using the MySQL ODBC 3.51 driver to link three relatively small[/color]
> > MySQL[color=darkred]
> > > > tables to a Microsoft Access 2003 database. I am finding that the[/color][/color]
> data[color=green][color=darkred]
> > > from
> > > > the MySQL tables takes a hell of a long time to load making any kind
> > > linkage
> > > > with my Access data virtually useless.
> > > >
> > > > I have the MySQL driver setup in as a USER DSN. The MySQL data is[/color]
> > sitting[color=darkred]
> > > > out on a server and the Access database is running locally. The[/color][/color]
> network[color=green][color=darkred]
> > > > connection is very good.
> > > >
> > > > Is there anyway to tweak the DSN so it pulls the data faster?
> > >
> > > That driver is not slow!
> > >
> > > > I am finding that the data from
> > > > the MySQL tables takes a hell of a long time to load ...
> > >
> > > You mention that your (3) tables are relatively small. If you are[/color][/color]
> linking[color=green][color=darkred]
> > > to small MySQL tables, they will typically load very quickly. You[/color][/color][/color]
need[color=blue]
> to[color=green][color=darkred]
> > > give us some more details about your setup. Something is not quite[/color][/color][/color]
right[color=blue][color=green][color=darkred]
> > > here!
> > >
> > > Large (linked) table may indeed be slow. Access itself is probably
> > > requireing an immense amount of data to xfer into a recordset upfront.[/color][/color]
> I[color=green][color=darkred]
> > > preferentially use passthrough queries, bypassing the JET software.[/color]
> > "SELECT[color=darkred]
> > > * FROM SomeTable;" sent directly to MySQL will tend to load an ADO[/color]
> > recordset[color=darkred]
> > > quite a bit faster than a linked table. Particularly if you can use a
> > > ForwardOnly recordset. Of course this is a one way load. You will[/color][/color][/color]
need[color=blue]
> a[color=green][color=darkred]
> > > separate passthrough query to write back to the table.
> > >
> > > Pass through queries are a very efficient way to bind a MySQL server[/color][/color][/color]
to[color=blue][color=green]
> > the[color=darkred]
> > > MS Office system. In any case, it is doubtful that the ODBC 3.51[/color][/color][/color]
driver[color=blue][color=green]
> > is[color=darkred]
> > > the problem. Perhaps you could provide a bit more detail?
> > >
> > > Thomas Bartkus
> > >
> > >
> > >[/color]
> >
> >[/color]
>
>[/color]

re: MySQL ODBC 3.51 driver Very Slow!

Added to my code a debug print

Debug.Print rsInventory!stock_id

And I could see the first record in the table. So the connection seems to
be working but when the control source is set to stock_id no records show.
Would you know why that is?

Thanks for all your help so far...

DJJ


"DJJ" <gemdjj@writme.com> wrote in message
news:6SJpe.8703$r71.8132@newssvr31.news.prodigy.co m...[color=blue]
> The table is small. The database is on a Windows 2K Server.
>
> I tried an ADO recordset to populate a single text box on a form as per[/color]
your[color=blue]
> suggestion. It seems to run faster but the text box shows no data[/color]
(#Name?)[color=blue]
>
> The SQL query was tested on using MySQL query browser. The DSN connection
> tests ok. Here's the code I used:
>
> Option Compare Database
> Dim Conn As ADODB.Connection
> Dim rsInventory As ADODB.Recordset
> Dim strConn As String
> Dim strSQLInventory As String
>
> Private Sub Form_Open(Cancel As Integer)
>
> strConn = "Provider=MSDASQL.1;Persist Security Info=False;Data
> Source=BusinessMind"
>
> Set Conn = New ADODB.Connection
> Conn.Open strConn
>
> Set rsInventory = New ADODB.Recordset
> rsInventory.Open "SELECT stock_id FROM inventory", Conn, adOpenStatic,
> adLockOptimistic
>
> Me!txtStockNum.ControlSource = "stock_id"
>
> End Sub
>
> Private Sub Form_Close()
>
> rsInventory.Close
> Conn.Close
> Set rsInventory = Nothing
> Set Conn = Nothing
>
> End Sub
>
>
> "Thomas Bartkus" <tom@dtsam.com> wrote in message
> news:5uSdnRzHFs2_hAPfRVn-rA@telcove.net...[color=green]
> > "DJJ" <gemdjj@writme.com> wrote in message
> > news:dynne.11346$iA6.1360@newssvr19.news.prodigy.c om...[color=darkred]
> > > What kind of info do you need?
> > >[/color]
> >
> > How big is the table?
> > "relatively small" doesn't tell us too much. Linked tables that[/color]
> contain[color=green]
> > more than >100000 records are going to be slow. I generally work around
> > this by using pass through queries instead of linked tables.
> >
> > Does the linked table have a primary key that DAO/ADO recognizes?
> > BigInt type fields won't cut it here because it is alien to these[/color]
> Microsoft[color=green]
> > libraries.
> >
> > Have you tried the [Test Data Source] button on the ODBC dialog box?
> > If it takes more than a split second to snap back with a "Success"[/color][/color]
dialog[color=blue][color=green]
> > box, then you have probably have some kind of interface parameter[/color][/color]
problem.[color=blue][color=green]
> >
> > Where is MySQL running? On a seperate server (Linux? Windows?) or is it
> > running locally on the same Windows machine you are running Access?
> >[color=darkred]
> > > I've tried placing an initial select statement under the odbc options[/color][/color][/color]
to[color=blue][color=green][color=darkred]
> > > reduce the number of fields and records but it doesn't work.[/color]
> >
> > You might be better off looking inside Access for the [Advanced] tab of
> > Tools_Options. where you will find several ODBC settings to tweak. I
> > confess I haven't played much with these, opting instead to work with[/color][/color]
pass[color=blue][color=green]
> > through queries. Note that you are working with an *extra* software[/color][/color]
layer[color=blue][color=green]
> > with Microsoft JET. Linked tables are going to be slower than working
> > directly with MySQL for reasons that have nothing to do with ODBC. But[/color]
> the[color=green]
> > good news is that you can work around this by using pass through[/color][/color]
queries.[color=blue][color=green]
> > Filling a DAO or ADO recordset with data coming from a MySQL server[/color][/color]
should[color=blue][color=green]
> > be quite fast. The speed limitation should be the pipes between your[/color]
> server[color=green]
> > and the cpu where ADO is running. The overhead caused by the MySQL/ODBC
> > driver shouldn't even enter the picture.
> >[color=darkred]
> > > This odbc is unbelievably slow.[/color]
> > Again I would suggest that blaming odbc will yield little in terms of
> > speeding up your queries. It is quite doubtful that the V3.51 MySQL[/color]
> driver[color=green]
> > is the bottleneck. I have too many examples of joining tables from a
> > separate MySQL server with local Access tables with excellent results.[/color]
> They[color=green]
> > are *fast*!
> >
> > Thomas Bartkus
> >[color=darkred]
> > > "Thomas Bartkus" <tom@dtsam.com> wrote in message
> > > news:5JSdncSWy_108wHfRVn-ig@telcove.net...
> > > > "DJJ" <gemdjj@writme.com> wrote in message
> > > > news:RD3me.2483$2u1.1248@newssvr19.news.prodigy.co m...
> > > > > I am using the MySQL ODBC 3.51 driver to link three relatively[/color][/color][/color]
small[color=blue][color=green][color=darkred]
> > > MySQL
> > > > > tables to a Microsoft Access 2003 database. I am finding that the[/color]
> > data[color=darkred]
> > > > from
> > > > > the MySQL tables takes a hell of a long time to load making any[/color][/color][/color]
kind[color=blue][color=green][color=darkred]
> > > > linkage
> > > > > with my Access data virtually useless.
> > > > >
> > > > > I have the MySQL driver setup in as a USER DSN. The MySQL data is
> > > sitting
> > > > > out on a server and the Access database is running locally. The[/color]
> > network[color=darkred]
> > > > > connection is very good.
> > > > >
> > > > > Is there anyway to tweak the DSN so it pulls the data faster?
> > > >
> > > > That driver is not slow!
> > > >
> > > > > I am finding that the data from
> > > > > the MySQL tables takes a hell of a long time to load ...
> > > >
> > > > You mention that your (3) tables are relatively small. If you are[/color]
> > linking[color=darkred]
> > > > to small MySQL tables, they will typically load very quickly. You[/color][/color]
> need[color=green]
> > to[color=darkred]
> > > > give us some more details about your setup. Something is not quite[/color][/color]
> right[color=green][color=darkred]
> > > > here!
> > > >
> > > > Large (linked) table may indeed be slow. Access itself is probably
> > > > requireing an immense amount of data to xfer into a recordset[/color][/color][/color]
upfront.[color=blue][color=green]
> > I[color=darkred]
> > > > preferentially use passthrough queries, bypassing the JET software.
> > > "SELECT
> > > > * FROM SomeTable;" sent directly to MySQL will tend to load an ADO
> > > recordset
> > > > quite a bit faster than a linked table. Particularly if you can use[/color][/color][/color]
a[color=blue][color=green][color=darkred]
> > > > ForwardOnly recordset. Of course this is a one way load. You will[/color][/color]
> need[color=green]
> > a[color=darkred]
> > > > separate passthrough query to write back to the table.
> > > >
> > > > Pass through queries are a very efficient way to bind a MySQL server[/color][/color]
> to[color=green][color=darkred]
> > > the
> > > > MS Office system. In any case, it is doubtful that the ODBC 3.51[/color][/color]
> driver[color=green][color=darkred]
> > > is
> > > > the problem. Perhaps you could provide a bit more detail?
> > > >
> > > > Thomas Bartkus
> > > >
> > > >
> > > >
> > >
> > >[/color]
> >
> >[/color]
>
>[/color]

re: MySQL ODBC 3.51 driver Very Slow!

"DJJ" <gemdjj@writme.com> wrote in message
news:6SJpe.8703$r71.8132@newssvr31.news.prodigy.co m...[color=blue]
> The table is small. The database is on a Windows 2K Server.
>
> I tried an ADO recordset to populate a single text box on a form as per[/color]
your[color=blue]
> suggestion. It seems to run faster but the text box shows no data[/color]
(#Name?)[color=blue]
>
> The SQL query was tested on using MySQL query browser. The DSN connection
> tests ok. Here's the code I used:[/color]
<snip>

Okay. I am going to put my own minimalist code here that I just tested on a
small table. Part of the problem is that I can't see into the ODBC
configuration [MSDASQL] you set up. The following code addresses the MySQL
driver directly so we can see up front all the configuration settings. My
guess is that you have a few bad settings in tje [MSDASQL] definition.

I tested this in a 4500 record table. It only takes a *tiny* fraction of a
second to retrieve, loop through, and close the recordset. By far the
biggest overhead is the xfer of data over the network and the loop. The
speed of that, of course, depends on your workstation. The point is - the
MySQL/ODBC driver is *not* a speed bottleneck!

I used ADO 2.8 for this test.
FWIW - DAO is substantially faster than ADO!
I won't get into pass through queries here - but that would speed things up
too!

Try this on your database and see that you can read the data !
And if it's slow - then I have nothing left to say :-)
Thomas Bartkus

===========================================
Public Sub AdoTest()
Dim Conn As New ADODB.Connection
Dim rsInventory As New ADODB.Recordset
Dim sql As String, strCon As String
Dim cnt As Long

strCon = "DRIVER=MySQL ODBC 3.51 Driver;" & _
"SERVER=Your Servers IP;" & _
"DATABASE=Your Databases name;" & _
"UID=Your Id;" & _
"PWD=Your Password" & _
"OPTION=18435;" ' <- BigInt->Int, Compressed Protocol, Don't
optimize col width,
' return matching rows

Conn.Mode = adModeRead ' If you only want to read, then set it so!
It's faster R/O
Conn.Open strCon

sql = "SELECT stock_id FROM inventory;"

rsInventory.Source = sql
rsInventory.ActiveConnection = Conn

' Fastest cursor - so long as we don't need to move back & forth
(forward only!)
rsInventory.CursorType = adOpenForwardOnly
rsInventory.Open

' Prove that we got something.
Do While (Not rsInventory.EOF)
' You should do something useful with the data here!
' I'll just count.
cnt = cnt + 1
rsInventory.MoveNext
Loop
MsgBox "We captured " & cnt & " Records", vbExclamation

' Clean it up, shut it down.
rsInventory.Close
Set rsInventory = Nothing

Conn.Close
Set Conn = Nothing
End Sub

===========================================

re: MySQL ODBC 3.51 driver Very Slow!

I setup code similar to yours that still takes a while to load but it's much
faster. Once the data is loaded in my form I can scroll 1300 records very
quickly, which is great. However, when I try to edit a record I get the
message "This recordset is not updateable". Would you know how to get
around that? Here is the entire code set I'm using:

Private Sub Form_Open(Cancel As Integer)
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset

Set cn = New ADODB.Connection

With cn
.Provider = "MSDASQL.1"
.Properties("Persist Security Info").Value = "False"
.Properties("Data Source").Value = "MyDatabase"
.Open
End With

Set rs = New ADODB.Recordset
With rs
Set .ActiveConnection = cn
.Source = "SELECT i.stock_id as Stock_ID, i.vendor_style_number
as StyleNum," & _
"c.name as Name, l.qty as Qty, i.description as 'Desc'," &
_
"i.cost_per_price_unit as Cost, i.price_per_unit as
Retail," & _
"i.price_2 as Wholesale FROM inventory i INNER JOIN
inventory_location l " & _
"ON i.ideaxid = l.inventory INNER JOIN contacts c ON
i.vendor = c.ideaxid " & _
"WHERE c.name = 'MC2'"
.LockType = adLockOptimistic
.CursorType = adOpenForwardOnly
.CursorLocation = adUseClient
.Open

End With

Set Me.Recordset = rs
Set rs = Nothing
Set cn = Nothing

End Sub


Private Sub Form_Unload(Cancel As Integer)
Dim cn As ADODB.Connection
Set cn = Me.Recordset.ActiveConnection
cn.Close
Set cn = Nothing
End Sub


"Thomas Bartkus" <thomasbartkus@comcast.net> wrote in message
news:ErSdnbcPfPoHSTTfRVn-hQ@telcove.net...[color=blue]
> "DJJ" <gemdjj@writme.com> wrote in message
> news:6SJpe.8703$r71.8132@newssvr31.news.prodigy.co m...[color=green]
> > The table is small. The database is on a Windows 2K Server.
> >
> > I tried an ADO recordset to populate a single text box on a form as per[/color]
> your[color=green]
> > suggestion. It seems to run faster but the text box shows no data[/color]
> (#Name?)[color=green]
> >
> > The SQL query was tested on using MySQL query browser. The DSN[/color][/color]
connection[color=blue][color=green]
> > tests ok. Here's the code I used:[/color]
> <snip>
>
> Okay. I am going to put my own minimalist code here that I just tested on[/color]
a[color=blue]
> small table. Part of the problem is that I can't see into the ODBC
> configuration [MSDASQL] you set up. The following code addresses the[/color]
MySQL[color=blue]
> driver directly so we can see up front all the configuration settings. My
> guess is that you have a few bad settings in tje [MSDASQL] definition.
>
> I tested this in a 4500 record table. It only takes a *tiny* fraction of[/color]
a[color=blue]
> second to retrieve, loop through, and close the recordset. By far the
> biggest overhead is the xfer of data over the network and the loop. The
> speed of that, of course, depends on your workstation. The point is - the
> MySQL/ODBC driver is *not* a speed bottleneck!
>
> I used ADO 2.8 for this test.
> FWIW - DAO is substantially faster than ADO!
> I won't get into pass through queries here - but that would speed things[/color]
up[color=blue]
> too!
>
> Try this on your database and see that you can read the data !
> And if it's slow - then I have nothing left to say :-)
> Thomas Bartkus
>
> ===========================================
> Public Sub AdoTest()
> Dim Conn As New ADODB.Connection
> Dim rsInventory As New ADODB.Recordset
> Dim sql As String, strCon As String
> Dim cnt As Long
>
> strCon = "DRIVER=MySQL ODBC 3.51 Driver;" & _
> "SERVER=Your Servers IP;" & _
> "DATABASE=Your Databases name;" & _
> "UID=Your Id;" & _
> "PWD=Your Password" & _
> "OPTION=18435;" ' <- BigInt->Int, Compressed Protocol,[/color]
Don't[color=blue]
> optimize col width,
> ' return matching rows
>
> Conn.Mode = adModeRead ' If you only want to read, then set it so!
> It's faster R/O
> Conn.Open strCon
>
> sql = "SELECT stock_id FROM inventory;"
>
> rsInventory.Source = sql
> rsInventory.ActiveConnection = Conn
>
> ' Fastest cursor - so long as we don't need to move back & forth
> (forward only!)
> rsInventory.CursorType = adOpenForwardOnly
> rsInventory.Open
>
> ' Prove that we got something.
> Do While (Not rsInventory.EOF)
> ' You should do something useful with the data here!
> ' I'll just count.
> cnt = cnt + 1
> rsInventory.MoveNext
> Loop
> MsgBox "We captured " & cnt & " Records", vbExclamation
>
> ' Clean it up, shut it down.
> rsInventory.Close
> Set rsInventory = Nothing
>
> Conn.Close
> Set Conn = Nothing
> End Sub
>
> ===========================================
>
>[/color]

re: MySQL ODBC 3.51 driver Very Slow!

On Fri, 10 Jun 2005 23:35:18 +0000, DJJ wrote:
[color=blue]
> I setup code similar to yours ...
> With cn
> .Provider = "MSDASQL.1"[/color]

Well, that's not too similar!
The configuration string is buried in [MSDASQL.1]. What the heck does it
look like?
[color=blue]
> that still takes a while to load ...[/color]

And what is your standard of comparison? I'm guessing you have been
working with Access using local data that resides inside an .mdb file.
And
Now you are using Access to manipulate data that resides on a separate
server. MySQL server/Access client/

The client/server setup will never give you the raw data xfer speed you
get with an Access file based setup. Sorry - just ain't gonna happen!
However, there are compensatory benefits and coping strategies.

Here are some coping strategies. You can.
1) Copy data from MySQL server to local tables for subsequent (faster)
processing.
2) Minimize the the data xfer overhead with PassThroughQueries.
This means that, instead of sucking 10,000 records down the pipes in order
to execute a query that will 100 records - You let the server query it's
own data which means only 100 records need to be xferred.
3) Use ADOs asynchronous queries to take advantage of the extra cpu on
the server.
4) Use DAO - it's much faster! Yes, I know MS deprecates this in it's
long, slow, march backwards but DAO 3.6 is mature, stable, and still
distributed with Windos XP (I think!).
[color=blue]
> it's much faster. Once the data is loaded in my form I can scroll 1300
> records very quickly, which is great. However, when I try to edit a
> record I get the message "This recordset is not updateable". Would you
> know how to get around that?[/color]

You don't directly set your connection type to R/W and are accepting the
default which may be read only.

Thomas Bartkus

Here is the entire code set I'm using:[color=blue]
>
> Private Sub Form_Open(Cancel As Integer)
> Dim cn As ADODB.Connection
> Dim rs As ADODB.Recordset
>
> Set cn = New ADODB.Connection
>
> With cn
> .Provider = "MSDASQL.1"
> .Properties("Persist Security Info").Value = "False"
> .Properties("Data Source").Value = "MyDatabase" .Open
> End With
>
> Set rs = New ADODB.Recordset
> With rs
> Set .ActiveConnection = cn
> .Source = "SELECT i.stock_id as Stock_ID,
> i.vendor_style_number
> as StyleNum," & _
> "c.name as Name, l.qty as Qty, i.description as
> 'Desc'," &
> _
> "i.cost_per_price_unit as Cost, i.price_per_unit as
> Retail," & _
> "i.price_2 as Wholesale FROM inventory i INNER JOIN
> inventory_location l " & _
> "ON i.ideaxid = l.inventory INNER JOIN contacts c ON
> i.vendor = c.ideaxid " & _
> "WHERE c.name = 'MC2'"
> .LockType = adLockOptimistic
> .CursorType = adOpenForwardOnly
> .CursorLocation = adUseClient
> .Open
>
> End With
>
> Set Me.Recordset = rs
> Set rs = Nothing
> Set cn = Nothing
>
> End Sub
>
>
> Private Sub Form_Unload(Cancel As Integer)
> Dim cn As ADODB.Connection
> Set cn = Me.Recordset.ActiveConnection cn.Close Set cn = Nothing
> End Sub
>
>
> "Thomas Bartkus" <thomasbartkus@comcast.net> wrote in message
> news:ErSdnbcPfPoHSTTfRVn-hQ@telcove.net...[color=green]
>> "DJJ" <gemdjj@writme.com> wrote in message
>> news:6SJpe.8703$r71.8132@newssvr31.news.prodigy.co m...[color=darkred]
>> > The table is small. The database is on a Windows 2K Server.
>> >
>> > I tried an ADO recordset to populate a single text box on a form as
>> > per[/color]
>> your[color=darkred]
>> > suggestion. It seems to run faster but the text box shows no data[/color]
>> (#Name?)[color=darkred]
>> >
>> > The SQL query was tested on using MySQL query browser. The DSN[/color][/color]
> connection[color=green][color=darkred]
>> > tests ok. Here's the code I used:[/color]
>> <snip>
>>
>> Okay. I am going to put my own minimalist code here that I just tested
>> on[/color]
> a[color=green]
>> small table. Part of the problem is that I can't see into the ODBC
>> configuration [MSDASQL] you set up. The following code addresses the[/color]
> MySQL[color=green]
>> driver directly so we can see up front all the configuration settings.
>> My guess is that you have a few bad settings in tje [MSDASQL]
>> definition.
>>
>> I tested this in a 4500 record table. It only takes a *tiny* fraction
>> of[/color]
> a[color=green]
>> second to retrieve, loop through, and close the recordset. By far the
>> biggest overhead is the xfer of data over the network and the loop. The
>> speed of that, of course, depends on your workstation. The point is -
>> the MySQL/ODBC driver is *not* a speed bottleneck!
>>
>> I used ADO 2.8 for this test.
>> FWIW - DAO is substantially faster than ADO! I won't get into pass
>> through queries here - but that would speed things[/color]
> up[color=green]
>> too!
>>
>> Try this on your database and see that you can read the data ! And if
>> it's slow - then I have nothing left to say :-) Thomas Bartkus
>>
>> =========================================== Public Sub AdoTest() Dim
>> Conn As New ADODB.Connection
>> Dim rsInventory As New ADODB.Recordset Dim sql As String, strCon As
>> String
>> Dim cnt As Long
>>
>> strCon = "DRIVER=MySQL ODBC 3.51 Driver;" & _
>> "SERVER=Your Servers IP;" & _
>> "DATABASE=Your Databases name;" & _
>> "UID=Your Id;" & _
>> "PWD=Your Password" & _
>> "OPTION=18435;" ' <- BigInt->Int, Compressed Protocol,[/color]
> Don't[color=green]
>> optimize col width,
>> ' return matching rows
>>
>> Conn.Mode = adModeRead ' If you only want to read, then set it
>> so!
>> It's faster R/O
>> Conn.Open strCon
>>
>> sql = "SELECT stock_id FROM inventory;"
>>
>> rsInventory.Source = sql
>> rsInventory.ActiveConnection = Conn
>>
>> ' Fastest cursor - so long as we don't need to move back & forth
>> (forward only!)
>> rsInventory.CursorType = adOpenForwardOnly rsInventory.Open
>>
>> ' Prove that we got something.
>> Do While (Not rsInventory.EOF)
>> ' You should do something useful with the data here! ' I'll
>> just count.
>> cnt = cnt + 1
>> rsInventory.MoveNext
>> Loop
>> MsgBox "We captured " & cnt & " Records", vbExclamation
>>
>> ' Clean it up, shut it down.
>> rsInventory.Close
>> Set rsInventory = Nothing
>>
>> Conn.Close
>> Set Conn = Nothing
>> End Sub
>>
>> ===========================================
>>
>>[/color][/color]

re: MySQL ODBC 3.51 driver Very Slow!

Tom,

You're right; I forgot to list those properties. The following are checked
off in the ODBC connector:

Don't optimize column width
Return matching rows
Allow big results
Use compressed protocol
Change BigInt columns to int

I've run the same exact code with a local copy of MySql with it's own DSN.
The speed doesn't significantly change whether the data is sitting locally
or out on a server.

A server based pass through query sounds like the best bet but I am not sure
how that's done. Is that like a stored procedure? Could you provide an
example of that?

I did specify read/write in the code initially but it made no difference.
Access still said the records were not updateable. I am begining to think
it is in fact the table design because I noticed 5 Text fields.

Many thanks...

DJJ


"tom" <thomasbartkus@comcast.net> wrote in message
news:pan.2005.06.11.17.43.17.852215@comcast.net...[color=blue]
> On Fri, 10 Jun 2005 23:35:18 +0000, DJJ wrote:
>[color=green]
> > I setup code similar to yours ...
> > With cn
> > .Provider = "MSDASQL.1"[/color]
>
> Well, that's not too similar!
> The configuration string is buried in [MSDASQL.1]. What the heck does it
> look like?
>[color=green]
> > that still takes a while to load ...[/color]
>
> And what is your standard of comparison? I'm guessing you have been
> working with Access using local data that resides inside an .mdb file.
> And
> Now you are using Access to manipulate data that resides on a separate
> server. MySQL server/Access client/
>
> The client/server setup will never give you the raw data xfer speed you
> get with an Access file based setup. Sorry - just ain't gonna happen!
> However, there are compensatory benefits and coping strategies.
>
> Here are some coping strategies. You can.
> 1) Copy data from MySQL server to local tables for subsequent (faster)
> processing.
> 2) Minimize the the data xfer overhead with PassThroughQueries.
> This means that, instead of sucking 10,000 records down the pipes in order
> to execute a query that will 100 records - You let the server query it's
> own data which means only 100 records need to be xferred.
> 3) Use ADOs asynchronous queries to take advantage of the extra cpu on
> the server.
> 4) Use DAO - it's much faster! Yes, I know MS deprecates this in it's
> long, slow, march backwards but DAO 3.6 is mature, stable, and still
> distributed with Windos XP (I think!).
>[color=green]
> > it's much faster. Once the data is loaded in my form I can scroll 1300
> > records very quickly, which is great. However, when I try to edit a
> > record I get the message "This recordset is not updateable". Would you
> > know how to get around that?[/color]
>
> You don't directly set your connection type to R/W and are accepting the
> default which may be read only.
>
> Thomas Bartkus
>
> Here is the entire code set I'm using:[color=green]
> >
> > Private Sub Form_Open(Cancel As Integer)
> > Dim cn As ADODB.Connection
> > Dim rs As ADODB.Recordset
> >
> > Set cn = New ADODB.Connection
> >
> > With cn
> > .Provider = "MSDASQL.1"
> > .Properties("Persist Security Info").Value = "False"
> > .Properties("Data Source").Value = "MyDatabase" .Open
> > End With
> >
> > Set rs = New ADODB.Recordset
> > With rs
> > Set .ActiveConnection = cn
> > .Source = "SELECT i.stock_id as Stock_ID,
> > i.vendor_style_number
> > as StyleNum," & _
> > "c.name as Name, l.qty as Qty, i.description as
> > 'Desc'," &
> > _
> > "i.cost_per_price_unit as Cost, i.price_per_unit as
> > Retail," & _
> > "i.price_2 as Wholesale FROM inventory i INNER JOIN
> > inventory_location l " & _
> > "ON i.ideaxid = l.inventory INNER JOIN contacts c ON
> > i.vendor = c.ideaxid " & _
> > "WHERE c.name = 'MC2'"
> > .LockType = adLockOptimistic
> > .CursorType = adOpenForwardOnly
> > .CursorLocation = adUseClient
> > .Open
> >
> > End With
> >
> > Set Me.Recordset = rs
> > Set rs = Nothing
> > Set cn = Nothing
> >
> > End Sub
> >
> >
> > Private Sub Form_Unload(Cancel As Integer)
> > Dim cn As ADODB.Connection
> > Set cn = Me.Recordset.ActiveConnection cn.Close Set cn = Nothing
> > End Sub
> >
> >
> > "Thomas Bartkus" <thomasbartkus@comcast.net> wrote in message
> > news:ErSdnbcPfPoHSTTfRVn-hQ@telcove.net...[color=darkred]
> >> "DJJ" <gemdjj@writme.com> wrote in message
> >> news:6SJpe.8703$r71.8132@newssvr31.news.prodigy.co m...
> >> > The table is small. The database is on a Windows 2K Server.
> >> >
> >> > I tried an ADO recordset to populate a single text box on a form as
> >> > per
> >> your
> >> > suggestion. It seems to run faster but the text box shows no data
> >> (#Name?)
> >> >
> >> > The SQL query was tested on using MySQL query browser. The DSN[/color]
> > connection[color=darkred]
> >> > tests ok. Here's the code I used:
> >> <snip>
> >>
> >> Okay. I am going to put my own minimalist code here that I just tested
> >> on[/color]
> > a[color=darkred]
> >> small table. Part of the problem is that I can't see into the ODBC
> >> configuration [MSDASQL] you set up. The following code addresses the[/color]
> > MySQL[color=darkred]
> >> driver directly so we can see up front all the configuration settings.
> >> My guess is that you have a few bad settings in tje [MSDASQL]
> >> definition.
> >>
> >> I tested this in a 4500 record table. It only takes a *tiny* fraction
> >> of[/color]
> > a[color=darkred]
> >> second to retrieve, loop through, and close the recordset. By far the
> >> biggest overhead is the xfer of data over the network and the loop. The
> >> speed of that, of course, depends on your workstation. The point is -
> >> the MySQL/ODBC driver is *not* a speed bottleneck!
> >>
> >> I used ADO 2.8 for this test.
> >> FWIW - DAO is substantially faster than ADO! I won't get into pass
> >> through queries here - but that would speed things[/color]
> > up[color=darkred]
> >> too!
> >>
> >> Try this on your database and see that you can read the data ! And if
> >> it's slow - then I have nothing left to say :-) Thomas Bartkus
> >>
> >> =========================================== Public Sub AdoTest() Dim
> >> Conn As New ADODB.Connection
> >> Dim rsInventory As New ADODB.Recordset Dim sql As String, strCon As
> >> String
> >> Dim cnt As Long
> >>
> >> strCon = "DRIVER=MySQL ODBC 3.51 Driver;" & _
> >> "SERVER=Your Servers IP;" & _
> >> "DATABASE=Your Databases name;" & _
> >> "UID=Your Id;" & _
> >> "PWD=Your Password" & _
> >> "OPTION=18435;" ' <- BigInt->Int, Compressed Protocol,[/color]
> > Don't[color=darkred]
> >> optimize col width,
> >> ' return matching rows
> >>
> >> Conn.Mode = adModeRead ' If you only want to read, then set it
> >> so!
> >> It's faster R/O
> >> Conn.Open strCon
> >>
> >> sql = "SELECT stock_id FROM inventory;"
> >>
> >> rsInventory.Source = sql
> >> rsInventory.ActiveConnection = Conn
> >>
> >> ' Fastest cursor - so long as we don't need to move back & forth
> >> (forward only!)
> >> rsInventory.CursorType = adOpenForwardOnly rsInventory.Open
> >>
> >> ' Prove that we got something.
> >> Do While (Not rsInventory.EOF)
> >> ' You should do something useful with the data here! ' I'll
> >> just count.
> >> cnt = cnt + 1
> >> rsInventory.MoveNext
> >> Loop
> >> MsgBox "We captured " & cnt & " Records", vbExclamation
> >>
> >> ' Clean it up, shut it down.
> >> rsInventory.Close
> >> Set rsInventory = Nothing
> >>
> >> Conn.Close
> >> Set Conn = Nothing
> >> End Sub
> >>
> >> ===========================================
> >>
> >>[/color][/color][/color]