Results 1 to 3 of 3
  1. #1
    Join Date
    Apr 2003
    Posts
    18

    Question Unanswered: query plan problem

    Folks,
    I have encountered a very weird problem. I have two tables . the only difference between the two tables is one column which exists on the T1 table has been ommitted from the second table T2. Then I took the data out from the first table and bcp'ed them into T2 via sqsh. All the indexes are still the same. Now, one query with T1 table is faster than the same query when T2 is used instead of T1.

    E.g

    select a.col1,a.col2,T1.col1
    from a,T1
    where T1.col3=<>

    is faster than

    select a.col1,a.col2,T2.col1
    from a,T2
    where T2.col3=<>

    Now, when I saw the query plan they were different, the indexes used by the optimizer is different. What I donot understand is, the first query is using the index on col3 but the second query is not using the index on col3 because it thinks it's better off using the index on col2??? And this difference is making the second query run slower. What should I do in such a case??? Please help!!!!!!!!!!!!!!!


    Regards

    db

  2. #2
    Join Date
    Nov 2002
    Location
    Switzerland
    Posts
    524
    You can force the index, change the abstract plan... or try to understand why the optimizer is choosing the bad plan.

    Do you have the same data in the both tables ? Did you already execute an update statistics + sp_recompile.

    Can you please provide us a repro ? DDL of the both tables (incl indexes), query and both showplans.

  3. #3
    Join Date
    Apr 2003
    Posts
    18

    Cool

    Originally posted by fadace
    You can force the index, change the abstract plan... or try to understand why the optimizer is choosing the bad plan.

    Do you have the same data in the both tables ? Did you already execute an update statistics + sp_recompile.

    Can you please provide us a repro ? DDL of the both tables (incl indexes), query and both showplans.
    I guess it was a update stats problem.

    Thanks

    DB

Posting Permissions

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