cancel
Showing results for 
Search instead for 
Did you mean: 

Select on BSAD significantly longer fetch time in Prod than Pre-Prod

former_member55320
Discoverer
0 Kudos

Hi there,

We've had a specific issue with a custom report that intermittently runs slower in production than pre-prod. The issue is a specific select statement on table BSAD. There is around x1.8 more data in prod than pre-prod, but the select statement shows x141 slower in prod than pre-prod.

The SQL trace appears to show the same indexes being used in both system and this is an intermittent issue. We don't see any link to wider system load, in fact one occasion was during a public holiday with virtually no users in the system.

My question is primarily whether the answer is there in the SQL trace results and we don't have the knowledge to spot it, or there is something else we can check - any additional logging at the basis layer for example that may give further clues?

The SQL trace shows:

Prod

Operation = open, duration = 805,379

Pre-prod

Operation = open, duration = 5,202

Both are using index BSAD~5 (index seek)

BSAD~0 (clustered index seek)

DB = MSSQL 11.00.6579.00.

Accepted Solutions (0)

Answers (3)

Answers (3)

jonel_rienton
Participant
0 Kudos

Hi, what's the size of both boxes in terms of CPU and memory? Are both SQL configured the same way? What does the wait stats show? Are there any disk latency?

Your basis should be able to get this from DB02.

former_member55320
Discoverer
0 Kudos

Hi Eduardo, thanks for responding. BSAD in pre-prod has 1.1 million rows, prod has 2 millions rows.

The select statement where the issue occurs is below.

The Z table that feeds the internal table used in the select statement has the same number of entries in both systems and populates the itab with the same number of entries.

SELECT bukrs augdt augbl zuonr gjahr belnr buzei blart dmbtr vbeln INTO TABLE gt_bsad FROM bsad FOR ALL ENTRIES IN gt_whatif WHERE bukrs EQ gt_whatif-pbukr AND gjahr EQ gt_whatif-gjahr AND ( belnr EQ gt_whatif-refbn OR belnr EQ gt_whatif-belnr ).

Ive attached some SQL screenshots which I hope are of use.

DB corruption was checked and ruled out by our Basis team. DB statistics were confirmed to run daily and had run successful that morning.

Sorry I'm not sure what the DB execution plan is?

kiran_k8
Active Contributor
0 Kudos

Phil,

You can explore Open/Fetch/Close Cursor methodology.

More info available on SCN if you search with the keyword "Open Cursor".

K.Kiran.

former_member184473
Active Contributor
0 Kudos

Hello Phil,

Are there any differences between pre-prd and prd (number of rows on BSAD, DB size, ...)?
Is there any differences in the DB execution plan?

Regards,
Eduardo Rezende
SAP Support