Query optimization

Problem definition:

At my workplace, I have an Oracle 10g database with production data. Due to limited resources and various reasons, I was task to access the Oracle data through SQL 2005. So I have to use MS-Reporting server with MS-SQL 2005.

My goal was to transfer as few data from Oracle to SQL as possible; therefore, select only what's needed in the openquery(). Second, since Oracle database manages production, I do not want to create an extra burden on Oracle database; therefore, I'm performing all the aggregate functions on the SQL server.

Query #1 ran for 1 minutes and 54 seconds (total: 114 seconds) and Query #2 ran for 9 seconds only. Query is approximately 12 times as efficient as the old query.
The trick lies in the subquery of the trhship table. The trhship table is an enormous table to be involved in a JOIN; using a sub-query became a very effective
solution.

Query #1: Before
SELECT
SUM(qty_ord /def_crtn_qty) AS 'TOTAL_ORDERED',
SUM(qty_alloc/def_crtn_qty) AS 'TOTAL_ALLOCATED',
SUM(qty_pic /def_crtn_qty) AS 'TOTAL_PICKED'
FROM openquery(CATPROD, '
SELECT dtl.ord_num, dtl.itm_num, hdr.sch_shp_dt,
dtl.qty_ord, i.def_crtn_qty, dtl.qty_pic, dtl.qty_alloc
FROM outorddtl dtl
LEFT JOIN outordhdr hdr ON hdr.ord_num = dtl.ord_num
LEFT JOIN item i ON i.itm_num = dtl.itm_num
where dtl.ood_udef_c = ''C''
') as oh
RIGHT JOIN
(
SELECT DISTINCT obnd_ord_num
FROM openquery(CATPROD ,'
SELECT obnd_ord_num
FROM trhship
WHERE TRUNC(DT_TM_START) = TO_DATE(''07/21/2009'', ''MM/DD/YYYY'')
')) as trh
ON oh.ord_num = trh.obnd_ord_num

Total run-time: 1 minute 54 seconds

Query #2: After

SELECT
SUM(qty_ord /def_crtn_qty) AS 'TOTAL_ORDERED',
SUM(qty_alloc/def_crtn_qty) AS 'TOTAL_ALLOCATED',
SUM(qty_pic /def_crtn_qty) AS 'TOTAL_PICKED'
FROM openquery(CATPROD, '
SELECT dtl.ord_num, dtl.itm_num, hdr.sch_shp_dt,
dtl.qty_ord, i.def_crtn_qty, dtl.qty_pic, dtl.qty_alloc
FROM outorddtl dtl
LEFT JOIN outordhdr hdr ON hdr.ord_num = dtl.ord_num
LEFT JOIN item i ON i.itm_num = dtl.itm_num
WHERE dtl.ood_udef_c = ''C''
AND dtl.ord_num in (
SELECT DISTINCT obnd_ord_num
FROM trhship
WHERE TO_CHAR(DT_TM_START, ''MM/DD/YYYY'') = ''07/21/2009''
)
')

Total run-time: 9 seconds

First step to RC world champion.

My first 2WD RC race today, it was really "exciting". My car was way 3 seconds slower than the 2nd slowest car on the track. Needless-to-say, I got last but it was a fun experience.

Projects

1. iMoney
2. Service Website (Web development, Application development, Wedding photography, ...)
3. iAuction
4. 3D user interface
5. iViolin
6. iCreative (VoIP)
7. Linux Beowolf

Project: Data Visualization
A 3D-data visualization tool, which can be used to analyse data using 3D representation of the data points.

Human brain is a powerfulgraphic intepreting mechanism; therefore, a 3D visualization tool can help user to better analyze their.

Technology: ODBC, C#, OpenGL
Developed by:
Dr. Russel Anderson & Steven (Jui-Hsan) Ho

[above: Visualization & Control Window ]