ODBC mysql-access slow

[Javcal]

I have a data base Access that links to another mysql by
mysql-connector-odbc-3.51.12. If I execute a query by odbc to a table with
1000 records this query takes between 5 and 6 minutes, whereas if I directly
execute it in server it takes less of a second. what I can do so that it is
executed but fast?

[Stefan Hoffmann]

hi,

Post by Javcal
I have a data base Access that links to another mysql by
mysql-connector-odbc-3.51.12. If I execute a query by odbc to a table with
1000 records this query takes between 5 and 6 minutes, whereas if I directly
execute it in server it takes less of a second. what I can do so that it is
executed but fast?

Have you checked your network connection? What throughput do you have,
what kind of connection is it LAN, WLAN or broadband?

I don't think that this is an Access issue. I have used this drivers
succesfully without having issues like yours.


mfG
--> stefan <--

[Javcal]

Post by Stefan Hoffmann
hi,

Post by Javcal
I have a data base Access that links to another mysql by
mysql-connector-odbc-3.51.12. If I execute a query by odbc to a table
with 1000 records this query takes between 5 and 6 minutes, whereas if I
directly execute it in server it takes less of a second. what I can do so
that it is executed but fast?

Have you checked your network connection? What throughput do you have,
what kind of connection is it LAN, WLAN or broadband?
I don't think that this is an Access issue. I have used this drivers
succesfully without having issues like yours.
mfG
--> stefan <--

Hi Stefan.

I tested my connection and I think it can be the problem, because if I
exceute the query in a lan this takes about 10 secs, but the problem becomes
in the localitation of database. I think that access dont work fast with
databases out of lan. how do you make to work with access and to comunicate
with other databases out of lan?

[Stefan Hoffmann]

hi,

Post by Javcal
I tested my connection and I think it can be the problem, because if I
exceute the query in a lan this takes about 10 secs, but the problem becomes
in the localitation of database. I think that access dont work fast with
databases out of lan. how do you make to work with access and to comunicate
with other databases out of lan?

So you are using a broadband connection and your MySQL server is located
at some (web-)server?

Can you outline your working scenario a little bit?


mfG
--> stefan <--

[Javcal]

like you suposed, my mysql database is located at a webserver, there is a
table with 1000 products, and I would like update prices and stoks from my
access 2003 database.

Post by Stefan Hoffmann
hi,

Post by Javcal
I tested my connection and I think it can be the problem, because if I
exceute the query in a lan this takes about 10 secs, but the problem
becomes in the localitation of database. I think that access dont work
fast with databases out of lan. how do you make to work with access and
to comunicate with other databases out of lan?

So you are using a broadband connection and your MySQL server is located
at some (web-)server?
Can you outline your working scenario a little bit?
mfG
--> stefan <--

[Stefan Hoffmann]

hi,

Post by Javcal
like you suposed, my mysql database is located at a webserver, there is a
table with 1000 products, and I would like update prices and stoks from my
access 2003 database.

Then you cannot do anything to improve the speed whilst using the MySQL
ODBC driver beside getting a faster connection.

You may consider this:

When starting your application, copy the data from your MySQL server to
local tables.
Make copies of these tables.

Change your data locally.

Compare the changed tables with the copies of tables.
Create SQL scripts for DELETE, INSERT and UPDATE.

Zip these scripts, FTP them to the server, unpack them, and execute them
on the server.


mfG
--> stefan <--

[Javcal]

Ok, this is an easy solution, but this solution implices to run a process at
server and I dont know how do this, for example: I export tables to a txt
file, after that I zip this file and send it to server, but in server...
what I have to do to unzip and insert this zip on database?, and if I want
to do it from server to my local PC?

Post by Stefan Hoffmann
hi,

Post by Javcal
like you suposed, my mysql database is located at a webserver, there is a
table with 1000 products, and I would like update prices and stoks from
my access 2003 database.

Then you cannot do anything to improve the speed whilst using the MySQL
ODBC driver beside getting a faster connection.
When starting your application, copy the data from your MySQL server to
local tables.
Make copies of these tables.
Change your data locally.
Compare the changed tables with the copies of tables.
Create SQL scripts for DELETE, INSERT and UPDATE.
Zip these scripts, FTP them to the server, unpack them, and execute them
on the server.
mfG
--> stefan <--

