469,962 Members | 2,375 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,962 developers. It's quick & easy.

Searching View with LIKE predicate

Hello Friends;
I am asking this as a database administrator, perhaps more on behalf of
the SQL Programmer.

We have a search on a view that uses a street number and a partial
street name in a LIKE clause. It is a very slow, inefficient search,
pretty much unusable, and disruptive when we do use it. I believe the
view has about 200,000 records. I will post the view code and the
select statement below, I hope that is useful, but I believe I may be
asking more "What is a good method/process for doing searches with
partial data available?, is the a proven widely used technique?"
Please tell me what to add to make this a more complete inquiry.
Thanks. The application is websphere, the database is IBM DB2 UDB V8.2
for Linux Intel 32 bit, at fixpak 8. Following are the view and the
select code incase it shows something glaring, Thanks sincerely
Claude

This is the VIEW definition:

create view CCDB.BASIC_CLIENT_V as select CCDB.CLIENT.ID as
CLIENT_ID, CCDB.CLIENT.CLIENT_TYPE_ID as TYPE_ID,
CCDB.CLIENT_NAME.ID as NAME_ID, CCDB.CLIENT_NAME.NAME_TYPE_ID as
NAME_TYPE_ID, CCDB.NAME_LIST.FIRST_NAME as FIRST_NAME,
CCDB.NAME_LIST.MID_NAME as MID_NAME, CCDB.NAME_LIST.LAST_NAME as
LAST_NAME, CCDB.NAME_LIST.FULL_NAME as FULL_NAME,
CCDB.NAME_LIST.GIVEN_NAMES as GIVEN_NAMES,
CCDB.CLIENT_EVENT.OCCURRED_ON AS OCCURRED_ON, CCDB.ADDRESS.STREET_NUM
AS STREET_NUM, CCDB.ADDRESS.STREET_NAME AS STREET_NAME, '' AS NAME1,
'' AS NAME2, CCDB.CLIENT_IDENTIFIER.IDENTIFIER AS CLIENT_IDENTIFIER,
CCDB.CLIENT_IDENTIFIER.IDEN_TYPE_ID AS IDENTIFIER_TYPE_DESCRIPTION,
CCDB.CLIENT_IDENTIFIER.IDEN_TYPE_ID AS IDENTIFIER_TYPE_CODE,
CCDB.CLIENT_NAME.END_DATE AS NAME_END_DATE,
CCDB.PHONE_NUMBER.PHONE_NUMBER AS PHONE_NUMBER FROM
CCDB.CLIENT_PHONE RIGHT OUTER JOIN CCDB.CLIENT ON
CCDB.CLIENT_PHONE.CLIENT_ID = CCDB.CLIENT.ID LEFT JOIN
CCDB.PHONE_NUMBER ON CCDB.CLIENT_PHONE.PHONE_ID = CCDB.PHONE_NUMBER.ID,
CCDB.CLIENT_EVENT,
CCDB.CLIENT_IDENTIFIER, CCDB.CLIENT_TYPE,
CCDB.CLIENT_NAME, CCDB.NAME_LIST,
CCDB.CLIENT_ADDRESS, CCDB.ADDRESS WHERE
CCDB.CLIENT_TYPE.ID = CCDB.CLIENT.CLIENT_TYPE_ID AND ( CCDB.CLIENT.ID
= CCDB.CLIENT_EVENT.CLIENT_ID ) and ( CCDB.CLIENT.ID =
CCDB.CLIENT_IDENTIFIER.CLIENT_ID ) and ( CCDB.CLIENT.ID =
CCDB.CLIENT_NAME.CLIENT_ID ) and ( CCDB.CLIENT.ID =
CCDB.CLIENT_ADDRESS.CLIENT_ID ) AND 2 = CCDB.CLIENT_TYPE.ID AND 1 =
CCDB.CLIENT_ADDRESS.ADDRESS_TYPE_ID AND 1 =
CCDB.CLIENT_IDENTIFIER.IDEN_TYPE_ID AND 1 =
CCDB.CLIENT_EVENT.EVENT_TYPE_ID AND ( CCDB.ADDRESS.ID =
CCDB.CLIENT_ADDRESS.ADDRESS_ID ) AND (
CCDB.CLIENT_ADDRESS.END_DATE > CURRENT DATE OR
CCDB.CLIENT_ADDRESS.END_DATE IS NULL) AND (
CCDB.CLIENT_EVENT.END_DATE > CURRENT DATE OR CCDB.CLIENT_EVENT.END_DATE
IS NULL) AND ( CCDB.CLIENT_IDENTIFIER.END_DATE > CURRENT DATE OR
CCDB.CLIENT_IDENTIFIER.END_DATE IS NULL) and (
CCDB.CLIENT_NAME.NAME_ID = CCDB.NAME_LIST.ID ) AND (
CCDB.CLIENT_NAME.NAME_TYPE_ID in (1,2)) AND (
CCDB.CLIENT_NAME.END_DATE > CURRENT DATE OR CCDB.CLIENT_NAME.END_DATE
IS NULL)
Here is the where portion of the select statement that is paining us,
it is a SELECT DISTINCT bunch of fields from the view
WHERE BASIC_CLIENT_V.STREET_NUM='15' AND BASIC_CLIENT_V.STREET_NAME
LIKE 'MACKINNON%' ORDER BY UPPER(BASIC_CLIENT_V.LAST_NAME) ASC,
UPPER(BASIC_CLIENT_V.FIRST_NAME) ASC, UPPER(BASIC_CLIENT_V.MID_NAME) ASC

