1

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?

2
  • Instead of "most performant", what would be "good enough" performance? Answer within 1 second, 100 ms less than 1 ms? Something else? How much data is there on a single row? What data types the columns of interest are? How often are you querying the data? What other operations are going on when you are querying?
    – vonPryz
    Dec 2 '20 at 12:11
  • Consider a non-clustered index on date and amount columns along with a single SELECT query.
    – Dan Guzman
    Dec 2 '20 at 12:15
2

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.

1

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.

4
  • thanks, I thought that a column store index only makes sense if the data in the column will be not super dynamic. So I thought it would not fit for amounts or dates. But in the next days, I will try it with some test-data and post the outcome.
    – Bambuk
    Dec 2 '20 at 13:00
  • I think from a compression standpoint, it's performance is maximized when the columns being indexed have a low level of uniqueness, but even so I've found columnstore indexes to work well for analytical queries on a very small subset of fields of a wider table. Probably partly because even something like a Date field will likely have less uniqueness (better compression) up and down it's column than it would at the row level when trying to compress that Date field left and right across other fields within the same row. But yes definitely worth testing between the types of indexes and comparing.
    – J.D.
    Dec 2 '20 at 15:28
  • 1
    If the data is dynamic in the sense that there are a lot of UPDATE statements a CCI can suffer. If you mean there are a lot of distinct value then a CCI copes fine. Dec 3 '20 at 12:11
  • Good point @MichaelGreen. Also I always strongly encourage testing in your own environment / context is the best way to get results relevant to your situation, since some of these guidelines are subjective and afterall are just guidelines. As the example Table with 10s of millions of rows in my answer was even updated fairly frequently (in my opinion) at a rate of about 100 records a minute, and we saw no issues with our columnstore indexes on it. To each their own case though.
    – J.D.
    Dec 3 '20 at 12:21
1

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.

0

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.