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.
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_alertTo 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, col3FROM order_headerUNION ALLSELECT col1, col2, col3FROM order_header_histWHERE order_no = @OrderNoHowever, 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_header3) else if order_no appears in order_header_hist table, select from order_header_hist4) else return null or errorSo 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_headerwhere ...selection criteria...union allselect COL_01, COL_02, COL_03, ETC...from order_header_histwhere order_id not in (select order_id from order_header) and ... additional selection criteria... CODO ERGO SUM |
|
|
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 |
|
|
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 |
|
|
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 |
|
|
|
|
|
|
|