SAP Classification vs Enhancing Master Data Tables – part II

OMER BRANDIS (SAP basis & db2 DBA) posted 7/1/2008 | Comments (3)
In my last post I wrote a little about sap’s classification module, and of why table AUSP can get so big. In this post, I want to show you why using the classification system to search for a Classified objects (the purpose for which it was created) is a lot more expensive then using a table enhancement structure.

Example 1 – searching for master data using a table enhancement structure:

Select * from mara

Where color=’BLACK’ and smell = ‘GREAT’ and taste = ‘AWSOME’

Example 2 – searching for "classified" master data:

Select *

From mara t1 , Ausp t2, Ausp t3, ausp t4

where t1.matnr = t2.object

and t2.classification_type = ‘MATERIAL-COLOR’

and t2.class_value = ‘BLACK’

and t1.matnr = t3.object

and t3.classification_type = ‘MATERIAL-SMELL’

and t3.class_value = ‘GREAT’

and t1.matnr = t4.object

and t4.classification_type = ‘MATERIAL-TASTE’

and t4.class_value = ‘AWSOME’

Why is this a problem?

The first sql statement is a very simple query that accesses just one table, and provided your data has high cardinality, may benefit from a database index to make it even quicker and cheaper to execute.

The second sql, must join 4 tables. At first the database must identify which condition is the one with the best filtering, then for each qualifying row two more scan’s of AUSP must be made in order to check the additional conditions. The overall cost per row is usually significantly higher then in the first case.

Moreover, determining the tables join order is not a trivial operation since it requires identifying the best (most filtering) "condition" , and if done incorrectly (because of inadequate statistics or database code) will cause the database to do more work and increase the query cost and runtime.

Another thing to consider is that the second query doesn’t scale very well, the more "classifications" are used to locate the data the more times table AUSP will be accessed hence the run time will probably increase. In the first case, adding conditions will usually improve the queries speed.

Omer brandis
"conquered storage will not be returned"

This message is for the designated recipient only and may contain privileged, proprietary, or otherwise private information. If you have received it in error, please notify the sender immediately and delete the original. Any other use of the email by you is prohibited.

Advertisements
Categories: Uncategorized | Leave a comment

Post navigation

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Blog at WordPress.com.

%d bloggers like this: