We have a table with a columnstore index with 300 million records and 40 fields.
Sometimes the same basic query takes 2 seconds or it takes 100 seconds.
All times from SSMS.
I selected a single record by two ID fields and return 4 fields. It took 2 seconds.
I returned all 40 fields, for this one record, and it took 100 seconds.
I ran the same query against a non-column store table. It took 2 seconds no matter how many fields I return.
I joined to the columnstore table and this selected 100 records, returning 10 fields for each one. This took 20 minutes. The same query on the non-columnstore table took 7 seconds.
I cannot work out why it sometimes takes so long and other times it is blazingly fast.
I have spent hours on this – the above are typical results. I have tried many combinations trying to figure out when columnstore is fast and when it is not.
We have looked at the different query plans and they are very different. But nothing there told us what we could do to force it to use the more efficient plan.
This table will eventually be queried by educated end users. We need a solution that they can be expected to do.