select query is slowly

[former_member210823]

hello

my select query is very slowly, how to increse speed of it?

select objnr UDATE

        from jcds

        into table

        where  INACT = ' ' and  STAT = 'I0009' and objnr like srch_str  and

             UDATE in budat

         %_HINTS ORACLE 'INDEX("JCDS" "JCD~ZDA")'.

and my index is same as picture :

[former_member210823]

Hi Masoumeh,

1. Are you sure that the index name that is being used is correct? Have you missed an 'S' in the index name JCDS~ZDA

select objnr UDATE

        from jcds

        into table

        where  INACT = ' ' and  STAT = 'I0009' and objnr like srch_str  and

             UDATE in budat

         %_HINTS ORACLE 'INDEX("JCDS" "JCD~ZDA")'.  "JCDS~ZDA

If the index name is not correct, there will be no syntax error, but the purpose of index will not be served.

2. Its good practice, though it may not make much difference to the performance , to use the fields in the query in the same order as is used in the index.

[RaymondGiuseppi]

• Did you execute a SQL trace (ST05) to insure that the hint is actually taken into account?
• You forgot client in the index keys : add MANDT
  
  

Regards,

Raymond

[Former Member]

Actually I think it is the OBJNR search that is causing the pain, can you replace this with an "=" instead of a "like" this table will be very large, you really need to find the OBJNR before running this.

Regards

Arden

[Former Member]

Dear Masoumeh,

Try the following code.

select objnr UDATE

        from jcds

        into table

        where  objnr like srch_str  and

                      STAT = 'I0009' and

                     INACT = ' ' and 

                    UDATE in budat

         %_HINTS ORACLE 'INDEX("JCDS" "JCD~ZDA")'.

Also as Arden said, try using = instead of like.....if it contains a Pattern then you can use CP instead of EQ.

Regards

Shaik

[Former Member]

Hi Masoumeh,

Select query on table JCDS will be very slow unless you pass the OBJNR.

Therefore you first get the OBJNR and STAT from table JEST by passing it to STAT "I0009" and INACT = ' ' then select entries from JCDS for all entries of JEST by passing OBJNR, STAT and UDATE.

JEST is kind of header table for JCDS therefore you first select the data from JEST and then from JCDS.

I am sure this will improve the performance.

Regards

Chudamani Gavel

[tapomay_sanyal]

Hi masoumeh,

Your select query is as below:

select objnr UDATE

        from jcds

        into table

        where  INACT = ' ' and  STAT = 'I0009' and objnr like srch_str  and

             UDATE in budat

         %_HINTS ORACLE 'INDEX("JCDS" "JCD~ZDA")'.

There are few thinks which needs to be considered :

a) what you are passing in srch_str?  Instead of using jcds with search string it is much better to gather the exact object numbers and then pass them to JCDS to gather status transitions.

e.g: You need status changes of   operations in a single day then get the operations changed from afvc or other tables first to get the object numbers and then pass them to jcds.Exact object numbers will speed up process.

b)TRACE your query in ST05, even with indexes this table normally contains larga data sets thus based on your requirement it would be better to tweak the query .

[Former Member]

Hi Masoumeh,

1. Are you sure that the index name that is being used is correct? Have you missed an 'S' in the index name JCDS~ZDA

select objnr UDATE

        from jcds

        into table

        where  INACT = ' ' and  STAT = 'I0009' and objnr like srch_str  and

             UDATE in budat

         %_HINTS ORACLE 'INDEX("JCDS" "JCD~ZDA")'.  "JCDS~ZDA

If the index name is not correct, there will be no syntax error, but the purpose of index will not be served.

2. Its good practice, though it may not make much difference to the performance , to use the fields in the query in the same order as is used in the index.

[Former Member]

You are using a custom index for this SELECT. Did you create an index just to speed this SELECT? If so, I would get rid of it and let the SELECT run slowly in the background.

But if you insist on keeping the custom index, analyze how the WHERE clause will be set up and modify the order of the fields in the index so that the more selective fields are at the beginning.

For example, if you will normally be running this for a single day or a small range of dates, make UDATE the first field in the index (after MANDT).

Rob