Double check a query
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2

Thread: Double check a query

  1. #1
    Join Date
    Apr 2013
    Posts
    1

    Double check a query

    I am a bit new to my poisition but for some reason it sometimes take a long time to pull a query from the db. Can I get some if to see if I can optimize my query any more?

    SELECT c.udf1_c as "Merial ID", c.udf2_c as "Location ID", c.bus_name as "Practice Name", e.synopsis as " Program Name", d.field4 as "Software", d.field26 as "HSI", d.field27 as "Shared DB",
    d.field28 as "Converted Pims", d.field29 as "Open 12 mo. or Less", d2.field1 as "Submission Type", d2.field4 as "Media Issue", c.state as "Clinic's State",
    to_char(n.completed_date,'MM/DD/YYYY') as "Assigned Date"
    FROM nxscuste c, nxstar_e e, nxsnotes n, nxsdatas d, nxsdatas d2
    WHERE e.symptom_code = 'CC'
    AND e.completed is null
    AND d.type_code = 'ENROLL'
    AND n.completed_date in (SELECT MAX(n2.completed_date) from nxsnotes n2 WHERE n2.id = n.id and n2.type = 'DS-BadMedia')
    AND d2.type_code = 'RECMEDIA'
    AND e.tar_num = d2.id
    and e.tar_num = d.id
    and e.tar_num = n.id
    and c.cust_code = e.cust_code;

    Thank you!

  2. #2
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    3,975
    You should be able to generate an explain plan on your own, otherwise,
    you are unqualified for your position. Here is an index, that might help.

    Code:
    create index nxsnotes_compdt_id ON nxsnotes ( completed_date, tar_num );
    It helps to use SQL 99 joins, since doing so helps to document how the tables
    are joined together, and sometimes results in better performance.

    Code:
    SET timing ON
    SET linesize 150
    set pagesize 50
    
    SET autotrace ON
    SELECT c.udf1_c   AS "Merial ID",           c.udf2_c   AS "Location ID", 
           c.bus_name AS "Practice Name",       e.synopsis AS " Program Name", 
           d.field4   AS "Software",            d.field26  AS "HSI", 
           d.field27  AS "Shared DB",           d.field28  AS "Converted Pims", 
           d.field29  AS "Open 12 mo. or Less", d2.field1  AS "Submission Type", 
           d2.field4  AS "Media Issue",         c.state    AS "Clinic's State", 
           to_char(n.completed_date,'MM/DD/YYYY') AS "Assigned Date"
      FROM nxscuste c
     INNER JOIN nxstar_e e
        ON c.cust_code = e.cust_code
     INNER JOIN nxsnotes n
        ON e.tar_num = n.id
     INNER JOIN nxsdatas d
        ON e.tar_num = d.id
     INNER JOIN nxsdatas d2
        ON e.tar_num = d2.id
     WHERE e.symptom_code = 'CC'
       AND e.completed IS NULL
       AND d.type_code = 'ENROLL'
       AND n.completed_date IN (SELECT MAX(n2.completed_date) 
                                  FROM nxsnotes n2 
                                 WHERE n2.ID = n.ID 
                                   AND n2.TYPE = 'DS-BadMedia')
       AND d2.type_code = 'RECMEDIA';
    this space intentionally left blank

Posting Permissions

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



Click Here to Expand Forum to Full Width