I have a large fact table with millions of rows called MyLargeFactTable, and its a clustered column store table.
There is a composite primary key constraint on it there as well (customer_id,location_id,order_date columns).
I also have a temp table #my_keys_to_filter_MyLargeFactTable, with the very same 3 columns, and it contains few thousand UNIQUE combination of these 3 key values.
The following query gives me back the desired result set
... FROM #my_keys_to_filter_MyLargeFactTable AS t JOIN dbo.MyLargeFactTable AS m ON m.customer_id = t.customer_id AND m.location_id = t.location_id AND m.order_date = t.order_date
but i notice that the Index Scan Operator on the fact table returns more rows than it should (about a million) and feed it into a Filter operator, which further reduce the result set to the desired few thousand rows.
Index Scan operator reads way to much rows (they quite wide rows) increasing IO, and significantly slows down the whole query.
Are my parameters not sargable?
How could I remove the Filter operator and somehow force the Index Scan operator to read only the few thousand rows?
create table #my_keys_to_filter_MyLargeFactTable ( customer_id varchar(96) not null, location_id varchar(96) not null, order_date date not null, primary key clustered (customer_id,location_id,order_date) ) create table MyLargeFactTable ( customer_id varchar(96) not null, location_id varchar(96) not null, order_date date not null, ... lot of wide decimal typed columns, and even large varchars ... PRIMARY KEY NONCLUSTERED (customer_id,location_id,order_date), INDEX cci CLUSTERED COLUMNSTORE )