We want to study behavior of some similar entities that changes based on time.
Currently I'm using MS SQL Server 2016
CREATE TABLE [dbo].[BehaviorHistory]( [EntityId] [uniqueidentifier] NOT NULL, [BehaveFactor] [bigint] NOT NULL, [TimeOffset] [bigint] NOT NULL, CONSTRAINT [PK_BehaviorHistory] PRIMARY KEY CLUSTERED ( [EntityId] ASC, [BehaveFactor] ASC, [TimeOffset] ASC )
We want to see this similar entities how often repeat same behavior as our test subject
SELECT * FROM ( SELECT EntityId, BehaveFactor, (TmpOffset.PerviousMainTimeOffset - TmpOffset.CurrentMainTimeOffset) MainDeltaTime , (TmpOffset.PerviousSubTimeOffset - TmpOffset.CurrentSubTimeOffset) SubDeltaTime FROM ( SELECT LAG(main.TimeOffset) OVER (ORDER BY main.EntityId ,history.TimeOffset,testSub.TimeOffset) As PerviousMainTimeOffset, LAG(testSub.TimeOffset) OVER (ORDER BY main.EntityId ,history.TimeOffset,testSub.TimeOffset) As PerviousSubTimeOffset, main.EntityId AS EntityId, main.BehaveFactor as BehaveFactor, main.TimeOffset AS CurrentMainTimeOffset, testSub.TimeOffset AS CurrentSubTimeOffset FROM BehaviorHistory AS main INNER JOIN SampleBehaviorHistory AS testSub ON main.BehaveFactor = testSub.BehaveFactor ) as TmpOffset )AS MatchTable Where MainDeltaTime = SubDeltaTime --repeated behavior in same time order AND MainDeltaTime <> 0 --exclude repeated behavior at the same time
We currently inserting large amount of data into BehaviorHistory table its size is like 100 GB more with more than 3 Billions records in last 8 months or so.
The sample query performance is good (like 300ms) if we have index but when we create non clustered index on BehaveFactor (includes TimeOffset) the insert operation timeout on this table, so currently I have two DB one without index used for inserting data and another that every day get copy of inserted DB and create index on it and used for reports.
With no index its works fine, when we try to insert in a indexed table after a couples of bulk insert (eg 2000 records) we got this error
[fnInsertBehaviors(EntityId : 1705438)] # 1 : The underlying provider failed on Open. # 2 : Connection Timeout Expired. The timeout period elapsed while attempting to consume the pre-login handshake acknowledgement. This could be because the pre-login handshake failed or the server was unable to respond back in time. The duration spent while attempting to connect to this server was - [Pre-Login] initialization=58416; handshake=9; # 3 : The wait operation timed out #
Looking for solution to provide real time reports (not based on yesterday data) and avoid two DB and scheduled backup restore to create indexed table.
~600 records inserted per second in a certain period of time in a day.
With non clustered index, insert query timeout and without it performance is out of question. Insert fails in normal mode with no lock on table, normal insert fails when we have no query running against that table.
Also I need to consider growing database maybe next year its size is 300 GB so I have to find a solution that can handle that much data before it gets too late and system fails.