Jun 27 '06 #1
2 1366
cj****@gov.pe.ca wrote:
Hello Friends;
I am asking this as a database administrator, perhaps more on behalf of
the SQL Programmer.

We have a search on a view that uses a street number and a partial
street name in a LIKE clause. It is a very slow, inefficient search,
pretty much unusable, and disruptive when we do use it. I believe the
view has about 200,000 records. I will post the view code and the
select statement below, I hope that is useful, but I believe I may be
asking more "What is a good method/process for doing searches with
partial data available?, is the a proven widely used technique?"
Please tell me what to add to make this a more complete inquiry.
Thanks. The application is websphere, the database is IBM DB2 UDB V8.2
for Linux Intel 32 bit, at fixpak 8. Following are the view and the
select code incase it shows something glaring, Thanks sincerely
Claude

This is the VIEW definition:

create view CCDB.BASIC_CLIENT_V as select CCDB.CLIENT.ID as
CLIENT_ID, CCDB.CLIENT.CLIENT_TYPE_ID as TYPE_ID,
CCDB.CLIENT_NAME.ID as NAME_ID, CCDB.CLIENT_NAME.NAME_TYPE_ID as
NAME_TYPE_ID, CCDB.NAME_LIST.FIRST_NAME as FIRST_NAME,
CCDB.NAME_LIST.MID_NAME as MID_NAME, CCDB.NAME_LIST.LAST_NAME as
LAST_NAME, CCDB.NAME_LIST.FULL_NAME as FULL_NAME,
CCDB.NAME_LIST.GIVEN_NAMES as GIVEN_NAMES,
CCDB.CLIENT_EVENT.OCCURRED_ON AS OCCURRED_ON, CCDB.ADDRESS.STREET_NUM
AS STREET_NUM, CCDB.ADDRESS.STREET_NAME AS STREET_NAME, '' AS NAME1,
'' AS NAME2, CCDB.CLIENT_IDENTIFIER.IDENTIFIER AS CLIENT_IDENTIFIER,
CCDB.CLIENT_IDENTIFIER.IDEN_TYPE_ID AS IDENTIFIER_TYPE_DESCRIPTION,
CCDB.CLIENT_IDENTIFIER.IDEN_TYPE_ID AS IDENTIFIER_TYPE_CODE,
CCDB.CLIENT_NAME.END_DATE AS NAME_END_DATE,
CCDB.PHONE_NUMBER.PHONE_NUMBER AS PHONE_NUMBER FROM
CCDB.CLIENT_PHONE RIGHT OUTER JOIN CCDB.CLIENT ON
CCDB.CLIENT_PHONE.CLIENT_ID = CCDB.CLIENT.ID LEFT JOIN
CCDB.PHONE_NUMBER ON CCDB.CLIENT_PHONE.PHONE_ID = CCDB.PHONE_NUMBER.ID,
CCDB.CLIENT_EVENT,
CCDB.CLIENT_IDENTIFIER, CCDB.CLIENT_TYPE,
CCDB.CLIENT_NAME, CCDB.NAME_LIST,
CCDB.CLIENT_ADDRESS, CCDB.ADDRESS WHERE
CCDB.CLIENT_TYPE.ID = CCDB.CLIENT.CLIENT_TYPE_ID AND ( CCDB.CLIENT.ID
= CCDB.CLIENT_EVENT.CLIENT_ID ) and ( CCDB.CLIENT.ID =
CCDB.CLIENT_IDENTIFIER.CLIENT_ID ) and ( CCDB.CLIENT.ID =
CCDB.CLIENT_NAME.CLIENT_ID ) and ( CCDB.CLIENT.ID =
CCDB.CLIENT_ADDRESS.CLIENT_ID ) AND 2 = CCDB.CLIENT_TYPE.ID AND 1 =
CCDB.CLIENT_ADDRESS.ADDRESS_TYPE_ID AND 1 =
CCDB.CLIENT_IDENTIFIER.IDEN_TYPE_ID AND 1 =
CCDB.CLIENT_EVENT.EVENT_TYPE_ID AND ( CCDB.ADDRESS.ID =
CCDB.CLIENT_ADDRESS.ADDRESS_ID ) AND (
CCDB.CLIENT_ADDRESS.END_DATE > CURRENT DATE OR
CCDB.CLIENT_ADDRESS.END_DATE IS NULL) AND (
CCDB.CLIENT_EVENT.END_DATE > CURRENT DATE OR CCDB.CLIENT_EVENT.END_DATE
IS NULL) AND ( CCDB.CLIENT_IDENTIFIER.END_DATE > CURRENT DATE OR
CCDB.CLIENT_IDENTIFIER.END_DATE IS NULL) and (
CCDB.CLIENT_NAME.NAME_ID = CCDB.NAME_LIST.ID ) AND (
CCDB.CLIENT_NAME.NAME_TYPE_ID in (1,2)) AND (
CCDB.CLIENT_NAME.END_DATE > CURRENT DATE OR CCDB.CLIENT_NAME.END_DATE
IS NULL)
Here is the where portion of the select statement that is paining us,
it is a SELECT DISTINCT bunch of fields from the view
WHERE BASIC_CLIENT_V.STREET_NUM='15' AND BASIC_CLIENT_V.STREET_NAME
LIKE 'MACKINNON%' ORDER BY UPPER(BASIC_CLIENT_V.LAST_NAME) ASC,
UPPER(BASIC_CLIENT_V.FIRST_NAME) ASC, UPPER(BASIC_CLIENT_V.MID_NAME) ASC

