SAP Queries are tools provided to SAP implementers and super-users. There is some maneuvering room for ABAPers, but this is not considered a programming tool. This doesn’t mean that there aren't programming problems with the tool, but they are mostly ignored until it's too late.
Consider the following situation. This is a situation I saw several times in different modules in different sites. I explain it here recreating one of those examples, and you can try too. Slightly after going live, the users request a simple report connecting customer orders and deliveries – simple details that they need. Consider this a very primitive open orders report, that someone from IT does. No need for specialists here. He suggests using SAP Query and this takes around 30 minutes of development, joining the tables VBAK+VBAP and LIPS.
The users then say that Deliveries won't always be available. So they are moved to 'Left Outer Join' by a quick click on the right mouse button.
"We really could use the document type description", says a user and this is added in the SAP Query as a SELECT SINGLE.
(In recreating the example, I called both the Infoset and the user group SAP_TEST. Nothing stopped me).
This is a look at the field groups. By default, all fields are moved into the field groups. The last one is my creation. All others were there on creation.
All that's left is some simple design work in SQ01 – choosing the fields for the report and the selection screen. The SAP-Query will also be called SAP_TEST. This is the selection screen I've created:
The original had much more fields. I've simply ticked our DOC_TYPE, Delivery (LIPS-VBELN), Actual quantity Delivered (LIPS-LFIMG) and Delivery Item (LIPS-POSNR). Have you caught on to what happened?
Back to the users - everybody's happy for the first month. After a while they notice things get slow. Two years later – this is the situation: This report, and a couple of similar ones, cannot be run on-line, even running them as a job is slowing the servers down. The system personnel are opting for more server power, the Basis people suggest indexing something, the ABAPers are pushing for a re-development as an ALV report since debugging SAP Query is out of the question, SAP says this is a customer report, the users will not hear about limiting their selections, and the Integration team start mentioning BI.
What actually happened? Something very reasonable yet logically impossible. The SAP Query selection screen can allow for a small wonder – the option of adding selection fields using whatever's on hand. The usual suspect is obviously the Select Single mentioned earlier (DOC_TYPE). SAP Query allows this parameter to be placed on the selection screen. This means that the entire selection process will happen and each line selected will be tested. Before you consider this, let's go one step further. Unindexed fields are quite slow when you try to select. Notice the Quantity field? But these mistakes can be written in standard code (Shame on you if you do). Adding fields from a LEFT JOIN (outer join) as part of the WHERE condition is something that cannot be written into the ABAP Select command. Yet SAP Query will allow it. That's our biggest 'Bad Guy' by the way – because he's flying below every radar. All of this doesn't happen in the Development-Oriented screen known as SQ02. This happens in the design screen – SQ01.
What happens, scarily enough, is that the Query actually selects every Delivery line and Order line (on average, several millions per site). Only after they're selected, it checks them one by one to see if they fit the selection condition.
Solutions are easy (once you know the problem). Find out what the users are actually searching for in the selection screen and work your way through there. In my case, it turned out that some users constantly used the delivery number, and so we created a separate report for them, where Delivery Tables led the way. This dropped run-time from 6 hours to 9 seconds. Fixing the select-single followed – there were less than 10 records which could've been selected once, but were selected several millions of times (and there's also a feature that can display them without writing the Select). And finally, an Implementer sat with the users to figure out what should be indexed, and why they thought an amount field was to be selected.
I located this error at over ten different customer sites, with varying degrees of damage. It's usually hidden by two outside factors:
(1) SAP Query customer options aren't required to adhere to customer name-spaces.
(2) SAP Query, and its little brother – SQVI – are usually out of a project's scope, they can be done by super-users or 'someone from IT' as a favor or for personal use, and can go undocumented for quite some time. SQVI can be created in the Production System directly (meaning everything shown above can be created very quickly, minus the ABAP Code).
So – the only true solution is keeping tabs on what's happening there, via authorizations and monitoring transport requests – and treating the SAP Query as you would treat a development tool, and not a user-friendly report generator.