topic Re: Select on VBRK in Application Development and Automation Discussions

Select on VBRK

Former Member — Tue, 08 Jul 2008 10:28:44 GMT

Hi Xperts,

very simple, following query takes long time.......pls suggest alternative.

SELECT * INTO TABLE IT_VBRK FROM VBRK

WHERE ( FKDAT >= FIRST_DATE AND FKDAT <= LAST_DATE )

and VBTYP = 'M' AND FKSTO <> 'X'.

Re: Select on VBRK

former_member195698 — Tue, 08 Jul 2008 10:34:23 GMT

One solution if this query is used frequently

Index on MANDT, FKDAT, VBTYP and FKSTO

and change in the query to



SELECT * INTO TABLE IT_VBRK FROM VBRK
WHERE ( FKDAT >= FIRST_DATE AND FKDAT <= LAST_DATE )
and VBTYP = 'M' AND FKSTO = ' '.

Re: Select on VBRK

Former Member — Tue, 08 Jul 2008 10:35:15 GMT

hi,

do this way ..


ranges r_fkdat for vbrk-fkdat.

initialization.
r_fkdat-low = FIRST_DATE .
r_fkdat-high = LAST_DATE .
r_fkdat-option = 'BT'.
r_fkdat-sign = 'I'.
append r_fkdat.

 

SELECT * INTO TABLE IT_VBRK FROM VBRK
WHERE fkdat in r_fkdat
and VBTYP = 'M' AND FKSTO <> 'X'.

Re: Select on VBRK

Former Member — Tue, 08 Jul 2008 10:36:48 GMT

Hi Chetan,

Don't use Select * ...

Instead bring the data for the fields that you want.

Re: Select on VBRK

Former Member — Tue, 08 Jul 2008 10:39:59 GMT

HI,

~You are not using any Index in your select. this is one main reason. If required u can add one.

~wat is the volume of data for the slect given above..

~wat will be the volume of data expected if you remove the condition

fksto <> 'X'

from the WHERE condition.

By removing the <> condition, if you see not-a-significant change then remove it.

~ Any possibilty to add more(relelvant) fields to the WHERE clause??

regards,

madhu

Re: Select on VBRK

valter_oliveira — Tue, 08 Jul 2008 10:45:28 GMT

Hello.

The ideia of creating a range to fkdat looks fine and use fksto EQ space (instead of NE 'X') looks fine too.

However, you may need to create an index if that program will be used too often. But you do so, create with minimum necessary fields. Like MANDT-FKDAT is enough.

Regards.

Valter Olieira.

Re: Select on VBRK

Former Member — Tue, 08 Jul 2008 11:13:53 GMT

Hi joshi,

In general if you want to reduce the time for your query and you dont require all the field in the table than try not to use SELECT * instead specfic the fields that is required .

Regards,

Pritam.

Edited by: Pritam Ghosh on Jul 8, 2008 1:14 PM

Re: Select on VBRK

venkatasriram_mygapula — Wed, 09 Jul 2008 01:49:28 GMT

Hi,

Best on is dont mention select * better to mention required fields instead of select *.

regards.

sriram.

Re: Select on VBRK

Former Member — Wed, 09 Jul 2008 04:27:12 GMT

Dear all,

Heartly thanks to everybody for their contribution in soultioning.

I tried with everything you all have suggested............sorry to say but nothing works.

This single query takes over all 80-90% of processing time of whole program.

It fetches around 12299 entries from VBRK table.

Hence, I am planning to write another program which fetch data and keep in dataset. My actual (main ) program will get data from dataset and do further processing.

How's this alternative, is it recommended.............???

Pls suggest.

Thanks & Regards,

Chetan Joshi.

Re: Select on VBRK

valter_oliveira — Wed, 09 Jul 2008 09:15:03 GMT

Hi again.

As I said in previous post, all the advices about ranges, replace NE 'X' and avoid Select * where ok, but in your case looked like you should need an index. Did you create the index MANDT-FKDAT? And after that did you run optimizer statistics?

In my opinion that alternative is not good, because that program that fetches data won't be able to have your table with the data always up to date ... so, try the index, and avoid select * and NE 'X' ... all together.

Regards.

Valter Oliveira.

Re: Select on VBRK

Former Member — Thu, 10 Jul 2008 06:50:38 GMT

Thanks to everybody for contribution.

But Surprisingly none were improved performance of select query. I have tried with everything.

Finally, I am going with the last option which I have described in my reply.

Dear Valter

since this VBRK data is belongs only previous year of current year, there is not fear of not having latest updates of DB tables....

Thanks Again.....

Chetan Joshi

Re: Select on VBRK

Former Member — Thu, 10 Jul 2008 08:16:59 GMT

Chetan - depending on your functional requirement maybe investigate a Select using period POPER or year GJAHR. You may be then able to filter FKDAT by deleting from the internal table. You would have to experiment this first though..