SELECT QAUERY FINE TUNE or Alternate of LIKE prdicate



IBM's flagship relational database management system

SELECT QAUERY FINE TUNE or Alternate of LIKE prdicate

Postby gauravfrankly » Thu Nov 19, 2015 3:31 pm

Hi All,
I need to fine tune the below query fro better performance, please help.

Select FNAME, MNAME, SURNAME, DOB, ADDRESS, PHONE from INDIVIDUAL_DATA
WHERE DOB = V_DOB
AND (SURNAME = V_SURNAME
OR (SURNAME LIKE '%' || ' ' || V_SURNAME)
OR (SURNAME LIKE V_SURNAME || ' ' || '%')
OR (SURNAME LIKE '%' || ' ' ||
V_SURNAME || ' ' || '%'));

I have an index on DOB and SURNAME.
I have a huge amount of data.
please suggest ways to fine tune it. or any alternate to pattern match using LIKE predicate.
gauravfrankly
 
Posts: 39
Joined: Fri Aug 07, 2015 3:30 pm
Has thanked: 0 time
Been thanked: 0 time

Re: SELECT QAUERY FINE TUNE or Alternate of LIKE prdicate

 

Re: SELECT QAUERY FINE TUNE or Alternate of LIKE prdicate

Postby NicC » Thu Nov 19, 2015 7:38 pm

Please use the code tags to present your query and make it more readable.
Perhaps an index on:
SURNAME' 'V_SURNAME
The problem I have is that people can explain things quickly but I can only comprehend slowly.
Regards
Nic
NicC
Global moderator
 
Posts: 2702
Joined: Sun Jul 04, 2010 12:13 am
Location: Pushing up the daisys (almost)
Has thanked: 4 times
Been thanked: 105 times

Re: SELECT QAUERY FINE TUNE or Alternate of LIKE prdicate

Postby gauravfrankly » Fri Nov 20, 2015 10:33 am

Pardon Nicc, not getting your point.
V_Surname is the variable which will have the surname passed to this query.
gauravfrankly
 
Posts: 39
Joined: Fri Aug 07, 2015 3:30 pm
Has thanked: 0 time
Been thanked: 0 time

Re: SELECT QAUERY FINE TUNE or Alternate of LIKE prdicate

Postby NicC » Fri Nov 20, 2015 5:04 pm

If V_SURNAME is a host variable then it should be pre-pended with ':'. Nowhere in your post is that done. If you want help then post accurately instead of wasting everyone's time. As a programmer you need to take care of the details ALL the time - not just when coding your program.
The problem I have is that people can explain things quickly but I can only comprehend slowly.
Regards
Nic
NicC
Global moderator
 
Posts: 2702
Joined: Sun Jul 04, 2010 12:13 am
Location: Pushing up the daisys (almost)
Has thanked: 4 times
Been thanked: 105 times

Re: SELECT QAUERY FINE TUNE or Alternate of LIKE prdicate

Postby gauravfrankly » Fri Nov 20, 2015 6:10 pm

Nicc, I didn't said these are host variables. this code snippet is the part of an stored procedure. and V_SURNAME is the stored procedure local variable.
gauravfrankly
 
Posts: 39
Joined: Fri Aug 07, 2015 3:30 pm
Has thanked: 0 time
Been thanked: 0 time

Re: SELECT QAUERY FINE TUNE or Alternate of LIKE prdicate

Postby NicC » Fri Nov 20, 2015 8:46 pm

You also did not say that it was a stored procedure. How are we supposed to know. Please give full details next time.
The problem I have is that people can explain things quickly but I can only comprehend slowly.
Regards
Nic
NicC
Global moderator
 
Posts: 2702
Joined: Sun Jul 04, 2010 12:13 am
Location: Pushing up the daisys (almost)
Has thanked: 4 times
Been thanked: 105 times

Re: SELECT QAUERY FINE TUNE or Alternate of LIKE prdicate

Postby prino » Sun Nov 22, 2015 2:25 pm

SURNAME LIKE '%' || ' ' || V_SURNAME)

will never be able to do an index scan, only a full table scan. In other words, your design is brown, the same colour as the stuff that hits the fan!
Robert AH Prins
robert.ah.prins @ the.17+Gb.Google thingy
User avatar
prino
 
Posts: 537
Joined: Wed Mar 11, 2009 12:22 am
Location: Oostende, Belgium
Has thanked: 3 times
Been thanked: 21 times


Return to DB2

 


  • Related topics
    Replies
    Views
    Last post