Re: Compare 2 tables

From: Shakespeare <whatsin_at_xs4all.nl>
Date: Mon, 09 Mar 2009 12:11:21 +0100
Message-ID: <49b4f95d$0$182$e4fe514c_at_news.xs4all.nl>

coral.ressources_at_gmail.com schreef:
> I have 2 tables: Input and Output.
> Input table stores records in this manner:
> recno, prod_first, prod_span, prod_last
> 1, 1, 1000, 1000
> 2, 1001, 700, 1700
> 3, 1701, 100, 1800
> ...and so on
> prod_first is the id of the product,
> prod_span is the number of products stored by recno,
> prod_last is the last product stored by recno
>
> Output tables stores records related to outgoings:
> recno, prod_first, prod_span, prod_last
> 1, 500, 25, 525
> 2, 300, 100, 400
> 3, 1243, 100, 1343
> That mean productss no from 500 to 525, 300 to 400 and 1243 to 1343
> were delivered, while the rest from input table are still in stock.
> Question: how to find what is [still] in input table and not [yet] in
> output table?
> prod_first to prod_last are the items to be found in both tables.
> I tought like this:
> 1. make 2 temp tables: input_temp, output_temp where i would insert
> all records [one-by-one] since in original tables i have only start &
> end.
> 2. make a comparison between the 2 temp tables (using SQL aggregation)
> I am concerned that step 1 would hit the db performance as i have for
> input table aprox 100 000 [and growing!] records to insert.
>

My first idea would be:

1) Take (a copy of) your input table as a start (in_stock table)
2) For each outputrecord, split records in in_stock and do some calculation
3) Keep the result table, and write triggers on input and output tables 

to keep the in_stock table up to date (or make your in_stock table up to date every now and than with a batch job, only processing new input and   output records in some way)

This approach would stress your system only once. I know it is not 3NF, for it contains data that can be derived from other tables, but may help boost your system performance!

You'll have to put in some error checking though, for output may contain records that are not (yet) in stock (if possible) or may break your input table record boundaries (see my previous post).

Shakespeare Received on Mon Mar 09 2009 - 06:11:21 CDT