Results 1 to 6 of 6
  1. #1
    Join Date
    Oct 2002
    Posts
    37

    Unanswered: To subquery or not to subquery?

    A query statement, I have, can be written using subqueries. If subquering are employed, they will query different fields of the same table along the query statement. Will the job can be done more effectively to have two queries instead of one query statement and use the first query to fetch data and pass it into the second query?

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    that depends on the queries

    i wonder what they look like


    rudy

  3. #3
    Join Date
    Oct 2002
    Posts
    37
    Thanks Andy for your interests.

    The query is something like the followings:

    ... AND (EXISTS (SELECT 1 FROM preference_type_table WHERE userid=p.userid AND type IN (SELECT type FROM profile_table WHERE userid='Tom') OR NOT EXISTS (SELECT 1 FROM preference_type_table WHERE userid=pf.userid) )

    AND (EXISTS (SELECT 1 FROM preference_plan_table WHERE userid=p.userid AND plan IN (SELECT plan FROM profile_table WHERE userid='Tom') ) OR NOT EXISTS (SELECT 1 FROM "preference_plan_table WHERE userid=pf.userid) )

    AND EXISTS (SELECT userid FROM preference_table WHERE p.userid=userid AND (2003 - (SELECT year FROM profile_table WHERE userid = 'Tom')) BETWEEN minage AND maxage) )

    where the three subqueries

    SELECT type FROM profile_table WHERE userid='Tom'

    SELECT plan FROM profile_table WHERE userid='Tom'

    SELECT year FROM profile_table WHERE userid = 'Tom'

    are what I mentioned in my posting whether shall be obtained thorough a separated query, but not in this subquery form.

    Thanks again for your advice.

    v.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    if they run fine, good performance, then leave them alone

    i think some of the double EXISTS (if a preference exists and it's one of his, or if a preference doesn't exist) could be replaced by a left outer join

    but don't rewrite the queries unless you have a real performnace problem

    because then you may end up in a redesign, too

    i can't really tell without seeing your data

    like i said, if your queries run okay, leave them


    rudy

  5. #5
    Join Date
    Oct 2002
    Posts
    37
    I raise this issue when I modified the query yesterday to add the type and plan as search criteria. I guess I shall use a common sense using a separated query to fetch data when the number of the subqueries is high.

    Thanks Rudy for you opinion (sorry for reading your name wrong. I just got up from bed).

    v.

  6. #6
    Join Date
    Oct 2002
    Posts
    37
    Rudy,

    Also, I take a look at your suggestion that using left outer join instead of EXISTS in the subqueries. How to using outer join in different levels' query?


    Originally posted by r937
    if they run fine, good performance, then leave them alone

    i think some of the double EXISTS (if a preference exists and it's one of his, or if a preference doesn't exist) could be replaced by a left outer join

    but don't rewrite the queries unless you have a real performnace problem

    because then you may end up in a redesign, too

    i can't really tell without seeing your data

    like i said, if your queries run okay, leave them


    rudy

Posting Permissions

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