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

Optimize Linked Server to Oracle

I have a linked server to Oracle (I have one using MS and one Using Oracle
Driver), both take about 20+mins to return 114k rows using OPENQUERY, is
there any way to speed this up, and why does oracle always ruin my day ?
- DavidIt depends on the query being executed, capabilities of the
driver or provider you are using, connection between the
servers, etc. Have you tried using an OLE DB provider
instead of an ODBC driver? Check the network connection
between the two servers. You may want to go through the
following topic in books online:
Optimizing Distributed Queries
In addition, the following link has some suggestions:
http://www.sql-server-performance.com/linked_server.asp
-Sue
On Mon, 19 Sep 2005 14:56:15 -0400, "David J. Cartwright"
<davidcartwright@.hotmail.com> wrote:

>I have a linked server to Oracle (I have one using MS and one Using Oracle
>Driver), both take about 20+mins to return 114k rows using OPENQUERY, is
>there any way to speed this up, and why does oracle always ruin my day ?
>- David
>

Optimize Linked Server to Oracle

I have a linked server to Oracle (I have one using MS and one Using Oracle
Driver), both take about 20+mins to return 114k rows using OPENQUERY, is
there any way to speed this up, and why does oracle always ruin my day ?
- David
It depends on the query being executed, capabilities of the
driver or provider you are using, connection between the
servers, etc. Have you tried using an OLE DB provider
instead of an ODBC driver? Check the network connection
between the two servers. You may want to go through the
following topic in books online:
Optimizing Distributed Queries
In addition, the following link has some suggestions:
http://www.sql-server-performance.com/linked_server.asp
-Sue
On Mon, 19 Sep 2005 14:56:15 -0400, "David J. Cartwright"
<davidcartwright@.hotmail.com> wrote:

>I have a linked server to Oracle (I have one using MS and one Using Oracle
>Driver), both take about 20+mins to return 114k rows using OPENQUERY, is
>there any way to speed this up, and why does oracle always ruin my day ?
>- David
>