In some posts here: Performance - what will kill you and what will leave you with only a flesh woundAnyone Got Some Real Benchmark Stats on "For all Entries"???"Yes, Virginia, the check is in the mail" (or, why you can't trust SQL) Rob Burbank and I have been tossing around various ideas on how to do efficient data retrieval when you're forced to retrieve your data inside the tiger-cage of SQL.*** Here is yet another approach that some may not have thought of because to think of it, you have to think OUTSIDE the SQL box, with all its attendant nonsense concerning cardinalities, joins, views, etc. Your client wants a program against MARA in which customers must select by creation date (ERSDA) and may optionally select on group (MATKL) and type (MTART) as well. Fuirthermore, the ERSDA criteria will be narrow - this report is run frequently to find out certain information on new MARA data. So you do the obvious thing first - you ask your DBAs to check the cardinalities on prod mara-ersda, and when they tell you they're OK, you ask them to add a custom index to MARA on ERSDA. But now - what about mtart and matkl? There is an SAP delivered index on these, but before you start thinking about adding these to your select ... STOP! and do the following instead: 1) define a hashed itab i_mtart with one field in it: mtart 2) define a hashed itab i_matkl with one field in it: matkl. 3) Populate i_matkl from your select criteria for matkl using a routine like this: FORM get_matkls. CLEAR i_matkl. SELECT matkl FROM t023 INTO wa_matkl WHERE matkl in s_matkl. INSERT wa_matkl INTO TABLE i_matkl. ENDSELECT. ENDFORM. "get_matkls (Or, just a select into itab if this will work on a hashed itab - I don't really remember.) 4) Do the same for mtart 5) When you retrieve your mara rows into an itab i_mara using the simple index you've created on ersda, make sure to grab matkl and mtart in this retrieval. 6) Then after the mara retrieval, loop thru your i_mara itab doing hashed look-ups on i_mtart and i_matkl (either or both, depending on your selection criteria). When these look-ups return sy-subrc <> 0, delete i_mara. So here's the challenge to all you SQL-jockeys out there. Take your best shot at a select statement against MARA with just ersda, matkl, and mtart in it, and post your statement in response to this blog post. (Assume when writing your select that mara-ersda has a custom singleton index on it, as noted above.) I will then test your select against the hashed approach and report the results. Anyone wanna bet on the outcome? (All proceeds going to Craig , Mark, Marilyn, and Mark, of course - they're "the house".) Finally, for those who missed yesterday's post on why the SAP consulting community must do a better job of policing itself, here is the link to that post: Why the SAP Consulting Community Must Become a Collective Change-Agent