There is nothing inherently bad about the sort of LIKE processing you
are doing. DB2 will add a BETWEEN predicate to limit the search.
How does your explain (db2exfmt) look like?

Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

IOD Conference
http://www.ibm.com/software/data/ond...ness/conf2006/
Jun 27 '06 #2
Serge;
Thank you for the rapid response, and the re-assurance that this is an
ok way to code. A few minutes ago we found that an index on address
table had been dropped somehow, we put it back and now our searches are
like lightening. Learning the tools as I go, getting better every day.
I appreciate your input

Claude

Serge Rielau wrote:
cj****@gov.pe.ca wrote:
Hello Friends;
I am asking this as a database administrator, perhaps more on behalf of
the SQL Programmer.

We have a search on a view that uses a street number and a partial
street name in a LIKE clause. It is a very slow, inefficient search,
pretty much unusable, and disruptive when we do use it. I believe the
view has about 200,000 records. I will post the view code and the
select statement below, I hope that is useful, but I believe I may be
asking more "What is a good method/process for doing searches with
partial data available?, is the a proven widely used technique?"
Please tell me what to add to make this a more complete inquiry.
Thanks. The application is websphere, the database is IBM DB2 UDB V8.2
for Linux Intel 32 bit, at fixpak 8. Following are the view and the
select code incase it shows something glaring, Thanks sincerely
Claude

This is the VIEW definition:

