Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 sproc design suggestions - current & history table

Author  Topic 

CorpDirect
Yak Posting Veteran

92 Posts

Posted - 2006-01-04 : 18:12:24
Good day,

Please take a look at the scenario and offer feedback or possibly suggestions on the best way to return data for reports. Thanks!

We use an accounting system that stores order and invoice information in current and history tables, and sometimes in both at once (if, for example, an order is pending when the period is closed). When an order appears in both current and history, only the current table is guaranteed to be correct if changes have been made.

We need to generate reports that return filtered data from several sources, and can include several recordsets with multiple records. For example, say we have the following tables (some with corresponding history tables):

- order_header
-> order_header_hist
- order_item
-> order_item_hist
- customer_alert

To return the report data, we have a stored procedure for each segment:

sp_report_header
- order header information (e.g. order_no, order_date, cust_no, cust_name, etc.)
sp_report_items
- order item information (e.g. item_no, item_desc, cust_note, etc.)
sp_report_alerts
- customer alert information (e.g. alert_priority, alert_text, etc.)

The question is, how best to write the stored procedures to return data from current or hist tables as appropriate?

If the application kept order information in only current OR history tables, I could simply use e.g.

SELECT col1, col2, col3
FROM order_header
UNION ALL
SELECT col1, col2, col3
FROM order_header_hist
WHERE order_no = @OrderNo

However, since data for one order can appear in both current and history tables, this does not work (and also the history tables grow quite large compared to the current tables). Also, since there may be several procedures executed to generate a single report, I need to determine the most efficient way of determining for each procedure whether to pull from current or history tables.

Basically here's how data should be pulled, using order_item and order_item_hist:

1) given order_no,
2) if order_no appears in order_header table, select from order_header
3) else if order_no appears in order_header_hist table, select from order_header_hist
4) else return null or error

So how should this be done? If half a dozen, or ten, or 15 procedures will be executed to generate a report, and each needs to know whether to pull from current or history tables, would it be best to have each one look for the order location, or to have a seperate procedure that checks for the valid order and its location first before the rest run?

If we do that, we can pass a parameter e.g. @CurrHist = 'C' or 'H' to each subsequent procedure. How does that sound?

Suggestions are welcome!

Thank you,

Daniel

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-01-04 : 19:41:18
This type of query should do the job for you. If you run a lot of reports with the same section criteria, you may want to run the query once, store the results in a table, and run your reports from that.

select
COL_01,
COL_02,
COL_03,
ETC...
from
order_header
where
...selection criteria...
union all
select
COL_01,
COL_02,
COL_03,
ETC...
from
order_header_hist
where
order_id not in (select order_id from order_header)
and
... additional selection criteria...



CODO ERGO SUM
Go to Top of Page

CorpDirect
Yak Posting Veteran

92 Posts

Posted - 2006-01-05 : 11:30:06
Is this an efficient method? UNION ALL will at least perform an index scan on the history table each time, right?

The part that worries me is "WHERE order_no NOT IN (SELECT order_id FROM order_header)". The history table has about 30x more records than the current table. If there are several procedures running each time a report is generated, and each has UNION ALL statements like this, how will this perform?

Would it be better to determine whether the order information should be pulled from current or history first, then specify the source when each subsequent procedure for the report is run? That way no UNION ALL would have to be performed, just SELECT FROM the appropriate tables.

Just curious, not really sure how to do that either, but if it would work better I will learn how.

Thanks,

Daniel
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-01-05 : 12:30:13
There performance really depends on the other selection criteria that you have and if they can use the indexes. You didn't provide that info, so I can't say.

UNION ALL is not bad by itself, but it is entirely possible that you can come up with a better method.

The best thing to do is try various methods, look at the query plan, and see which performs better with your data.




CODO ERGO SUM
Go to Top of Page

CorpDirect
Yak Posting Veteran

92 Posts

Posted - 2006-01-05 : 12:35:09
Thanks, I'll run some tests. I appreciate the suggestion!

Regards,

Daniel
Go to Top of Page
   

- Advertisement -