Results 1 to 7 of 7
  1. #1
    Join Date
    Jul 2002
    Location
    Tulsa
    Posts
    4

    Unanswered: Fastest AddNew Using ADO & Oracle

    About the fastest I have been able to achieve adding records into an Oracle table through Visual Basic ADO is about 1-2 records per second. I've tried various "tweakings" to improve performance. Does anyone have a success method that they would be willing to share?

    Here is the essence of my code:

    madoRecordSet.CursorLocation = adUseClient
    madoRecordSet.CacheSize = 100
    madoRecordSet.Open "UPLOAD_TABLE", madoConnection, adOpenStatic, adLockOptimistic
    For lngCell = 2 To lngMaxCell
    strFIELD1 = Range("C" & lngCell).Value
    strFIELD2 = Range("H" & lngCell).Value
    madoRecordSet.AddNew
    madoRecordSet("FIELD1") = strFIELD1
    madoRecordSet("FIELD2") = strFIELD2
    madoRecordSet.Update
    next lngCell

    I'm using some VBA scripts behind an Excel spreadsheet, to allow users to upload their worksheets into some Oracle tables, and would appreciate anyone who has been down this road sharing a tip or two.

    Thank you,
    David Sacker

  2. #2
    Join Date
    Jul 2001
    Location
    NC
    Posts
    102
    Don't know if you can work with the connectionin VBA. If you can, and you're still tweaking your code, you might try something along the lines of:

    for cellRowNo = cellRow1 to cellRowMax
    strSQL = "INSERT INTO tableName (field1Name, field2Name) _
    VALUES (cell1Value,cell2Value)"
    cnn.execute strSQL
    next cellRowNo
    cnn.execute "commit"

    The real problem is having to return to the worksheet, finding the row and selected columns, and reading the cell values each time you go around the loop. Why not read the target cells into an array and loop through the array?

  3. #3
    Join Date
    Jul 2002
    Location
    Tulsa
    Posts
    4
    To vududoc,

    I really didn't get any noticeable improvement via an array. However, since my original question, I have discovered the beauty of Oracle's OLE DB connection rather than ADO, which has given me about 8 times (yes, 8 times) the throughput.

    Let's pretend we want to insert the cell values in columns A and B up into an Oracle database table. Here is the essence of what I'm doing now (what to substitute should be obvious):

    Public Sub Kick_Ass_Method()
    Dim OraDatabase As Object
    Dim OraDynaSet As Object
    Dim OraSession As Object
    Dim lngRow As Long
    Dim strFIELD1 As String
    Dim strFIELD2 As String

    Set OraSession = CreateObject("OracleInProcServer.XOraSession")
    Set OraDatabase = OraSession.OpenDatabase ("my_database", "userid/password", 0)
    OraDatabase.Parameters.Add "FIELD1", "", 1
    OraDatabase.Parameters.Add "FIELD2", "", 1
    Set OraDynaSet = OraDatabase.CreateDynaset("select FIELD1, FIELD2 from MY_TABLE", 0)

    For lngRow = 1 To 100
    strFIELD1 = Range("A" & CStr(lngRow)).Value
    strFIELD2 = Range("B" & CStr(lngRow)).Value
    With OraDatabase
    .Parameters("FIELD1").Value = strFIELD1
    .Parameters("FIELD2").Value = strFIELD2
    .ExecuteSQL "insert into MY_TABLE values(:FIELD1,:FIELD2)"
    End With
    If lngRow Mod 10 = 0 Then
    DoEvents
    End If
    Next lngRow

    OraDynaSet.Close
    OraDatabase.Close
    OraSession.Close
    Set OraDynaSet = Nothing
    Set OraDatabase = Nothing
    Set OraSession = Nothing
    End Sub

    I've done some "trench digging" to learn this, but this really is an awesome connection method.

    Thanks for responding to my original question.

    David

    PS: Anyone with questions about this, I'm more than happy to share the wealth of what I've dug out.

  4. #4
    Join Date
    May 2003
    Posts
    2

    Oracle Connection to Excel

    I am trying to get some data from Oracle using Excel. I am faced with the problem of my querystring being complex and > 255 characters.
    I am trying to use CreateDynaset (variant,&0) . Though the variant holds my entire query, I am not able to execute as only 255 chars are read.

    Else can I do a Nested Dynaset. First I create the Dynaset and then from the Dynaset use another query on the first dynase?

    Regards

    Anand

  5. #5
    Join Date
    Jul 2002
    Location
    Tulsa
    Posts
    4

    Re: Oracle Connection to Excel

    I never have a limitation (255 or otherwise) on an OraSession.CreateDynaset ... are you doing a CreateDynaset through some other method?

    Also, rule out that you're not truncating your string via some code, property or method limitation that you may be executing prior to the CreateDynaset command.

    And yes, OraSession works great in VBA (in response to a comment further above). I use it more than any other method now.

  6. #6
    Join Date
    Jul 2002
    Location
    Tulsa
    Posts
    4

    And yet fastest would be ...

    I hadn't been on here in awhile and thought I'd share another method of inserting data into a table. You can always write it to a text file, then invoke SQL Loader. I load approximately 2000 records per second with SQL Loader, so if large data inserts are the objective, you really can't beat it. It's worth the extra coding care.

  7. #7
    Join Date
    May 2003
    Posts
    2
    thankyou Dsacker..

    I am trying to create an inventory from our main database into an excel sheet and this has about 400 tables data moved to excel. i am trying to get data out of Oracle into Excel.

    But its got to be tailored for different requirements so i have a macro which i run which generates the query based on the request form i get .
    Some queries tend to be long due to several where conditions and also length of table name is large. So I used aliasing ..but still the query stored in the variable truncates the query after 255 characters. I changed this to a variant variable. Still no luck. Hence the request. Thank you for your time and advice .

    Regards

    Anand

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •