RFC_READ_TABLE with MSAccess VB performance issue for more than 10000 rec ?

[Former Member]

Hello,

I am using MS Access to download tables from SAP with RFC_READ_TABLE.

It work fine, let's say almost perfectly, this is a short version of my VB code :

Set R3 = CreateObject("SAP.Functions")

R3.Connection.System = mySystem

R3.Connection.HostName = myHost

...

R3.Connection.Logon(0, True) ' Connection OK

RFC_READ_TABLE = R3.Add("RFC_READ_TABLE")

Set TOPTIONS = RFC_READ_TABLE.tables("OPTIONS")

Set TDATA = RFC_READ_TABLE.tables("DATA")

Set TFIELDS = RFC_READ_TABLE.tables("FIELDS")

Set delim = RFC_READ_TABLE.Exports("DELIMITER")

For i = 1 To myFields.Count

TFIELDS.Rows.Add

TFIELDS.Value(i, "FIELDNAME") = myFields(i)

Next i

RFC_READ_TABLE.Call ' There the data are filling the memory in the object TDATA

' Then I must copy the data to a MS access table or an Array (to be short here is an array)

i=0

For Each ROW In TDATA.Rows

For Each xField In TFIELDS.Rows

myFieldsValue(i).Value = Trim(Mid(ROW(1), xField("OFFSET") + 1, xField("LENGTH")))

i=i+1

Next

next

What else, it works fine ?

The TDATA object is feeded very fast by the ocx, 5 seconds for 500.000 lignes of AGR_HIERT

table as exemple. When I need to read TDATA "For Each ROW In TDATA.Rows" there is a very strange phenomena :

to copy 100 "ROW" in TDATA to a new structure in memory => 0.1s perfect

to copy 1.000 "ROW" in TDATA to a new structure in memor => 0.2s perfect

to copy 10.000 "ROW" in TDATA to a new structure in memor => 1.0s perfect

to copy 100.000 "ROW" in TDATA to a new structure in memor => 60.0s not cool !

to copy 500.000 "ROW" in TDATA to a new structure in memor => 3600.0s misery !

Does anymone experience this. Does anyone know a solution to read memory in a more efficient way ?

[hynek_petrak]

Hi Jean Michel,

try to test with BBP_RFC_READ_TABLE instead of plain RFC_READ_TABLE. I've achieved much better results with the BBP_ variant. The plain RFC_ has got also troubles with larger tables and I experienced quite some crash dumps. With BPP_ no crash dumps at all.

In parallel I'm posting today to my blog here on SCN a piece of VBA code, which downloads data via BPP_RFC_READ_TABLE and creates local MS Access table on the fly, with exact column widths as in SAP. This saved me a lot of hassle, when you need multiple tables and/or you change the columns downloaded a lot. I spent some time on optimization of that function as I work usually with material (large) tables.

Example 1:

RFC_READ_TABLE("MAST", "MATNR,WERKS,STLAN,STLNR,STLAL", "WERKS = 'XX10'", "MY_MAST")

... will create local table MY_MAST, with given columns for all MAST records for plant XX10

http://scn.sap.com/community/interoperability/microsoft-net/blog/2013/10/15/rfcreadtable-data-into-m...

Hynek

[Former Member]

I have been Working with RFC into Excel with great success but im not pulling more than 100,000 rows or so. I havent tried access and was hoping you could send me some of your code and notes on your implementation you present here?

[Sergiu]

Document http://scn.sap.com/docs/DOC-45293

[Former Member]

In addition I created a new kind of text mask to insert record at light speed in Access, it can help.

[Former Member]

Hello Jean,

I am facing a similar problem, can you please explain more in detail what changes you made?

[roland_giger]

hi

can you send me your solution please?

thank you

Roland

[Former Member]

Can u mail me the solution in c#? I don't know even how to extract 100 records from the table

[Former Member]

Issue is solved, I can donwload 1.000.000 records of 512 byte in 6 minutes, faster than between SAP systems.

Do not use the object provide by SAP dll to read data, use your own !