Results 1 to 4 of 4
  1. #1
    Join Date
    Sep 2003
    Posts
    364

    Unanswered: Interesting SQL...

    Anyone else experience this?

    A developer just finished complaining about the performance of one of our databases. Well, he sent me the query and I couldn't understand why it was such a dog. Anyways I rewrote it. The execution plan is totally different between the two. I had no idea specifying the join made such a difference. First sql executed in 7 minutes that 2nd took 1 second.

    SELECT
    dbo.contract_co.producer_num_id, contract_co_status
    FROM
    dbo.contract_co,
    dbo.v_contract_co_status
    WHERE ( dbo.v_contract_co_status.contract_co_id = dbo.contract_co.contract_co_id )
    AND contract_co_status = 'Pending'
    OR ( contract_co_status = 'Active' and effective_date > '1/1/2004' )

    SELECT
    dbo.contract_co.producer_num_id, contract_co_status
    FROM dbo.contract_co
    INNER JOIN dbo.v_contract_co_status
    ON dbo.contract_co.contract_co_id = dbo.v_contract_co_status.contract_co_id
    WHERE contract_co_status = 'Pending'
    OR ( contract_co_status = 'Active' and effective_date > '1/1/2004' )

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    The two queries look like they will give different results, too. The first one appears to include a cartesian join. The OR in the where clause makes all the difference.

  3. #3
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Don't want to sound like a snob, but it's all due to the order of processing by QP:

    1. JOIN
    2. GROUP
    3. WHERE
    4. HAVING

    By rewriting the old query you filtered out what the first query had to deal with while still trying to JOIN.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    actually, i believe it's

    1. JOIN
    2. WHERE
    3. GROUP

    4. HAVING
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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