Re: DB Schema suitable for searching

The LIKE usage will impact performance, but only if there is a % at the beginning of the like value. But I'd kind of guess that is a given and probably is added by the application.

If you are one 6.1 you could create a derived index over digits(phone) and that might help, but again, it's got a like.

EVI's might help, as someone else suggested.

However, if the index advisor isn't recommending anything new, then maybe there currently is no answer except a redesign.

Another thought: If you recreated the table using DDL rather than DDS you might get better throughput.

Don't know enough about star schema to comment.

Sam

On 4/30/2010 10:29 AM, Charles Wilt wrote:

All,

I've got an application that's design to search open invoices in order
to allow users to match a payment to an invoice, when there's no
remittance document included.

The users will search parts of the address, name, amount, ect.

Currently, the app builds a special table daily of customer/contract
data for just those customers who have open invoices. Here's the DDS:
A R FARSRCHP TEXT( 'AR Search.Physical table' )
A ARSKEY 10P TEXT( 'AR Search Key' )
A LOCNBR 5A TEXT( 'Location Number' )
A SENTID 5P TEXT( 'Search Entity ID' )
A SENTTP 1A TEXT( 'Search Entity Type' )
A SENTNM 25A TEXT( 'Search Entity Name' )
A ADDR1 25A TEXT( 'Address Line 1' )
A ADDR2 25A TEXT( 'Address Line 2' )
A ACITY 25A TEXT( 'City' )
A STPR 3A TEXT( 'State-Province' )
A ZIP 9A TEXT( 'Zip' )
A PHONE 17P TEXT( 'Phone' )
A INVOIC 6P TEXT( 'Invoice Number' )
A INVDAT L TEXT( 'Invoice Date' )
A INVAMT 9P 2 TEXT( 'Invoice Amount' )
A AMTDUE 9P 2 TEXT( 'Invoice remaining amt due' )

ARSKEY - AR Search Key is just a surrogate primary key.
SENTTP - Search Entity Type tells me if the SENTID and the name and
address in this record is a
Customer Delivery Address, Customer Mailing,
Contract Address, Billing Address
SENTID - Search Entity ID is the customer or contract number

This means every open invoice appears in this table 4 times, once for
each SENTTP.

The app uses SQL to search for possible matches given the criteria
entered by the user. Here's some example statements:
Select * from ARSRCHP Where SENTNM like ? Order by INVDAT desc, INVOIC, SENTTP
Select * from ARSRCHP Where INVOIC = ? Order by INVDAT desc, INVOIC, SENTTP
Select * from ARSRCHP Where INVOIC = ? and (abs(INVAMT - ?)< ?

or abs(AMTDUE - ?)< ?)
Order by INVDAT desc, INVOIC, SENTTP
Select * from ARSRCHP Where SENTNM like ? and INVOIC = ? Order by
INVDAT desc, INVOIC, SENTTP
Select * from ARSRCHP Where ZIP like ? Order by INVDAT desc, INVOIC, SENTTP
Select * from ARSRCHP Where digits(PHONE) like ? Order by INVDAT desc,
INVOIC, SENTTP
Select * from ARSRCHP Where (ADDR1 like ? or ADDR2 like ?) Order by
INVDAT desc, INVOIC, SENTTP


This app is having performance issues, specifically resource use.

So I got called in to take a look.

First off, there were no indexes, so I looked at the advised indexes
and the SQL plan cache and created some that I though would be useful;
and those indexes have been useful, but not as much as would have
liked. Doing some more digging, it seems to me that the
design/implimentation of the ARSRCHP table is part of the problem.
There's a lot of duplicated data inside it, some numbers:
3,045,000 records in ARSRCHP
1,946,000 distinct records (excluding ARSKEY, SENTID, SENTTP)
761,000 Distinct Invoices
336,000 Distinct ADDR1
243,470 Distinct Phone #
232,000 Distinct Names
20,000 Distinct Zip Codes
12,000 Distinct Cities

Given the use of LIKE in the queries, I believe that means that lots
of full index scans (best case) or full table scans (worse case) being
done that are looking at the same value multiple times.

Now, I do recall some initial discussions between myself and the
developer where we discussed building the ARSRCHP table or just
searching over our normal invoice, customer, contract, and address
tables. I didn't initially feel that ARSRCHP was necessary, but he
felt having ARSRCHP would be better due to the size of our actual
tables. I went along with that and I still believe he's probably
correct.

However, from looking at the production data and the production
queries, I'm thinking that instead of a single flattened ARSRCHP
table, he should have used basically a star schema like you'd use in a
data warehouse.

I believe a Star Schema would provide better performance for the
searches our users are doing.

Does any have any experience with a similar app or any thoughts or comments?

Thanks in advance,
Charles Wilt