[Alex Dybenko]

Hi,
try to use pass-through query, if you don't need to update data
--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com

Post by Javcal
I have a data base Access that links to another mysql by
mysql-connector-odbc-3.51.12. If I execute a query by odbc to a table with
1000 records this query takes between 5 and 6 minutes, whereas if I
directly execute it in server it takes less of a second. what I can do so
that it is executed but fast?

[Javcal]

thank you very much. this has been a great discovery for my.

I did a pass-through query and it made the query in 1 sec, but if I want
insert or update from my local table it fails, what do I have to do to up my
tables to server as fast as a pass-through query?

Post by Alex Dybenko
Hi,
try to use pass-through query, if you don't need to update data
--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com

Post by Javcal
I have a data base Access that links to another mysql by
mysql-connector-odbc-3.51.12. If I execute a query by odbc to a table with
1000 records this query takes between 5 and 6 minutes, whereas if I
directly execute it in server it takes less of a second. what I can do so
that it is executed but fast?

[Stefan Hoffmann]

hi,

Post by Javcal
I did a pass-through query and it made the query in 1 sec, but if I want
insert or update from my local table it fails, what do I have to do to up my
tables to server as fast as a pass-through query?

Passthrough queries are read only.

You may create them on the fly with VBA then you can use it to execute a
INSERT or UPDATE statement.

E.g. You have a passthrough query name "PT"

With CurrentDb
.QueryDefs.Item("PT").SQL = "UPDATE tableOnServer " & _
"SET field = value ..."
End With


mfG
--> stefan <--

[Javcal]

OK, but if I want insert 1000 records from a local table to web table, I'm
sure that it will be bery slow because I'll have to build 1000 times the sql
of query an execute 1000 times this query...

Post by Stefan Hoffmann
hi,

Post by Javcal
I did a pass-through query and it made the query in 1 sec, but if I want
insert or update from my local table it fails, what do I have to do to up
my tables to server as fast as a pass-through query?

Passthrough queries are read only.
You may create them on the fly with VBA then you can use it to execute a
INSERT or UPDATE statement.
E.g. You have a passthrough query name "PT"
With CurrentDb
.QueryDefs.Item("PT").SQL = "UPDATE tableOnServer " & _
"SET field = value ..."
End With
mfG
--> stefan <--

[Stefan Hoffmann]

hi,

Post by Javcal
OK, but if I want insert 1000 records from a local table to web table, I'm
sure that it will be bery slow because I'll have to build 1000 times the sql
of query an execute 1000 times this query...

This correct. But you can use a MySQL feature here. You can place
multiple INSERT statements in your passthrough query and execute it once
in a batch. E.g.

.QueryDefs.Item("PT").SQL = insertSQL1 & vbCrLf & _
.. & _
insertSQL1000



mfG
--> stefan <--

[Javcal]

very good idea, but as always I look for problems to it. I suppose that the
string will have a shortest limit than the volume of data that I want to
insert, so it will continue been slow.

hi,

Post by Javcal
OK, but if I want insert 1000 records from a local table to web table,
I'm sure that it will be bery slow because I'll have to build 1000 times
the sql of query an execute 1000 times this query...

This correct. But you can use a MySQL feature here. You can place multiple
INSERT statements in your passthrough query and execute it once in a
batch. E.g.
.QueryDefs.Item("PT").SQL = insertSQL1 & vbCrLf & _
.. & _
insertSQL1000
mfG
--> stefan <--

[Javcal]

There is no problem with the long of the string (63000), but I try to insert
100 records and it late 1 minute, so it isn't fast. Do you have other idea?

thank you

Post by Javcal
very good idea, but as always I look for problems to it. I suppose that
the string will have a shortest limit than the volume of data that I want
to insert, so it will continue been slow.

Post by Stefan Hoffmann
hi,

Post by Javcal
OK, but if I want insert 1000 records from a local table to web table,
I'm sure that it will be bery slow because I'll have to build 1000 times
the sql of query an execute 1000 times this query...

This correct. But you can use a MySQL feature here. You can place
multiple INSERT statements in your passthrough query and execute it once
in a batch. E.g.
.QueryDefs.Item("PT").SQL = insertSQL1 & vbCrLf & _
.. & _
insertSQL1000
mfG
--> stefan <--

