SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Member
    Join Date
    Jul 2003
    Location
    Rochester
    Posts
    14
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Unhappy subquery - taking too long

    Hi,

    I am having a problem with subqueries...and I need help!!!!


    I have table (Table1)with these rows)

    caseno case_type projno
    ---------- --------- ----------
    01 BLD 100
    02 BLD 200
    03 ELE 200
    04 MEC 400

    what I am trying to do is get rows

    1. which has only rows with "BLD" and no other case_type has same
    projno ex: 01 BLD 100

    2. rows which has maching projno - then get only row with "BLD" case-type. (and do not get the rows which has same projno with other case_types like 'MEC' or 'ELE')
    ex: 02 BLD 200 (get this row)
    03 ELE 200 (eliminate this row)

    3. Get rows which does not have maching projno for "BLD" but has rows for case_types like "ELC" or "MEC"


    SELECT csm_caseno,case_type,csm_projno
    FROM Table1
    WHERE (case_type = 'BLD' and (csm_projno in
    (select csm_projno from Table1 where
    (case_type ='MEC' or case_type ='ELE' )
    )
    )
    ) or
    (case_type = 'BLD' and (csm_projno not in
    (select csm_projno from Table1 where
    (case_type ='MEC' or case_type ='ELE' )
    )
    )
    ) or
    ((case_type = 'MEC' or case_type ='ELE' ) and (csm_projno not in
    (select csm_projno from Table1 where
    (case_type ='BLD')
    )
    ))

    ____________________________________________________

    Above subquery is working fine for small table I created..But when I use a real database it is taking a veryyyyyyyyyy long time...

    Please help me!!!!!!!!!

    Thanks,
    Jani....

  2. #2
    Database Jedi MattR's Avatar
    Join Date
    Jan 2001
    Location
    buried in the database shell (Washington, DC)
    Posts
    1,107
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    What are your indexes?

  3. #3
    SitePoint Addict
    Join Date
    Aug 2002
    Location
    Ottawa, Ontario, Canada
    Posts
    214
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Don't your rule #1 and #2 together mean "return all cases of BLD where the projno is unique, AND return all cases of BLD where the projno is NOT unique)"? In other words...
    Code:
    SELECT case_type, projno
      FROM table1
     WHERE case_type = 'BLD';
    Taken seperately, your third query suggests:
    Code:
    SELECT case_type, projno
      FROM table1
     WHERE case_type IN ('MEC','ELE')
       AND projno NOT IN (SELECT projno
    						FROM table1
    					   WHERE case_type='BLD');
    If we put them together using a UNION we get:
    Code:
    SELECT case_type, projno
      FROM table1
     WHERE case_type = 'BLD'
     UNION
    SELECT case_type, projno
      FROM table1
     WHERE case_type IN ('MEC','ELE')
       AND projno NOT IN (SELECT projno
    						FROM table1
    					   WHERE case_type='BLD');
    Does that get the results you wanted?
    Cheers,
    Keith.

  4. #4
    SitePoint Guru asterix's Avatar
    Join Date
    Jun 2003
    Posts
    847
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    If its still slow, you can check out this article about indexing SQL Server:

    http://www.richard-quinn.com/self-promo/MSSQL_1.html


Bookmarks

Posting Permissions

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