Results 1 to 7 of 7
  1. #1
    Join Date
    Jul 2008
    Posts
    13

    Unanswered: Which Plan is the most efficient one?

    Hello,

    I have been trying to fine tune a query that involves this one table. The table contains 725,027 rows.

    Plan 1
    The original query completes in 6.2 seconds. The optimizer makes use of the clustered index. Performs a clustered index scan. The primary key is not used in the query.
    Physical operation: Clustered Index Scan
    Row Count: 725,027
    I/O cost: 6.73
    CPU cost: 0.39
    Number of executes: 2
    Cost: 3%
    Estimated row count: 723,244


    Plan 2
    I created a covering index. Completes in 1.72 seconds. Uses the covering index but performs an index scan.
    Physical operation: Index Scan
    Row Count: 725,027
    I/O cost: 3.09
    CPU cost: 0.79
    Number of executes: 1
    Cost: 11%
    Estimated row count: 723,244



    Plan 3
    I used 'set forceplan'. Completes in 2.31 seconds. Uses the covering index. Performs an Index Seek.
    Physical operation: Index Seek
    Row Count: 20,215
    I/O cost: 0.00320
    CPU cost: 0.000080
    Number of executes: 10449
    Cost: 22%
    Estimated row count: 1


    Plan 3 performs a seek, I/O and CPU is good BUT the number of executes is too high. Reads are high.

    Would like to know which plan is ideal with regard to this table?


    Thank you.

  2. #2
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    You numbers don't jive. 3+11+22=36% What else were you running in this batch? Also, your row count is different. Is it the value from the branch or the value of rows affected? How about posting a query and the number of rows you're returning?
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  3. #3
    Join Date
    Jul 2008
    Posts
    13
    I posted only the portion of the query plan related to the 1 table for which I created the covering index as the difference in the query plan is with this one table.

    It is a value from the branch.

    The number of rows returned by the query (output) is 23213.

    Query accesses 3 tables, inner joins. Also uses a 'union'.

    select t1.num, t1.field_date, t2.fld_code , t3.prod
    from table_1 t1
    inner join table_2 t2
    on t1.fld_id = t2.fld_id
    inner join table_3 t3
    on t3.fld_code = t2.fld_code
    where (
    (t1.field_date >= @fld_start_date)
    and
    (t1.field_date <= @fld_end_date)
    )

    union

    select t1.num, t1.field_date, t2.fld_code , t3.prod
    from table_1 t1
    inner join table_2 t2
    on t1.fld_id = t2.fld_id
    inner join table_3 t3
    on t3.fld_code = t2.fld_code
    where (
    (t1.field_date >= @fld_start_date)
    and
    (t1.field_date <= @fld_end_date)
    )


    Thank you.

  4. #4
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    So why do you have it with a UNION? It's the same query repeated twice. Are you sure you truly represented your code?
    EDITED: post the output from SET STATISTICS IO ON
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    I'm going to bet that your covering index on t1 is (num, field_date). This would explain the plans. If it is changed to (field_date, num) then you will get a single, efficient index seek (the type affected by leaf level fragmentation!). If this is SQL 2005 and t1.num is the pk then you can exclude it from the index as it is in the leaf level pages anyway. If it is 2005 but not the pk then you can just pop num into an includes clause rather than have it as part of a composite index.

    I think the lesson here is: innappropriate index seeks can be FAR worse than a table\ index scan.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  6. #6
    Join Date
    Jul 2008
    Posts
    13
    Thanks for the replies.

    My mistake, I posted the code but changed the table names. Hence the error.
    The second part of the union clause uses a different table (first inner join).

    select t1.num, t1.field_date, t2.fld_code , t3.prod
    from table_1 t1
    inner join table_2 t2
    on t1.fld_id = t2.fld_id
    inner join table_3 t3
    on t3.fld_code = t2.fld_code
    where (
    (t1.field_date >= @fld_start_date)
    and
    (t1.field_date <= @fld_end_date)
    )

    union

    select t1.num, t1.field_date, t2.fld_code , t3.prod
    from table_1 t1
    inner join table_4 t2
    on t1.fld_id = t2.fld_id
    inner join table_3 t3
    on t3.fld_code = t2.fld_code
    where (
    (t1.field_date >= @fld_start_date)
    and
    (t1.field_date <= @fld_end_date)
    )

  7. #7
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Oops. And I didn't provide a covering index.
    (field_date, fld_id) with num in an includes statement.
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

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