Select on VBRK

[Former Member]

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'.

[Former Member]

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'.

[former_member195698]

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 = ' '.

[valter_oliveira]

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.

[Former Member]

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'.

[Former Member]

Hi Chetan,

Don't use Select * ...

Instead bring the data for the fields that you want.

[Former Member]

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

[Former Member]

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

[venkatasriram_mygapula]

Hi,

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

regards.

sriram.

[Former Member]

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.

[valter_oliveira]

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.

[Former Member]

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

[Former Member]

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..