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

    Unanswered: Optimizing query execution...

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

    Code:
    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?

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

    -PatP

Posting Permissions

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