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.