Database Index Choices vs Throughput

Observations/Conclusions

1. There are some hard trade-offs here: Queue Performance, Delete Performance, Processing Performance, and Query Performance. According to the numbers, it appears a cluster key of ExternalDeviceId, CreatedDateTime, MessageId (Test 5) is the best choice, but not by a significant amount. Further testing will be required to see if this choice performs well enough when Querying for messages, since it has no other indexes and requires a clustered indexed range scan for every query.

2. Clustering by ExternalDeviceId, CreatedDateTime, MessageId results in the smallest database size of all the options because it has the fewest indexes.

3. We expected having a separate QueuedMessages table (to store new messages entering the system) would provided the best solution with respect to table locking during database maintenance (index rebuilds, etc), however; we did not see much improvement in this regard. Further testing on this solution should be investigated when we have more time.

4. The database log file was by far the hardest hit (by a factor of 3-4) from the 3 database files that make up the Queue database. We recommend separating the 3 database files on separate drives and IO channels with the LOG file being on the fastest disk. Additionally, backups should be performed to a separate disk so there is no disk contention during the backup.

5. There is a huge advantages from a scalability standpoint to having the database run on SSDs which is likely not possible running on vms in the data center. The machine running the database on SSDs exhibited none of the issues seen on the disk based solution with respect to timeouts during database maintenance.

6. The biggest outstanding risk is how the database performance over time with 100s of million records. On the disk based system we see dramatic performance degradation during database maintenance activities such as backup and index maintenance. The concern here is that if HL7 messages cannot be stored in a timely fashion that HL7 interfaces will timeout.

Test Scenario

1. Chicago Message set x 2 streams = 200K messages

2. ThroughputTest.cxn (attached) Database Index Choices vs Throughput

3. Pause all queues except the Branch Channel measure total time to Queue all messages (Queue Time)

4. Un-Pause all channels. Measure total time to Process all Messages (Processing Time)

5. Execute the delete command below, but WITHOUT the DROPCLEANBUFFERS call.

6. Query for Patient with ID = 105160 from UI

7. Execute the delete command below, but WITH the DROPCLEANBUFFERS call.

8. Query for Patient with ID = 105160 from UI

9. Clear Queues after each test using clear command below

10. Execute a database backup from the Client UI to clear log, etc

* Calculated from:

select DATEDIFF(ss,(select top 1 ModifiedDateTime from Message order by MessageId)
                                ,(select top 1 ModifiedDateTime from Message order by MessageId desc))

** Calculated from the following where {0} = the ExternalDeviceId of one of the non-branch devices found in SELECT * FROM MessageStatistic

DECLARE @nonBranchDeviceId int
set @nonBranchDeviceId = {0}
select DATEDIFF(ss,(select top 1 ModifiedDateTime from Message where ExternalDeviceId = 2 order by MessageId)
                                ,(select top 1 ModifiedDateTime from Message where ExternalDeviceId = 2 order by MessageId desc))

** Command for Delete:

DBCC DROPCLEANBUFFERS
SET STATISTICS IO ON

DECLARE @diff int,
        @startDate DateTime,
        @olderThan DateTime,
        @timer DateTime,
        @nonBranchDeviceId int

set @nonBranchDeviceId = {0}
select top 1 @startDate = CreatedDateTime from Message where ExternalDeviceId=@nonBranchDeviceId order by MessageId
select @diff = DATEDIFF(SECOND, @startDate, (select top 1 ModifiedDateTime from Message where ExternalDeviceId=@nonBranchDeviceId order by MessageId desc))

set @olderThan = DATEADD(SECOND, @diff/10, @startDate)
set @timer = SYSDATETIME()
delete TOP(5000) from Message where ExternalDeviceId = @nonBranchDeviceId AND QueueTypeCode != 'Q' AND ModifiedDateTime < @olderThan
PRINT 'Delete Time: ' + CAST(DATEDIFF(millisecond,@timer,SYSDATETIME()) as varchar(255))

*** Query command

DECLARE @timer DATETIME,
        @nonBranchDeviceId int

set @timer = SYSDATETIME()
set @nonBranchDeviceId = {0}

SELECT TOP(100) * from Message
    where ExternalDeviceId = @nonBranchDeviceId and PatientId = '105160' order by CreatedDateTime desc, MessageId desc
PRINT 'Query Time: ' + CAST(DATEDIFF(millisecond,@timer,SYSDATETIME()) as varchar(255))

*** Clear command

IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_Message_MessageData]') AND parent_object_id = OBJECT_ID(N'[dbo].[Message]'))
ALTER TABLE [dbo].[Message] DROP CONSTRAINT [FK_Message_MessageData]

TRUNCATE TABLE Message
TRUNCATE TABLE MessageData
TRUNCATE TABLE ProcessingHistory
TRUNCATE TABLE MessageStatistic
TRUNCATE TABLE MessageStatisticHistory
CHECKPOINT