Results 1 to 2 of 2
  1. #1
    Join Date
    Apr 2003
    Posts
    1

    Unanswered: SQL optimization

    Hi,
    I have a table called Segments that have three columns:
    on_ramp_id, off_ramp_id, and user_id
    example: (12, 45, 23)
    where on_ramp_id/off_ramp_id is a foreign key exit_id in another table called EXITS that thave two columns:
    exit_id, exit_name
    (12, Cannon Rd)
    (45, Stevens Creek)
    Now my question is:
    if I execute a query like this:

    select exit1.exit_name, exit2.exit_name
    from Segments seg, Exits exit1, Exit exit2
    where seg.user_id=23, and seg.on_ramp_id=exit1.exit_id and seg.off_ramp_id=exit2.exit_id

    it will output all the exits associated with a user. But it seems inefficient. It essentially take a cross-product the three tables: seg, exit1. and exit2 and find the matching row.
    Is there a faster way? I think maybe something along the line of taking exit_name from Exits that have matching exit_id with on_ramp_id, and exit_name from Exits that have matching exit_id with off_ramp_id, and concatenate the result together. Could anyone give me the code? thanks.

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: SQL optimization

    Originally posted by terminator800tl
    Hi,
    I have a table called Segments that have three columns:
    on_ramp_id, off_ramp_id, and user_id
    example: (12, 45, 23)
    where on_ramp_id/off_ramp_id is a foreign key exit_id in another table called EXITS that thave two columns:
    exit_id, exit_name
    (12, Cannon Rd)
    (45, Stevens Creek)
    Now my question is:
    if I execute a query like this:

    select exit1.exit_name, exit2.exit_name
    from Segments seg, Exits exit1, Exit exit2
    where seg.user_id=23, and seg.on_ramp_id=exit1.exit_id and seg.off_ramp_id=exit2.exit_id

    it will output all the exits associated with a user. But it seems inefficient. It essentially take a cross-product the three tables: seg, exit1. and exit2 and find the matching row.
    Is there a faster way? I think maybe something along the line of taking exit_name from Exits that have matching exit_id with on_ramp_id, and exit_name from Exits that have matching exit_id with off_ramp_id, and concatenate the result together. Could anyone give me the code? thanks.
    This is an extremely simple join query, which the optimizer is perfectly capable of handling the best way. You don't need to worry about it, unless it performs too slow: even then, the solution would not be to re-write the query, but to ensure appropriate indexes and statistics exist, etc.

Posting Permissions

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