[Stefan Hoffmann]

hi,

Post by Javcal
There is no problem with the long of the string (63000), but I try to insert
100 records and it late 1 minute, so it isn't fast. Do you have other idea?

No, because the problem is the speed of your broadband line. As long as
you can't get any faster line, there is not really anything you can do.

If your MySQL runs on a Windows machine with installed Terminal Services
you can run your application local to your MySQL server, but I assume
that is not an option.


mfG
--> stefan <--

[Javcal]

I think that I have not explained well (my English is very bad). I have
copied the string of 100 insertions in the window of mysql and has taken 1
minute in inserting the 100 registries, reason why one is not affected by my
speed of connection.


Private Sub Subir_Servidor()
Dim Rec As DAO.Recordset
Dim MyDb As Database
Dim MyQ As QueryDef
Dim Cont As Long

Cont = 0
Set MyDb = CurrentDb()
Set MyQ = MyDb.CreateQueryDef("")

MyQ.Connect =
"ODBC;DATABASE=xxx;DESCRIPTION=xxx;DSN=xxx;OPTION=18699;PWD=xxx;PORT=xxx;SERVER=xxx;UID=calamobel"
MyQ.ReturnsRecords = False
MyQ.sql = " "
Set Rec = CurrentDb.OpenRecordset("Albaranes_lineas_pruebas")
Do While Not Rec.EOF Or Cont < 100 ' limito los registros a subir
If Len(MyQ.sql) > 50000 Then
MyQ.Execute
Cont = 0
Else
MyQ.sql = "INSERT INTO Albaranes " & _
"( IdAlbaran, Tienda, Linea, Referencia, Descripcion, Precio,
Descuento, Cant_Mandada, Cant_Recepcionada, IdColor, IdPedido, Subido,
Bajado, Facturado )" & _
" values ( '" & Rec(0) & "' ,'" & Rec(1) & "' ,'" & Rec(2) & "'
,'" & Rec(3) & "' ,'" & Rec(4) & "' ,'" & Rec(5) & "' ,'" & Rec(6) & "' ,'"
& Rec(7) & "' ,'" & Rec(8) & "' , '" & _
Rec(9) & "' ,'" & Nz(Rec(10), 0) & "' ,'" & CInt(Rec(11)) & "'
,'" & CInt(Rec(12)) & "' ,'" & CInt(Rec(13)) & "' );" & vbCrLf & MyQ.sql
Cont = Cont + 1
End If
Rec.MoveNext
Loop
If Cont > 0 Then
MyQ.Execute
End If
Rec.Close
MyQ.Close
MyDb.Close
End Sub

The resulted string is like this:
INSERT INTO Albaranes ( IdAlbaran, .....) values ( '436' ,.....);
INSERT INTO Albaranes ( IdAlbaran, .....) values ( '436' ,.....);
.......

when sub arrives to MsQ.Execute send an error, but if I copy the string and
after that I paste it to MySql query window, it runs perfectly, but slow

Post by Stefan Hoffmann
hi,

Post by Javcal
There is no problem with the long of the string (63000), but I try to
insert 100 records and it late 1 minute, so it isn't fast. Do you have
other idea?

No, because the problem is the speed of your broadband line. As long as
you can't get any faster line, there is not really anything you can do.
If your MySQL runs on a Windows machine with installed Terminal Services
you can run your application local to your MySQL server, but I assume that
is not an option.
mfG
--> stefan <--

[Stefan Hoffmann]

hi,

Post by Javcal
I think that I have not explained well (my English is very bad). I have
copied the string of 100 insertions in the window of mysql and has taken 1
minute in inserting the 100 registries, reason why one is not affected by my
speed of connection.

Was this MySQL window local to your machine or was it a web interface
like MyPhpAdmin?

One minute for 100 inserts in not really fast.

Post by Javcal
when sub arrives to MsQ.Execute send an error, but if I copy the string and
after that I paste it to MySql query window, it runs perfectly, but slow

What error message and code do you get exactly?


mfG
--> stefan <--

[Javcal]

it's true, it's send from local machine, when I run from server directly it
goes faster.
I think that I'm going to build a function who ups a txt file to server with
the query results and after that I'm going to execute a store procedure to
insert data. I upped one file with 8500 records and it lates 30 secs in up
an 1 sec in insert... I think is the best option

