|
|
|
date: Wed, 10 Oct 2007 09:54:52 +0200,
group: microsoft.public.access.odbcclientsvr
back
Re: ODBC mysql-access slow
hi,
Javcal wrote:
> 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 <--
date: Wed, 10 Oct 2007 11:37:28 +0200
author: Stefan Hoffmann
Re: ODBC mysql-access slow
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?
"Stefan Hoffmann" escribió en el mensaje
news:ukA9oEyCIHA.5208@TK2MSFTNGP04.phx.gbl...
> hi,
>
> Javcal wrote:
>> 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 <--
date: Wed, 10 Oct 2007 12:28:12 +0200
author: Javcal
Re: ODBC mysql-access slow
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
"Stefan Hoffmann" escribió en el mensaje
news:uDdxLp%23CIHA.5228@TK2MSFTNGP05.phx.gbl...
> hi,
>
> Javcal wrote:
>> 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 <--
date: Thu, 11 Oct 2007 12:14:05 +0200
author: Javcal
Re: ODBC mysql-access slow
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
"Javcal"wrote:
>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
>
>
>
> "Stefan Hoffmann" escribió en el mensaje
> news:uDdxLp%23CIHA.5228@TK2MSFTNGP05.phx.gbl...
>> hi,
>>
>> Javcal wrote:
>>> 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 <--
>
>
date: Thu, 11 Oct 2007 07:34:45 -0500
author: Gary Walter
|
|
