Re: Export fast, my code slow =[
- From: "Neil" <nospam@xxxxxxxxxx>
- Date: Fri, 29 Jun 2007 18:36:47 GMT
Yes, that is correct: your code is taking much longer because it's appending
the records one at a time, instead of in batch. Use Insert Into to insert
your tables in batch into the target table. In its simplest incarnation, it
would be:
INSERT INTO Table2 ( FieldSomething )
SELECT Table1.FieldWhatever
FROM Table1
You can also add a Where statement, or append multiple fields, or whatever
you need.
Neil
<eselk@xxxxxxxxxxxx> wrote in message
news:1183141511.114836.99010@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
I'm doing some speed tests. I created a brand-new table, with just
one "Long Integer" field. I'm testing adding 1000 records. If I use
the "Export" feature in Access, it takes only a few seconds to export
the 1000 records to a new table on the server. If I use my code
below, it takes 100 secods (10 records per second). Can someone help
me speed up my code?
--- code ---
Dim con As New ADODB.Connection
con.Open "Provider=MSDASQL.1;DRIVER={MySQL ODBC 3.51
Driver};SERVER=www.mydomain.com;PORT=3306;OPTION=3;DATABASE=mydbname;UID=myuid;PWD=mypwd;"
'con.BeginTrans
Dim rs As New ADODB.Recordset
rs.Open "Table1", con, adOpenKeyset, adLockOptimistic
' This loop takes ~100 seconds
For x = 1 To 1000
rs.AddNew
rs.Fields("ID").Value = x
rs.Update
Next x
rs.Close
'con.CommitTrans
con.Close
---- end code ----
Please note I had to comment out the BeginTrans and CommitTrans
because I was getting an error at CommitTrans saying that "No
Transaction has been started". I think this is because either the
ODBC driver (MyODBC) or the server (MySQL, with MyISAM) doesn't
support transactions (probably because I'm using MyISAM data format,
with a 3.x version of MySQL server).
At any rate, export is fast, so there must be a way to speed up adding
records. I can't just use export because I actually need more control
than just dumping records over, but for now I'm just trying to find
the fastest method for adding several records.
I think the above code is sending an "INSERT INTO" SQL statement to
the server for each record I add. Is there another method I can use,
without hand-coding the SQL myself, that would send all 1000 adds as a
single INSERT INTO statement? MySQL does support multiple rows in the
VALUES() part of the INSERT INTO statement. If possible I'd like to
stick with code that also works with MSSQL, or would work with only
minor changes... which is one reason I haven't tried any native MySQL
APIs.
The reason I posted this to the MSAccess group instead of MySQL/MyODBC
is to see if anyone knows what method MSAccess uses for Export, why is
it so much faster? And also because my code is in VBA. I'll probably
setup netmon.. maybe I can see the actual SQL statements being sent by
each method.
.
- Follow-Ups:
- Re: Export fast, my code slow =[
- From: eselk
- Re: Export fast, my code slow =[
- References:
- Export fast, my code slow =[
- From: eselk
- Export fast, my code slow =[
- Prev by Date: Export fast, my code slow =[
- Next by Date: Re: Export fast, my code slow =[
- Previous by thread: Export fast, my code slow =[
- Next by thread: Re: Export fast, my code slow =[
- Index(es):
Relevant Pages
|
