Query Performance and Result Set Query/Sub-Query

[Former Member]

Hi,

I have an infoset with as many as <b>15 joins</b> with different ODS and Master Data..<b>The ODS are quite huge with 20 million, 160 million records)...</b>Its taking a lot of time even for a few days and need to get atleast 3 months data in a reasonable amount of time....

Could anyone please tell me whether <b>Sub-Query or Result Set Query have to be written against the same InfoProvider</b> (Cube, Infoset, etc)...or they can be used in queries

on different infoprovider...

Please suggest...Thanks in Advance.

Regards

Anil

[former_member188975]

Hi Anil,

As far as your question on the ResultSet query is concerned, it is possible to have the PreQuery and ResultSet query on different infoproviders. They do not have to be on the same infoprovider.

Hope this helps...

[Former Member]

HI Bhanu,

Thanks for your reply. Does SubQuery/Result Set query speed up performance of your Query. These queries are accessing the Indexes and I have played quite with the Query to change its access path but with little improvments.

Regards

Anil

[former_member188975]

Hi Anil,

I am not sure about what your requirement is, but have you thought about using pre-calculates Value Sets. This will help you to pre calculate the PreQuery results for the char you are interested in and then use as a variable in your main query. You can do this in RSA1 > Reporting Agent > Value Set. For more info, check out the following:

http://help.sap.com/saphelp_nw04/helpdata/en/84/00783b3af21f5ee10000000a11402f/content.htm

Hope this helps...

[Former Member]

Hi Bhanu,

Needed some help defining the Precalculated Value Set as I wasnt succesful....

Please suggest answers if possible for the questions below

1) Can I use Filter Criteria for restricting the value set for the characteristic when I Define a Query on an ODS. When i tried this it gave me errors as below ..

"System error in program CL_RSR_REQUEST and form EXECUTE_VTABLE:COB_PRO_GET_ALWAYS.... Error when filling value set DELIVERY..

Job cancelled after system exception ERROR_MESSAGE"

2) Can I create a create a Value Set predefined on an Infoset - Not Succesful as Infoset have names such as "InfosetName_F000232" and cannot find the characteristic in Paramter Tab for Value Set in Reprting Agent.

3) How does the precalculated value Set variable help if I am not using any Filtering Criteria and is storing the List of all values for the Variable from the ODS which consists of 20 millio records.

Thanks for your help.

Kind Regards

Anil