执行计划不准确,该如何解决

执行计划不准确
http://social.msdn.microsoft.com/Forums/en/sqldatabaseengine/thread/35d3df7d-ff82-48a4-bfbc-ffb95e31017f
没人鸟我...来csdn发个..
多谢

I have a typical fact - dimensional data warehouse, and when I am querying data from it I see strange thing happens...

sample query(query 1):

SQL code

Select count(1) from fct_device  tWhere t.monitored_Id=2 and      t.data_time_id in (       [b] select data_time_id from dim_date t        where  data_date >= 28 and               not exists(select 1 from dim_date                          where data_date > t.data_date and                           data_year = t.data_year and                          data_month = t.data_month )[/b]        )

Tables:

  fct_device is partitioned by data_time id (int) and contains roughly 20,000,000 records.

  dim_date is a relatively small dimension table, containing ~30 records.

  If I only execute the sub-query(marked in bold) it returns 7 records(the sub-query is supposed to return all the end-of-month date)

Query 1 takes more than 1 min to complete. The execution plan for query 1:

 |--Compute Scalar(DEFINE:([Expr1010]=CONVERT_IMPLICIT(int,[Expr1013],0)))
  |--Stream Aggregate(DEFINE:([Expr1013]=Count(*)))
  |--Nested Loops(Left Anti Semi Join, WHERE:(dim_date.[DATA_DATE]>fct_device.[DATA_DATE] AND ....)
  |--Nested Loops(Inner Join, OUTER REFERENCES:([t].[DATA_TIME_ID]))
  | |--Clustered Index Scan(OBJECT:(dim_date. where data_date>=28)
  | |--Table Scan(OBJECT:(fct_device AS [t])....)
  |--Clustered Index Scan(dim_date)

The dim_date(predicate data_date>=28) first does Nested Loops join with the fact table, and then join with dim_date again(to do the Not Exists process). And seems it doesn't use the partition at all(no parallelism)... 

I initially believed query 1 will be optimized to execute the sub-query first and then do the other steps, and it will be equivalent to the query below:

(query 2)

SQL code

Select count(1) from fct_device  tWhere t.monitored_Id=2 and      t.data_time_id in (       20111031,20111130,20111231,20120131,20120229,20120331,20120430       )

 Plan for query 2:

  |--Compute Scalar(DEFINE:([Expr1004]=CONVERT_IMPLICIT(int,[globalagg1006],0)))
  |--Stream Aggregate(DEFINE:([globalagg1006]=SUM([partialagg1005])))
  |--Parallelism(Gather Streams)
  |--Stream Aggregate(DEFINE:([partialagg1005]=Count(*)))
  |--Table Scan(...)

Query 2 takes 15 sec to complete. You can see it takes the advantage of the partition table by running in parallelism.


After that I did another modification and it's the most weird part...

query 3:

SQL code

Select count(1) from fct_device  tWhere t.monitored_Id=2 and      t.data_time_id in (        select [b]Cast(data_time_id as char)[/b] from dim_date t        where  data_date >= 28 and               not exists(select 1 from dim_date                          where data_date > t.data_date and                           data_year = t.data_year and                          data_month = t.data_month )        )

The only change I made(marked in bold) is to cast the data_time_id to char in the sub-query first. I thought it would be a performance penalty because the data_time_id in both fact table(fct_device) and dimension table(dim_date) is INT type but I was wrong..

Query 3 takes less than 1 min(still more than what query 2 takes of cause but better than query 1)

Plan for query 3:

|--Compute Scalar(DEFINE:([Expr1011]=CONVERT_IMPLICIT(int,[globalagg1014],0)))
  |--Stream Aggregate(DEFINE:([globalagg1014]=SUM([partialagg1013])))
  |--Parallelism(Gather Streams)
  |--Stream Aggregate(DEFINE:([partialagg1013]=Count(*)))
  |--Hash Match(Right Semi Join, HASH:([Expr1012])=([t].[Data_time_Id]), RESIDUAL:(fct_device.[Data_time_Id]=[Expr1012]))
  |--Bitmap(HASH:([Expr1012]), DEFINE:([Bitmap1015]))
  | |--Parallelism(Distribute Streams, Broadcast Partitioning)
  | |--Nested Loops(Left Anti Semi Join, WHERE:(dim_date.[DATA_DATE]>fct_device.[DATA_DATE]...)
  | |--Compute Scalar(DEFINE:([Expr1012]=CONVERT_IMPLICIT(int,CONVERT(char(30)...)
  | | |--Clustered Index Scan(OBJECT:(dim_date where data_date>=(28))
  | |--Clustered Index Scan(OBJECT:(dim_date)
  |--Table Scan(OBJECT:(fct_device where monitor_id =2 and PROBE([Bitmap1015])

Seems the CONVERT makes the optimizer decide to execute the sub-query first, and apparently it made a very well decision..

I think the key is to take advantage of the partition table but I am not an expert.. So can someone please help me understand what's going on here it would be great!

thanks

p.s Where can I find some really good articles which can help me understand what execution plan shows(e.g. what is RESIDUAL in query plan)? many thanks

p.p.s

----update-----

The estimated execution gives the estimated cost:

Query 1 25% (actual: more than 1min)

Query 2 12% (actual: 15 sec)

Query 3 63%(actual: ~50 sec)

And I already updated the statistics for both tables before querying.

[解决办法]
那是MSDN，csdn请用中文描述问题，看到英语偶头疼
[解决办法]
满屏的英格里许哦，头痛啊

查看更多 下一篇