执行计划不准确

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):

Select count(1) from fct_device  t

Where t.monitored_Id=2 and

      t.data_time_id in (

        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 )

        )

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)

Select count(1) from fct_device  t

Where 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:

Select count(1) from fct_device  t

Where t.monitored_Id=2 and

      t.data_time_id in (

        select Cast(data_time_id as char) 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.