Estimated number of rows
in [Programming SQL]
|
From: Johnny Persson on 6 Aug 2010 08:16 Hi, I am working with a query which gives my inconsistent actual execution plan results. I have a query with a temporary table (#T) which is joined with some normal tables. Case 1 ------ The estimated number of rows is same as the real number of rows (23K) if I set a specific index on #T. The subtree cost is around 20. Case 2 ------ The estimated number of rows is close to zero (17) if I skip the very same index. The subtree cost is however around 1. ------ What does that indicate? Is it better to use a query which correspond to the real result - or should I use the query with the least estimated subtree cost? Thanks in advance. Regards, Johnny
From: Erland Sommarskog on 6 Aug 2010 17:20 Johnny Persson (a(a)a.a) writes: > I am working with a query which gives my inconsistent actual execution > plan results. > > I have a query with a temporary table (#T) which is joined with some > normal tables. > > Case 1 > ------ > The estimated number of rows is same as the real number of rows (23K) if > I set a specific index on #T. The subtree cost is around 20. > > Case 2 > ------ > The estimated number of rows is close to zero (17) if I skip the very > same index. The subtree cost is however around 1. > > ------ > What does that indicate? Is it better to use a query which correspond to > the real result - or should I use the query with the least estimated > subtree cost? The subtree cost is only an estimate, and in the latter case it's an estimate based on incorrect information. Adding indexes, of even better constraints on your temp tables is often a good idea, as this can help the optimizer to find a good plan. In this case, do you add the index when you have populated the table? When you add an index, the corresponding statistics are also computed. -- Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se Links for SQL Server Books Online: SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
From: Johnny Persson on 7 Aug 2010 05:52 Yes, I add the index when the table is populated. Thanks Erland, I will check out constraints! On 2010-08-06 23:20, Erland Sommarskog wrote: > Johnny Persson (a(a)a.a) writes: >> I am working with a query which gives my inconsistent actual execution >> plan results. >> >> I have a query with a temporary table (#T) which is joined with some >> normal tables. >> >> Case 1 >> ------ >> The estimated number of rows is same as the real number of rows (23K) if >> I set a specific index on #T. The subtree cost is around 20. >> >> Case 2 >> ------ >> The estimated number of rows is close to zero (17) if I skip the very >> same index. The subtree cost is however around 1. >> >> ------ >> What does that indicate? Is it better to use a query which correspond to >> the real result - or should I use the query with the least estimated >> subtree cost? > > The subtree cost is only an estimate, and in the latter case it's an > estimate based on incorrect information. > > Adding indexes, of even better constraints on your temp tables is > often a good idea, as this can help the optimizer to find a good > plan. > > In this case, do you add the index when you have populated the table? > When you add an index, the corresponding statistics are also computed. >
|
Pages: 1 Prev: Clustered and Keys Next: Estimated plan using view vs. guts of view |