Help on Optimising process of Large table

[orion127]

Hi all,

I have a few customised SAP tables that I must import in Access. It works fine for the smaller tables. In my test DB one table has about 48000 records and it takes several hours to process (in my production Database, the number of records exceed 100000). I've tried splitting the process, closing my table, but it doesn't make a big difference.

Can you guys help me optimse this process please?

I am using Access 97. One table has about 48000 records and 63 fields and an another table has 47 000 and 167 fields.

Here is my code for one table :

Option Compare Database
Option Explicit
Dim objFunctions As Object
Dim objFuncUpload As Object
Dim objZCOWLINE_ITEMS As Object
Dim lcYN As String
Dim i, j, deb_i, fin_i, deb_j, fin_j As Double
Dim db As Database
Dim rs_zcowline_items As Recordset
Dim iRow, iCol As Integer

Sub TraiteDetailed()
'**************
' Logon SAP
'**************
Set objFunctions = CreateObject("SAP.Functions&quot
objFunctions.Connection.Destination = "ER3"
objFunctions.Connection.User = "DEVEL"
objFunctions.Connection.Password = "12ab34"
objFunctions.Connection.Client = "500"
objFunctions.Connection.Language = "EN"
objFunctions.Connection.System = "WQA SERVER"

If objFunctions.Connection.Logon(0, True) = False Then
lcYN = MsgBox("Unable to Connect. Exiting...", vbCritical + vbOKOnly, "SAP - RFC Connection&quot
End If

Set objFuncUpload = objFunctions.Add("Z_READ_CO_LINE_ITEMS&quot

If objFuncUpload.call = True Then
Set objZCOWLINE_ITEMS = objFuncUpload.tables("ZCOWLINE_ITEMS_TAB&quot
Else
MsgBox "Call failed DetLineItems! Error: " + objFuncUpload.Exception
End If

objFunctions.Connection.logoff

Set db = CurrentDb

' I've tried spliting the process.

fin_i = (objZCOWLINE_ITEMS.ROWCOUNT / 5000) + 1
deb_i = 1
For i = deb_i To fin_i
deb_j = (5000 * (i - 1)) + 1
fin_j = (5000 * i)
If fin_j > objZCOWLINE_ITEMS.ROWCOUNT Then
fin_j = objZCOWLINE_ITEMS.ROWCOUNT
End If

For j = deb_j To fin_j
Set rs_zcowline_items = db.OpenRecordset("ZCOWLINE_ITEMS&quot
rs_zcowline_items.AddNew
For iCol = 1 To 63
If Len(Mid(objZCOWLINE_ITEMS(j, iCol), 1)) = 0 Then
rs_zcowline_items(iCol - 1) = " "
Else
rs_zcowline_items(iCol - 1) = Mid(objZCOWLINE_ITEMS(j, iCol), 1)
End If
Next
rs_zcowline_items.Update
Next
rs_zcowline_items.Close
Set rs_zcowline_items = Nothing
Next
Set objZCOWLINE_ITEMS = Nothing

End Sub



Public Function Process_detailed()
TraiteDetailed
End Function

Thanks
Sonia.

 

[boriska40]

Hi,
I don't know SAP, but try to do next:
1.Export from SAP your tables to ASCII file (txt).
2.Import to Access.

 

[willir]

orion127

Do you know where in the code ther performance problem resides?

Put a "Stop" command at the top, within the subroutine, but prior to the SAP login. Step through the code, F8, ti see where the bottleneck resides.

Second, you are using ADO to add a new record but you seem to be closing the record set each time. I am not strong on ADO, but I suspect you can move set and close of the recordset outside the second For / Next loop (deb_j to fin_j). If I am right about this, then you will avoid defining the same record set 40,000+ times.

Amd if I am wrong, you can shoot me.

Richard

 

[orion127]

Boriska40,

I was trying to avoid importing because I didn't want to manipulate another file on the network, but It seems the only soultion so far.

Richard,

Getting the info from SAP is a matter of seconds. The bottle neck is in the inner loop I believe. I'll try what you wrote, and post the results.

Thanks.

 

[orion127]

Richard,

I tried what you said, moving the set and close, but It's not faster. With the debugger I can conclude that the more record I process, the slower it gets. Is there anyway I can free some memory within the code?

Anything else I can try?

Sonia

 

[willir]

orion127

Are you permitted to use an ODBC connection to SAP?

If so, you can access the tables in a more direct fashion.

Also, I am surprized the moving the set and close out one loop. Maybe I don't understand your code well enough. Is the record set being opened for each record, or at the start and end of the process?

When in debug more, CTRL-G will bring up the "immediate window". A cool trick is to use the "?" question mark to echo the value of a field to the screen. Example...
? objZCOWLINE_ITEMS

I think you need to make sure the record set is opened at the start and closed at the end of processing. I think you can move it to after the line deb_i = 1

Speaking of which, are these results from a dairy or beef breeding program? Been way too long since I touched this stuff, but I still well remember the number of records involved, and the value of the work.

Richard

 

[jsteph]

First, if you could get ODBC connection to the SAP backend, that would be a good start, but it's unlikely. I work in an SAP environment and not only is SAP itself very protective of it's db (Oracle in my case), but the SAP proj. mgr. is even more prickly about giving even readonly access to his data.

Second, what kind of object is objZCOWLINE_ITEMS? Is this a bapi function that is returning rows? If so, it's going to be slow. There are export functions in SAP that can export to an ascii file as boriska pointed out, if this is a daily situation maybe you could coordinate these exports, I think this would be quicker.

Finally, as a general Access coding technique, you could
A. Open the recordset with the type,option: 'dbOpenDynaset,dbAppendOnly'

B. You could declare a field array:

Dim arrF() as field
ReDim arrF(63)
<<open the recordset>>
'Assign the field array
for iCol = 0 to 62
   Set arrF(iCol) = rs_zcowline_items.fields(iCol)
next iCol
rs_zcowline_items.AddNew
   For iCol = 0 To 62
     IF Len(Mid(objZCOWLINE_ITEMS(j, iCol), 1)) = 0 Then
         arrF(iCol) = &quot; &quot;
      Else
         arrF(iCol) = Mid(objZCOWLINE_ITEMS(j, iCol), 1)
      End If
   Next iCol
rs_zcowline_items.Update

(not sure if the bapi object is Zero based)
Anyway, this Field Array method is a huge performance boost, especially with anything over a dozen or so fields, with 63 your saving a tremendous amount of time.
If the SAP object had a Field or Column object, you could also array-ize that for better performance.
--jsteph