Add these statements immediately after the file header and before the first domain statement that begins on line 7. These edits modify the tempdb and model database size, change the database settings, and bind the cache to memory, preventing data from being paged-out to disk.
use master go alter database tempdb on master=100 go alter database model on master=100 go sp_dboption model, single, 'true' go use model go sp_bindcache c_log, model, syslogs go use master go sp_dboption model, single, 'true' go use model go sp_bindcache c_log, model, syslogs go use master go sp_dboption model, single, 'false' go use model go sp_logiosize '16K' go exec sp_addsegment s1 , model, master exec sp_addsegment s2 , model, master exec sp_addsegment s3 , model, master exec sp_addsegment o1 , model, master go
The STOCK_QUOTE table stores real-time (intraday) quotes. To modify this table, comment out the constraint statement and add statements to reduce lock contention and partition the table to distribute I/O over different devices.
Locate the STOCK_QUOTE table; use double dashes to comment out the line that reads:
constraint PK_STOCK_QUOTE primary key (INSTRUMENT_ID, QUOTE_DATE, QUOTE_SEQ_NBR) on o1
The line should now read:
-- constraint PK_STOCK_QUOTE primary key (INSTRUMENT_ID, QUOTE_DATE, QUOTE_SEQ_NBR) on o1
Add these lines after the open parenthesis ) on
the next line:
) lock datarows partition by roundrobin (p1 on s1, p2 on s2, p3 on s3)
The entire edit looks like this:
-- constraint PK_STOCK_QUOTE primary key (INSTRUMENT_ID, QUOTE_DATE, QUOTE_SEQ_NBR) on o1 ) lock datarows partition by roundrobin (p1 on s1, p2 on s2, p3 on s3) go
The STOCK_TRADE table stores real-time (intraday) trade data. To modify this table, comment out the constraint statement and add statements to reduce lock contention and partition the table to distribute I/O over different devices.
Locate the STOCK_TRADE table, use double dashes to comment out the line that reads:
constraint PK_STOCK_TRADE primary key (INSTRUMENT_ID, TRADE_SEQ_NBR, TRADE_DATE) on o1
The line should now read:
-- constraint PK_STOCK_TRADE primary key (INSTRUMENT_ID, TRADE_SEQ_NBR, TRADE_DATE) on o1
Add these lines after the open parenthesis )on
the next line:
) lock datarows partition by roundrobin (p1 on s1, p2 on s2, p3 on s3)
The entire edit looks like this:
-- constraint PK_STOCK_TRADE primary key (INSTRUMENT_ID, TRADE_SEQ_NBR, TRADE_DATE) on o1 ) lock datarows partition by roundrobin (p1 on s1, p2 on s2, p3 on s3) go
ULYING_INSTR is an association table used to define a financial instrument. You need to add statements after this block to create a local index on quote time for the STOCK_QUOTE table and a local index on trade time for the STOCK_TRADE table.
Locate the ULYING_INSTR table and look for the block that reads:
create table <database owner>.ULYING_INSTR ( OPTION_INSTR_ID SYB_ID not null, INSTRUMENT_ID SYB_ID not null, constraint PK_ULYING_INSTR primary key (OPTION_INSTR_ID, INSTRUMENT_ID) ) go
Add the following lines after the go command:
create index STOCK_QUOTE_QUOTE_TIME on STOCK_QUOTE ( QUOTE_TIME ASC ) on o1 local index go create index STOCK_TRADE_TRADE_TIME on STOCK_TRADE ( TRADE_TIME ASC ) on o1 local index go
The entire edit looks like this:
create table <database owner>.ULYING_INSTR ( OPTION_INSTR_ID SYB_ID not null, INSTRUMENT_ID SYB_ID not null, constraint PK_ULYING_INSTR primary key (OPTION_INSTR_ID, INSTRUMENT_ID) ) go create index STOCK_QUOTE_QUOTE_TIME on STOCK_QUOTE ( QUOTE_TIME ASC ) on o1 local index go create index STOCK_TRADE_TRADE_TIME on STOCK_TRADE ( TRADE_TIME ASC ) on o1 local index go
The LAST_TRADE_PRICE table stores information about the last trade prices of the trading day. The SQL statements that create the LAST_TRADE_PRICE table are not included in the DDL generated from the data model. Follow these instructions to add the necessary SQL statements to your custom script.
This table is used like a temporary table in the cache to support the tick_qry3 sample query. If you run this query against the VLDBServer database instead of RAPCache, you do not need this table, as you have access to historical data.
Locate the section of code that creates the MAJOR_IDST_CLS table. This section begins with the following lines:
/*==============================================*/ /* Table: MAJOR_IDST_CLS */ /*==============================================*/
Add the following lines before the create MAJOR_IDST_CLS table section:
/*==============================================*/ /* Table: LAST_TRADE_PRICE */ /*==============================================*/ create table LAST_TRADE_PRICE (INSTRUMENT_ID SYB_ID not null, TRADING_SYMBOL SYB_CODE_VAR not null, TRADE_PRICE SYB_MONEY null, TRADE_DATE SYB_DATE not null ) create index LAST_TRADE_PRICE_INSTRUMENT_ID on LAST_TRADE_PRICE ( INSTRUMENT_ID ASC ) go
The entire edit looks like this:
/*==============================================*/ /* Table: LAST_TRADE_PRICE */ /*==============================================*/ create table LAST_TRADE_PRICE (INSTRUMENT_ID SYB_ID not null, TRADING_SYMBOL SYB_CODE_VAR not null, TRADE_PRICE SYB_MONEY null, TRADE_DATE SYB_DATE not null ) create index LAST_TRADE_PRICE_INSTRUMENT_ID on LAST_TRADE_PRICE ( INSTRUMENT_ID ASC ) go /*==============================================*/ /* Table: MAJOR_IDST_CLS */ /*==============================================*/
