SAP transaction returns more columns than required

Question

When I run some SAP transaction, e.g. MB51 Material document list I can specify some conditions, that filter and reduce the number of records returned by the transaction. (Date, Material etc.) I can specify or filter the columns that are returned only by selecting layout.

It takes then a relatively long time to get required records from e.g. MB51 transaction. However when I have the resultset on the screen and when I change layout and include other columns, the transaction is not re-run: instead column values are shown immediately. I suppose that all the column values are being returned regardless on the selected layout.

Since there is usually a large amount of columns that do not provide any interesting data, it slows significantly down the performance of some SAP transactions.

I do have only regular user access to the SAP installation and have no experience with ABAP or with creating custom transactions in SAP. So I need to stick mostly with standard transactions only.

Is this behaviour a normal problem of SAP or is it rather a problem of our installation? Is there any workaround for a non-privileged user to filter returned columns?

this is not a problem. The columns a program returns are determined by the program itself. You can't remove columns from the selection, you can only remove them from the display. And I am not sure the additional columns slow down transactions as much as you might think. Most of the time selecting the few wanted records out of a large table takes a lot more time than returning a few (even a few hundred) unneeded columns. Slow selections often result from using selection criteria that isn't represented in table indexes, resulting in full table scans. Your admin may be able to help you there... — Dirk Trilsbeek
So as far as I understand, if you want a really fast report in SAP that returns only desired columns and nothing else, you need to create a custom transaction? I unfortunately do not select few records but many records for further analysis. — Vojtěch Dohnal
there is SAP Query if you want to have some influence on what is queried and returned, you can create your own custom queries with it, some database knowledge provided. But standard SAP transactions can't be modified that way. They usually get all the data and display what you need. — Dirk Trilsbeek
@DirkTrilsbeek Your comments look to me as an answer to my question. When SQ01 returns User vdohnal does not belong to any group - it probably means that I cannot use SAP Query, right? — Vojtěch Dohnal
if you can call SQ01 you at least have the needed SAP roles to do so. The groups in SAP Query are used to group queries into different areas to make them easier to manage. That you don't belong to any group (yet) doesn't necessary mean you can't use SAP Query. It basically works like this: first you create an Infoset, which is generally the data your query is based on. Single table, table join or even a custom abap program. Then you create a query based on that infoset. You can create several queries on the same infoset. — Dirk Trilsbeek

Dirk Trilsbeek Dirk Trilsbeek · Accepted Answer · 2014-09-16T19:57:46

You can't modify SAP standard programs to make them return fewer columns as they do right now. That would mean modifying the SAP owned source code (and modify it quite extensively at that), which is technically possible but not at all recommended and of course completely unsupported.

First of all, the number of columns a report returns usually isn't a big performance concern. In some cases, if you return a very high number of rows and need only a few columns, you may be right. But quite often you return only a moderate number of rows (moderate compared to the total number of rows in the table), so the actual data transferred from the database isn't that much. Slow queries can result from a number of reasons, for instance joining several large tables or not having the right indexes to join the tables. Here your admins can help, they have tools available to monitor exactly what the SAP system is doing during your query and they may find the reason for the low performance.

In cases where you actually need or want to be able to influence queries yourself you have to resort to tools like SAP Query (transaction SQ01/SQ03). SAP Query is for data display only, you can't modify data from there. But you can create your own queries, the appropriate knowledge provided. You need to create an Infoset that contains the data you need, after that you create a query (or several queries) on that Infoset. In your infoset you decide which tables you need and which columns should be returned. In your query you then decide on which columns to filter your query and how to display the results. There is a bunch of tutorials on SAP Query (one example here) out there, including a few on youtube.

SAP transaction returns more columns than required

2 Answers