DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2

Thread: SQL Query Performance

  1. #1
    Join Date
    Feb 2000
    Location
    Alexandria, VA, 22314
    Posts
    41
    Hi all,

    I have the following query that uses an IN clause, specifically for the 'SUBMITTAL.FACILITY_ID IN (select facility_id from vu_vendor where vendor_id in (1010))' part. Is there any way I can replace the IN clause to make the SQL Query use INDEXES and perform faster?

    Thanks in advance

    -ka

    select STATUS.STATUS_TXT, count(*) count, sum(equipment_total_cost) totalCost
    FROM ODS_SUBMITTAL_STATUS STATUS, ODS_SUBMITTAL SUBMITTAL
    WHERE (SUBMITTAL.submittal_id, STATUS.CREATED_DATE)
    IN (SELECT submittal_id, MAX(created_date) FROM ods_submittal_status GROUP BY submittal_id)
    AND (SUBMITTAL.submittal_id, SUBMITTAL.version_nbr)
    IN (SELECT submittal_id, MAX(version_nbr) FROM ods_submittal GROUP BY submittal_id)
    AND (SUBMITTAL.SUBMITTAL_ID = STATUS.SUBMITTAL_ID)
    AND SUBMITTAL.OWNER_USERNAME = 'username'
    AND ( SUBMITTAL.FACILITY_ID IN (select facility_id from vu_vendor where vendor_id in (1010)) )
    GROUP BY STATUS.STATUS_TXT

  2. #2
    Join Date
    Jan 2002
    Posts
    33
    I will try WHERE EXISTS.
    The WHERE EXISTS clause works more like an indexed column because your search ends the instance a hit is made.
    The IN clause as you correctly stated uses full table scan because it ensures that the entire table is searched.

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