High performance count with where clause and dynamic data

Question

I am looking for a high-performance approach to solve this problem.

I have a table on a SQL-Server 2019 with 316 columns and several million rows.

For this query, there are only a few of those columns that are relevant. Something like a date-field and an amount-field (maybe the id-field as well).

Now I would like to know how many entries exist in a specific date range (from now, to X days in the past) that has an amount that is lower than Y.

My current approach (split the request into two requests):

1. Create a nonclustered index over the date-field & id-field.

2. Do a first select with a where-clause to get only entries in the relevant date range. (Select only id) Will this avoid a full-table scan?

3. Do a second select (count) with a where-clause over the ids (and maybe the amount-field). The id-field is also the primary key and clustered index.

At step three I am not really sure if I can put the amount-field into the where-clause without any performance loss. If that is not possible, maybe I should select only the ids and the amount-field and do the count in my application (in memory)? It would be great if it is possible to avoid this.

Also, I am not sure if it will be necessary to split the request at step three if I will have 1,000,000 Ids which will hit the condition from step two.

What do you think about my approach? Should I go for it, or is there a better one?

Answer 1

Create a composite index and use only one SELECT statement. That index would either be on (date, amount) or (amount, date). Which one you choose depends on your query and the selectivity for each column.

Imagine an old-school (physical) phone book. You find something by last name, and keep searching for the first name you want (Doe, John).

Sure, columnstore indexex can be useful, but that might also be overkill. There are specific performance concerns when you modify data frequently and there are no SEEKs in a columnstore index.

Answer 2

It sounds like you might benefit from a Columnstore Index. They're typically performant when you want to aggregate a smaller subset of fields from a table. (The efficiencies come in the compression they offer.) They can be the clustered or a nonclustered index.

You'll likely just need to create it on your Date and Amount field, and then you can do the following kind of query efficiently:

SELECT COUNT(1) AS ResultsCount
FROM Table
WHERE DateField > SomeDateValue AND DateField < SomeOtherDateValue
    AND AmountField < SomeAmount

I've had good experiences with them overall with tables in the 10s of millions of rows.

Another option, if you do stick to a rowstore based index instead of columnstore, then you should also look into 2019's new Batch Mode Execution Feature for rowstore data. That can possibly help you too.

Answer 3

Partition the table vertically. Those few high-use columns go in one table (let's call it table A). The other three hundred or so go in another table (table B). Both tables have the same primary key. There is a one-to-one relationship between these two tables. The two physical tables together make a single logical entity. They are separated only for performance reasons.

Each table can have its own indexes to support the specific access pattern. The full entity type can be retrieved by inner joining the two tables on the key. You many even define a view specifically for this.

This way a page from table A contains only values which are directly relevant to the query. None of the IO to read a page is wasted retrieving data from the other 300 columns; that data is now in table B.

This doesn't have the full read performance advantages of a columnstore index. Its compression is typically much better than page compression. There may be an advantage if you can create a clustered index for the specific workload, or if the data has point, rather than batch, updates.

Answer 4

You will need to look at the data repartition first. (Selectivity)

Let's say you're looking for rows where the amount is especially high or low.
If there are not that many rows that match, then an index on amount that includes the date column could do the job as SQL Server will only need to read the rows where the amount is high/low and hopefully that won't be that many rows. It will then look at the date field that is included to filter out unneeded rows.

If none of your columns are "selective" and if you end up reading way more rows than the one you need to count from the index "date/Amount" then splitting the query into 2 operations may be a good option (even if there is a bit of overhead to do so).

P.s. It may be a good idea to also create an index on the amount and ID columns so that SQL could read this smaller index instead of reading from the clustered index in your step 3.

If you want a more precise answer, please add to the execution plan of your original Count query