topic Re: Select Query with Like statement in Application Development and Automation Discussions

Select Query with Like statement

Former Member — Wed, 06 May 2009 09:40:57 GMT

Hi every one,

My requirement is, user will enter part of the employee name.I need to search the employees whose name contains the word entered by the user.For that I have used the following query.Functionality wise,It is working fine.But its taking more time.Please kindly advise me how can I improve the performance.

concatenate '%' user_input '%' into v_user_inp_pattern .

Select emp_name from <Database table> into table <internal table> where emp_name like v_user_inp_pattern.

Thanks,

Selva

Re: Select Query with Like statement

Former Member — Wed, 06 May 2009 09:56:48 GMT

First get the data from database table into an internal table on the basis of primary key.

Now use loop with where clause to read the required user name.

This method will fetch all the data from the database table still can be faster as in present approach no key is used .

Also, you can contact basis to create a secondary index on the fields you are using.

hope this may help you.

Re: Select Query with Like statement

Former Member — Wed, 06 May 2009 10:12:22 GMT

But the table which I used is the master one.It contains more than 2 Lakhs record.

Re: Select Query with Like statement

Former Member — Wed, 06 May 2009 11:15:22 GMT

Try to use primary key of the database table as follows :

*-- Declare fields as initial.

CONSTANTS: c_vbeln TYPE vbfa-vbeln VALUE IS INITIAL,

c_posnn TYPE vbfa-posnn VALUE IS INITIAL,

c_vbtyp_n TYPE vbfa-vbtyp_n VALUE IS INITIAL.

*-- Use this in select query

SELECT vbelv posnv vbeln posnn vbtyp_n rfmng meins

INTO TABLE gi_vbfa FROM vbfa

WHERE vbelv IN s_vbeln

AND posnv IN s_posnv

AND vbeln GE c_vbeln

AND posnn GE c_posnn

AND vbtyp_n GE c_vbtyp_n.

This will fetch data using primary key and will improve results.

Re: Select Query with Like statement

Former Member — Wed, 06 May 2009 11:22:09 GMT

Hi,

you can do it this way....

RANGES : r_user_input for v_user_inp_pattern.

r_user_input-sign = 'I'.
r_user_input-sign = 'CP'.
concatenate '*' user_input '*' into r_user_input-low.
append r_user_input.

Select emp_name from <Database table> into table <internal table> where emp_name in r_user_input.

This will improve your performance to a lot more extent.

Regards,

Siddarth

Re: Select Query with Like statement

HermannGahm — Wed, 06 May 2009 11:32:26 GMT

Hi Selva,

if that is your only where condition...


Select emp_name from <Database table> into table <internal table> where emp_name like v_user_inp_pattern.

depending on db... and indexes you have to do one of this 4 options:

a big range scan (with client, if the table is client dependent) -> expensive

a full table scan

a full index scan

a fast full index scan

the last one would probably be the fastest... and only possible if you are

running on ORACLE and your index contains emp_name.

If you could change your wild card to:

concatenate user_input '%' into v_user_inp_pattern .

it would be different... and faster solutions would become possible.

Kind regards,

Hermann

However all options have to touch ALL records... (in index blocks and or table blocks)

Re: Select Query with Like statement

Former Member — Wed, 06 May 2009 12:34:22 GMT

> First get the data from database table into an internal table on the basis of primary key.

it is better to think twice before you write such nonsense!

Only the last answer is useful.

Overall, what do you expect?

You should try to offer a search, which uses a wildcard only at the end 'abcd%' is dramatically faster

than '%bcde%'. The second one is a full search of the whole content, there is not much you can do.

Siegfried

Re: Select Query with Like statement

Former Member — Wed, 06 May 2009 13:25:25 GMT

What's the table?

Rob

Re: Select Query with Like statement

Former Member — Fri, 22 Oct 2010 04:45:11 GMT

This message was moderated.

Re: Select Query with Like statement

Former Member — Fri, 22 Oct 2010 14:33:06 GMT

@Rob,

this question is completely out-dated, why do you ask?

Re: Select Query with Like statement

Former Member — Fri, 22 Oct 2010 15:18:25 GMT

Hi Siegfried - did you see the date of my question

The OP had marked this as closed and included a meaningless message like 'resolved' or something. That brought it up to the top of the list. So I deleted that comment, leaving my old one as the last.

Rob