Time Series
A user at a small stock exchange (20-25M quotes/day, IDS 11.70 moving to 12.10 on Linux) built a TimeSeries row type, raw table and virtual table, but loading existing data through the virtual table was very slow. Advice given: drop the redundant timestamp column and slim the row type (shorter datetimes/chars) to fit more elements per page; set threshold(0) so data goes straight into containers; add a primary key; create and name your own containers rather than letting autopool create them (more containers, per dbspace, with controlled extents); use the TSL loader in 12.10, or a virtual table with the elem_insert (128) flag, and consider waiting for 12.10.FC3. The poster said he would try these; no load timings confirming a fix are recorded.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Hi guys, I am exploring the option of using Time Series in my environment
where my company, which is a small Stock Exchange, recieves 20-25 million
quotes daily. Please see the schemas of the existing table:
create table "informix".t_quote_history
(
qh_seq serial not null ,
qh_timestamp datetime year to fraction(3),
qh_userid varchar(25),
qh_update_type integer,
qh_id integer not null ,
qh_security integer not null ,
qh_trader integer not null ,
qh_upd_seqnum integer,
qh_montage_rank_seqnum integer,
qh_tstamp datetime year to fraction(3),
qh_currency varchar(3),
qh_bidpricetype varchar(3),
qh_bidprice decimal(11,5),
qh_bidquantity integer,
qh_askpricetype varchar(3),
qh_askprice decimal(11,5),
qh_askquantity integer,
qh_open smallint,
qh_unsolicited smallint,
qh_lockcross smallint,
qh_otcbb smallint,
qh_source_node_cd varchar(4),
qh_nqbsecid integer not null ,
qh_symbol varchar(10),
qh_shortname varchar(25),
qh_trid char(8),
qh_mmid char(5),
qh_inbid_mmid char(5),
qh_inask_mmid char(5),
qh_inside_type integer,
qh_bidtstamp datetime year to fraction(3) not null ,
qh_asktstamp datetime year to fraction(3) not null ,
qh_bid_qap_rt integer
default 0,
qh_ask_qap_rt integer
default 0,
qh_publishtime_ts datetime year to fraction(3)
default current year to fraction(3),
qh_lockcross_occurred_in integer
default 0,
qh_inside_seqnum integer
default 0,
qh_origtime_ts datetime year to fraction(3)
) in t_quote_hist extent size 1000000 next size 50000 lock mode row;
I created the ROW TYPE AND NEW TABLE as below:
create row type 'informix'.quote (
qh_timestamp DATETIME YEAR TO FRACTION(5),
qh_userid VARCHAR(25),
qh_update_type INTEGER,
qh_id INTEGER,
qh_security INTEGER,
qh_trader INTEGER,
qh_upd_seqnum INTEGER,
qh_montage_rank_seqnum INTEGER,
qh_tstamp DATETIME YEAR TO FRACTION,
qh_currency VARCHAR(3),
qh_bidpricetype VARCHAR(3),
qh_bidprice DECIMAL(11,5),
qh_bidquantity INTEGER,
qh_askpricetype VARCHAR(3),
qh_askprice DECIMAL(11,5),
qh_askquantity INTEGER,
qh_open SMALLINT,
qh_unsolicited SMALLINT,
qh_lockcross SMALLINT,
qh_otcbb SMALLINT,
qh_source_node_cd VARCHAR(4),
qh_nqbsecid INTEGER,
qh_symbol VARCHAR(10),
qh_shortname VARCHAR(25),
qh_trid CHAR(8),
qh_inbid_mmid CHAR(5),
qh_inask_mmid CHAR(5),
qh_inside_type INTEGER,
qh_bidtstamp DATETIME YEAR TO FRACTION,
qh_asktstamp DATETIME YEAR TO FRACTION,
qh_bid_qap_rt INTEGER,
qh_ask_qap_rt INTEGER,
qh_publishtime_ts DATETIME YEAR TO FRACTION,
qh_lockcross_occurred_in INTEGER,
qh_inside_seqnum INTEGER,
qh_origtime_ts DATETIME YEAR TO FRACTION)
;
create raw table 'informix'.quote_history (
qh_mmid CHAR(5),
quote_q timeseries(quote) not null
)
extent size 160000 next size 160000
lock mode page;
Virtual Table:
EXECUTE PROCEDURE TSCreateVirtualTab('t_quote_history','quote_history','origin(2010-11-10
00:00:00.00000),calendar(ts_1min),threshold(10),irregular', 1, 'quote_q');
I tried to load the existing data into this table using a virtual table, but
the load is very slow. Please suggest how to use TS in the correct way.
regards,
Nitin Mathur
Nitin:
I have some concerns about your quote_q row type. You should not need the
qh_timestamp column as the timestamp that is part of the timeseries column
should take care of that. The existence of four other timestamp columns
qh_bidtimestamp, qh_asktimestamp, qh_publishtime_ts, and qh_origtime_ts
have me wondering whether this row type and timestamp table are being used
to hold four different kinds of quotes that perhaps should be separate
tables?
None of that addresses the performance problem, but ...
Version and platform my help us help you.
Art
Art S. Kagel, Principal Consultant
ASK Database Management
Blog: http://informix-myview.blogspot.com/
Disclaimer: Please keep in mind that my own opinions are my own opinions
and do not reflect on the IIUG, nor any other organization with which I am
associated either explicitly, implicitly, or by inference. Neither do
those opinions reflect those of other individuals affiliated with any
entity with which I am affiliated nor those of the entities themselves.
On Tue, Mar 4, 2014 at 1:06 PM, NITIN MATHUR <nitin_maths@rediffmail.com>wrote:
> Hi guys, I am exploring the option of using Time Series in my environment
> where my company, which is a small Stock Exchange, recieves 20-25 million
> quotes daily. Please see the schemas of the existing table:
>
> create table "informix".t_quote_history
> (
>
> qh_seq serial not null ,
>
> qh_timestamp datetime year to fraction(3),
>
> qh_userid varchar(25),
>
> qh_update_type integer,
>
> qh_id integer not null ,
>
> qh_security integer not null ,
>
> qh_trader integer not null ,
>
> qh_upd_seqnum integer,
>
> qh_montage_rank_seqnum integer,
>
> qh_tstamp datetime year to fraction(3),
>
> qh_currency varchar(3),
>
> qh_bidpricetype varchar(3),
>
> qh_bidprice decimal(11,5),
>
> qh_bidquantity integer,
>
> qh_askpricetype varchar(3),
>
> qh_askprice decimal(11,5),
>
> qh_askquantity integer,
>
> qh_open smallint,
>
> qh_unsolicited smallint,
>
> qh_lockcross smallint,
>
> qh_otcbb smallint,
>
> qh_source_node_cd varchar(4),
>
> qh_nqbsecid integer not null ,
>
> qh_symbol varchar(10),
>
> qh_shortname varchar(25),
>
> qh_trid char(8),
>
> qh_mmid char(5),
>
> qh_inbid_mmid char(5),
>
> qh_inask_mmid char(5),
>
> qh_inside_type integer,
>
> qh_bidtstamp datetime year to fraction(3) not null ,
>
> qh_asktstamp datetime year to fraction(3) not null ,
>
> qh_bid_qap_rt integer
>
> default 0,
>
> qh_ask_qap_rt integer
>
> default 0,
>
> qh_publishtime_ts datetime year to fraction(3)
>
> default current year to fraction(3),
>
> qh_lockcross_occurred_in integer
>
> default 0,
>
> qh_inside_seqnum integer
>
> default 0,
>
> qh_origtime_ts datetime year to fraction(3)
> ) in t_quote_hist extent size 1000000 next size 50000 lock mode row;
>
> I created the ROW TYPE AND NEW TABLE as below:
>
> create row type 'informix'.quote (
> qh_timestamp DATETIME YEAR TO FRACTION(5),
> qh_userid VARCHAR(25),
> qh_update_type INTEGER,
> qh_id INTEGER,
> qh_security INTEGER,
> qh_trader INTEGER,
> qh_upd_seqnum INTEGER,
> qh_montage_rank_seqnum INTEGER,
> qh_tstamp DATETIME YEAR TO FRACTION,
> qh_currency VARCHAR(3),
> qh_bidpricetype VARCHAR(3),
> qh_bidprice DECIMAL(11,5),
> qh_bidquantity INTEGER,
> qh_askpricetype VARCHAR(3),
> qh_askprice DECIMAL(11,5),
> qh_askquantity INTEGER,
> qh_open SMALLINT,
> qh_unsolicited SMALLINT,
> qh_lockcross SMALLINT,
> qh_otcbb SMALLINT,
> qh_source_node_cd VARCHAR(4),
> qh_nqbsecid INTEGER,
> qh_symbol VARCHAR(10),
> qh_shortname VARCHAR(25),
> qh_trid CHAR(8),
> qh_inbid_mmid CHAR(5),
> qh_inask_mmid CHAR(5),
> qh_inside_type INTEGER,
> qh_bidtstamp DATETIME YEAR TO FRACTION,
> qh_asktstamp DATETIME YEAR TO FRACTION,
> qh_bid_qap_rt INTEGER,
> qh_ask_qap_rt INTEGER,
> qh_publishtime_ts DATETIME YEAR TO FRACTION,
> qh_lockcross_occurred_in INTEGER,
> qh_inside_seqnum INTEGER,
> qh_origtime_ts DATETIME YEAR TO FRACTION)
> ;
>
> create raw table 'informix'.quote_history (
>
> qh_mmid CHAR(5),
>
> quote_q timeseries(quote) not null
> )
> extent size 160000 next size 160000
> lock mode page;
>
> Virtual Table:
>
> EXECUTE PROCEDURE TSCreateVirtualTab('t_quote_history',> 'quote_history','origin(2010-11-10
> 00:00:00.00000),calendar(ts_1min),threshold(10),irregular', 1, 'quote_q');
>
> I tried to load the existing data into this table using a virtual table,
> but
> the load is very slow. Please suggest how to use TS in the correct way.
>
> regards,
>
> Nitin Mathur
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--001a11c3ce329525ae04f3cc9082
Define 'running' slow ?
How are the containers constructed?
What is the page size of the dbspace containing the TS data
Do you have the TS in row and in containers ?
Cheers
Paul
> -----Original Message-----
> From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Art
> Kagel
> Sent: Tuesday, March 04, 2014 1:04 PM
> To: ids@iiug.org
> Subject: Re: Time Series [32632]
>
> Nitin:
>
> I have some concerns about your quote_q row type. You should not need
> the
> qh_timestamp column as the timestamp that is part of the timeseries column
> should take care of that. The existence of four other timestamp columns
> qh_bidtimestamp, qh_asktimestamp, qh_publishtime_ts, and
> qh_origtime_ts
> have me wondering whether this row type and timestamp table are being
> used
> to hold four different kinds of quotes that perhaps should be separate
> tables?
>
> None of that addresses the performance problem, but ...
>
> Version and platform my help us help you.
>
> Art
>
> Art S. Kagel, Principal Consultant
> ASK Database Management
>
> Blog: http://informix-myview.blogspot.com/
>
> Disclaimer: Please keep in mind that my own opinions are my own opinions
> and do not reflect on the IIUG, nor any other organization with which I am
> associated either explicitly, implicitly, or by inference. Neither do
> those opinions reflect those of other individuals affiliated with any
> entity with which I am affiliated nor those of the entities themselves.
>
> On Tue, Mar 4, 2014 at 1:06 PM, NITIN MATHUR
> <nitin_maths@rediffmail.com>wrote:
>
> > Hi guys, I am exploring the option of using Time Series in my
environment
> > where my company, which is a small Stock Exchange, recieves 20-25
million
> > quotes daily. Please see the schemas of the existing table:
> >
> > create table "informix".t_quote_history
> > (
> >
> > qh_seq serial not null ,
> >
> > qh_timestamp datetime year to fraction(3),
> >
> > qh_userid varchar(25),
> >
> > qh_update_type integer,
> >
> > qh_id integer not null ,
> >
> > qh_security integer not null ,
> >
> > qh_trader integer not null ,
> >
> > qh_upd_seqnum integer,
> >
> > qh_montage_rank_seqnum integer,
> >
> > qh_tstamp datetime year to fraction(3),
> >
> > qh_currency varchar(3),
> >
> > qh_bidpricetype varchar(3),
> >
> > qh_bidprice decimal(11,5),
> >
> > qh_bidquantity integer,
> >
> > qh_askpricetype varchar(3),
> >
> > qh_askprice decimal(11,5),
> >
> > qh_askquantity integer,
> >
> > qh_open smallint,
> >
> > qh_unsolicited smallint,
> >
> > qh_lockcross smallint,
> >
> > qh_otcbb smallint,
> >
> > qh_source_node_cd varchar(4),
> >
> > qh_nqbsecid integer not null ,
> >
> > qh_symbol varchar(10),
> >
> > qh_shortname varchar(25),
> >
> > qh_trid char(8),
> >
> > qh_mmid char(5),
> >
> > qh_inbid_mmid char(5),
> >
> > qh_inask_mmid char(5),
> >
> > qh_inside_type integer,
> >
> > qh_bidtstamp datetime year to fraction(3) not null ,
> >
> > qh_asktstamp datetime year to fraction(3) not null ,
> >
> > qh_bid_qap_rt integer
> >
> > default 0,
> >
> > qh_ask_qap_rt integer
> >
> > default 0,
> >
> > qh_publishtime_ts datetime year to fraction(3)
> >
> > default current year to fraction(3),
> >
> > qh_lockcross_occurred_in integer
> >
> > default 0,
> >
> > qh_inside_seqnum integer
> >
> > default 0,
> >
> > qh_origtime_ts datetime year to fraction(3)
> > ) in t_quote_hist extent size 1000000 next size 50000 lock mode row;
> >
> > I created the ROW TYPE AND NEW TABLE as below:
> >
> > create row type 'informix'.quote (
> > qh_timestamp DATETIME YEAR TO FRACTION(5),
> > qh_userid VARCHAR(25),
> > qh_update_type INTEGER,
> > qh_id INTEGER,
> > qh_security INTEGER,
> > qh_trader INTEGER,
> > qh_upd_seqnum INTEGER,
> > qh_montage_rank_seqnum INTEGER,
> > qh_tstamp DATETIME YEAR TO FRACTION,
> > qh_currency VARCHAR(3),
> > qh_bidpricetype VARCHAR(3),
> > qh_bidprice DECIMAL(11,5),
> > qh_bidquantity INTEGER,
> > qh_askpricetype VARCHAR(3),
> > qh_askprice DECIMAL(11,5),
> > qh_askquantity INTEGER,
> > qh_open SMALLINT,
> > qh_unsolicited SMALLINT,
> > qh_lockcross SMALLINT,
> > qh_otcbb SMALLINT,
> > qh_source_node_cd VARCHAR(4),
> > qh_nqbsecid INTEGER,
> > qh_symbol VARCHAR(10),
> > qh_shortname VARCHAR(25),
> > qh_trid CHAR(8),
> > qh_inbid_mmid CHAR(5),
> > qh_inask_mmid CHAR(5),
> > qh_inside_type INTEGER,
> > qh_bidtstamp DATETIME YEAR TO FRACTION,
> > qh_asktstamp DATETIME YEAR TO FRACTION,
> > qh_bid_qap_rt INTEGER,
> > qh_ask_qap_rt INTEGER,
> > qh_publishtime_ts DATETIME YEAR TO FRACTION,
> > qh_lockcross_occurred_in INTEGER,
> > qh_inside_seqnum INTEGER,
> > qh_origtime_ts DATETIME YEAR TO FRACTION)
> > ;
> >
> > create raw table 'informix'.quote_history (
> >
> > qh_mmid CHAR(5),
> >
> > quote_q timeseries(quote) not null
> > )
> > extent size 160000 next size 160000
> > lock mode page;
> >
> > Virtual Table:
> >
> > EXECUTE PROCEDURE TSCreateVirtualTab('t_quote_history',> > 'quote_history','origin(2010-11-10
> > 00:00:00.00000),calendar(ts_1min),threshold(10),irregular', 1,
'quote_q');
> >
> > I tried to load the existing data into this table using a virtual table,
> > but
> > the load is very slow. Please suggest how to use TS in the correct way.
> >
> > regards,
> >
> > Nitin Mathur
> >
> >
> >
> >
> **********************************************************
> *********************
> > Forum Note: Use "Reply" to post a response in the discussion forum.
> >
> >
>
> --001a11c3ce329525ae04f3cc9082
>
>
> **********************************************************
> *********************
> Forum Note: Use "Reply" to post a response in the discussion forum.
Thanks Art for the reply. Yes, I am wrong with the qh_timestamp column, will remove it. The other datetime columns capture different timestamps of the quote (when was it initiated, when was it published, database write time etc). The IFX version in Prod is 11.70 FC5 but I am in process of upgrading these to 12.10 FC2, so have been experimenting with TS in 12.10 on Linux 86_64. Paul, I am comparing the load times with other load methods, HPL, external tables, DBLoad. I created containers in 4 dbspaces which are 16K page size. However, my TS is irregular, while the containers I created using OAT are all regular, so I am not sure whether they are being used. regards, Nitin
Select * from tscontainertable, or select * from <tstable> - you will seethe container in the result set. If you see autopool then the engine is
creating the containers automagically and you probably do not want to do
that
You also want to look at the TSL is you are loading in bulk
How many containers in each dbspace ?
Are they dedicated to the row type ?
How to you allocate the containers ?
I'd wait for FC3 - the TS performance is better and there are some nice new
functions
Cheers
Paul
> -----Original Message-----
> From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of
> NITIN MATHUR
> Sent: Tuesday, March 04, 2014 1:47 PM
> To: ids@iiug.org
> Subject: Re: Time Series [32635]
>
> Thanks Art for the reply.
>
> Yes, I am wrong with the qh_timestamp column, will remove it. The other
> datetime columns capture different timestamps of the quote (when was it
> initiated, when was it published, database write time etc).
>
> The IFX version in Prod is 11.70 FC5 but I am in process of upgrading
these to
> 12.10 FC2, so have been experimenting with TS in 12.10 on Linux 86_64.
>
> Paul, I am comparing the load times with other load methods, HPL, external
> tables, DBLoad.
>
> I created containers in 4 dbspaces which are 16K page size. However, my TS
is
> irregular, while the containers I created using OAT are all regular, so I
am
> not sure whether they are being used.
>
> regards,
>
> Nitin
>
>
> **********************************************************
> *********************
> Forum Note: Use "Reply" to post a response in the discussion forum.
Hi Nitin,
There are a few first easy steps to try. First change try changing
threshold(10) to threshold(0). This will cause all the elements in the
timeseries to be placed in the container from the start. Next, add a
primary key to your base table. With these two changes, it will open up
some options to use the faster utilities that load directly in
containers.. If you are on Informix 12.10, you can try the TimeSeries
Loader (TSL interface). This is the fastest way to load elements into the
TimeSeries. If you want load with the TimeSeries Virtual Table, you can
create a TimeSeries Virtual Table and enable the elem_insert flag (128) .
Both TSL and the virtual table elem_insert require you create all the
timeseries value in the base table before you load the timeseries values.
insert into quote_history values (yourpk, 'origin(2010-11-1000:00:00.00000),calendar(ts_1min),threshold(0),irregular');
Since you have not specified a container name, a container will be created
automatically. You may want to consider creating one or containers and
distributing the ts values across the containers. If you create your own
containers, you have control over which dbspace is used and the extent
sizes.
With elem_insert, you cannot use the nts parameter on a ts virtual table
with element server.
EXECUTE PROCEDURE TSCreateVirtualTab('t_quote_history', 'quote_history',128+1, 'quote_q');
With the Virtual table, the dbaccess load command does a good job of
loading the timeseries elements values.
Hope this helps.
-- Mark.
Mark Ashworth
IBM Informix Extensibility Architect
Office phone: +1 (905) 413-5033
Alternate: +1 (905) 697-8094
Email: ashworth@ca.ibm.com
Check out my blog
From: "NITIN MATHUR" <nitin_maths@rediffmail.com>
To: ids@iiug.org,
Date: 03/04/2014 01:06 PM
Subject: Time Series [32631]
Sent by: ids-bounces@iiug.org
Hi guys, I am exploring the option of using Time Series in my environment
where my company, which is a small Stock Exchange, recieves 20-25 million
quotes daily. Please see the schemas of the existing table:
create table "informix".t_quote_history
(
qh_seq serial not null ,
qh_timestamp datetime year to fraction(3),
qh_userid varchar(25),
qh_update_type integer,
qh_id integer not null ,
qh_security integer not null ,
qh_trader integer not null ,
qh_upd_seqnum integer,
qh_montage_rank_seqnum integer,
qh_tstamp datetime year to fraction(3),
qh_currency varchar(3),
qh_bidpricetype varchar(3),
qh_bidprice decimal(11,5),
qh_bidquantity integer,
qh_askpricetype varchar(3),
qh_askprice decimal(11,5),
qh_askquantity integer,
qh_open smallint,
qh_unsolicited smallint,
qh_lockcross smallint,
qh_otcbb smallint,
qh_source_node_cd varchar(4),
qh_nqbsecid integer not null ,
qh_symbol varchar(10),
qh_shortname varchar(25),
qh_trid char(8),
qh_mmid char(5),
qh_inbid_mmid char(5),
qh_inask_mmid char(5),
qh_inside_type integer,
qh_bidtstamp datetime year to fraction(3) not null ,
qh_asktstamp datetime year to fraction(3) not null ,
qh_bid_qap_rt integer
default 0,
qh_ask_qap_rt integer
default 0,
qh_publishtime_ts datetime year to fraction(3)
default current year to fraction(3),
qh_lockcross_occurred_in integer
default 0,
qh_inside_seqnum integer
default 0,
qh_origtime_ts datetime year to fraction(3)
) in t_quote_hist extent size 1000000 next size 50000 lock mode row;
I created the ROW TYPE AND NEW TABLE as below:
create row type 'informix'.quote (
qh_timestamp DATETIME YEAR TO FRACTION(5),
qh_userid VARCHAR(25),
qh_update_type INTEGER,
qh_id INTEGER,
qh_security INTEGER,
qh_trader INTEGER,
qh_upd_seqnum INTEGER,
qh_montage_rank_seqnum INTEGER,
qh_tstamp DATETIME YEAR TO FRACTION,
qh_currency VARCHAR(3),
qh_bidpricetype VARCHAR(3),
qh_bidprice DECIMAL(11,5),
qh_bidquantity INTEGER,
qh_askpricetype VARCHAR(3),
qh_askprice DECIMAL(11,5),
qh_askquantity INTEGER,
qh_open SMALLINT,
qh_unsolicited SMALLINT,
qh_lockcross SMALLINT,
qh_otcbb SMALLINT,
qh_source_node_cd VARCHAR(4),
qh_nqbsecid INTEGER,
qh_symbol VARCHAR(10),
qh_shortname VARCHAR(25),
qh_trid CHAR(8),
qh_inbid_mmid CHAR(5),
qh_inask_mmid CHAR(5),
qh_inside_type INTEGER,
qh_bidtstamp DATETIME YEAR TO FRACTION,
qh_asktstamp DATETIME YEAR TO FRACTION,
qh_bid_qap_rt INTEGER,
qh_ask_qap_rt INTEGER,
qh_publishtime_ts DATETIME YEAR TO FRACTION,
qh_lockcross_occurred_in INTEGER,
qh_inside_seqnum INTEGER,
qh_origtime_ts DATETIME YEAR TO FRACTION)
;
create raw table 'informix'.quote_history (
qh_mmid CHAR(5),
quote_q timeseries(quote) not null
)
extent size 160000 next size 160000
lock mode page;
Virtual Table:
EXECUTE PROCEDURE TSCreateVirtualTab('t_quote_history','quote_history','origin(2010-11-10
00:00:00.00000),calendar(ts_1min),threshold(10),irregular', 1, 'quote_q');
I tried to load the existing data into this table using a virtual table,
but
the load is very slow. Please suggest how to use TS in the correct way.
regards,
Nitin Mathur
*******************************************************************************
Forum Note: Use "Reply" to post a response in the discussion forum.
Thanks Paul for the quick response.
Yes, I see the autopool containers at the end along with the 40 containers I
added. So, TS is not using my containers probably coz it is for regular TS?
I created 10 containers in each dbspace (4).
I didnt allocate the containers, can I use the below syntax for this?
EXECUTE PROCEDURE TSCreateVirtualTab('MeterReading_v','MeterReading', 'origin(2012-01-01
00:00:00.00000),calendar(ts_15min),container(autopool00000000),
threshold(0),regular', 0, 'Reading');
regards,
Nitin
Cos you are not creating the TS in the container - just give it a container
name when you create the TS
Generally you want more containers rather than less
I never (intentionally) use autopools
Cheers
Paul
> -----Original Message-----
> From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of
> NITIN MATHUR
> Sent: Tuesday, March 04, 2014 2:13 PM
> To: ids@iiug.org
> Subject: Re: RE: Time Series [32638]
>
> Thanks Paul for the quick response.
>
> Yes, I see the autopool containers at the end along with the 40 containers
I
> added. So, TS is not using my containers probably coz it is for regular
TS?
>
> I created 10 containers in each dbspace (4).
>
> I didnt allocate the containers, can I use the below syntax for this?
>
> EXECUTE PROCEDURE TSCreateVirtualTab('MeterReading_v',> 'MeterReading', 'origin(2012-01-01
> 00:00:00.00000),calendar(ts_15min),container(autopool00000000),
> threshold(0),regular', 0, 'Reading');
>
> regards,
> Nitin
>
>
> **********************************************************
> *********************
> Forum Note: Use "Reply" to post a response in the discussion forum.
Hello Nitin, Other important considerations are the peak arrival rates and the distributions of the ticks amongst the securities. The TSL is great, particularly for meter type data where the readings, probably in batches, are evenly distributed between the meters. With this type of distribution it is unlikely that the time series pages are already in the cache. This is not the case with live financial data where the top 100 securities may account for the majority of the readings, in this case the pages will already be in memory for most of the readings. The TSL will still be the fastest but there may be other methods that are fast enough. I would advise looking at the data to find the peak arrival rates and the distributions amongst the stocks, decide the target latentcies and then find the simplest method that meets these conditions. Best regards John
Thanks Mark and John, I will try to use the TSL now as VT seems slower compared to traditional load methods. However, I was wondering, how would the application write to this table? I guess, app will have to rely on VT only. Also, how is the query performance on TS table when we have a filter on a column which is a part of ROW data. regards, Nitin
On a 8 CPU machine you should easily be loading 400K data points per second once it is configure correctly, http://www.linkedin.com/groups/Informix-Time-Series-4012278 Top article The app layer can use the VT or the tables directly - purely up to you Blindingly quick Cheers Paul > -----Original Message----- > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of > NITIN MATHUR > Sent: Wednesday, March 05, 2014 8:37 AM > To: ids@iiug.org > Subject: Re: Time Series [32648] > > Thanks Mark and John, I will try to use the TSL now as VT seems slower > compared to traditional load methods. > > However, I was wondering, how would the application write to this table? I > guess, app will have to rely on VT only. > > Also, how is the query performance on TS table when we have a filter on a > column which is a part of ROW data. > > regards, > > Nitin > > > ********************************************************** > ********************* > Forum Note: Use "Reply" to post a response in the discussion forum.
Hello Nitin, I think the row type and table needs some explanation/work. Usually the security is part of the table and the time series is a time series of trades of that security, ie 1 timeseries per security. Some implementations have separate timeseries for bid and ask. It looks like some of the fields are related, eg qh_symbol, qh_security, qh_shortname, if they are it should be normalised out. Is currency a function of security ? Also, there are a lot of datetimes, the possible range of the datetimes as specified is large, I think this could be reduced to either an interval relative to the timestamp (as an interval type or as seconds as a float), this will save space. The small varchar should be replaced by chars, it takes less space and perhaps some of the other elements could be combined and enumerated to save space but it hard to be certain unless without more knowledge of the data and what proportion are null. The goal is to get as many elements as possible on each timeseries page, this rowsize is 241 bytes, I would try to reduce this as much as possible. Best regards John
Thanks Paul and John, I will use the recommendations and see if it helps. regards, Nitin