topic Re: Select Query Performance issue in Application Development and Automation Discussions

Select Query Performance issue

Santosh21N — Wed, 03 Jun 2020 12:04:21 GMT

Dear Experts,

Could you please tell me how to improvise the below code.

Fetching the data from LIKP and LIPS without non key fields, its taking 5 to 10 min to execute,as user requirement is not use the vbeln as input and improve performance.

SELECT t1~vbeln t1~vstel
t1~kodat t1~kunag t1~kunnr
t1~wadat t1~wadat_ist t2~posnr t2~pstyv
t2~werks t2~matnr t2~arktx t2~charg
t2~spart t2~lgort
INTO CORRESPONDING FIELDS OF TABLE i_delpos
FROM likp AS t1 INNER JOIN lips AS t2
ON t2~vbeln = t1~vbeln
WHERE t1~wadat IN s_date
AND t1~vstel IN s_vstel
AND t2~werks IN s_werks
AND t2~matnr IN s_matnr
AND t2~spart IN s_spart
AND t2~lgort in s_lgort.

Re: Select Query Performance issue

ziolkowskib — Wed, 03 Jun 2020 12:19:50 GMT

Hi santoshnadumani,

I had similar issue once and in my case the solution was splitting those 2 selects - firstly I selected VBELN from LIKP (using the selection criteria applicable to LIKP) and then having the document numbers I executed another selection from LIPS using FOR ALL ENTRIES and selection criteria applicable for LIPS (and afterwards removing entries gathered from LIKP that did not exist in LIPS).
I would not call it an elegant solution but in my case it worked as a charm.
If someone has a better suggestion I would be glad to hear it.

Regards,
Bartosz

Re: Select Query Performance issue

MateuszAdamus — Wed, 03 Jun 2020 12:29:23 GMT

Hello santoshnadumani

ziolkowskib 's solution is a good idea. It could work. You should try it out

In case it does not, check the report's selection criteria - are there any fields marked as obligatory? If yes, then you could create a DB index on the table using those fields (and MANDT field) - this should help with performance.

If no fields are marked as obligatory (maybe this should be changed?), then ask users which fields they use the most and make a DB index based on these fields.

Kind regards,

Mateusz

Re: Select Query Performance issue

Santosh21N — Wed, 03 Jun 2020 12:43:09 GMT

Hi ziolkowskib,

Thanks for your input, if write seperate select statments for LIKP and LIPS but its without key fields, so do you think it wont be any performnce issue?

Re: Select Query Performance issue

Santosh21N — Wed, 03 Jun 2020 12:54:40 GMT

Hi mateuszadamus,

Thanks for the valuable input, how to use the DB index in the program?

Regards

Santosh

Re: Select Query Performance issue

Sandra_Rossi — Wed, 03 Jun 2020 12:56:18 GMT

For any post about SQL performance issue, please provide the values of input variables and the execution plan, because it depends only on your data.

Re: Select Query Performance issue

MateuszAdamus — Wed, 03 Jun 2020 13:07:35 GMT

Hello santoshnadumani

You don't need to worry about using the DB index in the program. In most cases, the DB selects the best index available.

However, this works only if the index is created in the table. That has to checked and implemented (if needed) by a developer.

Kind regards,
Mateusz

Re: Select Query Performance issue

Santosh21N — Wed, 03 Jun 2020 13:14:16 GMT

likp-and-lips.jpg

Hi Sandra,

I will be selecting based on the below input, from this only 4 entires are fetched but still it takes 5 to 10 mins.

PFA screen shot

Re: Select Query Performance issue

ziolkowskib — Wed, 03 Jun 2020 14:27:43 GMT

Hi santoshnadumani,
As I mentioned in my case splitting one SELECT with JOIN into 2 separate selections improved performance. In this case the 1st selection would be executed without key fields but the second selection from LIPS would be executed with partial key (VBELN)

Regards,
Bartosz

Re: Select Query Performance issue

gasparerdelyi — Fri, 05 Jun 2020 10:03:26 GMT

Can you upload output of explain functionality, or in case of HDB, the PlanViz file?

Re: Select Query Performance issue

Jelena_Perfiljeva — Fri, 05 Jun 2020 20:04:04 GMT

Any performance-related investigation should start with using SQL Trace (ST05) and checking Execution path. (Other tools could also exist in the HANA systems but I assume this is not the case here.)

Fast access on DB level is only possible by primary key or secondary index. That's pretty much it. I'm guessing there is no suitable secondary index for LIKP or LIPS in your system, so it has to go through all the records (aka "full table scan") to find the ones that match the selection criteria. Even though it might as well find just 4 or even 0 matching records, still the time is spent just looking for them. Imagine if you mixed up a bag of rice and a bag of beans and then had to find all the rice grains that are longer than 3 mm. The same thing but with data.

I'm a bit puzzled by the recommendation by Bartosz to split the SELECT into two separate ones. While it's not improbable that it could improve the performance, I just find it odd that DB optimizer doesn't take care of it more efficiently. This could be a YMMV situation that could work differently based on DB or even specific SAP system. Also, a secondary index could still be a better solution.

Performance tuning is not the exact science, so your best bet would be to use DB tools and experiment in the specific system. Reach out to a local Basis admin or DBA if you need assistance with using the tools or interpreting findings.