Friday, March 30, 2012

Optimizing query execution...

We have SQL Server 2000 and int is an Oracle linked server. I'm trying to run the following query...

SELECT DISTINCT a.auf_nr AS OrderNo,
e.ku_name AS Customer,
d.bestell_dat AS OrdDate,
d.liefer_dat AS DelvDate,
CAST(SUM(b.anz) AS FLOAT) Qty,
CAST(SUM((CAST(c.breite AS FLOAT) / 1000 * CAST(c.hoehe AS FLOAT) / 1000) * b.anz) AS FLOAT) SQM,
CAST(SUM(a.liefer_offen) - (SUM(a.anz) - SUM(b.anz)) AS FLOAT) AvailDelv,
CAST(SUM(a.liefer_anz) AS FLOAT) Delvd,
CAST(SUM(c.sum_brutto*a.anz) AS FLOAT) Value

FROM liorder..LIORDER.AUF_STAT a,
liorder..LIORDER.AUF_LIP_STATUS b,
liorder..LIORDER.AUF_POS c,
liorder..LIORDER.AUF_KOPF d,
liorder..LIORDER.KUST_ADR e

WHERE a.auf_nr = b.auf_nr and
b.auf_nr = c.auf_nr and
c.auf_nr = d.auf_nr and
d.kunr = e.ku_nr and
a.auf_pos = b.auf_pos and
b.auf_pos = c.auf_pos and
b.lip_status = 7 and
c.ver_art !='V' and
a.history = 0 and
a.rg_stat != 2 and
e.ku_name IS not null and
e.ku_vk_ek = 0 and
d.bestell_dat BETWEEN '01/01/2005' and '12/17/2005'

GROUP BY a.auf_nr,
d.liefer_dat,
b.lip_status,
d.bestell_dat,
e.ku_name,
d.kopf_tour,
d.kopf_firma

HAVING CAST(SUM(a.liefer_offen)-(SUM(a.anz)-SUM(b.anz)) AS FLOAT) > 0

..and it takes around 2 minutes to show the results even if the date range is of the same date. I even tried to use an indexed column but I still get the same slow execution time. I even tried to create a UDF so that the WHERE clause would be resolved remotely on the Oracle DB but still the same. Is there anyway I can do it in much more efficient and faster way?I'd use OPENQUERY (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_oa-oz_5xix.asp).

-PatP

No comments:

Post a Comment