hi,

Post by Javcal
I think that I have not explained well (my English is very bad). I have
copied the string of 100 insertions in the window of mysql and has taken
1 minute in inserting the 100 registries, reason why one is not affected
by my speed of connection.

Was this MySQL window local to your machine or was it a web interface like
MyPhpAdmin?
One minute for 100 inserts in not really fast.

Post by Javcal
when sub arrives to MsQ.Execute send an error, but if I copy the string
and after that I paste it to MySql query window, it runs perfectly, but
slow

What error message and code do you get exactly?
mfG
--> stefan <--

[Gary Walter]

Hi Javcal,

First...I am no expert but I thought passthru's could
only have one SQL stmt (I could be wrong).

Second...our company interacts with several websites
where we just upload tab-delimited files to update what
we sell on their sites. Is it possible that might be a strategy
you could implement?

I *know nothing about MySQL* but in SQL Server I could
imagine a stored procedure that accepts a string parameter
that could be tab-delimited (or xml) string {or filepath on server
where you just uploaded it}, then updates db.

one possible example:

your code below would collect values in string var (say "strPar"),
then when done feeds it to stored proc (say "xsp_upload")

MyQ.sql = "EXECUTE xsp_upload " & strPar
MyQ.Execute

Sorry...just what I thought about when I read your posts...

gary

Post by Javcal
I think that I have not explained well (my English is very bad). I have
copied the string of 100 insertions in the window of mysql and has taken 1
minute in inserting the 100 registries, reason why one is not affected by
my speed of connection.
Private Sub Subir_Servidor()
Dim Rec As DAO.Recordset
Dim MyDb As Database
Dim MyQ As QueryDef
Dim Cont As Long
Cont = 0
Set MyDb = CurrentDb()
Set MyQ = MyDb.CreateQueryDef("")
MyQ.Connect =
"ODBC;DATABASE=xxx;DESCRIPTION=xxx;DSN=xxx;OPTION=18699;PWD=xxx;PORT=xxx;SERVER=xxx;UID=calamobel"
MyQ.ReturnsRecords = False
MyQ.sql = " "
Set Rec = CurrentDb.OpenRecordset("Albaranes_lineas_pruebas")
Do While Not Rec.EOF Or Cont < 100 ' limito los registros a subir
If Len(MyQ.sql) > 50000 Then
MyQ.Execute
Cont = 0
Else
MyQ.sql = "INSERT INTO Albaranes " & _
"( IdAlbaran, Tienda, Linea, Referencia, Descripcion, Precio,
Descuento, Cant_Mandada, Cant_Recepcionada, IdColor, IdPedido, Subido,
Bajado, Facturado )" & _
" values ( '" & Rec(0) & "' ,'" & Rec(1) & "' ,'" & Rec(2) & "'
,'" & Rec(3) & "' ,'" & Rec(4) & "' ,'" & Rec(5) & "' ,'" & Rec(6) & "'
,'" & Rec(7) & "' ,'" & Rec(8) & "' , '" & _
Rec(9) & "' ,'" & Nz(Rec(10), 0) & "' ,'" & CInt(Rec(11)) & "'
,'" & CInt(Rec(12)) & "' ,'" & CInt(Rec(13)) & "' );" & vbCrLf & MyQ.sql
Cont = Cont + 1
End If
Rec.MoveNext
Loop
If Cont > 0 Then
MyQ.Execute
End If
Rec.Close
MyQ.Close
MyDb.Close
End Sub
INSERT INTO Albaranes ( IdAlbaran, .....) values ( '436' ,.....);
INSERT INTO Albaranes ( IdAlbaran, .....) values ( '436' ,.....);
.......
when sub arrives to MsQ.Execute send an error, but if I copy the string
and after that I paste it to MySql query window, it runs perfectly, but
slow

Post by Stefan Hoffmann
hi,

Post by Javcal
There is no problem with the long of the string (63000), but I try to
insert 100 records and it late 1 minute, so it isn't fast. Do you have
other idea?

No, because the problem is the speed of your broadband line. As long as
you can't get any faster line, there is not really anything you can do.
If your MySQL runs on a Windows machine with installed Terminal Services
you can run your application local to your MySQL server, but I assume
that is not an option.
mfG
--> stefan <--