topic Re: Need a Best SELECT Statement. in Application Development and Automation Discussions

Need a Best SELECT Statement.

Former Member — Tue, 17 Feb 2009 02:04:47 GMT

Hi Experts,

I have a situation like this,

My client got database since from 1998. So his records crossed 20 Million in each table.

Now my situation is like this,

I have Budat(posting date) & matnr(Material) as my Selection screen.

I need to show the FG (Movement type 601) with billing documnet(VBRP).

The link is MKPF-XBLNR = VBRP-VGBEL.

but when I try to select the records from MKPF for a perticular month, because of overload(20 Million records) its taking more time(say 25-40 Sec).

But when I try get data from VBRP with help of XBLNR, Its "Time Exceeded" Runtime error,

I Even tried to concatinate some date, like let say my input is march, then I ristrict VBRP by FEB to APRIL for that particular year. Still not working.

Is there anyway to do this Job in SAP.

My Client's PRD is Almost 30Million Records in Each Table.

Version 4.7 EE..

Please Help me out.

Thanks & Regards,

Dileep .C

Re: Need a Best SELECT Statement.

Sougata — Tue, 17 Feb 2009 02:18:25 GMT

I would create Indexes on both MKPF & VBRP with the linked fields that my program is trying to SELECT with. Make sure they are Non-unique index with "index on all database systems" when you create them via SE11.

Hope this helps.

Cheers,

Sougata.

So I think, your new index on MKPF should be created with fields BUDAT & XBLNR and new index on VBRP should be created with fields VGBEL & VGPOS.

Edited by: Sougata Chatterjee on Feb 17, 2009 1:21 PM

Re: Need a Best SELECT Statement.

Former Member — Tue, 17 Feb 2009 02:23:12 GMT

Hi Sougata,

Please can you explain a bit more clear.

how to make indexes for both VBRP & MKPF. I have no idea.

Thanks & regards,

Dileep .C

Re: Need a Best SELECT Statement.

Sougata — Tue, 17 Feb 2009 02:31:05 GMT

Hi Dileep,

Its easy and its all [here|http://help.sap.com/saphelp_47x200/helpdata/en/cf/21eb20446011d189700000e8322d00/frameset.htm].

You'll find "Creating Secondary Indexes" under "Creating Tables" on the same page as per the link above.

Hope this helps.

Sougata.

Re: Need a Best SELECT Statement.

Former Member — Tue, 17 Feb 2009 03:15:54 GMT

No need to create a secondary index. Use the material/billing document secondary index table.

Rob

Re: Need a Best SELECT Statement.

Former Member — Tue, 17 Feb 2009 03:15:57 GMT

Hi Sougata,

Appreciate your effort in Contibution.

My Problem is not yet solved,

I dont have Authorization to create Indexes.

And i am trying on that.

Thanks Sougata.

Dileep .C

Re: Need a Best SELECT Statement.

Former Member — Tue, 17 Feb 2009 03:18:21 GMT

Hi RoB,

need to ask you some thing.

My Points in forums vanished yesterday,

and i posted a new thread asking about thta,

when i see in th emorning my question is also vanished.

sorry to ask this here,.

Thnks

Dileep. C

Re: Need a Best SELECT Statement.

Former Member — Tue, 17 Feb 2009 03:19:12 GMT

Hi ,

try to get all data from MKPF in internal table and use parallel cursor to reduce the data from other internal table(also get data from VBRP into one internal table on some condition), it will be more faster. always try to reduce data base hit. check your code in SE30 and compare your older code you will get the differenece.

Edited by: abhishek prakash on Feb 17, 2009 4:24 AM

Edited by: abhishek prakash on Feb 17, 2009 4:27 AM

Re: Need a Best SELECT Statement.

Former Member — Tue, 17 Feb 2009 03:22:41 GMT

As some friends told, Creating secondary index is advisible. Also try to restrict the data fetch from database using SQL stmts.,

Refer PACKAGE SIZE

Thanks & regards

Re: Need a Best SELECT Statement.

Former Member — Tue, 17 Feb 2009 03:23:32 GMT

Did you get an e-mail explaining what happenned?

Rob

Re: Need a Best SELECT Statement.

Former Member — Tue, 17 Feb 2009 03:30:10 GMT

Hi Rob,

Sorry to say this again, Unfortunately I clicked on watches on ABAP General by without knowing it.

when I opened see the mail, I have seen around 1000 mails in my inbox.

So I was unable to catch the right mail, because of the similar subjects in my inbox.

I dont know what happened. I am not bothered about points, It can be taken or given easily.

Actually My concern is if i could know the reason clarly why it was terminated, I can be more cautious to not repeat the mistakes if I had made.

But it was very embarasimg to me, After a long gap I started to contribute and started solutions to queries as far as I can,

in the next hour all my points dissapered.

Thanks for reading all this.

Dileep ,C

Re: Need a Best SELECT Statement.

Former Member — Tue, 17 Feb 2009 03:37:54 GMT

Hi Rob,

Also when I was reading your Blogs regarding the present Thread, I feel very happy in one case coz.

I recently update a code in the code gallery stating that "JOINS are BETTER than FOR ALL ENTRIES" in some cases..

But unfortunately i was unable to explain as detail as you in your "Performance - what will kill you and what will leave you with only a flesh wound"..

But I feel very happy to see some my expectations went true in some angles.

Thanks & regards,

Dileep .C

Re: Need a Best SELECT Statement.

matt — Tue, 17 Feb 2009 05:06:13 GMT

I've sent you an email with an explanation - and your points have been partially restored.

matt

Edited by: Matt on Feb 17, 2009 6:20 AM

Re: Need a Best SELECT Statement.

Former Member — Tue, 17 Feb 2009 06:23:02 GMT

Hi Matt,

I have checked my mails keenly,,

I realized the mistakes where I did,

I agree with what you did.

but I really want to contribute to the SCN. but some (Most of them) really dont do a better search and will not reply after we give a solution to them and they even dont know properly to close the thread.

simply regestering and asking some queries and leaving like that.

We cant even know did their problem solved or left away, and how we have contributed to them, Is that was helpful to them are not, we are unable to know. This made me little frustated and just did like that.

And I am sorry to do like that.Here after I will take care of my Posts myself.

As I already read some blogs yesterday, I already came to know the real meaning of contribution.

I really not intrested in points. But some how wanted to know the reason.

Thanks for restoring my points MATT.

Thanks & Regards,

Dileep .C

Re: Need a Best SELECT Statement.

matt — Tue, 17 Feb 2009 10:30:13 GMT

I like your response and attitude - it's very refreshing. If you encounter posts that are easily searchable, or you find that there are people who aren't closing their questions and awarding points, then use the abuse button. That will be a great help to us.

Regards

matt

Re: Need a Best SELECT Statement.

Former Member — Tue, 17 Feb 2009 14:12:25 GMT

Back to your original question. The most common bit of advice you'll see in this particular forum, when you have a performance problem with a SELECT statement is to create a new secondary index.

This is generally the wrong approach to take. You have a programming problem and it's best to look for a programming solution. Creating a new index for a single SELECT statement is inadvisable because:

1) the new index will have to be maintained every time the table is updated. This will add a small amount of overhead to these updates.

