SQL Server 2016 Highly Efficient and Performant Database
Overview
Typical SQL Server relational database implementations that need to support mission critical workloads need to implement high availability (HA) and disaster recovery (DR) strategies with demanding high performance and low latency requirements. Additionally, there is an increasing need for real-time insights into the transactional data to support quick business decisions. However, the reporting and dashboarding workloads end up causing performance problems on the OLTP systems. To support both types of workloads, the database implementation extracts data out of the OLTP system and loads it into separate databases that supported operational reporting in real-time on the incoming transactional data. This increases complexity, costs and results in delays in the availability of data in the reporting and dashboards.
SQL Server 2016 supports a number of capabilities that enable real-time reporting and dashboarding on a high performance, low latency, HADR OLTP database. With these capabilities hosting service providers can offer a high performance database to their customers. This implementation leverages the following capabilities:
An In-Memory OLTP with AlwaysOn Availability Groups
Synchronous Replica in the primary datacenter for High Availability and Asynchronous Replicas in the secondary datacenter for disaster recovery
Read only load balancing to the read only secondary asynchronous replica databases
Rebalancing of load during failure conditions
Query Datastore for performance tuning and troubleshooting
ColumnStore Index on the asynchronous replicas to support high performance querying for reports and dashboards
DBA Considerations
For a Highly Efficient and Performant Database scenario, it is important to have an in depth understanding of:
- Customer's HADR strategy
- Performance tuning and optimization of queries of the relational structure of the database tables and how the data is being accessed for both the OLTP and the real time report and dashboard workloads
Highly Efficient and Performant Database Features
Once the access requirements have been finalized, the next step is to work with a Database Administrator to design and implement the final database deployment. The scenario detailed in this document leverages three key features of SQL Server 2016 which are listed below.
Always On Availability Groups
AlwaysOn Availability Groups is an enterprise-level high-availability and disaster recovery solution introduced in SQL Server 2012 to enable you to maximize availability for one or more user databases. AlwaysOn Availability Groups requires that the SQL Server instances reside on Windows Server Failover Clustering (WSFC) nodes.
For more information, see AlwaysOn Availability Groups (SQL Server).
In Memory Column Store Indexes
The columnstore index is the standard for storing and querying large data warehousing fact tables. It uses column-based data storage and query processing to achieve up to 10x query performance gains in your data warehouse over traditional row-oriented storage, and up to 10x data compression over the uncompressed data size. Beginning with SQL Server 2016, columnstore indexes enable operational analytics, the ability to run performant real-time analytics on a transactional workload.
For more information, see Columnstore Indexes Guide.
Example Scenario
Fabrikam Investments provide security portfolio management services for millions of customers in the US.
High performance - low-latency trading application and OLTP is hosted by HSP.
They have a need to develop real-time dashboards and reports harvesting data during the trading day showing:
- Most Active Stocks.
- Top and Bottom Movers
They have already implemented a HADR solution with both synchronous and asynchronous replicas in their datacenters.
HSP analyzes the workload and implements the reporting and dashboarding needs using the highly efficient and performant database offer.
Developing the Fabrikam Investments Deployment
The HSP analyzed the workload using the Query Store and determined that the database and tables will need to be reconstructed and the existing data migrated from the existing database.
- The new database will be created to include a memory optimized file group and container.
- The new database tables will be constructed with non-clustered in memory column store indexes.
For more information see: Monitoring Performance By Using the Query Store
Implementation Steps
Fabrikam Database Table Relations
The following diagram provides the foreign key relations in the Fabrikam Investments database tables.
Creating the Customer Database
USE [master]
GO
CREATE DATABASE [FabrikamInvestmentsDb]CONTAINMENT = NONE
ON PRIMARY
( NAME = N'FabrikamInvestmentsDb', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\FabrikamInvestmentsDb.mdf',
SIZE = 8192KB,
MAXSIZE = UNLIMITED,
FILEGROWTH = 65536KB
)
LOG ON
(
NAME = N'FabrikamInvestmentsDb_log',
FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\FabrikamInvestmentsDb_log.ldf',
SIZE = 8192KB, MAXSIZE = 2048GB, FILEGROWTH = 65536KB
)GO
-- Create the memory optimized Filegroup and Container
ALTER DATABASE FabrikamInvestmentsDb ADD
FILEGROUP FabrikamInvestmentsDb_mod CONTAINS MEMORY_OPTIMIZED_DATA
GO
ALTER DATABASE FabrikamInvestmentsDb ADD
FILE (name='FabrikamInvestments_mod1',
filename='C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\FabrikamInvestmentsDb_mod1')
TO FILEGROUP FabrikamInvestmentsDb_mod
GO
ALTER DATABASE FabrikamInvestmentsDb SET MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT=ON
GO
Adding the Fabrikam Investment Database to the Availability Group
Make a full backup of the FabrikamInvestmentsDb database
Click the OK button
Click the OK button
Add the database to the appropriate availability group
Click the next button
Select the FabrikamInvestmentsDb Database
Select Full Synchronization
Connect to all the SQL Servers
Connect using Windows Authentication
Click the Next button
Click the Next button
Click the Finish button
Wait for the Availability Group updates to complete
Verify all processing was successful
Configure the Read Only Routing for the Availability Group
The following statements will configure the load balancing as follows:
- Primary replica will be DATA1-SQL1
- Round robin the read only workload across DATA2-SQL1 and DATA2-SQL2
- If Site2-DC is down then send the read only workload to DATA1-SQL2
- If DATA1-SQL2 becomes the new primary replica, then send the read only workload to DATA1-SQL1
- If a failover occurs to Site2-DC and DATA2-SQL1 becomes the new primary replica then the read only workload should be sent to DATA1-SQL1 and DATA1-SQL2
- If Site1-DC is down then send the read only workload to DATA2-SQL2
- If DATA2-SQL2 becomes the new primary replica then send the read only workload to DATA2-SQL1
Create the Failover Load Balanced Routing Tables
USE master
GO
-- Set DATA1-SQL1 to allow read only connections
ALTER AVAILABILITY GROUP [TestAG]MODIFY REPLICA ON N'DATA1-SQL1'
WITH (SECONDARY_ROLE (ALLOW_CONNECTIONS = READ_ONLY));
-- Set DATA1-SQL1 readonly routing url
ALTER AVAILABILITY GROUP [TestAG]MODIFY REPLICA ON N'DATA1-SQL1'
WITH (SECONDARY_ROLE (READ_ONLY_ROUTING_URL = N'TCP://Data1.SQL1.Site1.net:5022'));
-- Set DATA1-SQL2 to allow read only connections
ALTER AVAILABILITY GROUP [TestAG]MODIFY REPLICA ON N'DATA1-SQL2'
WITH (SECONDARY_ROLE (ALLOW_CONNECTIONS = READ_ONLY));
-- Set DATA1-SQL2 readonly routing url ALTER AVAILABILITY GROUP [TestAG]MODIFY REPLICA ON N'DATA1-SQL2'
WITH (SECONDARY_ROLE (READ_ONLY_ROUTING_URL = N'TCP://Data1.SQL2.Site1.net:5022'));
-- Set DATA2-SQL1 to allow read only connections
ALTER AVAILABILITY GROUP [TestAG]MODIFY REPLICA ON N'DATA2-SQL1'
WITH (SECONDARY_ROLE (ALLOW_CONNECTIONS = READ_ONLY));
-- Set DATA2-SQL1 readonly routing url
ALTER AVAILABILITY GROUP [TestAG]MODIFY REPLICA ON N'DATA2-SQL1'
WITH (SECONDARY_ROLE (READ_ONLY_ROUTING_URL = N'TCP://Data2.SQL1.Site1.net:5022'));
-- Set DATA2-SQL2 to allow read only connections
ALTER AVAILABILITY GROUP [TestAG]MODIFY REPLICA ON N'DATA2-SQL2'
WITH (SECONDARY_ROLE (ALLOW_CONNECTIONS = READ_ONLY));
-- Set DATA2-SQL2 readonly routing url
ALTER AVAILABILITY GROUP [TestAG]MODIFY REPLICA ON N'DATA2-SQL2'
WITH (SECONDARY_ROLE (READ_ONLY_ROUTING_URL = N'TCP://Data2.SQL2.Site1.net:5022'));
-- Setup the routing --
-- If DATA1-SQL1 is the primary replica
ALTER AVAILABILITY GROUP [TestAG]MODIFY REPLICA ON N'DATA1-SQL1'
WITH (PRIMARY_ROLE (READ_ONLY_ROUTING_LIST=(('DATA2-SQL1','DATA2-SQL2'),'DATA1-SQL2')));
-- If DATA1-SQL2 is the primary replica
ALTER AVAILABILITY GROUP [TestAG]MODIFY REPLICA ON N'DATA1-SQL2'
WITH (PRIMARY_ROLE (READ_ONLY_ROUTING_LIST=(('DATA2-SQL1','DATA2-SQL2'),'DATA1-SQL1')));
-- If DATA2-SQL1 is the primary replica
ALTER AVAILABILITY GROUP [TestAG]MODIFY REPLICA ON N'DATA2-SQL1'
WITH (PRIMARY_ROLE (READ_ONLY_ROUTING_LIST=(('DATA1-SQL1','DATA1-SQL2'),'DATA2-SQL2')));
-- If DATA2-SQL2 is the primary replica
ALTER AVAILABILITY GROUP [TestAG]MODIFY REPLICA ON N'DATA2-SQL2'
WITH (PRIMARY_ROLE (READ_ONLY_ROUTING_LIST=(('DATA1-SQL1','DATA1-SQL2'),'DATA2-SQL1')));
GO
Creating the Sample Investment Tables
USE [FabrikamInvestmentsDb]GO
-----------------------------------------------------------------------------------------
-- The Order Type table contains the types of orders as an integer (Buy and Sell).
-----------------------------------------------------------------------------------------
CREATE TABLE [dbo].[OrderType]
(
[ID] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY NONCLUSTERED HASH WITH (BUCKET_COUNT=1000000), [Name] [varchar](10) ) WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA
);
-----------------------------------------------------------------------------------------
-- The Risk Type table contains risk type of an account (LOW, MEDIUM or HIGH).
-----------------------------------------------------------------------------------------
CREATE TABLE [dbo].[RiskType](
[ID] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY NONCLUSTERED HASH WITH (BUCKET_COUNT=1000000), [Name] [varchar](10))
WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA);
-----------------------------------------------------------------------------------------
-- The Account Manager table contains information relating to an account manager.
-----------------------------------------------------------------------------------------
CREATE TABLE [dbo].[AccountManager](
[ID] [bigint] IDENTITY(1,1) NOT NULL PRIMARY KEY NONCLUSTERED HASH WITH (BUCKET_COUNT=1000000), [FirstName] [varchar](20) NOT NULL, [LastName] [varchar](20) NOT NULL, [RiskSpecialty] [int] NOT NULL CONSTRAINT FK_AccountManager_RiskType FOREIGN KEY REFERENCES RiskType (ID))
WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA);
GO
-----------------------------------------------------------------------------------------
-- The Customer Account table contains information relating to a customer.
-----------------------------------------------------------------------------------------
CREATE TABLE [dbo].[CustomerAccount](
[ID] [bigint] IDENTITY(1,1) NOT NULL PRIMARY KEY NONCLUSTERED HASH WITH (BUCKET_COUNT=1000000), [FirstName] [varchar](20) NOT NULL, [LastName] [varchar](20) NOT NULL, [Email] [varchar](50) NOT NULL,
[Address] [varchar](50) NOT NULL, [City] [varchar](20) NOT NULL,
[State] [varchar](2) NOT NULL, [Zip] [varchar](10) NOT NULL, [Phone] [varchar](20) NOT NULL
)
WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA);
GO
-----------------------------------------------------------------------------------------
-- The Managed Account contains information related to a managed account.
-----------------------------------------------------------------------------------------
CREATE TABLE [dbo].[ManagedAccount](
[ID] [bigint] IDENTITY(1,1) NOT NULL PRIMARY KEY NONCLUSTERED HASH WITH (BUCKET_COUNT=1000000), [Name] [varchar](10), [RiskLevel] [int] NOT NULL CONSTRAINT FK_ManagedAccount_RiskType FOREIGN KEY REFERENCES RiskType (ID), [CashAvailable] [money] DEFAULT('0.00'))
WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA);
GO
-----------------------------------------------------------------------------------------
-- The Managed Account Open Orders table contains all open orders and the status
-- (Completed).
-----------------------------------------------------------------------------------------
CREATE TABLE [dbo].[ManagedAccountOpenOrders]( [ID] [bigint] IDENTITY(1,1) NOT NULL PRIMARY KEY NONCLUSTERED HASH WITH (BUCKET_COUNT=1000000), [ManagedAccountID] [bigint] NOT NULL CONSTRAINT FK_ManagedAccountOpenOrders_ManagedAccount FOREIGN KEY REFERENCES ManagedAccount (ID), [OrderType] [int] NOT NULL,
[CompanySymbol] [varchar](10) NOT NULL, [Shares] [int] NOT NULL, [Price] [money] NOT NULL, [Completed] [bit] DEFAULT(0) NOT NULL, [CreatedDate] [datetime] NOT NULL DEFAULT(getdate()))
WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA);
-----------------------------------------------------------------------------------------
-- The Managed Account Completed Transactions contain information from the external
-- exchange relating to all orders that have been completed on the exchange.
-----------------------------------------------------------------------------------------
CREATE TABLE [dbo].[ManagedAccountCompletedTransactions](
[ID] [bigint] IDENTITY(1,1) NOT NULL PRIMARY KEY NONCLUSTERED HASH WITH (BUCKET_COUNT=1000000), [OpenOrderID] [bigint] NOT NULL CONSTRAINT FK_ManagedAccountCompletedTransactions_ManagedAccountOpenOrders FOREIGN KEY REFERENCES ManagedAccountOpenOrders (ID), [CompanySymbol] [varchar](10) NOT NULL, [Shares] [int] NOT NULL,
[Price] [money] NOT NULL, [CreatedDate] [datetime] NOT NULL DEFAULT(getdate())
)
WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA);
-----------------------------------------------------------------------------------------
-- The Customer Account Managed Account contains a relation between the customer account
-- and the managed accounts. A customer may have multiple managed accounts.
-----------------------------------------------------------------------------------------
CREATE TABLE [dbo].[CustomerAccountManagedAccount](
[ID] [bigint] IDENTITY(1,1) NOT NULL PRIMARY KEY NONCLUSTERED HASH WITH (BUCKET_COUNT=1000000), [CustomerAccountID] [bigint] NOT NULL CONSTRAINT FK_CustomerAccountManagedAccount_CustomerAccount FOREIGN KEY REFERENCES CustomerAccount (ID), [ManagedAccountID] [bigint] NOT NULL CONSTRAINT FK_CustomerAccountManagedAccount_ManagedAccount FOREIGN KEY REFERENCES ManagedAccount (ID), [AccountManagerID] [bigint] NOT NULL CONSTRAINT FK_CustomerAccountManagedAccount_AccountManager FOREIGN KEY REFERENCES AccountManager (ID),)
WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA);
GO
-----------------------------------------------------------------------------------------
-- The Managed Account Positions contains all stock positions held in a managed account.
-----------------------------------------------------------------------------------------
CREATE TABLE [dbo].[ManagedAccountPositions]( [ID] [bigint] IDENTITY(1,1) NOT NULL PRIMARY KEY NONCLUSTERED HASH WITH (BUCKET_COUNT=1000000), [ManagedAccountID] [bigint] NOT NULL CONSTRAINT FK_ManagedAccountPositions_ManagedAccount FOREIGN KEY REFERENCES ManagedAccount (ID), [CompanySymbol] [varchar](10) NOT NULL, [Shares] [int] NOT NULL, [Date] [datetime] NOT NULL
)
WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA);
GO
Populating the Investment Tables with sample data
The embedded file below contains all table data insert statements used for this scenario while debugging the new database deployment.
USE [FabrikamInvestmentsDb] GO INSERT INTO [dbo].[OrderType] ([Name]) VALUES ('BUY'), ('SELL') GO INSERT INTO [dbo].[RiskType] ([Name]) VALUES ('LOW'), ('MEDIUM'), ('HIGH') GO INSERT INTO [dbo].[AccountManager] ([FirstName],[LastName],[RiskSpecialty]) VALUES ('John', 'Doe', 1), ('Steve', 'Smith', 2), ('Jane', 'Doe', 3) GO INSERT INTO [dbo].[CustomerAccount] ([FirstName],[LastName],[Email],[Address],[City],[State],[Zip],[Phone]) VALUES ('Peter', 'James', 'pjames@hotmail.com', '111 Newport Way', 'Nowhere', 'NV', '99999', '555-555-5555'), ('David', 'Smith', 'davidsmith@hotmail.com', '101 Bellevue Way', 'Bellevue', 'WA', '99999', '444-444-4444'), ('Daniel', 'Peterson', 'danp@hotmail.com', '2751 North 1250 East', 'Denver', 'CO', '99999', '333-333-3333') GO INSERT INTO [dbo].[ManagedAccount] ([Name],[RiskLevel],[CashAvailable]) VALUES ('401K', 3, '1050000.00'), ('Personal', 2, '1100000.00'), ('IRA', 1, '2030000.00') GO INSERT INTO [dbo].[CustomerAccountManagedAccount] ([CustomerAccountID],[ManagedAccountID],[AccountManagerID]) VALUES (1,1,3), (2,2,2), (3,3,1) GO INSERT INTO [dbo].[ManagedAccountPositions] ([ManagedAccountID],[CompanySymbol],[Shares],[Date]) VALUES (1,'AAA',1000,getdate()), (1,'AAB',1000,getdate()), (1,'AAC',1000,getdate()), (1,'AAD',1000,getdate()), (1,'AAE',1000,getdate()), (1,'AAF',1000,getdate()), (1,'AAG',1000,getdate()), (1,'AAH',1000,getdate()), (1,'AAI',1000,getdate()), (1,'AAJ',1000,getdate()), (1,'AAK',1000,getdate()), (1,'AAL',1000,getdate()), (1,'AAM',1000,getdate()), (1,'BBB',1000,getdate()), (1,'BBC',1000,getdate()), (1,'BBD',1000,getdate()), (1,'BBE',1000,getdate()), (1,'BBF',1000,getdate()), (1,'BBG',1000,getdate()), (1,'BBH',1000,getdate()), (1,'BBI',1000,getdate()), (1,'BBJ',1000,getdate()), (1,'BBK',1000,getdate()), (1,'BBL',1000,getdate()), (2,'AAA',1000,getdate()), (2,'AAB',1000,getdate()), (2,'AAC',1000,getdate()), (2,'AAD',1000,getdate()), (2,'AAE',1000,getdate()), (2,'AAF',1000,getdate()), (2,'AAG',1000,getdate()), (2,'AAH',1000,getdate()), (2,'AAI',1000,getdate()), (2,'AAJ',1000,getdate()), (2,'AAK',1000,getdate()), (2,'AAL',1000,getdate()), (2,'AAM',1000,getdate()), (2,'BBB',1000,getdate()), (2,'BBC',1000,getdate()), (2,'BBD',1000,getdate()), (2,'BBE',1000,getdate()), (2,'BBF',1000,getdate()), (2,'BBG',1000,getdate()), (2,'BBH',1000,getdate()), (2,'BBI',1000,getdate()), (2,'BBJ',1000,getdate()), (2,'BBK',1000,getdate()), (2,'BBL',1000,getdate()), (3,'AAA',1000,getdate()), (3,'AAB',1000,getdate()), (3,'AAC',1000,getdate()), (3,'AAD',1000,getdate()), (3,'AAE',1000,getdate()), (3,'AAF',1000,getdate()), (3,'AAG',1000,getdate()), (3,'AAH',1000,getdate()), (3,'AAI',1000,getdate()), (3,'AAJ',1000,getdate()), (3,'AAK',1000,getdate()), (3,'AAL',1000,getdate()), (3,'AAM',1000,getdate()), (3,'BBB',1000,getdate()), (3,'BBC',1000,getdate()), (3,'BBD',1000,getdate()), (3,'BBE',1000,getdate()), (3,'BBF',1000,getdate()), (3,'BBG',1000,getdate()), (3,'BBH',1000,getdate()), (3,'BBI',1000,getdate()), (3,'BBJ',1000,getdate()), (3,'BBK',1000,getdate()), (3,'BBL',1000,getdate()) GO INSERT INTO [dbo].[ManagedAccountOpenOrders] ([ManagedAccountID],[OrderType],[CompanySymbol],[Shares],[Price]) VALUES (3,2,'BBF',100,1005.3), (3,2,'AAH',100,230.4), (3,2,'AAK',100,195.9), (2,2,'BBI',100,808.5), (2,2,'AAB',100,381.9), (3,1,'AAC',100,813.1), (1,1,'BBJ',100,283), (1,2,'BBG',100,827.3), (3,2,'AAM',100,196.7), (2,1,'AAK',100,177.5), (2,2,'AAK',100,173.6), (2,1,'AAC',100,845.3), (3,2,'AAJ',100,221), (1,2,'AAK',100,151.2), (3,2,'AAF',100,601.7), (3,2,'BBL',100,503.9), (2,2,'AAJ',100,216.8), (3,2,'BBL',100,539.9), (1,1,'BBE',100,389.1), (3,1,'AAG',100,1060), (3,1,'AAD',100,149.4), (1,2,'AAA',100,323.6), (1,1,'BBI',100,729.3), (2,2,'AAI',100,741.1), (3,2,'BBL',100,515.1), (3,1,'AAD',100,117.6), (1,2,'BBB',100,782.9), (3,2,'AAG',100,1022.4), (1,2,'BBB',100,779), (2,2,'AAI',100,749.1), (2,1,'BBK',100,1072), (1,1,'AAE',100,793.1), (1,2,'BBI',100,788.9), (2,1,'AAA',100,264.4), (3,2,'BBC',100,984.6), (1,1,'AAM',100,255.2), (2,2,'AAA',100,253.4), (3,2,'BBL',100,529.5), (1,2,'AAM',100,194.1), (2,2,'BBL',100,483.6), (2,2,'AAF',100,574.3), (2,2,'AAJ',100,149.1), (1,2,'AAG',100,1026.7), (2,1,'BBE',100,378), (2,1,'AAA',100,301.7), (1,2,'AAE',100,796.8), (3,1,'BBL',100,450.7), (1,2,'BBF',100,1046.1), (1,2,'BBB',100,782.8), (2,1,'AAB',100,387.9), (2,1,'AAE',100,817.8), (2,1,'BBG',100,856.6), (3,1,'BBG',100,863.1), (2,1,'BBJ',100,336.3), (3,1,'AAG',100,1055.9), (2,1,'BBC',100,981.5), (3,2,'AAL',100,279.4), (1,2,'BBC',100,1028.1), (3,1,'AAC',100,805.6), (3,1,'AAI',100,787.7), (2,1,'AAE',100,734.6), (2,1,'BBB',100,813.1), (1,1,'BBH',100,605.3), (2,2,'AAE',100,819.5), (1,2,'AAB',100,394.8), (1,1,'AAC',100,863.2), (1,1,'AAE',100,817.2), (1,2,'AAB',100,392.9), (2,1,'AAF',100,526.1), (2,1,'AAC',100,801.2), (1,2,'AAD',100,157.2), (2,2,'BBJ',100,281.7), (3,1,'AAF',100,601.5), (2,1,'BBF',100,1052.1), (2,1,'BBH',100,607.7), (2,1,'BBC',100,990.2), (1,2,'AAA',100,273.3), (2,2,'BBH',100,599.2), (3,2,'AAE',100,773.4), (2,2,'AAD',100,111.1), (3,2,'BBH',100,584.1), (1,2,'BBF',100,1074.8), (3,1,'AAL',100,299), (2,2,'AAE',100,773.5), (3,2,'AAC',100,809.4), (3,2,'BBE',100,390.2), (2,1,'AAE',100,802.8), (2,2,'BBD',100,606.1), (3,2,'BBL',100,479), (2,1,'BBB',100,765.4), (2,2,'BBB',100,826.2), (1,2,'BBH',100,614.9), (3,2,'BBF',100,1068.8), (1,1,'AAC',100,836.3), (1,2,'AAE',100,763.3), (1,2,'AAH',100,226.4), (1,2,'AAF',100,600), (3,1,'AAM',100,238.5), (1,1,'AAE',100,818.7), (2,2,'BBK',100,1079.6), (3,2,'BBD',100,675.6), (2,2,'BBF',100,1024.6), (3,2,'AAC',100,826.2), (2,1,'AAB',100,331.9), (1,1,'BBB',100,781.6), (1,2,'AAI',100,814.5), (3,2,'AAK',100,213.4), (1,1,'BBJ',100,309.5), (1,2,'AAL',100,303.4), (2,1,'AAF',100,578.8), (2,2,'AAI',100,810.8), (1,2,'AAG',100,1050.6), (2,1,'BBH',100,596), (2,1,'AAK',100,145.1), (3,1,'BBB',100,808.5), (2,1,'AAC',100,812.8), (2,1,'AAE',100,757.7), (2,2,'AAI',100,811.5), (2,1,'BBB',100,830.3), (1,1,'AAE',100,814), (3,1,'BBH',100,580), (3,1,'AAI',100,819.5), (2,2,'AAJ',100,174.5), (3,2,'BBG',100,784.8), (1,2,'BBG',100,819.6), (3,1,'AAI',100,743.6), (2,2,'BBE',100,341.1), (2,2,'AAJ',100,166.8), (2,1,'BBL',100,466.6), (2,2,'BBI',100,756.4), (1,2,'AAK',100,162.7), (1,2,'AAK',100,193.8), (1,2,'AAK',100,155.5), (2,1,'BBJ',100,334.6), (2,2,'BBH',100,598.1), (2,1,'BBJ',100,353.4), (2,1,'AAG',100,1010.2), (2,2,'BBG',100,798.6), (2,1,'BBJ',100,297.9), (3,1,'BBK',100,1036.5), (1,1,'BBE',100,396.9), (1,2,'BBG',100,819.7), (2,1,'AAL',100,333.6), (1,2,'AAI',100,795.4), (3,2,'AAK',100,147.1), (1,2,'AAH',100,303.6), (3,2,'AAG',100,995.3), (2,2,'AAI',100,783.4), (2,1,'BBF',100,1094.9), (2,1,'AAF',100,529.4), (2,1,'AAI',100,802), (3,2,'AAG',100,978.6), (1,1,'AAD',100,186), (1,1,'AAL',100,278.2), (3,1,'BBB',100,832.2), (1,2,'AAL',100,348.3), (3,1,'AAK',100,197.3), (3,2,'BBK',100,1041), (1,1,'BBF',100,1067.9), (1,1,'AAG',100,986.8), (2,2,'BBK',100,1055), (1,2,'AAE',100,781.2), (3,2,'AAG',100,993.7), (3,2,'AAC',100,792.6), (1,1,'AAB',100,347.4), (1,1,'AAM',100,197.6), (3,1,'AAI',100,783.5), (3,1,'AAF',100,571), (3,1,'AAC',100,874.8), (2,1,'AAJ',100,191.2), (3,2,'BBJ',100,354.8), (3,1,'AAG',100,985.8), (1,1,'AAL',100,328.5), (3,1,'BBG',100,821.5), (1,1,'BBC',100,1052.4), (3,2,'BBC',100,1024.1), (1,1,'AAH',100,248.4), (1,2,'AAD',100,182.4), (1,1,'BBL',100,525.5), (3,1,'BBJ',100,323.3), (1,1,'BBJ',100,356.1), (1,1,'AAE',100,805.7), (1,2,'BBD',100,640.8), (3,1,'BBL',100,458.1), (3,2,'AAA',100,296.8), (2,2,'BBL',100,481), (1,1,'AAM',100,263.8), (1,2,'AAD',100,131.6), (3,2,'BBG',100,861.3), (1,1,'AAI',100,770.5), (2,2,'BBK',100,1017.2), (2,2,'AAC',100,880.3), (2,1,'BBH',100,620.6), (1,1,'BBL',100,461.8), (1,1,'BBE',100,380.6), (3,1,'AAM',100,224.2), (1,2,'BBE',100,328.8), (2,1,'BBB',100,820.2), (1,2,'BBI',100,764.1), (3,1,'BBD',100,610.8), (2,1,'BBK',100,1038.1), (1,1,'BBK',100,1066), (2,1,'AAG',100,1030.6), (3,1,'AAG',100,1007.1), (3,1,'AAB',100,342.1), (3,2,'AAM',100,243.1), (2,1,'AAB',100,359.4), (2,2,'AAM',100,221.7), (3,2,'BBD',100,616.9), (1,2,'AAH',100,238.2), (3,1,'AAE',100,782.7), (2,2,'BBJ',100,361.8), (3,1,'BBK',100,1056), (1,1,'AAK',100,145.6), (1,2,'AAF',100,541.5), (2,1,'AAA',100,310.9), (1,1,'AAF',100,535), (3,2,'AAJ',100,142), (1,1,'AAD',100,152.6), (3,1,'AAM',100,270), (1,2,'AAK',100,153.9), (2,1,'AAD',100,138.9), (1,1,'AAK',100,191.6), (3,1,'BBC',100,1017.2), (1,1,'AAK',100,179), (2,1,'AAL',100,352.1), (1,2,'BBH',100,633.8), (3,1,'AAD',100,154.6), (2,2,'AAH',100,281.1), (1,2,'AAF',100,565.9), (2,2,'AAB',100,358.2), (3,1,'AAF',100,577.6), (1,2,'AAG',100,1012.5), (1,2,'BBJ',100,346.4), (2,2,'AAF',100,537.2), (1,1,'BBC',100,989.7), (2,1,'BBJ',100,295.2), (1,2,'AAL',100,277.5), (1,2,'BBE',100,386.9), (3,2,'BBD',100,688.7), (3,1,'BBE',100,392.4), (2,1,'AAD',100,152.9), (2,2,'AAM',100,195.1), (2,1,'BBI',100,792.9), (1,1,'BBJ',100,351.7), (3,2,'AAL',100,287.9), (3,2,'BBE',100,350.3), (2,1,'AAF',100,548.5), (3,1,'AAG',100,1057.1), (2,1,'AAD',100,117.8), (3,1,'AAM',100,210.2), (2,2,'BBJ',100,286.1), (2,2,'AAE',100,748.6), (1,1,'BBC',100,1009.3), (3,2,'AAF',100,591.9), (3,2,'AAK',100,132.9), (3,2,'AAF',100,608.2), (3,2,'AAH',100,229.5), (3,2,'AAM',100,257.1), (1,1,'AAL',100,304.7), (2,2,'AAA',100,247.9), (2,1,'AAE',100,815.9), (1,1,'AAG',100,1045.5), (2,1,'AAB',100,378.2), (2,2,'BBF',100,1073.5), (1,1,'AAK',100,213.6), (2,2,'AAH',100,273.4), (2,1,'AAA',100,315), (2,1,'AAK',100,219.4), (1,2,'AAG',100,1065.8), (1,2,'BBK',100,1052.6), (3,1,'AAG',100,986.4), (2,1,'BBH',100,599.4), (1,1,'AAA',100,295.5), (3,1,'AAF',100,522.4), (3,1,'AAE',100,804.2), (1,2,'BBC',100,986.8), (2,1,'BBC',100,1055.9), (2,1,'AAJ',100,195.3), (2,1,'AAJ',100,166.8), (2,1,'BBE',100,346.7), (3,2,'AAC',100,819.1), (3,2,'AAB',100,375.3), (3,1,'BBB',100,812.6), (2,2,'BBF',100,1025.2), (3,1,'AAH',100,249.9), (2,1,'BBB',100,802.8), (3,1,'BBH',100,662.8), (2,1,'AAC',100,879.7), (2,2,'AAD',100,124.2), (1,1,'AAG',100,1000.2), (3,1,'AAC',100,803.8), (2,2,'BBB',100,784.4), (3,1,'BBL',100,497.1), (2,1,'AAK',100,211.2), (1,1,'BBF',100,1095), (2,1,'AAB',100,385.2), (3,2,'AAC',100,834.3), (1,1,'BBJ',100,362), (1,1,'AAI',100,816.7), (2,2,'AAB',100,377.1), (1,1,'AAF',100,583.4), (3,1,'AAE',100,760.6), (3,2,'BBJ',100,357.4), (2,2,'BBD',100,657.5), (1,2,'AAA',100,261.3), (3,1,'BBG',100,860.7), (1,1,'BBC',100,1025.6), (1,1,'AAD',100,150.1), (1,2,'BBJ',100,365.3), (3,1,'BBI',100,803.3), (1,2,'BBH',100,667.4), (2,2,'AAJ',100,164.8), (2,1,'AAA',100,283.6), (3,2,'BBC',100,1052.3), (2,1,'AAD',100,173.6), (2,1,'AAJ',100,162.6), (1,1,'BBC',100,987.4), (1,1,'BBI',100,764.6), (3,2,'AAA',100,288.7), (3,2,'AAB',100,365.3), (2,1,'AAF',100,575.1), (1,2,'BBH',100,646.8), (1,1,'BBG',100,858.8), (3,1,'BBH',100,643.1), (2,1,'BBB',100,794.3), (1,2,'BBJ',100,354), (2,1,'AAD',100,176.3), (2,1,'AAA',100,304.4), (3,2,'BBE',100,310.7), (1,1,'AAC',100,799.1), (1,2,'AAH',100,288.1), (2,2,'AAL',100,274.9), (1,1,'AAF',100,592.1), (1,1,'AAD',100,180.5), (1,2,'BBF',100,1070.2), (1,2,'BBI',100,729.3), (1,2,'AAA',100,308.2), (2,2,'AAL',100,362.3), (1,1,'AAA',100,251.5), (1,2,'AAA',100,284.4), (1,2,'AAJ',100,205.7), (3,1,'BBB',100,808.3), (2,2,'AAJ',100,132.4), (3,2,'AAB',100,350.9), (3,2,'AAK',100,153.9), (1,2,'AAI',100,805.7), (1,1,'AAH',100,298.8), (1,1,'BBG',100,835.6), (1,1,'AAI',100,753.1), (2,2,'BBI',100,788.2), (1,2,'AAE',100,776.5), (3,1,'BBG',100,790.8), (1,2,'AAD',100,154.3), (2,1,'AAG',100,1015.2), (3,1,'BBK',100,1007.9), (1,2,'AAI',100,817.8), (2,2,'AAK',100,172.1), (3,1,'AAI',100,750.4), (1,1,'BBL',100,470), (3,1,'AAI',100,761.9), (1,1,'AAM',100,197.8), (3,1,'BBE',100,398.2), (2,1,'BBG',100,820.5), (3,1,'AAD',100,191.3), (2,1,'BBL',100,461), (3,2,'BBC',100,1057), (1,2,'AAK',100,181.4), (2,2,'AAM',100,242.9), (2,2,'AAI',100,753.4), (1,1,'BBL',100,512.6), (2,2,'BBI',100,787.2), (3,1,'AAG',100,996.3), (3,1,'AAC',100,821.2), (2,2,'AAE',100,736.7), (3,2,'BBL',100,510.1), (2,1,'BBE',100,317.2), (3,1,'BBF',100,1074.4), (1,2,'BBD',100,653.5), (1,2,'BBL',100,492.3), (1,2,'BBE',100,360.2), (1,1,'AAG',100,979.2), (2,2,'AAJ',100,144.8), (2,2,'AAK',100,169.7), (1,1,'BBK',100,1039.2), (1,1,'BBK',100,1061.8), (2,1,'BBI',100,784.9), (1,2,'AAI',100,782.7), (3,2,'AAF',100,553.2), (1,2,'BBE',100,378.1), (1,1,'AAE',100,804.5), (2,1,'AAA',100,295), (2,1,'BBE',100,355.3), (1,2,'AAC',100,859.8), (2,1,'AAF',100,588.9), (3,2,'AAE',100,758), (1,2,'BBF',100,1079.6), (1,2,'AAJ',100,140.6), (3,1,'BBE',100,318.9), (1,1,'AAH',100,242.1), (1,2,'BBK',100,1032.5), (1,2,'AAA',100,265.9), (3,2,'BBK',100,1024.5), (2,1,'BBE',100,343.4), (3,2,'BBE',100,355.5), (1,1,'AAM',100,186.2), (2,1,'BBB',100,786.4), (1,1,'AAH',100,291.1), (3,1,'BBJ',100,315.4), (2,2,'BBJ',100,345.5), (2,1,'AAB',100,405), (2,1,'AAE',100,746.3), (2,2,'AAJ',100,216.9), (3,2,'BBK',100,1071.1), (1,1,'AAL',100,331.8), (3,1,'BBE',100,333.4), (2,1,'BBB',100,852.2), (1,1,'AAI',100,784.2), (1,1,'BBG',100,788.4), (3,2,'AAI',100,760.5), (1,2,'BBG',100,826.9), (2,1,'BBJ',100,316.7), (2,1,'AAD',100,130), (1,1,'BBF',100,1083.9), (2,1,'BBB',100,798.8), (2,1,'AAC',100,803.8), (1,1,'AAK',100,142.4), (2,2,'AAB',100,362.7), (2,1,'AAM',100,238.1), (3,1,'AAE',100,801.2), (2,1,'AAK',100,205.2), (2,2,'AAH',100,313.5), (1,2,'BBD',100,665.6), (3,2,'AAF',100,550.2), (1,1,'AAI',100,751.1), (1,1,'AAD',100,142.3), (1,2,'BBC',100,1044.1), (1,1,'AAE',100,800.8), (2,1,'BBL',100,533.8), (1,1,'AAC',100,821.5), (2,2,'BBK',100,1037.7), (1,1,'AAK',100,209.1), (2,2,'AAF',100,529.7), (1,2,'AAM',100,238.2), (3,2,'AAL',100,349.7), (2,1,'BBL',100,508.8), (1,1,'AAF',100,574.9), (2,2,'BBJ',100,348.4), (1,1,'BBL',100,461.6), (2,1,'AAG',100,1020.1), (1,1,'AAE',100,758), (3,1,'BBD',100,631), (3,1,'AAJ',100,135), (3,1,'AAC',100,864.3), (1,1,'AAE',100,796.5), (2,2,'AAB',100,397.1), (3,1,'BBD',100,606.5), (2,1,'AAH',100,252.5), (3,2,'AAG',100,1064), (2,1,'AAD',100,143.7), (1,2,'AAB',100,400.8), (1,1,'AAM',100,258.9), (2,1,'BBI',100,743.1), (3,2,'AAG',100,1036.8), (1,2,'BBC',100,1052.7), (3,1,'BBG',100,865.8), (2,1,'AAA',100,290.6), (2,1,'BBH',100,632.8), (3,2,'AAF',100,568.5), (1,1,'BBB',100,789.1), (2,1,'BBI',100,767.3), (2,2,'BBL',100,472.2), (3,1,'AAE',100,788.6), (1,1,'AAI',100,799.3), (1,1,'BBG',100,867.9), (3,2,'BBH',100,632.6), (1,1,'AAJ',100,164), (1,1,'BBB',100,818.5), (2,1,'AAE',100,734.9), (3,1,'BBJ',100,344.5), (2,2,'AAI',100,784.2), (2,1,'AAG',100,1006.5), (1,2,'BBB',100,791.7), (3,2,'AAE',100,741.5), (2,2,'AAI',100,778), (2,1,'AAH',100,274), (2,1,'BBG',100,869.7), (1,1,'BBH',100,653), (3,1,'AAB',100,377.4), (2,1,'BBJ',100,307.7), (2,2,'BBG',100,813.8), (3,1,'BBG',100,829.7), (1,2,'AAG',100,1047), (1,2,'AAC',100,840.8), (3,1,'BBL',100,493.1), (2,1,'AAA',100,293.2), (3,2,'BBF',100,1069.8), (2,2,'AAF',100,559.2), (1,1,'AAI',100,793.5), (3,1,'BBC',100,1051.7), (1,2,'AAM',100,199.2), (2,1,'BBD',100,694.6), (3,1,'AAB',100,347), (1,1,'BBI',100,802.8), (1,2,'BBG',100,787.9), (3,1,'AAB',100,326.2), (1,2,'BBE',100,368), (2,1,'AAM',100,213.5), (3,1,'BBH',100,656.2), (1,2,'BBI',100,737.5), (2,1,'AAM',100,228.3), (2,2,'AAD',100,145), (3,1,'AAC',100,816.5), (3,2,'BBI',100,747.9), (3,1,'AAH',100,297.2), (3,2,'AAB',100,370.7), (3,2,'BBG',100,827), (3,1,'BBC',100,973.9), (2,2,'BBB',100,779.5), (2,2,'AAE',100,766.4), (2,1,'AAG',100,1062.5), (2,1,'BBE',100,367.9), (3,2,'BBE',100,309.4), (1,1,'BBL',100,466.6), (2,1,'BBH',100,663.2), (3,1,'AAK',100,211.7), (1,1,'AAB',100,363.2), (2,1,'AAB',100,327.9), (2,2,'AAB',100,344.3), (3,2,'AAM',100,223.6), (1,1,'AAI',100,815.3), (2,1,'AAM',100,268.7), (1,1,'AAB',100,409.7), (3,2,'AAA',100,255.7), (3,2,'AAE',100,786.9), (3,2,'AAM',100,236.1), (2,1,'BBI',100,734), (1,2,'AAD',100,156.1), (3,1,'AAB',100,326.2), (3,2,'BBL',100,489.9), (1,1,'BBD',100,687.4), (3,2,'BBD',100,664.8), (1,1,'AAB',100,409), (2,2,'AAD',100,157.1), (1,1,'AAF',100,599.4), (3,2,'AAK',100,217), (3,1,'BBB',100,783.1), (2,2,'BBE',100,360.6), (1,2,'BBE',100,376.2), (1,2,'BBH',100,661.5), (2,2,'AAF',100,523.6), (3,1,'BBG',100,842), (1,1,'BBC',100,989.6), (2,2,'AAL',100,298.2), (2,1,'AAB',100,409.3), (3,2,'BBF',100,1050.4), (3,1,'AAA',100,334.7), (3,2,'BBG',100,791.5), (2,2,'BBG',100,861.1), (2,1,'AAD',100,124.3), (2,1,'BBF',100,1058.1), (3,2,'AAD',100,164), (1,2,'BBB',100,768.4), (3,2,'AAD',100,151.3), (1,2,'AAC',100,808.7), (3,1,'BBB',100,811.9), (1,2,'AAM',100,222), (1,1,'BBD',100,622.9), (1,2,'AAD',100,165.7), (1,2,'BBB',100,784.7), (1,1,'AAG',100,1019.4), (3,2,'BBB',100,851.2), (1,1,'AAA',100,247.8), (3,1,'AAM',100,253.2), (2,1,'AAA',100,289), (2,1,'AAC',100,817.8), (1,2,'AAH',100,285.8), (1,1,'AAC',100,834.3), (1,1,'AAL',100,301.1), (3,1,'AAF',100,609.8), (3,1,'BBC',100,1029.4), (3,1,'AAD',100,190.4), (1,2,'AAF',100,549.6), (1,1,'AAF',100,548.9), (2,2,'AAA',100,282), (1,2,'AAI',100,756), (2,1,'BBH',100,639.9), (3,1,'AAB',100,384.9), (2,2,'BBJ',100,288.4), (1,1,'AAI',100,744), (1,1,'AAK',100,182.5), (3,1,'AAB',100,408.9), (2,1,'AAE',100,789.4), (1,1,'AAG',100,1056.8), (1,2,'AAB',100,398.6), (2,1,'BBB',100,790.4), (2,1,'BBD',100,658.6), (2,1,'BBE',100,355.5), (2,2,'BBF',100,1033.1), (2,2,'AAE',100,761.3), (2,1,'AAK',100,180.3), (3,2,'BBJ',100,280.2), (2,1,'BBI',100,815.2), (1,2,'BBB',100,806.8), (1,1,'AAK',100,171), (2,2,'BBK',100,1010.9), (3,1,'BBL',100,508.4), (1,2,'AAE',100,775.5), (3,1,'AAI',100,806.8), (3,2,'BBC',100,1028.2), (2,2,'BBJ',100,349.4), (2,2,'BBH',100,621.9), (2,2,'AAE',100,803.6), (3,1,'AAG',100,1055.8), (3,1,'AAD',100,153.5), (2,2,'AAC',100,853.8), (3,1,'BBB',100,786.8), (1,1,'AAK',100,198.2), (2,2,'AAH',100,228.7), (3,2,'AAB',100,409.3), (1,1,'BBE',100,386.3), (2,2,'BBC',100,1032.6), (2,1,'BBK',100,1011.7), (3,2,'BBG',100,828.4), (1,1,'BBF',100,1065.2), (2,2,'AAL',100,319.8), (2,2,'AAE',100,764.5), (3,1,'BBL',100,461.5), (2,2,'BBI',100,810.3), (1,1,'AAC',100,804.9), (3,1,'BBL',100,515.4), (3,1,'AAI',100,750.4), (3,1,'AAD',100,126.8), (3,1,'AAE',100,821.2), (1,1,'BBI',100,767.3), (3,1,'AAB',100,379), (2,1,'AAE',100,815.4), (3,2,'AAH',100,297.9), (1,2,'BBB',100,839.3), (3,1,'AAE',100,764.4), (2,2,'BBG',100,836.2), (2,1,'AAC',100,809.8), (3,2,'BBF',100,1085.8), (2,1,'BBJ',100,318.8), (3,1,'AAM',100,211.2), (2,1,'BBK',100,1015.1), (1,2,'BBE',100,324.7), (2,2,'BBL',100,518), (2,2,'AAK',100,167.9), (2,2,'BBD',100,619), (2,1,'BBD',100,609.6), (2,2,'AAL',100,347.8), (1,2,'AAF',100,562.4), (3,1,'AAH',100,254.7), (1,2,'BBI',100,770.3), (1,2,'BBE',100,385.3), (2,2,'BBB',100,851.8), (2,2,'AAL',100,314.4), (1,1,'BBB',100,836.8), (2,1,'AAL',100,358), (1,2,'BBK',100,1068.9), (3,1,'BBL',100,497.4), (3,1,'AAK',100,181.5), (3,1,'BBJ',100,333.7), (3,1,'BBD',100,690.8), (3,1,'BBD',100,663.7), (3,2,'AAI',100,763.3), (3,1,'BBF',100,1077.5), (3,1,'AAK',100,170.9), (3,2,'BBC',100,1044.4), (1,1,'AAG',100,1046.8), (3,1,'BBB',100,802.8), (1,1,'AAL',100,354.6), (2,2,'BBL',100,513.4), (1,1,'AAG',100,1009.9), (3,2,'AAK',100,187.8), (2,2,'BBI',100,802), (3,1,'AAE',100,784.4), (1,1,'AAI',100,754.4), (2,1,'BBJ',100,328.2), (1,1,'AAA',100,277.2), (3,1,'BBK',100,1061.7), (1,1,'AAE',100,795.5), (3,2,'AAM',100,253.9), (2,2,'AAA',100,257.3), (2,2,'BBC',100,1049.1), (3,2,'BBK',100,1071.3), (3,1,'AAG',100,1051.4), (2,2,'AAF',100,568.1), (3,2,'AAF',100,573.4), (2,2,'AAM',100,242.5), (3,2,'AAD',100,152.8), (3,1,'BBI',100,818.1), (2,2,'AAI',100,817.2), (3,1,'BBI',100,788.9), (3,1,'AAD',100,117.5), (3,1,'BBJ',100,334.1), (1,2,'BBH',100,600.6), (2,2,'AAJ',100,180.1), (3,1,'AAL',100,331.7), (3,2,'BBC',100,1038), (1,1,'BBJ',100,309.4), (3,2,'AAI',100,761.4), (3,1,'BBL',100,502.6), (2,1,'BBH',100,609.9), (3,1,'BBC',100,1021.6), (2,1,'AAM',100,268.5), (2,1,'AAE',100,801.1), (3,2,'BBG',100,870), (2,1,'BBE',100,383.1), (3,2,'AAD',100,113.7), (2,1,'BBC',100,1006.1), (2,1,'BBJ',100,297.4), (2,1,'BBJ',100,298.7), (2,2,'BBI',100,782.4), (2,2,'AAE',100,739.6), (2,1,'BBH',100,651.2), (3,1,'BBF',100,1077.9), (1,1,'AAB',100,368.8), (2,2,'AAH',100,228.1), (1,2,'BBG',100,866.3), (3,1,'AAL',100,316.9), (2,2,'AAH',100,249), (3,1,'AAD',100,189.2), (2,1,'BBF',100,1023.8), (1,2,'BBC',100,997.1), (1,2,'AAI',100,775.8), (2,2,'BBH',100,666.1), (3,1,'BBJ',100,278.8), (2,2,'AAJ',100,159.2), (1,2,'BBG',100,872.5), (2,2,'AAI',100,785), (1,2,'AAJ',100,136.3), (1,2,'AAF',100,572.4), (3,1,'BBI',100,801.2), (1,1,'AAA',100,333.9), (3,2,'BBL',100,478.5), (2,2,'BBE',100,320.2), (2,1,'BBG',100,819), (3,2,'BBI',100,776.5), (1,1,'BBG',100,870.1), (1,2,'AAH',100,309.4), (1,2,'AAH',100,301.7), (1,1,'BBJ',100,339.4), (3,2,'AAL',100,323.1), (3,1,'AAC',100,810), (2,1,'AAL',100,294.1), (1,2,'AAJ',100,199.2), (1,2,'BBG',100,811.5), (2,1,'BBI',100,781.7), (1,1,'BBI',100,751.5), (3,2,'AAK',100,205.2), (3,1,'BBF',100,1071.7), (1,2,'AAD',100,154.5), (3,2,'AAH',100,288.6), (3,2,'BBB',100,788.3), (3,1,'BBJ',100,284.2), (1,1,'BBE',100,347.4), (1,2,'BBD',100,639.4), (1,2,'BBB',100,766.1), (3,1,'AAL',100,285.5), (3,1,'BBH',100,662), (3,2,'AAA',100,312.3), (1,2,'AAJ',100,192.6), (1,2,'AAC',100,864.5), (2,2,'AAG',100,1018.9), (1,2,'AAM',100,218.8), (1,1,'BBK',100,1067), (3,1,'AAD',100,150.8), (1,2,'AAJ',100,188.4), (2,1,'BBK',100,1076.4), (3,1,'AAM',100,247.9), (1,2,'BBH',100,614), (2,2,'AAJ',100,220.9), (2,1,'AAD',100,117.7), (3,2,'AAE',100,820.4), (3,2,'AAA',100,302), (2,1,'AAL',100,325.4), (3,2,'AAC',100,819), (2,1,'BBB',100,851.2), (3,1,'AAG',100,984.2), (3,2,'BBG',100,846.8), (2,1,'AAM',100,245.1), (2,2,'AAA',100,285.9), (1,2,'BBE',100,313.2), (1,2,'AAA',100,261), (2,2,'BBH',100,651.1), (1,2,'AAJ',100,182.7), (2,1,'BBJ',100,352.8), (3,2,'BBD',100,652.6), (2,2,'AAI',100,814.2), (3,1,'BBL',100,530.2), (2,1,'BBF',100,1072.9), (1,2,'AAE',100,764.8), (1,1,'AAK',100,165.9), (1,1,'BBG',100,785.5), (2,2,'AAE',100,752), (3,2,'AAJ',100,149.8), (3,2,'AAG',100,1055.4), (2,2,'AAD',100,118), (3,1,'BBB',100,838.4), (1,2,'AAF',100,571.8), (1,2,'AAE',100,759.4), (3,2,'BBF',100,1067.7), (3,1,'AAD',100,184.7), (1,2,'BBC',100,1018.8), (3,1,'AAI',100,773.4), (1,1,'BBD',100,674), (2,2,'AAB',100,359), (3,2,'BBE',100,375.9), (1,2,'AAE',100,792.8), (2,2,'BBB',100,808.4), (1,2,'AAB',100,331.8), (3,2,'BBF',100,1039.6), (2,1,'AAM',100,271.3), (3,2,'AAD',100,148.8), (1,1,'BBK',100,1031.8), (2,2,'BBJ',100,302.5), (1,1,'AAH',100,280.1), (1,2,'BBF',100,1064.7), (3,1,'BBL',100,509.4), (1,2,'BBI',100,801.7), (3,1,'BBI',100,801.8), (1,2,'BBH',100,660.9), (3,1,'BBH',100,644.2), (3,2,'BBH',100,657), (2,1,'AAJ',100,217.2), (1,1,'AAG',100,980.5), (2,2,'BBH',100,630.3), (1,1,'AAF',100,525.1), (2,1,'AAM',100,253.7), (3,1,'BBD',100,644.2), (2,2,'BBK',100,1070), (2,2,'AAB',100,343.8), (3,2,'AAF',100,533.8), (2,2,'AAK',100,177.1), (3,1,'AAD',100,148), (3,1,'BBH',100,664.8), (2,2,'AAE',100,788.2), (3,2,'AAJ',100,176.1), (2,2,'AAB',100,388.3), (2,1,'BBB',100,768), (2,1,'BBD',100,619.7), (2,1,'AAI',100,744.7), (2,2,'BBJ',100,352.3), (3,2,'AAD',100,144.2), (2,2,'AAJ',100,202.3), (3,2,'BBJ',100,309.4), (2,2,'AAJ',100,168.6), (1,2,'AAK',100,217.9), (3,2,'BBF',100,1073.3), (3,2,'BBH',100,660.6), (3,1,'AAE',100,783.7), (3,2,'AAG',100,995.9), (2,2,'AAM',100,223.5), (3,1,'AAI',100,737.8), (2,2,'BBC',100,1036.2), (1,1,'AAG',100,1009.9), (3,1,'BBG',100,868.5), (1,2,'AAA',100,330.6), (3,2,'AAL',100,328.7), (2,1,'AAG',100,1010.8), (2,2,'AAM',100,200.4), (2,1,'AAA',100,279), (1,2,'AAI',100,823.1), (1,2,'BBG',100,805), (3,1,'BBF',100,1078.4), (3,2,'BBB',100,840.2), (3,2,'BBJ',100,297.8), (3,2,'BBE',100,348.1), (1,2,'AAK',100,130.9), (2,2,'AAC',100,817.7), (3,1,'BBL',100,511.2), (1,2,'BBE',100,321.5), (3,1,'AAD',100,194.4), (2,1,'BBI',100,781.8), (2,2,'AAD',100,170.3), (1,2,'BBB',100,825.5), (2,2,'AAK',100,137.3), (3,2,'BBD',100,639.5), (1,1,'AAJ',100,141.7), (3,2,'BBH',100,629.1), (2,2,'AAI',100,792.6), (2,1,'BBJ',100,331.1), (2,1,'AAJ',100,158.2), (2,1,'BBL',100,525.8), (1,1,'AAA',100,323.2), (3,2,'BBG',100,826.1), (3,1,'AAI',100,808.1), (1,2,'BBK',100,1016.9), (1,2,'AAL',100,342.8), (2,2,'BBC',100,972.1), (1,1,'AAH',100,225.4), (1,1,'BBI',100,790.1), (3,2,'BBH',100,656), (3,2,'BBK',100,1038.7), (3,1,'BBG',100,863.3), (1,2,'BBI',100,799.7), (1,2,'AAJ',100,143.9), (2,1,'AAK',100,183.9), (1,1,'BBC',100,1018.6), (3,2,'AAA',100,328.4), (3,2,'AAC',100,802), (3,2,'BBC',100,1055.9), (3,2,'AAL',100,360.4), (2,2,'BBK',100,1048), (3,2,'BBL',100,473.1), (2,1,'BBB',100,801.2), (3,2,'BBK',100,1033.2), (1,1,'BBL',100,525.6), (1,2,'AAM',100,218.9), (1,2,'AAK',100,134.5), (2,1,'BBK',100,1033.5), (3,2,'BBB',100,814.6), (3,1,'BBL',100,525.4), (1,1,'BBJ',100,279.7), (1,1,'AAB',100,388.8), (1,2,'AAI',100,785), (1,1,'BBB',100,768.9), (2,1,'AAD',100,123.9), (1,2,'AAL',100,308.9), (1,2,'AAA',100,276.2), (1,2,'AAH',100,228.5), (3,1,'AAA',100,258.2), (3,2,'AAC',100,850), (2,2,'BBL',100,537.9), (2,2,'BBG',100,808.5), (2,1,'AAB',100,411.5), (1,2,'BBL',100,474.3), (3,2,'AAH',100,275.8), (3,2,'BBK',100,1069.7), (2,2,'AAI',100,813.1), (3,1,'AAI',100,786.4), (1,2,'BBI',100,772), (3,1,'AAG',100,1049.7), (2,1,'AAF',100,538.4), (3,2,'AAJ',100,172.3), (3,2,'BBF',100,1065.7), (2,1,'AAJ',100,190.2), (3,1,'BBI',100,803.8), (3,2,'BBL',100,503.3), (3,2,'AAG',100,1014.3), (3,1,'BBI',100,745.9), (2,2,'BBH',100,665.7), (3,2,'AAH',100,290), (1,1,'AAB',100,387.2), (1,1,'BBL',100,517.8), (1,1,'BBJ',100,315.3), (2,2,'AAE',100,809.1), (3,2,'AAE',100,807.5), (2,1,'AAD',100,165.9), (2,1,'BBI',100,759), (1,2,'BBH',100,641.8), (3,1,'AAK',100,218.6), (2,1,'AAK',100,148.5), (1,2,'BBG',100,802.9), (2,1,'AAA',100,323.8), (3,2,'AAC',100,880.2), (3,1,'BBL',100,478.3), (1,1,'BBH',100,667.1), (1,1,'BBB',100,777.3), (1,1,'AAH',100,247.2), (1,2,'AAL',100,288.6), (3,2,'BBC',100,1032.7), (1,2,'AAG',100,988.9), (3,1,'BBH',100,622.7), (1,1,'BBF',100,1048), (2,2,'BBH',100,650.4), (3,1,'BBC',100,1037.5), (2,1,'AAC',100,847.7), (1,1,'BBD',100,659.6), (2,2,'AAL',100,279.5), (3,2,'AAJ',100,214.5), (1,2,'BBE',100,330.7), (3,2,'BBK',100,1057.5), (1,1,'AAG',100,1063.9), (1,2,'BBB',100,774.8), (2,2,'AAJ',100,138.6), (1,1,'AAB',100,357.9), (3,1,'AAC',100,875.1), (2,1,'BBG',100,802.9), (1,2,'AAH',100,300.4), (1,2,'AAF',100,533.5), (2,2,'AAI',100,773.5), (2,2,'BBC',100,1055.5), (2,1,'BBI',100,760.9), (1,2,'AAH',100,237.8), (1,2,'BBD',100,659.1), (2,1,'AAM',100,210.2), (3,2,'BBF',100,1058.3), (3,1,'BBJ',100,306.1), (3,2,'AAK',100,170.3), (3,2,'AAF',100,553.8), (1,2,'AAL',100,331.8), (3,1,'AAL',100,333.6), (2,1,'AAM',100,214.7), (2,2,'BBD',100,628.8), (3,1,'BBE',100,352.3), (2,1,'BBJ',100,351.9), (1,2,'AAA',100,267.3) GO USE [FabrikamInvestmentsDb] GO INSERT INTO [dbo].[ManagedAccountCompletedTransactions] ([OpenOrderID],[CompanySymbol],[Shares],[Price],[CreatedDate]) VALUES (742,'AAH',100,'309.4',getdate()), (743,'AAH',100,'301.7',getdate()), (744,'BBJ',100,'339.4',getdate()), (745,'AAL',100,'323.1',getdate()), (746,'AAC',100,'810',getdate()), (814,'BBF',100,'1039.6',getdate()), (815,'AAM',100,'271.3',getdate()), (816,'AAD',100,'148.8',getdate()), (817,'BBK',100,'1031.8',getdate()), (818,'BBJ',100,'302.5',getdate()), (819,'AAH',100,'280.1',getdate()), (820,'BBF',100,'1064.7',getdate()), (821,'BBL',100,'509.4',getdate()), (822,'BBI',100,'801.7',getdate()), (823,'BBI',100,'801.8',getdate()), (824,'BBH',100,'660.9',getdate()), (825,'BBH',100,'644.2',getdate()), (826,'BBH',100,'657',getdate()), (827,'AAJ',100,'217.2',getdate()), (828,'AAG',100,'980.5',getdate()), (829,'BBH',100,'630.3',getdate()), (830,'AAF',100,'525.1',getdate()), (831,'AAM',100,'253.7',getdate()), (832,'BBD',100,'644.2',getdate()), (833,'BBK',100,'1070',getdate()), (834,'AAB',100,'343.8',getdate()), (835,'AAF',100,'533.8',getdate()), (836,'AAK',100,'177.1',getdate()), (837,'AAD',100,'148',getdate()), (838,'BBH',100,'664.8',getdate()), (839,'AAE',100,'788.2',getdate()), (840,'AAJ',100,'176.1',getdate()), (841,'AAB',100,'388.3',getdate()), (842,'BBB',100,'768',getdate()), (843,'BBD',100,'619.7',getdate()), (844,'AAI',100,'744.7',getdate()), (845,'BBJ',100,'352.3',getdate()), (846,'AAD',100,'144.2',getdate()), (847,'AAJ',100,'202.3',getdate()), (848,'BBJ',100,'309.4',getdate()), (849,'AAJ',100,'168.6',getdate()), (850,'AAK',100,'217.9',getdate()), (851,'BBF',100,'1073.3',getdate()), (852,'BBH',100,'660.6',getdate()), (853,'AAE',100,'783.7',getdate()), (854,'AAG',100,'995.9',getdate()), (855,'AAM',100,'223.5',getdate()), (856,'AAI',100,'737.8',getdate()), (857,'BBC',100,'1036.2',getdate()), (858,'AAG',100,'1009.9',getdate()), (859,'BBG',100,'868.5',getdate()), (860,'AAA',100,'330.6',getdate()), (861,'AAL',100,'328.7',getdate()), (862,'AAG',100,'1010.8',getdate()), (863,'AAM',100,'200.4',getdate()), (864,'AAA',100,'279',getdate()), (865,'AAI',100,'823.1',getdate()), (999,'BBJ',100,'351.9',getdate()), (1000,'AAA',100,'267.3',getdate()), (1,'BBF',100,'1005.3',getdate()), (2,'AAH',100,'230.4',getdate()), (3,'AAK',100,'195.9',getdate()), (4,'BBI',100,'808.5',getdate()), (5,'AAB',100,'381.9',getdate()), (6,'AAC',100,'813.1',getdate()), (7,'BBJ',100,'283',getdate()), (8,'BBG',100,'827.3',getdate()), (9,'AAM',100,'196.7',getdate()), (10,'AAK',100,'177.5',getdate()), (11,'AAK',100,'173.6',getdate()), (12,'AAC',100,'845.3',getdate()), (13,'AAJ',100,'221',getdate()), (14,'AAK',100,'151.2',getdate()), (15,'AAF',100,'601.7',getdate()), (16,'BBL',100,'503.9',getdate()), (17,'AAJ',100,'216.8',getdate()), (18,'BBL',100,'539.9',getdate()), (19,'BBE',100,'389.1',getdate()), (20,'AAG',100,'1060',getdate()), (21,'AAD',100,'149.4',getdate()), (22,'AAA',100,'323.6',getdate()), (23,'BBI',100,'729.3',getdate()), (24,'AAI',100,'741.1',getdate()), (25,'BBL',100,'515.1',getdate()), (26,'AAD',100,'117.6',getdate()), (27,'BBB',100,'782.9',getdate()), (28,'AAG',100,'1022.4',getdate()), (29,'BBB',100,'779',getdate()), (30,'AAI',100,'749.1',getdate()), (31,'BBK',100,'1072',getdate()), (32,'AAE',100,'793.1',getdate()), (33,'BBI',100,'788.9',getdate()), (34,'AAA',100,'264.4',getdate()), (35,'BBC',100,'984.6',getdate()), (36,'AAM',100,'255.2',getdate()), (37,'AAA',100,'253.4',getdate()), (38,'BBL',100,'529.5',getdate()), (39,'AAM',100,'194.1',getdate()), (40,'BBL',100,'483.6',getdate()), (41,'AAF',100,'574.3',getdate()), (42,'AAJ',100,'149.1',getdate()), (43,'AAG',100,'1026.7',getdate()), (44,'BBE',100,'378',getdate()), (45,'AAA',100,'301.7',getdate()), (46,'AAE',100,'796.8',getdate()), (47,'BBL',100,'450.7',getdate()), (48,'BBF',100,'1046.1',getdate()), (49,'BBB',100,'782.8',getdate()), (50,'AAB',100,'387.9',getdate()), (51,'AAE',100,'817.8',getdate()), (52,'BBG',100,'856.6',getdate()), (53,'BBG',100,'863.1',getdate()), (54,'BBJ',100,'336.3',getdate()), (55,'AAG',100,'1055.9',getdate()), (56,'BBC',100,'981.5',getdate()), (57,'AAL',100,'279.4',getdate()), (58,'BBC',100,'1028.1',getdate()), (59,'AAC',100,'805.6',getdate()), (60,'AAI',100,'787.7',getdate()), (61,'AAE',100,'734.6',getdate()), (62,'BBB',100,'813.1',getdate()), (63,'BBH',100,'605.3',getdate()), (64,'AAE',100,'819.5',getdate()), (65,'AAB',100,'394.8',getdate()), (133,'AAK',100,'155.5',getdate()), (134,'BBJ',100,'334.6',getdate()), (135,'BBH',100,'598.1',getdate()), (136,'BBJ',100,'353.4',getdate()), (137,'AAG',100,'1010.2',getdate()), (138,'BBG',100,'798.6',getdate()), (139,'BBJ',100,'297.9',getdate()), (140,'BBK',100,'1036.5',getdate()), (141,'BBE',100,'396.9',getdate()), (142,'BBG',100,'819.7',getdate()), (143,'AAL',100,'333.6',getdate()), (144,'AAI',100,'795.4',getdate()), (145,'AAK',100,'147.1',getdate()), (146,'AAH',100,'303.6',getdate()), (147,'AAG',100,'995.3',getdate()), (148,'AAI',100,'783.4',getdate()), (149,'BBF',100,'1094.9',getdate()), (150,'AAF',100,'529.4',getdate()), (151,'AAI',100,'802',getdate()), (152,'AAG',100,'978.6',getdate()), (153,'AAD',100,'186',getdate()), (154,'AAL',100,'278.2',getdate()), (155,'BBB',100,'832.2',getdate()), (156,'AAL',100,'348.3',getdate()), (157,'AAK',100,'197.3',getdate()), (158,'BBK',100,'1041',getdate()), (159,'BBF',100,'1067.9',getdate()), (160,'AAG',100,'986.8',getdate()), (161,'BBK',100,'1055',getdate()), (162,'AAE',100,'781.2',getdate()), (163,'AAG',100,'993.7',getdate()), (164,'AAC',100,'792.6',getdate()), (165,'AAB',100,'347.4',getdate()), (166,'AAM',100,'197.6',getdate()), (167,'AAI',100,'783.5',getdate()), (168,'AAF',100,'571',getdate()), (169,'AAC',100,'874.8',getdate()), (170,'AAJ',100,'191.2',getdate()), (171,'BBJ',100,'354.8',getdate()), (172,'AAG',100,'985.8',getdate()), (173,'AAL',100,'328.5',getdate()), (174,'BBG',100,'821.5',getdate()), (175,'BBC',100,'1052.4',getdate()), (176,'BBC',100,'1024.1',getdate()), (177,'AAH',100,'248.4',getdate()), (178,'AAD',100,'182.4',getdate()), (179,'BBL',100,'525.5',getdate()), (180,'BBJ',100,'323.3',getdate()), (181,'BBJ',100,'356.1',getdate()), (182,'AAE',100,'805.7',getdate()), (183,'BBD',100,'640.8',getdate()), (184,'BBL',100,'458.1',getdate()), (185,'AAA',100,'296.8',getdate()), (186,'BBL',100,'481',getdate()), (187,'AAM',100,'263.8',getdate()), (188,'AAD',100,'131.6',getdate()), (189,'BBG',100,'861.3',getdate()), (190,'AAI',100,'770.5',getdate()), (191,'BBK',100,'1017.2',getdate()), (192,'AAC',100,'880.3',getdate()), (193,'BBH',100,'620.6',getdate()), (194,'BBL',100,'461.8',getdate()), (195,'BBE',100,'380.6',getdate()), (196,'AAM',100,'224.2',getdate()), (197,'BBE',100,'328.8',getdate()), (198,'BBB',100,'820.2',getdate()), (199,'BBI',100,'764.1',getdate()), (200,'BBD',100,'610.8',getdate()), (201,'BBK',100,'1038.1',getdate()), (202,'BBK',100,'1066',getdate()), (203,'AAG',100,'1030.6',getdate()), (204,'AAG',100,'1007.1',getdate()), (205,'AAB',100,'342.1',getdate()), (206,'AAM',100,'243.1',getdate()), (207,'AAB',100,'359.4',getdate()), (208,'AAM',100,'221.7',getdate()), (209,'BBD',100,'616.9',getdate()), (210,'AAH',100,'238.2',getdate()), (211,'AAE',100,'782.7',getdate()), (212,'BBJ',100,'361.8',getdate()), (213,'BBK',100,'1056',getdate()), (214,'AAK',100,'145.6',getdate()), (215,'AAF',100,'541.5',getdate()), (216,'AAA',100,'310.9',getdate()), (217,'AAF',100,'535',getdate()), (218,'AAJ',100,'142',getdate()), (219,'AAD',100,'152.6',getdate()), (220,'AAM',100,'270',getdate()), (221,'AAK',100,'153.9',getdate()), (222,'AAD',100,'138.9',getdate()), (223,'AAK',100,'191.6',getdate()), (292,'AAC',100,'803.8',getdate()), (293,'BBB',100,'784.4',getdate()), (294,'BBL',100,'497.1',getdate()), (295,'AAK',100,'211.2',getdate()), (296,'BBF',100,'1095',getdate()), (297,'AAB',100,'385.2',getdate()), (298,'AAC',100,'834.3',getdate()), (299,'BBJ',100,'362',getdate()), (300,'AAI',100,'816.7',getdate()), (301,'AAB',100,'377.1',getdate()), (302,'AAF',100,'583.4',getdate()), (303,'AAE',100,'760.6',getdate()), (304,'BBJ',100,'357.4',getdate()), (305,'BBD',100,'657.5',getdate()), (306,'AAA',100,'261.3',getdate()), (307,'BBG',100,'860.7',getdate()), (308,'BBC',100,'1025.6',getdate()), (309,'AAD',100,'150.1',getdate()), (310,'BBJ',100,'365.3',getdate()), (311,'BBI',100,'803.3',getdate()), (312,'BBH',100,'667.4',getdate()), (313,'AAJ',100,'164.8',getdate()), (314,'AAA',100,'283.6',getdate()), (315,'BBC',100,'1052.3',getdate()), (316,'AAD',100,'173.6',getdate()), (317,'AAJ',100,'162.6',getdate()), (318,'BBC',100,'987.4',getdate()), (319,'BBI',100,'764.6',getdate()), (320,'AAA',100,'288.7',getdate()), (321,'AAB',100,'365.3',getdate()), (322,'AAF',100,'575.1',getdate()), (323,'BBH',100,'646.8',getdate()), (324,'BBG',100,'858.8',getdate()), (325,'BBH',100,'643.1',getdate()), (326,'BBB',100,'794.3',getdate()), (327,'BBJ',100,'354',getdate()), (328,'AAD',100,'176.3',getdate()), (329,'AAA',100,'304.4',getdate()), (330,'BBE',100,'310.7',getdate()), (331,'AAC',100,'799.1',getdate()), (332,'AAH',100,'288.1',getdate()), (333,'AAL',100,'274.9',getdate()), (334,'AAF',100,'592.1',getdate()), (335,'AAD',100,'180.5',getdate()), (336,'BBF',100,'1070.2',getdate()), (337,'BBI',100,'729.3',getdate()), (338,'AAA',100,'308.2',getdate()), (339,'AAL',100,'362.3',getdate()), (340,'AAA',100,'251.5',getdate()), (341,'AAA',100,'284.4',getdate()), (342,'AAJ',100,'205.7',getdate()), (343,'BBB',100,'808.3',getdate()), (344,'AAJ',100,'132.4',getdate()), (345,'AAB',100,'350.9',getdate()), (346,'AAK',100,'153.9',getdate()), (347,'AAI',100,'805.7',getdate()), (348,'AAH',100,'298.8',getdate()), (349,'BBG',100,'835.6',getdate()), (350,'AAI',100,'753.1',getdate()), (351,'BBI',100,'788.2',getdate()), (352,'AAE',100,'776.5',getdate()), (353,'BBG',100,'790.8',getdate()), (354,'AAD',100,'154.3',getdate()), (355,'AAG',100,'1015.2',getdate()), (356,'BBK',100,'1007.9',getdate()), (357,'AAI',100,'817.8',getdate()), (358,'AAK',100,'172.1',getdate()), (359,'AAI',100,'750.4',getdate()), (360,'BBL',100,'470',getdate()), (361,'AAI',100,'761.9',getdate()), (362,'AAM',100,'197.8',getdate()), (363,'BBE',100,'398.2',getdate()), (364,'BBG',100,'820.5',getdate()), (365,'AAD',100,'191.3',getdate()), (366,'BBL',100,'461',getdate()), (367,'BBC',100,'1057',getdate()), (368,'AAK',100,'181.4',getdate()), (369,'AAM',100,'242.9',getdate()), (370,'AAI',100,'753.4',getdate()), (371,'BBL',100,'512.6',getdate()), (372,'BBI',100,'787.2',getdate()), (373,'AAG',100,'996.3',getdate()), (374,'AAC',100,'821.2',getdate()), (375,'AAE',100,'736.7',getdate()), (376,'BBL',100,'510.1',getdate()), (377,'BBE',100,'317.2',getdate()), (378,'BBF',100,'1074.4',getdate()), (379,'BBD',100,'653.5',getdate()), (380,'BBL',100,'492.3',getdate()), (381,'BBE',100,'360.2',getdate()), (382,'AAG',100,'979.2',getdate()), (383,'AAJ',100,'144.8',getdate()), (384,'AAK',100,'169.7',getdate()), (385,'BBK',100,'1039.2',getdate()), (386,'BBK',100,'1061.8',getdate()), (387,'BBI',100,'784.9',getdate()), (388,'AAI',100,'782.7',getdate()), (389,'AAF',100,'553.2',getdate()), (390,'BBE',100,'378.1',getdate()), (391,'AAE',100,'804.5',getdate()), (392,'AAA',100,'295',getdate()), (393,'BBE',100,'355.3',getdate()), (394,'AAC',100,'859.8',getdate()), (395,'AAF',100,'588.9',getdate()), (396,'AAE',100,'758',getdate()), (397,'BBF',100,'1079.6',getdate()), (398,'AAJ',100,'140.6',getdate()), (399,'BBE',100,'318.9',getdate()), (400,'AAH',100,'242.1',getdate()), (401,'BBK',100,'1032.5',getdate()), (402,'AAA',100,'265.9',getdate()), (403,'BBK',100,'1024.5',getdate()), (404,'BBE',100,'343.4',getdate()), (405,'BBE',100,'355.5',getdate()), (406,'AAM',100,'186.2',getdate()), (407,'BBB',100,'786.4',getdate()), (408,'AAH',100,'291.1',getdate()), (409,'BBJ',100,'315.4',getdate()), (410,'BBJ',100,'345.5',getdate()), (411,'AAB',100,'405',getdate()), (412,'AAE',100,'746.3',getdate()), (413,'AAJ',100,'216.9',getdate()), (414,'BBK',100,'1071.1',getdate()), (415,'AAL',100,'331.8',getdate()), (416,'BBE',100,'333.4',getdate()), (417,'BBB',100,'852.2',getdate()), (418,'AAI',100,'784.2',getdate()), (419,'BBG',100,'788.4',getdate()), (420,'AAI',100,'760.5',getdate()), (421,'BBG',100,'826.9',getdate()), (422,'BBJ',100,'316.7',getdate()), (423,'AAD',100,'130',getdate()), (424,'BBF',100,'1083.9',getdate()), (425,'BBB',100,'798.8',getdate()), (426,'AAC',100,'803.8',getdate()), (427,'AAK',100,'142.4',getdate()), (428,'AAB',100,'362.7',getdate()), (429,'AAM',100,'238.1',getdate()), (430,'AAE',100,'801.2',getdate()), (431,'AAK',100,'205.2',getdate()), (432,'AAH',100,'313.5',getdate()), (599,'BBF',100,'1033.1',getdate()), (600,'AAE',100,'761.3',getdate()), (601,'AAK',100,'180.3',getdate()), (602,'BBJ',100,'280.2',getdate()), (603,'BBI',100,'815.2',getdate()), (604,'BBB',100,'806.8',getdate()), (605,'AAK',100,'171',getdate()), (606,'BBK',100,'1010.9',getdate()), (607,'BBL',100,'508.4',getdate()), (608,'AAE',100,'775.5',getdate()), (609,'AAI',100,'806.8',getdate()), (610,'BBC',100,'1028.2',getdate()), (611,'BBJ',100,'349.4',getdate()), (612,'BBH',100,'621.9',getdate()), (613,'AAE',100,'803.6',getdate()), (614,'AAG',100,'1055.8',getdate()), (615,'AAD',100,'153.5',getdate()), (616,'AAC',100,'853.8',getdate()), (617,'BBB',100,'786.8',getdate()), (618,'AAK',100,'198.2',getdate()), (619,'AAH',100,'228.7',getdate()), (620,'AAB',100,'409.3',getdate()), (621,'BBE',100,'386.3',getdate()), (622,'BBC',100,'1032.6',getdate()), (623,'BBK',100,'1011.7',getdate()), (624,'BBG',100,'828.4',getdate()), (625,'BBF',100,'1065.2',getdate()), (626,'AAL',100,'319.8',getdate()), (627,'AAE',100,'764.5',getdate()), (628,'BBL',100,'461.5',getdate()), (629,'BBI',100,'810.3',getdate()), (630,'AAC',100,'804.9',getdate()), (631,'BBL',100,'515.4',getdate()), (632,'AAI',100,'750.4',getdate()), (633,'AAD',100,'126.8',getdate()), (634,'AAE',100,'821.2',getdate()), (635,'BBI',100,'767.3',getdate()), (636,'AAB',100,'379',getdate()), (637,'AAE',100,'815.4',getdate()), (638,'AAH',100,'297.9',getdate()), (639,'BBB',100,'839.3',getdate()), (640,'AAE',100,'764.4',getdate()), (641,'BBG',100,'836.2',getdate()), (642,'AAC',100,'809.8',getdate()), (643,'BBF',100,'1085.8',getdate()), (644,'BBJ',100,'318.8',getdate()), (645,'AAM',100,'211.2',getdate()), (646,'BBK',100,'1015.1',getdate()), (647,'BBE',100,'324.7',getdate()), (648,'BBL',100,'518',getdate()), (649,'AAK',100,'167.9',getdate()), (650,'BBD',100,'619',getdate()), (651,'BBD',100,'609.6',getdate()), (652,'AAL',100,'347.8',getdate()), (653,'AAF',100,'562.4',getdate()), (654,'AAH',100,'254.7',getdate()), (655,'BBI',100,'770.3',getdate()), (656,'BBE',100,'385.3',getdate()), (657,'BBB',100,'851.8',getdate()), (658,'AAL',100,'314.4',getdate()), (659,'BBB',100,'836.8',getdate()), (660,'AAL',100,'358',getdate()), (661,'BBK',100,'1068.9',getdate()), (662,'BBL',100,'497.4',getdate()), (663,'AAK',100,'181.5',getdate()), (664,'BBJ',100,'333.7',getdate()), (665,'BBD',100,'690.8',getdate()), (666,'BBD',100,'663.7',getdate()), (667,'AAI',100,'763.3',getdate()), (668,'BBF',100,'1077.5',getdate()), (669,'AAK',100,'170.9',getdate()), (670,'BBC',100,'1044.4',getdate()), (671,'AAG',100,'1046.8',getdate()), (672,'BBB',100,'802.8',getdate()), (673,'AAL',100,'354.6',getdate()), (674,'BBL',100,'513.4',getdate()), (675,'AAG',100,'1009.9',getdate()), (676,'AAK',100,'187.8',getdate()), (677,'BBI',100,'802',getdate()), (678,'AAE',100,'784.4',getdate()), (679,'AAI',100,'754.4',getdate()), (680,'BBJ',100,'328.2',getdate()), (681,'AAA',100,'277.2',getdate()), (682,'BBK',100,'1061.7',getdate()), (683,'AAE',100,'795.5',getdate()), (684,'AAM',100,'253.9',getdate()), (685,'AAA',100,'257.3',getdate()), (686,'BBC',100,'1049.1',getdate()), (687,'BBK',100,'1071.3',getdate()), (688,'AAG',100,'1051.4',getdate()), (689,'AAF',100,'568.1',getdate()), (690,'AAF',100,'573.4',getdate()), (701,'BBC',100,'1038',getdate()), (702,'BBJ',100,'309.4',getdate()), (703,'AAI',100,'761.4',getdate()), (704,'BBL',100,'502.6',getdate()), (705,'BBH',100,'609.9',getdate()), (706,'BBC',100,'1021.6',getdate()), (707,'AAM',100,'268.5',getdate()), (708,'AAE',100,'801.1',getdate()), (709,'BBG',100,'870',getdate()), (710,'BBE',100,'383.1',getdate()), (711,'AAD',100,'113.7',getdate()), (712,'BBC',100,'1006.1',getdate()), (713,'BBJ',100,'297.4',getdate()), (714,'BBJ',100,'298.7',getdate()), (715,'BBI',100,'782.4',getdate()), (716,'AAE',100,'739.6',getdate()), (717,'BBH',100,'651.2',getdate()), (718,'BBF',100,'1077.9',getdate()), (719,'AAB',100,'368.8',getdate()), (720,'AAH',100,'228.1',getdate()), (721,'BBG',100,'866.3',getdate()), (722,'AAL',100,'316.9',getdate()), (723,'AAH',100,'249',getdate()), (724,'AAD',100,'189.2',getdate()), (725,'BBF',100,'1023.8',getdate()), (726,'BBC',100,'997.1',getdate()), (727,'AAI',100,'775.8',getdate()), (728,'BBH',100,'666.1',getdate()), (729,'BBJ',100,'278.8',getdate()), (730,'AAJ',100,'159.2',getdate()), (740,'BBI',100,'776.5',getdate()), (741,'BBG',100,'870.1',getdate()) GO
Example Insert Statements from embedded SQL script
Populate the Order Type Table
INSERT INTO [dbo].[OrderType] ([Name])VALUES ('BUY'), ('SELL')
GO
Populate the Risk Type Table
INSERT INTO [dbo].[RiskType] ([Name])
VALUES ('LOW'), ('MEDIUM'), ('HIGH')
GO
Populate the Account Manager Table
INSERT INTO [dbo].[AccountManager] ([FirstName],[LastName],[RiskSpecialty])
VALUES ('John', 'Doe', 1), ('Steve', 'Smith', 2), ('Jane', 'Doe', 3)
GO
Populate the Customer Account Table
INSERT INTO [dbo].[CustomerAccount] ([FirstName],[LastName],[Email],[Address],[City],[State],[Zip],[Phone])
VALUES
('Peter',
'James',
'pjames@hotmail.com',
'111 Newport Way',
'Nowhere',
'NV',
'99999',
'555-555-5555'),
('David',
'Smith',
'davidsmith@hotmail.com',
'101 Bellevue Way',
'Bellevue',
'WA',
'99999',
'444-444-4444'),
('Daniel',
'Peterson',
'danp@hotmail.com',
'2751 North 1250 East',
'Denver',
'CO',
'99999',
'333-333-3333')
GO
Populate the Managed Account Table
INSERT INTO [dbo].[ManagedAccount] ([Name],[RiskLevel],[CashAvailable])
VALUES
('401K', 3, '1050000.00'),('Personal', 2, '1100000.00'),('IRA', 1, '2030000.00')
GO
Populate the Cuatomer Account Managed Account Relation Table
INSERT INTO [dbo].[CustomerAccountManagedAccount]([CustomerAccountID],[ManagedAccountID],[AccountManagerID])
VALUES (1,1,3), (2,2,2), (3,3,1)
GO
Populate the Managed Account Positions Table (example)
INSERT INTO [dbo].[ManagedAccountPositions] ([ManagedAccountID],[CompanySymbol],[Shares],[Date])
VALUES
(1,'AAA',1000,getdate()),
(1,'AAB',1000,getdate()),
(1,'AAC',1000,getdate())
Populate the Managed Account Open Orders Table (example)
INSERT INTO [dbo].[ManagedAccountOpenOrders]
([ManagedAccountID],[OrderType],[CompanySymbol],[Shares],[Price])
VALUES
(3,2,'BBF',100,1005.3),
(3,2,'AAH',100,230.4),
(3,2,'AAK',100,195.9)
Populate the ManagedAccountCompletedTransactions Table (example)
INSERT INTO [dbo].[ManagedAccountCompletedTransactions] ([OpenOrderID],[CompanySymbol],[Shares],[Price],[CreatedDate])
VALUES
(742,'AAH',100,'309.4',getdate()),
(743,'AAH',100,'301.7',getdate()),
(744,'BBJ',100,'339.4',getdate())
Testing the new Fabrikam Investments Database
Execute the Read Only Queries against the Listener (load balanced)
Setup a connection to the listener
Add MultiSubnetFailover=true;ApplicationIntent=readonly; to the connection string and connect
Additional Connection Parameters (Copy/Paste)
_MultiSubnetFailover=true;ApplicationIntent=readonly;
_
Execute the Reporting Queries
Query for all Customer Account Information
USE [FabrikamInvestmentsDb]
GO
-- Select all information relating to a Customer Account
SELECT DISTINCT 'Customer Account Information', ma.Name, ca.*, rt1.Name AS RiskLevel, am.FirstName, am.LastName, rt2.Name AS RiskSpecialty, map.CompanySymbol, map.Shares
FROM CustomerAccount caJOIN CustomerAccountManagedAccount cama ON cama.CustomerAccountID = ca.IDJOIN ManagedAccount ma ON ma.ID = cama.ManagedAccountIDJOIN RiskType rt1 ON rt1.ID = ma.RiskLevelJOIN AccountManager am ON am.ID = cama.AccountManagerIDJOIN RiskType rt2 ON rt2.ID = am.RiskSpecialtyJOIN ManagedAccountPositions map ON map.ManagedAccountID = ma.IDJOIN ManagedAccountOpenOrders cmaoo ON cmaoo.ManagedAccountID = ma.IDORDER BY ca.ID
GO
Query for Open Orders by Company
-- Query Open Orders by Company Symbol
SELECT DISTINCT 'Open Orders by Company', CompanySymbol, OrderType, Count(*) AS CountFROM ManagedAccountOpenOrdersWHERE Completed = 0GROUP BY CompanySymbol, OrderTypeORDER BY CompanySymbol ASC
GO
Query for Customer's Stock Positions
-- Query Customer Positions
SELECT DISTINCT 'Customer Positions', ma.Name, ca.*, am.FirstName, am.LastName, map.CompanySymbol, map.SharesFROM CustomerAccount caJOIN CustomerAccountManagedAccount cama ON cama.CustomerAccountID = ca.IDJOIN ManagedAccount ma ON ma.ID = cama.ManagedAccountIDJOIN RiskType rt1 ON rt1.ID = ma.RiskLevelJOIN AccountManager am ON am.ID = cama.AccountManagerIDJOIN RiskType rt2 ON rt2.ID = am.RiskSpecialtyJOIN ManagedAccountPositions map ON map.ManagedAccountID = ma.IDORDER BY ca.ID
GO
Query for Customer's Open Orders
-- Query Customer Open Orders
SELECT DISTINCT 'Customer Open Orders', ma.Name, ca.*, am.FirstName, am.LastName, ot.Name, maoo.CompanySymbol, maoo.SharesFROM CustomerAccount caJOIN CustomerAccountManagedAccount cama ON cama.CustomerAccountID = ca.IDJOIN ManagedAccount ma ON ma.ID = cama.ManagedAccountIDJOIN RiskType rt1 ON rt1.ID = ma.RiskLevelJOIN AccountManager am ON am.ID = cama.AccountManagerIDJOIN RiskType rt2 ON rt2.ID = am.RiskSpecialtyJOIN ManagedAccountOpenOrders maoo ON maoo.ManagedAccountID = ma.ID AND maoo.Completed = 0JOIN OrderType ot ON ot.ID = maoo.OrderTypeORDER BY ca.ID
GO
Query for Customer's Completed Orders
-- Query Customer Completed Orders
SELECT DISTINCT 'Customer Completed Orders', ma.Name, ca.*, am.FirstName, am.LastName, ot.Name, maoo.CompanySymbol, maoo.SharesFROM CustomerAccount caJOIN CustomerAccountManagedAccount cama ON cama.CustomerAccountID = ca.IDJOIN ManagedAccount ma ON ma.ID = cama.ManagedAccountIDJOIN RiskType rt1 ON rt1.ID = ma.RiskLevelJOIN AccountManager am ON am.ID = cama.AccountManagerIDJOIN RiskType rt2 ON rt2.ID = am.RiskSpecialtyJOIN ManagedAccountOpenOrders maoo ON maoo.ManagedAccountID = ma.ID AND maoo.Completed = 1JOIN OrderType ot ON ot.ID = maoo.OrderTypeORDER BY ca.ID
GO
Query for the Most Active Stocks (Fabrikam Transactions)
-- Query for the Most Active stocks
SELECT TOP 3 'Most Active Stocks', [CompanySymbol], SUM(Shares) AS 'Shares Traded'FROM [ManagedAccountOpenOrders]WHERE [Completed] = 1GROUP BY [CompanySymbol]ORDER BY SUM(Shares) DESC
GO
Query for the Top and Bottom Movers
-- Top/Bottom Movers
-- Create a temp table with the first and last transactions for each company
SELECT * INTO #TempTable FROM (
SELECT maoo1.ID, maoo1.[CompanySymbol], maoo1.[Price] FROM [ManagedAccountOpenOrders] maoo1
JOIN (
SELECT [CompanySymbol], MAX(ID) AS ID FROM [ManagedAccountOpenOrders] WHERE [Completed] = 1 AND OrderType = 2 GROUP BY [CompanySymbol] ) maoo2 ON ( maoo1.ID = maoo2.ID)UNION
SELECT maoo1.ID, maoo1.[CompanySymbol], maoo1.[Price]FROM [ManagedAccountOpenOrders] maoo1
JOIN ( SELECT [CompanySymbol], MIN(ID) AS ID FROM [ManagedAccountOpenOrders] WHERE [Completed] = 1 AND OrderType = 2 GROUP BY [CompanySymbol] )
maoo2 ON ( maoo1.ID = maoo2.ID )) AS Temp;
-- Query the temp table for the Biggest Gainers
SELECT TOP 5 t1.CompanySymbol, t2.Price - t1.Price AS 'Gain'FROM #TempTable t1JOIN #TempTable t2 ON t2.Price > t1.Price AND t1.CompanySymbol = t2.CompanySymbol AND t2.ID > t1.IDORDER BY t2.Price - t1.Price DESC
-- Query the temp table for the Biggest Losers
SELECT TOP 5 t1.CompanySymbol, t2.Price - t1.Price AS 'Loss'FROM #TempTable t1JOIN #TempTable t2 ON t2.Price < t1.Price AND t1.CompanySymbol = t2.CompanySymbol AND t2.ID > t1.IDORDER BY t2.Price - t1.Price ASC
DROP TABLE #TempTable
GO
Execute the Process Open Orders Script against the Primary SQL Server
Connect to the Primary SQL Server
Clear the Additional Connection Parameters and click Connect
Execute the Process Open Orders SQL script
USE [FabrikamInvestmentsDb]
GO
-------------------------------------------------------------------------------------------------
-- NOTE: This script assumes all orders transact 100 shares of stock.
-------------------------------------------------------------------------------------------------
DECLARE @ID [bigint]
DECLARE @OpenOrderID [bigint]
DECLARE @Shares [int]
DECLARE @Price [money]
DECLARE @ManagedAccountID [bigint]
DECLARE @OrderType [int]
DECLARE @CashAvailable [money]
-- Display the current number of pending transactions
SELECT 'Before Processing Completed Transactions', count(*)
FROM [ManagedAccountCompletedTransactions]DECLARE CompletedOrderCursor CURSOR
FOR
SELECT [ID],[OpenOrderID],[Shares],[Price]
FROM [ManagedAccountCompletedTransactions]
OPEN CompletedOrderCursor
WHILE (1=1)
BEGIN
FETCH NEXT FROM CompletedOrderCursor INTO @ID, @OpenOrderID, @Shares, @Price
IF @@FETCH_STATUS <> 0
BREAK
-- Start the Settlement Transaction
BEGIN TRAN StockSettlement
-- Update the open orders to completed
UPDATE [ManagedAccountOpenOrders] WITH (Snapshot)SET [Completed] = 1WHERE [ID] = @OpenOrderID
-- Get the Open Order Transaction Information
SELECT @ManagedAccountID = maoo.[ID],@OrderType = maoo.[OrderType],@CashAvailable = ma.[CashAvailable]
FROM [ManagedAccountOpenOrders] maoo
JOIN [ManagedAccount] ma ON ma.ID = maoo.[ManagedAccountID]
WHERE maoo.[ID] = @OpenOrderID
-- Buy Order
IF @OrderType = 1
BEGIN
UPDATE [ManagedAccount]SET [CashAvailable] = [CashAvailable] -
(@Price * CAST(@Shares AS DECIMAL))
WHERE [ID] = @ManagedAccountID
END
ELSE
-- Sell Order
BEGIN
UPDATE [ManagedAccount]SET [CashAvailable] = [CashAvailable] +
(@Price * AST(@Shares AS ECIMAL))
WHERE [ID] = @ManagedAccountID
END
-- Complete the Open Order Transaction
UPDATE [ManagedAccountOpenOrders]SET [Completed] = 1WHERE [ID] = @OpenOrderID
-- Delete the transaction from the ManagedAccountCompletedTransactions table
DELETE FROM [ManagedAccountCompletedTransactions]WHERE ID = @ID
-- Commit the Settlement Transaction
COMMIT TRAN StockSettlement
END
-- Clean up
CLOSE CompletedOrderCursor
DEALLOCATE CompletedOrderCursor
-- Make sure all the transactions were processed (should be 0)
SELECT 'After Processing Completed Transactions', count(*)
FROM [ManagedAccountCompletedTransactions]
GO
References
Enhanced Always On:
In Memory Column Store Indexes:
Query Store: