High number of estimated rows

N 2016-09-19 14:56:38

I have 4 tables with 52 million rows. All have clustered index on primary key and the key is (id,date,areaType).

When I try to join these tables with a temp table and query, I am getting index scan and the number of estimated rows is equal to total number of rows.

My temp table is t(id,date,areatype) and contains 14 million rows.

Below is the query:

Select A.,B., C.col1, d.Col2 from a
inner join t on a.id=t.id and a.date=t.date and a.areatype=t.areatype
inner join b on t.id=b.id and t.date=b.date and t.areaType=b.AreaType
inner join c on t.id=c.id and t.date=c.date and t.areaType=c.AreaType
inner join d on t.id=d.id and t.date=d.date and t.areatype=d.areatype

When I see the plan, then all the tables have clustered index scan and estimated number of rows as 52 million

The distinct area type are 4 only. But id and date are many and approx count for one areatype is 13 million.

Could you please help on what is the wrong in the query or index?

Aaron Bertrand 2016-09-19 14:58:40
Did you mean to upload a plan?
N 2016-09-19 15:02:45
I am afraid I cannot get the plan. Though can provide the details required from the plan. Thanks
Vlady Oselsky 2016-09-19 15:06:00
You can always Anonymize the plan before uploading if you don't want to expose schema and queries.
N 2016-09-19 15:08:03
ok… Let me try to create one at cloud sql server. will share the plan soon.
Greg S 2016-09-21 21:04:59
Are there any indexes or a primary key on the temp table? Also, how many rows are returned by your query? From your description, it sounds like there's no predicate so are you returning 14 million rows (the number of rows in the temp table)?