Solved

Append to Oracle too slow

Posted on 2004-08-23
12
1,096 Views
Last Modified: 2008-02-26
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("jsht_LONG_DESCRIPTION")

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
===================
0
Comment
Question by:joelhoffman
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 3
  • 2
12 Comments
 

Author Comment

by:joelhoffman
ID: 11876112
Oh, and here is the structure of these very simple tables:

----------------------------------------- -------- -----
WONUM                                          CHAR(12)
LDKEY                                             CHAR(40)
REPORTDATE                                  DATE
LDTEXT                                          LONG
0
 
LVL 7

Expert Comment

by:rockmuncher
ID: 11877287
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

0
 
LVL 7

Assisted Solution

by:rockmuncher
rockmuncher earned 200 total points
ID: 11877336
Alternatively you could just run an append query that does the lot in one go (eliminating the recordset from your original example)

eg.   CurrentDb.Execute "INSERT INTO JSHL_WO_LD_TEST
     (WONUM, LDKEY, LDTEXT, REPORTDATE) SELECT jsht_LONG_DESCRIPTION.WONUM, jsht_LONG_DESCRIPTION.LDKEY,
     jsht_LONG_DESCRIPTION.LDTEXT, jsht_LONG_DESCRIPTION.REPORTDATE FROM jsht_LONG_DESCRIPTION;"
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 7

Expert Comment

by:rockmuncher
ID: 11877354
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.
0
 

Author Comment

by:joelhoffman
ID: 11888238
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!
0
 

Author Comment

by:joelhoffman
ID: 11931404
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
0
 

Author Comment

by:joelhoffman
ID: 12020951
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?
0
 

Author Comment

by:joelhoffman
ID: 12022386
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:

http://www.experts-exchange.com/Databases/MS_Access/Q_21104209.html

0
 
LVL 51

Accepted Solution

by:
Steve Bink earned 300 total points
ID: 12044797
I apologize for the delay in getting to this question.  These hurricanes are being rather inconsiderate in not checking my schedule....I'll have to talk to their manager...

In any case, I gather from your posts here that you solved the last ADO problem you were having from the previous question?

Triggers are a form of automation.  An easy example would be for a library check-out system.  If someone has a late book, their account is marked with a block until they pay the fine.  To automate removing the block, you could put a trigger on the Payments table to check the sum of fines still due.  This way, every time a payment is made, the trigger would run, check to see if all fines have been paid, and, if necessary, automatically update the table to reflect removal of the block.  rockmuncher's post regarding triggers is a valid point.  If you have any triggers operating on the Oracle side of your connection, they will likely slow down the INSERT considerably.

Another suggestion handled in our previous question involves looping through the recordset.  Going through a local recordset and conducting the INSERT record-by-record will always take longer than doing it all at once through SQL.  The best performance you can expect is if you link the Oracle table in Access, and do the INSERT on the Access side through that linked table.  I have to agree with rockmuncher again, though...you will likely not see any significant boost in performance.  At this point, I believe your execution time is a result of the communication overhead between Access and Oracle.

Importing a text could be another way around this issue for you.  You can set up a routine to export the data to a file, copy the file to the Oracle server, then import it locally on Oracle.  With 800+ records, including a memo field, you could potentially see MUCH better performance using this route.  Not being familiar with Oracle, I would recommend you start a question for this item in the Oracle TA with a pointer here for the back story.
0
 

Author Comment

by:joelhoffman
ID: 12049163
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-00942: 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?
0
 
LVL 51

Expert Comment

by:Steve Bink
ID: 12055934
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.
0
 

Author Comment

by:joelhoffman
ID: 12219499
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!
0

Featured Post

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

695 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question