ICE Trade Capture Solution Blocks and Legs saving

Overview

If a message contains repeating groups for Blocks and Legs then saving this information to DataBase significantly slows down the solution. To increase the performance of the solution the possibility to disable saving 'Blocks' and 'Legs' to the DB in ICE TC solution was implemented. By default saving is turned on but a user can easily disable it.

The Feature is available since FIXEdge 6.9.0.

Design

Installation scripts contain a code to create the table "ICESettings", and populate it with several properties with the default values ("1" stands for "TRUE" meaning "saving is turned on").

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ICESettings]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[ICESettings](
	[Name] [varchar](32) NOT NULL,
	[Value] [bit] NOT NULL
) ON [PRIMARY];
INSERT INTO [dbo].[ICESettings] ([Name], [Value])
VALUES ('StoreSecurityBlockDetails', 1)
INSERT INTO [dbo].[ICESettings] ([Name], [Value])
VALUES ('StoreStrategyBlockDetails', 1)
INSERT INTO [dbo].[ICESettings] ([Name], [Value])
VALUES ('StoreStrategyLegs', 1)
END

The table created:

Stored procedures retrieve settings on each run with code like below:

DECLARE @StoreStrategyBlockDetails bit = 1, @StoreStrategyLegs bit = 1
SELECT  @StoreStrategyBlockDetails = s.[Value] FROM [ICESettings] AS s WHERE s.[Name] = 'StoreStrategyBlockDetails'
SELECT  @StoreStrategyLegs = s.[Value] FROM [ICESettings] AS s WHERE s.[Name] = 'StoreStrategyLegs'

User can change the default values by running SQL requests to change property values in "ICESettings" table or using Microsoft SQL Server Management Studio.

Performance testing

Performance measurement results (time to load SecDefs and UDS):

	enabled Blocks/Legs saving	disabled Blocks/Legs saving
first run (Empty DB)	16:45 min	7:26 min
second run (DB is already filled with the data from the first run)	12:02 min	5:42 min

Upgrade procedure

For full upgrade procedure follow the upgrade instruction.

To implement the new feature only do the following

Stop FIXEdge server
Replace all files in DB_script directory recurcively with the new ones from 6.9.0.* ICE TC package or later
run MSSQL_ICE_database.sql script to update database
Run MSSQL_ICESecDef.sql, MSSQL_ICEStrategies.sql, and MSSQL_ICETrdCapt.sql scripts to create Stored Procedures
Start FIXEdge server