joelhoffman
asked on
Append to Oracle too slow
I am using an Access database front end to an Oracle back end. I create a temporary table for data within MS Access and then want to append that data to the Oracle table. The code I am currently using works, but take 6 minutes to append only 800 records! If anyone has any ideas on speeding this up, I'd sure be interested. I'm also analyzing and computing statistics on the Oracle side just about every time I do something just to try and help the speed.
I have set up two test tables to get this functioning before I go with live data. The table in Oracle is called JSHL_WO_LD_TEST. The table in Access is called jsht_LONG_DESCRIPTION. I'm not a programmer so please forgive my naming conventions. I'm using the following code to append from Access to Oracle:
===================
Sub append_ld_to_oracle()
DoCmd.SetWarnings False
Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("j sht_LONG_D ESCRIPTION ")
rs.MoveFirst
While Not rs.EOF
CurrentDb.Execute "INSERT INTO JSHL_WO_LD_TEST _
(WONUM, LDKEY, LDTEXT, REPORTDATE) VALUES _
('" & Replace(rs(0), "'", "''") & "', '" & Replace(rs(1), "'", "''") _
& "', '" & Replace(rs(2), "'", "''") & "', '" & Replace(rs(3), "'", "''") & "');"
rs.MoveNext
Wend
DoCmd.SetWarnings True
End Sub
===================
I have set up two test tables to get this functioning before I go with live data. The table in Oracle is called JSHL_WO_LD_TEST. The table in Access is called jsht_LONG_DESCRIPTION. I'm not a programmer so please forgive my naming conventions. I'm using the following code to append from Access to Oracle:
===================
Sub append_ld_to_oracle()
DoCmd.SetWarnings False
Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("j
rs.MoveFirst
While Not rs.EOF
CurrentDb.Execute "INSERT INTO JSHL_WO_LD_TEST _
(WONUM, LDKEY, LDTEXT, REPORTDATE) VALUES _
('" & Replace(rs(0), "'", "''") & "', '" & Replace(rs(1), "'", "''") _
& "', '" & Replace(rs(2), "'", "''") & "', '" & Replace(rs(3), "'", "''") & "');"
rs.MoveNext
Wend
DoCmd.SetWarnings True
End Sub
===================
2 records per second using discreet INSERT statements is not unreasonable for ACCESS -> ORACLE, even for simple structures.
On the ORACLE side of things you can often find that triggers will slow down inserts, and can be disabled for the duration of a batch insert if you are CERTAIN that you can accomplish what they are meant to do in data (eg. get the next sequence number for the primary key, adjust data in another table, etc. Make sure you update any sequences if you bypass them, and turn triggers back on!
On the Access side of things you might be able to increase speed by using Docmd.Transferdatabase instead of looping through a recordset. Obviously you would have to perform all of the data conversions (ie. replace single quote with 2 x single quote) first, or in ORACLE after completion
On the ORACLE side of things you can often find that triggers will slow down inserts, and can be disabled for the duration of a batch insert if you are CERTAIN that you can accomplish what they are meant to do in data (eg. get the next sequence number for the primary key, adjust data in another table, etc. Make sure you update any sequences if you bypass them, and turn triggers back on!
On the Access side of things you might be able to increase speed by using Docmd.Transferdatabase instead of looping through a recordset. Obviously you would have to perform all of the data conversions (ie. replace single quote with 2 x single quote) first, or in ORACLE after completion
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Again, you would have to perform all of the data conversions (ie. replace single quote with 2 x single quote) first, or in ORACLE after completion, or you could adjust the query adding the replace statements back in.
ASKER
rockmuncher:
I initially started with the Access Append command but it was horribly slow. So I came in here and saw where someone suggested doing it the "recordset" way and so I tried that. I'm in training this week so will try your suggestions early next week. Thanks for the help!
I initially started with the Access Append command but it was horribly slow. So I came in here and saw where someone suggested doing it the "recordset" way and so I tried that. I'm in training this week so will try your suggestions early next week. Thanks for the help!
ASKER
ok, here's what I just tried and it took 5 minutes to load only 831 records with only 4 fields:
==================
Sub append_ld_to_oracle()
CurrentDb.Execute "INSERT INTO JSHL_WO_LD_TEST (WONUM, LDKEY,LDTEXT, REPORTDATE) SELECT * FROM jsht_LONG_DESCRIPTION;"
End Sub
==================
I don't know how to do what has been suggested about "triggers". Maybe someone could explain that? Or maybe I should try the transferdatabase command mentioned above?
Thanks,
Joel
==================
Sub append_ld_to_oracle()
CurrentDb.Execute "INSERT INTO JSHL_WO_LD_TEST (WONUM, LDKEY,LDTEXT, REPORTDATE) SELECT * FROM jsht_LONG_DESCRIPTION;"
End Sub
==================
I don't know how to do what has been suggested about "triggers". Maybe someone could explain that? Or maybe I should try the transferdatabase command mentioned above?
Thanks,
Joel
ASKER
Is there a way I could import a text file on my PC directly into my Oracle tables using SQL Plus? Maybe that would be faster rather than using MS Access?
ASKER
I'm awarding points on another thread and moving that thread's conversation here since it has turned into one more relative to my question here. The other thread's location:
https://www.experts-exchange.com/questions/21104209/currentdb-execute-returns-error-cannot-execute-a-select-query.html
https://www.experts-exchange.com/questions/21104209/currentdb-execute-returns-error-cannot-execute-a-select-query.html
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
routinet:
Nope, I didn't solve the ADO question. I'm reposting it below. Thanks for your diligence in following me over to this thread. I'll post my text import question over in the Oracle area. Since our DBA only set this test up for me to work with I don't think there would be any triggers on the Oracle side. I'm the only person fooling with the database at the moment.
Here's my current code attempting to use ADO (error below):
======================
Dim Cnxn As ADODB.Connection
Dim cmdChange As ADODB.Command
Dim strSQL As String
' Define SQL statement to execute as command text
strSQL = "INSERT INTO rrcfat.WO_LD_TEST_TBL (WONUM, LDKEY, REPORTDATE, LDTEXT) SELECT * from jsh_long_desc;"
' Open a connection using a DSN and individual
' arguments instead of a connection string.
Set Cnxn = New ADODB.Connection
Cnxn.ConnectionTimeout = 30
Cnxn.Open "oxyt", "rrcfat", "rrcfat"
' See if I opened the connection
MsgBox "cnxn state: " & Cnxn.State
' Create and execute command object
Set cmdChange = New ADODB.Command
Set cmdChange.ActiveConnection = Cnxn
cmdChange.CommandText = strSQL
cmdChange.Execute
' Close the connection
Cnxn.Close
' See if I closed the connection
MsgBox "cnxn state: " & Cnxn.State
======================
When I run the above, I get an error:
Run-time error '-2147217865 (80040e37)':
[Oracle][ODBC][Ora]ORA-009 42: table or view does not exist
Remember: the table "jsh_long_desc" is a local MS Access table. Will the SQL going through ADO be able to correctly reference my local Access table to insert from?
Nope, I didn't solve the ADO question. I'm reposting it below. Thanks for your diligence in following me over to this thread. I'll post my text import question over in the Oracle area. Since our DBA only set this test up for me to work with I don't think there would be any triggers on the Oracle side. I'm the only person fooling with the database at the moment.
Here's my current code attempting to use ADO (error below):
======================
Dim Cnxn As ADODB.Connection
Dim cmdChange As ADODB.Command
Dim strSQL As String
' Define SQL statement to execute as command text
strSQL = "INSERT INTO rrcfat.WO_LD_TEST_TBL (WONUM, LDKEY, REPORTDATE, LDTEXT) SELECT * from jsh_long_desc;"
' Open a connection using a DSN and individual
' arguments instead of a connection string.
Set Cnxn = New ADODB.Connection
Cnxn.ConnectionTimeout = 30
Cnxn.Open "oxyt", "rrcfat", "rrcfat"
' See if I opened the connection
MsgBox "cnxn state: " & Cnxn.State
' Create and execute command object
Set cmdChange = New ADODB.Command
Set cmdChange.ActiveConnection
cmdChange.CommandText = strSQL
cmdChange.Execute
' Close the connection
Cnxn.Close
' See if I closed the connection
MsgBox "cnxn state: " & Cnxn.State
======================
When I run the above, I get an error:
Run-time error '-2147217865 (80040e37)':
[Oracle][ODBC][Ora]ORA-009
Remember: the table "jsh_long_desc" is a local MS Access table. Will the SQL going through ADO be able to correctly reference my local Access table to insert from?
No, you would have to set up a linked table on one side or the other, and use that side to run the INSERT. For example, if you create the link in Access, it would then consider table [rrcfat] as a local Access table for the purpose of your query. You could then remove the ADO connection from the sub and use DAO. Access would handle the connection to Oracle from there, though most likely it would ODBC (speed issue again..). You could also try it the other way (creating a link table in Oracle) and use the existing code you have now. In that scenario, you would be using ADO to connect to Oracle, which would probably use ODBC to implement the link back to Access.
You also have the use if the IN clause for SQL, but Microsoft recommends using a linked table instead due to performance issues.
You also have the use if the IN clause for SQL, but Microsoft recommends using a linked table instead due to performance issues.
ASKER
Just so anyone reading this thread knows, my DBA had these tables set up on what he called a "test instance". He moved my tables over to the "Production" instance and thought I would see a speed gain by doing so. Unfortunately, it still took 5 minutes to load only 800 records. I'm going to close this thread and award the points then move the discussion over to the Oracle area. Thanks for the comments!
ASKER
--------------------------
WONUM CHAR(12)
LDKEY CHAR(40)
REPORTDATE DATE
LDTEXT LONG