Post reply

Estimated row count for table that does not have statistics

  • November 25, 2013 at 5:13 am

    #300696

    I have come across a practical situation where a table does not have statistics because they are disabled. This table is used to temporary store ids of objects that require a refresh. The table has a small/medium/large version. The query looks like [font="Courier New"]select * from vview where id in (select id from ttable_small)[/font]. Before execution a bunch of ids are inserted into ttable and removed directly after the query is executed. Statistics are disabled to prevent unwanted recompilations.

    There are 3 execution plans in the cache. The [font="Courier New"]select id from ttable_small [/font]results in a table scan where the estimated rows is the rowcount of ttable at the first time the plan was compiled. Since this table does not have any statistics I assume it is retrieved from sys.partitions but I am not sure.

    I thought that statistics were the only data volumetrics input for the exec plan. Is there any other source as well?

  • November 25, 2013 at 12:56 pm

    #1668953

    Bouke Bruinsma (11/25/2013)

    I have come across a practical situation where a table does not have statistics because they are disabled. This table is used to temporary store ids of objects that require a refresh. The table has a small/medium/large version. The query looks like [font="Courier New"]select * from vview where id in (select id from ttable_small)[/font]. Before execution a bunch of ids are inserted into ttable and removed directly after the query is executed. Statistics are disabled to prevent unwanted recompilations.

    There are 3 execution plans in the cache. The [font="Courier New"]select id from ttable_small [/font]results in a table scan where the estimated rows is the rowcount of ttable at the first time the plan was compiled. Since this table does not have any statistics I assume it is retrieved from sys.partitions but I am not sure.

    I thought that statistics were the only data volumetrics input for the exec plan. Is there any other source as well?

    The optimizer is not that simple tool to only use the statistics (and one view - sys.partitions) to build the data volumetrics for one execution plan for a query/sp/fn... It uses info of constraints, indexes, columns, and etc, as well as some internal intermediate calculations for the more complex queries.

    What are you trying to find out?

    If you replace your ttables with temp variable tables combining with EXISTS(), then it should be faster. You can try that.

    Regards

    IgorMi

    Igor Micev,My blog: www.igormicev.com

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply