I think your approach with nested loops is suboptimal.
Why can't you do something like:
select * from sensors where deviceid =? AND pcode = ? AND rectime between ? and ?
This would return the whole dataset and you could process it locally.
Selecting 500 or even more rows in one correct select is better then 500 single row selects.
In this case I would ...
As I understand it, you created two non-clustered indexes and in order to cover the query, you have all the columns from the table in these indexed (the key is the key and the other columns are included columns).
If above is correct, you now have two more copies of the table table, sort of. I.e., three instances of the same data. You have the clustered index ...
You can get almost as good a plan with fewer indexes if you cluster tContactPhone by (ContactID,ID) instead of having a clustered index on ID and a seperate non-clustered index on ContactID. eg
CREATE TABLE tContactPhone(
[ContactID] [int] NOT NULL,
[ID] [int] IDENTITY(1,1) NOT NULL,
[Type] [nvarchar](25) NOT NULL,
[Number] [nvarchar](12) ...
First, and most prominently, the order of columns in your multicolumn index is backwards:
[SharedOrder] (order_id, shared_to_owner)
You need an index on "SharedOrder" (shared_to_owner, order_id) for this query. See:
Is a composite index also good for queries on the first field?
You may or may not need the one you have now (...
How many won claim do you have (as a percentage of the total claims)? If the percentage is lower than 1% (or if you have fewer than a thousand claims) maybe it would make sense to perform a Nested Loops join and index seeks in ContactID_Type for each contact. Else, hash joins (or merge joins) would probably be better (because using index seeks to read a ...
Included columns are more data that needs to be maintained in the index, so yes, there's likely at least a small additional overhead for INSERT operations with such a wide index.
Why not try creating your clustered index on (datetime, Column1), it doesn't have to be on your table's primary key. In fact, the primary key can have a nonclustered index on it ...
There are no such things as a seek in a columnstore index. There are only scans. The column order in the "key" you define for the index is totally irrelevant.
What SQL Server can do, though, is to eliminate rowgroups. When the columnstore index is built, SQL Server reads the data and builds the index 1 million rows at a time - this is called a ...
What are the data types of column4, column5, column6, and column7 and in general how unique are the values of each of those columns? Columnstore indexes might be useful for your use case if there's a high level of potential compression within in each column because there isn't a high variability in the values of each column. Often tends to be the case with ...
Consider trying to make these changes:
One row per minute.
The various metrics are in columns, not rows.
The Primary key is the time, truncated to the minute. (No AUTO_INCREMENT column is needed.)
INSERT .. ON DUPLICATE KEY UPDATE .. is used for each of the 4-6 updates, the first of which will be an insert. (That is, it does not matter which device gets ...