Endusers have security defined - batch rpts taking too long to run

Cathy Wilbur

Well Known Member
End users have security defined at the business unit level and on specific programs, functions, on and on.

A report that they run can take anywhere from 13-25+ minutes to run. I run the same reports for them and mine finishes between 4sec to 37sec (I have a much broader security-I have no security at business unit level).

How much does security that is defined to the system play a role in how fast jobs are processed in the system? Is there any way we can speed up these jobs thru the system?
 
What is the count of your F00950 table?

Have you ever updated statistics and indexes on that (and other) tables?
 
Cathy,

Row level security does have a performance impact, however your example looks excessive.
 
Cathy,

Row security, particularly with business units SUCK, performance-wise!

I can vouch for the issue. At a recent client - reports were being killed after two hours (2 hours). When I copied the SQL statement from the debug log - the SQL statement, by itself, took over two hours. When I removed all the business unit security from the statement - it completed within seven seconds.

The quick fix was to place the UBE in Application Exclusive mode.

There are definate issues with Business Unit Security!

(db)
 
Cathy,

In addition to the advice Brother of Karamazov has provided, have a look at the indexes on the security table F00950 and the tables referenced in your report. You could also run a debug log to pick up the SQL select statement to see exactly what is happening in each case.

Hope this helps.
 
Cathy,
In some cases you can have this problem.
The security system do not slow process, even if you have a lot of records in F00950 (we have 2M) and we dont have problems.
The problem is related on the indexes that the database decide to use to retrieve the data and exist into the data selection the MCU field our in your codes of the MCU.

When you put Business Unit you will have included additional WHERE clauses with MCU BETWEEN ('MCU A' AND 'MCU B' into you SQL statement selecting the ranges MCU.

I belive the database is deciding to use an index on MCU, and when view the statistics decide to make a full scan into the database.

I have some questions and will be easier to help you...
1. Do you have SQL command?
2. How many lines do you have into your table
3. How many different MCU values do you have into this table

Because if you have one table for ex. with 20 different values on MCU and 10 million lines, even with the index the database will make a full scan.
In this case the MCU index is not efficient and a possible solution is to drop it from the table as you mention that "without security" the job is running into an acceptable time..

Hope this help...
 
We have been using extensive business unit security since our go live in 2000. We experienced the same problem with some of our reports. In some views, the mcu filed may appear from different tables. By using the MCU field/table combination that was included in the index resulted in a dramatic increase in performance. The standard indices are indicated at the bottom of each table and view spec sheet.
 
For my end user with the most cost centres I have 108 entries in the table. This is a very small snapshot of the defn from the security table. This person has the most cost centres (business units across the company).
 

Attachments

  • 94625-Security Table Capture-User LULU.jpg
    94625-Security Table Capture-User LULU.jpg
    191.5 KB · Views: 127
What does putting the UBE in application exclusive mode mean? How do you do it and what will it do for you?
 
Hi Cathy,

Placing the UBE in Application Exclusive mode - basically runs it without security. The report will run over all business units (yes, that is an issue).

But - the quickest way to test if the performance issue is security is to have your security person place the UBE in AE, then run the longest running version - and time it. If the AE mode is significantly shorter - then you have the answer.

I have a fellow member, that believes he's resolved the issue. He's unavailable for a couple weeks - I'll make sure to follow-up when he's back in the states.

(db)
 
Back
Top