none
Similar table, but totally different search performance... RRS feed

  • Question

  • I have two table A and B:

     

    A   2000000 Rows  569288KB  8KB index

    B   3000000 Rows  853712KB  8KB index

     

    But when do "SELECT COUNT(*) FROM A/B", table B is significantly slower than A:

     

    A    0 secend

    B    8 seconds

     

    Does anyone know why?  So I can boost the performance to search table B.

     

    Thanks in advance.

    Thursday, September 20, 2007 10:50 PM

All replies

  • Just out of curiosity, do both tables have a clustered index?  Have you looked at the query plan for the query?
    Friday, September 21, 2007 6:44 PM
  • Cloned table, so they should be same.

     

    No index involved in this case.

    Friday, September 21, 2007 7:05 PM
  • If one table has 2,000,000 rows and one has 3,000,000 rows then one is not a clone of the other and your assumption that "they shold be same" isn't necssarily valid even if they were exact clones.  Perhaps the query plan for table one was cached.  Maybe table two is heavily fragmented.  Maybe there was some lock contention when you ran your query or perhaps there was some network latency.  Maybe there was I/O contention on the device where the data resides.  Maybe the fact that one table is 50% larger than the other caused the optimizer to use a different strategy.  There are a lot of reasons why a query might take longer for one table over another.

    Does the query against table two ALWAYS take eight seconds or is it only the first time?

    Check the query plans for each query and see where they differ.
    Friday, September 21, 2007 7:24 PM
  •  Wichemia wrote:

    Cloned table, so they should be same.

     

    No index involved in this case.



    To satisfy queries such as

    SELECY COUNT(*) FROM ...

    the optimizer typically chooses to scan the leanest index. So what do you mean by "No index involved"? Did you check up both execution plans?

    Also:
     - use SET STATISTICS TIME ON and SET STATISTICS IO ON to determine execution costs. What were these 8 seconds spent on?
     - even cloned tables with identical structure and similar number of rows may have very different sizes. Consider a table structure with a lot of VARCHAR(100) columns, which may be filled to capacity in one of your tables, and left empty in another. Use sp_spaceused to figure out table and index sizes.
    - depending on your isolation level and rate of modifications against your tables, your queries may spend significant time in lock waiting state. Run sp_locks to figure that out.
    Saturday, September 22, 2007 9:28 PM
  •  Wichemia wrote:

    Cloned table, so they should be same.

     

    No index involved in this case.



    To satisfy queries such as

    SELECT COUNT(*) FROM ...

    the optimizer typically chooses to scan the leanest index. So what do you mean by "No index involved"? Did you check up both execution plans?

    Also:
     - use SET STATISTICS TIME ON and SET STATISTICS IO ON to determine execution costs. What were these 8 seconds spent on?
     - even cloned tables with identical structure and similar number of rows may have very different sizes. Consider a table structure with a lot of VARCHAR(100) columns, which may be filled to capacity in one of your tables, and left empty in another. Use sp_spaceused to figure out table and index sizes.
    - depending on your isolation level and rate of modifications against your tables, your queries may spend significant time in lock waiting state. Run sp_locks to figure that out.
     Wichemia wrote:

    Cloned table, so they should be same.

     

    No index involved in this case.

    Saturday, September 22, 2007 9:29 PM
  • also forgot to mention that one of your tables could be heavily fragmented.
    Sunday, September 23, 2007 7:03 PM
  • also forgot to mention that one of your tables could be heavily fragmented.
    Sunday, September 23, 2007 7:03 PM
  •  

    use SET STATISTICS PROFILE ON and then run the query to find out which part of the plan takes the longest time by looking at the Estimate IO, CPU and the sub-tree cost of the plan elements.

     

     

    Tuesday, September 25, 2007 12:16 PM