create view CCDB.BASIC_CLIENT_V as select CCDB.CLIENT.ID as
CLIENT_ID, CCDB.CLIENT.CLIENT_TYPE_ID as TYPE_ID,
CCDB.CLIENT_NAME.ID as NAME_ID, CCDB.CLIENT_NAME.NAME_TYPE_ID as
NAME_TYPE_ID, CCDB.NAME_LIST.FIRST_NAME as FIRST_NAME,
CCDB.NAME_LIST.MID_NAME as MID_NAME, CCDB.NAME_LIST.LAST_NAME as
LAST_NAME, CCDB.NAME_LIST.FULL_NAME as FULL_NAME,
CCDB.NAME_LIST.GIVEN_NAMES as GIVEN_NAMES,
CCDB.CLIENT_EVENT.OCCURRED_ON AS OCCURRED_ON, CCDB.ADDRESS.STREET_NUM
AS STREET_NUM, CCDB.ADDRESS.STREET_NAME AS STREET_NAME, '' AS NAME1,
'' AS NAME2, CCDB.CLIENT_IDENTIFIER.IDENTIFIER AS CLIENT_IDENTIFIER,
CCDB.CLIENT_IDENTIFIER.IDEN_TYPE_ID AS IDENTIFIER_TYPE_DESCRIPTION,
CCDB.CLIENT_IDENTIFIER.IDEN_TYPE_ID AS IDENTIFIER_TYPE_CODE,
CCDB.CLIENT_NAME.END_DATE AS NAME_END_DATE,
CCDB.PHONE_NUMBER.PHONE_NUMBER AS PHONE_NUMBER FROM
CCDB.CLIENT_PHONE RIGHT OUTER JOIN CCDB.CLIENT ON
CCDB.CLIENT_PHONE.CLIENT_ID = CCDB.CLIENT.ID LEFT JOIN
CCDB.PHONE_NUMBER ON CCDB.CLIENT_PHONE.PHONE_ID = CCDB.PHONE_NUMBER.ID,
CCDB.CLIENT_EVENT,
CCDB.CLIENT_IDENTIFIER, CCDB.CLIENT_TYPE,
CCDB.CLIENT_NAME, CCDB.NAME_LIST,
CCDB.CLIENT_ADDRESS, CCDB.ADDRESS WHERE
CCDB.CLIENT_TYPE.ID = CCDB.CLIENT.CLIENT_TYPE_ID AND ( CCDB.CLIENT.ID
= CCDB.CLIENT_EVENT.CLIENT_ID ) and ( CCDB.CLIENT.ID =
CCDB.CLIENT_IDENTIFIER.CLIENT_ID ) and ( CCDB.CLIENT.ID =
CCDB.CLIENT_NAME.CLIENT_ID ) and ( CCDB.CLIENT.ID =
CCDB.CLIENT_ADDRESS.CLIENT_ID ) AND 2 = CCDB.CLIENT_TYPE.ID AND 1 =
CCDB.CLIENT_ADDRESS.ADDRESS_TYPE_ID AND 1 =
CCDB.CLIENT_IDENTIFIER.IDEN_TYPE_ID AND 1 =
CCDB.CLIENT_EVENT.EVENT_TYPE_ID AND ( CCDB.ADDRESS.ID =
CCDB.CLIENT_ADDRESS.ADDRESS_ID ) AND (
CCDB.CLIENT_ADDRESS.END_DATE > CURRENT DATE OR
CCDB.CLIENT_ADDRESS.END_DATE IS NULL) AND (
CCDB.CLIENT_EVENT.END_DATE > CURRENT DATE OR CCDB.CLIENT_EVENT.END_DATE
IS NULL) AND ( CCDB.CLIENT_IDENTIFIER.END_DATE > CURRENT DATE OR
CCDB.CLIENT_IDENTIFIER.END_DATE IS NULL) and (
CCDB.CLIENT_NAME.NAME_ID = CCDB.NAME_LIST.ID ) AND (
CCDB.CLIENT_NAME.NAME_TYPE_ID in (1,2)) AND (
CCDB.CLIENT_NAME.END_DATE > CURRENT DATE OR CCDB.CLIENT_NAME.END_DATE
IS NULL)
Here is the where portion of the select statement that is paining us,
it is a SELECT DISTINCT bunch of fields from the view
WHERE BASIC_CLIENT_V.STREET_NUM='15' AND BASIC_CLIENT_V.STREET_NAME
LIKE 'MACKINNON%' ORDER BY UPPER(BASIC_CLIENT_V.LAST_NAME) ASC,
UPPER(BASIC_CLIENT_V.FIRST_NAME) ASC, UPPER(BASIC_CLIENT_V.MID_NAME) ASC

There is nothing inherently bad about the sort of LIKE processing you
are doing. DB2 will add a BETWEEN predicate to limit the search.
How does your explain (db2exfmt) look like?

Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

IOD Conference
http://www.ibm.com/software/data/ond...ness/conf2006/


Jun 28 '06 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

3 posts views Thread by Peteris Krumins | last post: by
7 posts views Thread by Michael Beumers | last post: by
8 posts views Thread by xixi | last post: by
5 posts views Thread by Peter Guetens | last post: by
3 posts views Thread by bughunter | last post: by
15 posts views Thread by DavidW | last post: by
1 post views Thread by rainxy | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.