topic Re: Speed up select query in Application Development and Automation Discussions

Speed up select query

Former Member — Wed, 17 Aug 2011 10:49:26 GMT

Hi All,

My report needs to scan the entire DB table EANL and cleanse data. Following is the select statement that i need:

SELECT a~vstelle

INTO TABLE lt_premises

FROM eanl AS a

INNER JOIN euiinstln AS b

ON a_{anlage = b}anlage

INNER JOIN euitrans AS c

ON b_{int_ui = c}int_ui

WHERE c~ext_ui IN s_esiid.

Here the user can execute the report without entering anything in the select option s_esiid. In this case the select will need to query the entire EANL table.

As expected this takes hell of a time. Can anyone suggests ways to speed this up. I can break the join statement if that can help.

Thanks,

Jaideep

Re: Speed up select query

vimal — Wed, 17 Aug 2011 10:56:56 GMT

Hi Jaideep,

If you can use FOR ALL ENTRIES by breaking down this statement and make two select statements , and then debug which select statement is taking more time and then post .

Re: Speed up select query

Former Member — Wed, 17 Aug 2011 12:31:21 GMT

Hi Jaideep,

your only select-options are on euitrans, so select all int_ui values in an internal table with DISTINCT option.

Then select vstelle from euiinstln INNER JOIN eanl FOR ALL ENTRIES of your internal table into your result table lt_premises.

Hope, this will help. Matching database indexes for euiinstln-int_ui and eanl-anlage would be fine. I don't have those tables/views on any of our systems.

Regards,

Klaus

Re: Speed up select query

Former Member — Thu, 18 Aug 2011 07:18:42 GMT

If you intend to, or your requirement is - download all 3 tables content, you have to pay the price for such requirement.

Not only it will be too slow, it may also cause memory issues.

You might be running this program in development which might run successfully, but it might give TIME_OUT or memory dump in production.

It would be better to restrict the selection criteria and revise the requirements in development stage only.

For all entries or creating a customized view would not make much difference in my opinion.

If the requirement still holds, you would have to slice up the tables using "up to rows x" and clean up used data - just to avoid memory issues. I dont think performance issue can be solved.

Re: Speed up select query

Former Member — Thu, 18 Aug 2011 07:22:15 GMT

If it is a data cleanup report, it would be better if you make the select-options mandatory, and run the batch jobs with different variants. This is the closest you can get to performance tuning.

Re: Speed up select query

former_member182114 — Mon, 22 Aug 2011 20:06:14 GMT

Hi Jaideep,

Better check the requeriment as wrote before by other poster.

Anyway, if you plan to start from external UI try this one:

IF s_esiid[] IS INITIAL.
* not informed, read everything
  SELECT a~vstelle
  INTO TABLE lt_premises
  FROM eanl AS a
  WHERE anlage IN (SELECT b~anlage FROM euiinstln AS b
                                   INNER JOIN euitrans AS c
                                   ON b~int_ui = c~int_ui
                                WHERE c~ext_ui IN s_esiid)
ELSE.
* informed, start join with euitrans (filtering by ext_ui)
  SELECT DISTINCT a~vstelle
  INTO TABLE lt_premises
  FROM euitrans AS c INNER JOIN euiinstln AS b
                        ON b~int_ui = c~int_ui
                     INNER JOIN eanl AS a
                        ON a~anlage = b~anlage
  WHERE c~ext_ui IN s_esiid.
ENDIF.

Unfortunatelly, I don't have these tables populated on my system, so not sure the results, send results to forum.

Regards, Fernando Da Ros

Re: Speed up select query

Former Member — Fri, 26 Aug 2011 13:19:58 GMT

Hi All,

This requirement is no longer needed as i have come across a different select query.

I was scanning the entire EANL table as I needed to find those premises which have multiple installations. This functionality can be achieved in a single select statement as follows:

SELECT DISTINCT vstelle

FROM eanl

INTO TABLE gt_premises

GROUP BY vstelle

HAVING COUNT( * ) > 1.

Thanks for all your responses!

I am closing this thread now.