Internal Tables ... Internals

[Former Member]

Hello Everyone,

I've looked everywhere ... well, almost

but I couldn't find a lot of material that explains in more detail the different internal tables mechanisms.

The material I read (ABAP Objects from SAP Press) which mostly explains how to use internal tables

was immediatly following an example of a very simple, all in memory, linked list

which would suggest internal tables are smewhat similar, with some variations.

In another place (I believe WIKIPEDIA) it mentions block-wise operations for internal tables.

Blocks are more optimized for disk read/write operations ...

so is there any paging mechanism (DISK) or is it all in memory (RAM).

I think, I also read some place that when sorted internal tables reach a certain size they're broken into a tree-structure

which reminds me of a B-Tree ???

I read somewhere that LOOP ... WHERE are optimized for sorted internal tables.

Is it true ? if so was it always like that or was it added in a recent release ?

I was hoping some of you gurus can inlighten me

Even better if there was an article.

Thanks

[Former Member]

IMHO, a whole book could be written about internal tables.

When i'm developing, I examine how much data I will be storing in my itabs and then decide what type to make them.

The VAST majority of itabs we use are regular old internal tables.

When dealing with tens of thousands or more records, hashed tables improve performance.

Search google for some posts by horacio zapettini regarding the different itabs and their strengths/weaknesses.

[Former Member]

i agree, the subject deserves a book

an explanation of the underline internal tables mechanisms would be a good start.

it's interesting there's not one article about it ??

my feeling is that i would mostly go by access type rather then size.

thanks!

Edited by: Alon Blich on Jun 2, 2010 11:35 PM

[ravi_lanjewar]

I read somewhere that LOOP ... WHERE are optimized for sorted internal tables.

Is it true ? if so was it always like that or was it added in a recent release ?

Yes, Sorted table will give better performance loop with where condition depending on key defined by you. Where condition must contain the most left part of key fields of the sorted table and hash table having some limitation of maximum record store capacity.

You can alos know about internal table

There are three types of internal tables:

Standard tables In a standard table, you can access data using either the table index or the key. If data access is done using the primary key, the response time is linearly related to the number of table entries. The key of a standard table is always non-unique.

Sorted tables are always stored sorted according to their key. You can access them using either the table index or the key. If you use the key, the response time is in logarithmic relation to the number of table entries since the system uses a binary search to access the table. The key of a sorted table can be either unique or non-unique.

Index tables. Standard tables and sorted tables are generically known as index tables, since their values can be accessed via an index.

Hashed tables can only be accessed using the primary key. The data retrieval time is constant, regardless of the number of table entries, since access is done using a hash algorithm. The hash algorithm is determined internally. The key of a hashed table must be unique. You can neither implicitly nor explicitly access hashed tables using an index.

For more details you can read SAP Material BC490.

Rgds

Ravi Lanjewar

Edited by: Ravishankar Lanjewar on Jun 3, 2010 9:43 AM

[Former Member]

thank you for taking the time to reply

but that's mostly the basic, half a page info that's avail.

even the loop and sort tables is somewhat missing.

if there was a range for example where field >= 'a' and field <= 'b' does abap find the lowest and highest values and then scans through that range ?

what about if there were several ranges would abap combine them or choose one range ?

very similar to what you would have with database queries but obviously much simpler. one table, one index. we're not talk about query optimization.

so no one knows if internal tables are only stored in RAM or is there any paging to DISK ??

if itabs have paging or not has far reaching implications on scalability, and performance ... and their use.

no one knows how they work ? what do they think it's magic.

in contrast if you ask any database developer or dba about tables and index types

he could blindly quote their smallest details, structures, mechanisms and everything about them ... but then you would exect the same from a car mechanic. zzzing

[former_member192616]

Hi,

a whole book? lets start with a chapter first. I wrote some things about

internal tables in this book:

http://www.sap-press.com/products/ABAP-Performance-Tuning.html?

A test chapter (about internal tables) is available here:

http://www.sap-press.de/download/dateien/1880/sappress_abap_performance_tuning.pdf

Kind regards,

Hermann

[former_member192616]

Hi,

> if there was a range for example where field >= 'a' and field <= 'b' does abap find the lowest and highest values and then scans through that range ?

> what about if there were several ranges would abap combine them or choose one range ?

All conditions that are not equal conditions (=) can not be optimized and will lead to a full table scan regardless

what table type you have. A full table scan means for reads the search starts at the first line and processes

line by line and stops at the first hit, for loops the search starts at the first line and goes all the way down line

by line and copies (refrerences or assigns a field symbol) for the matching lines.

> so no one knows if internal tables are only stored in RAM or is there any paging to DISK ??

> if itabs have paging or not has far reaching implications on scalability, and performance ... and their use.

the pages of internal tables are stored in the user context (extended memory, roll memory or heap memory).

This is main memory or better virtual memory. From an operating system point of view virtual memory may

be in RAM or on disk (swap). If a system swaps (which should not be the case) your internal tables in

e.g. extended memory in the virtual memory might go to physical disks. (slow)

Additionally you can export internal tables to memory, shared memory and shared buffer. the later 2 are

again in virtual memory, the first one is SAP paging memory which ususally i partially in virtual memory

and partially on disk (ST02).

> in contrast if you ask any database developer or dba about tables and index types

> he could blindly quote their smallest details, structures, mechanisms and everything about them ...

Well, you just dont need to know all the details. Some them are helpful to understand performance

problems but in general there is no big need to know much details. Understanding the table types and

what statements can be optmized is very important. Understanding table sharing might be helpfule too.

Besides that e.g. how rows are stored the effect of initial size or the index stuff is a.) not VERY important

but b.) may help in certain situations to understand memory consumption for internal tables or things like that.

Kind regards,

Hermann

[former_member192616]

Hi,

> I've looked everywhere ... well, almost

> but I couldn't find a lot of material that explains in more detail the different internal tables mechanisms.

at SAP Press as well?

there is a book on tunging

http://www.sap-press.com/products/ABAP-Performance-Tuning.html?

and a test chapter on internal tables with some details.

http://www.sap-press.de/download/dateien/1880/sappress_abap_performance_tuning.pdf

> In another place (I believe WIKIPEDIA) it mentions block-wise operations for internal tables.

the data is indeed stored in pages. additionally we may have indexes (linear or btrees) or a

hash administration for optimized access.

> Blocks are more optimized for disk read/write operations ...

and for main memory storage. the databases manages the data in blocks/pages in the memory

as well.

> so is there any paging mechanism (DISK) or is it all in memory (RAM).

the pages are in main memory (user context, extended memory, roll or heap)

> I think, I also read some place that when sorted internal tables reach a certain size they're broken into a tree-structure

> which reminds me of a B-Tree ???

in certain cases, yes.

> I read somewhere that LOOP ... WHERE are optimized for sorted internal tables.

> Is it true ? if so was it always like that or was it added in a recent release ?

it is true. a loop where with a leading part of the key on a sorted table does a

binary search internally to find the first row and starts processing from there.

Not sure when this was available... 4.6C i guess... it is for sure in 6.40 and 7.00.

Hope this helps a bit.

Kind regards,

Hermann

[ThomasZloch]

Speaking of books, there is also

http://www.dpunkt.de/buecher/3096.html

by our forum participant Siegfried Boes. Chapter 6, only about internal tables (though in German, but maybe somebody can help you there...)

Don't tell us you did not find interesting material

Thomas

[Former Member]

Thank you very very much Hermann, Thomas and everyone else,

Looks great !