topic Re: select query is slowly in Application Development and Automation Discussions

select query is slowly

former_member210823 — Wed, 16 Dec 2015 08:38:56 GMT

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 :

Re: select query is slowly

RaymondGiuseppi — Wed, 16 Dec 2015 08:52:40 GMT

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

Re: select query is slowly

Former Member — Wed, 16 Dec 2015 09:01:24 GMT

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

Re: select query is slowly

Former Member — Wed, 16 Dec 2015 09:16:51 GMT

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

Re: select query is slowly

Former Member — Wed, 16 Dec 2015 10:39:48 GMT

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

Re: select query is slowly

tapomay_sanyal — Wed, 16 Dec 2015 12:28:07 GMT

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 .

Re: select query is slowly

Former Member — Wed, 16 Dec 2015 12:55:39 GMT

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.

Re: select query is slowly

Former Member — Wed, 16 Dec 2015 14:52:46 GMT

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