2) the new index will take up some of the tablespace assigned to the table.

3) there is no guarantee that the index will always be used by the SELECT.

4) eventually, the program you develop that uses this index may become obsolete, but no one will remember to get rid of the index.

5) it's lazy.

Rob

Re: Need a Best SELECT Statement.

Former Member — Wed, 18 Feb 2009 06:43:59 GMT

Hi Rob,

Then what can be a solution to my Query.

In detail, I explain my scenario again.

I have DATE & Material as my selctions.

I need my report as FG materials and its BOM rw materials used & its ratios and Quantities,

then the material movements in th epalants, 101-102..... and

PGI FG materials & its quantities,... 601-602... and

need billing document No. for 601 & 602(PGI) Document.

I know all the relations to tables and its fields to retreive all the data... but I Failed..

Because of Huge database(20 Million records in each table) in system from 1998.

I have no right to say my company to change database nor I cannot Quit because, I need to leave a mark my self that I missed this Object with out fulfilling my Role.

Tables Used

MKPF, MSEG (for movement & PGi details)

MAST, STKO, STPO,(BOM Details)

VBRK, VBFA, LIKP (Delivery & Billing details)

I did many reports on these tables. Invoice Lists & Proof of Deliveries.......many....

But I failed here... I am Helpless here at this point.

Please Let me know I could do in anyother way.

Thanks & Regards.

Dileep .C

Re: Need a Best SELECT Statement.

Former Member — Wed, 18 Feb 2009 14:00:32 GMT

since you are SELECTing by material, see if secondary index table VRPMA (SD Index: Billing Items per Material) helps. (It may have been suggested earlier.)

Rob

Re: Need a Best SELECT Statement.

Former Member — Fri, 27 Feb 2009 05:58:48 GMT

Hi Rob,

Sorry for a late responce, And thanks for your solution.

To clearly say that was a clear CLUE fro me to close my Issue for the time.

BUT that was not complete solution, Since by VRPMA I am getting Billing document no in date range by material, thats true.

Lets say I got 20 Billing Doc's in VRPMA.

And I have 20 movement types in MKPF.

How can I relate them for each Billing document has a seperate MBLNR... I strucked up here again.

But as a temporary patch, which I am expecting it can be a permanent patch,

I compared diractly with the Material Quantity, as it should show the Invoice amount properly,

Even if it is not right MBLNR & VBELN, but the Quantity & Value are same... Where I seen no trouble with that.

Thanks for your Knowledge sharing Once again.

And I have many several issues like this, which I will post back again with a Proper TITLE...

Thanks & Regards,

Dileep .C

Re: Need a Best SELECT Statement.

Former Member — Fri, 27 Feb 2009 14:17:49 GMT

Not sure I understand the problem now, but you may be able to use the Sales document flow table (VBFA) to determine the relationships.

Rob