Results 1 to 2 of 2
  1. #1
    Join Date
    Aug 2005
    Sharjah, UAE

    Unanswered: 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( AS FLOAT) Qty,
                    CAST(SUM((CAST(c.breite AS FLOAT) / 1000 * CAST(c.hoehe AS FLOAT) / 1000) * AS FLOAT) SQM,
                    CAST(SUM(a.liefer_offen) - (SUM( - SUM( AS FLOAT) AvailDelv,
                    CAST(SUM(a.liefer_anz) AS FLOAT) Delvd,
                    CAST(SUM(c.sum_brutto* 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,
    HAVING CAST(SUM(a.liefer_offen)-(SUM( 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?

  2. #2
    Join Date
    Feb 2004
    In front of the computer
    Provided Answers: 54
    I'd use OPENQUERY.


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts