Re: DB Schema suitable for searching

[Charles Wilt <charles.wilt@xxxxxxxxx>]

Oh yeah, one other specific question...

Do you think the DB2 extender that allows for full text searching
would perform better than simply using LIKE against the strings in the
address fields?

Charles

On Fri, Apr 30, 2010 at 10:29 AM, Charles Wilt <charles.wilt@xxxxxxxxx> 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