Order when retrieving data from a virtual table

I have some timeseries data in a table called mytest- there are 9 rows and each row contains a timeseries which contains somewhere between 5,000,000 and 55,000,000 elements. Each row has column 'secid' as the primary key. I have created a virtual table mytest_series from this data using the TSCreateVirtualTab() function. dbschema shows me the schema looks like this create table "informix".mytest_series ( secid integer, srcid integer, instname varchar(50), desc varchar(100), percentchange float, lowerbound float, upperbound float, isactive "informix".boolean, state varchar(20), instupdated integer, tstamp datetime year to fraction(5), ask float, bid float, cleansethisfield integer ); I have a SPL routine which needs to perform calculation on the bid and ask fields of the timeseries for every element so my SPL looks something like this DEFINE v_bid, v_ask FLOAT; FOREACH SELECT bid, ask INTO v_bid, v_ask FROM mytest_series WHERE secid = 12345 -- ORDER by tstamp ASC --- Do stuff with bid and ask END FOREACH My question is whether I need the 'ORDER by tstamp ASC' clause ? With it the SQL seems to run much slower. However without it I still get the data back in the correct order. I could not find anything in the documentation for the virtual table which explained the order in which the data would come back so I assumed it would be random. However in my tests it always seems to come back in ascending timestamp order. Can this be guaranteed ? TIA

Niall Macpherson wrote: > I could not find anything in the documentation for the virtual table > which explained the order in which the data would come back so I > assumed it would be random. However in my tests it always seems to come > back in ascending timestamp order. Can this be guaranteed ? No. IDS often seems to return data in the order in which it was inserted which for a log file can be date/time order but it can't be guaranteed. If the speed is too slow with an "ORDER BY" clause consider fragmenting the table and using PDQ to run the query. This is only really suitable for reporting though and not on-line transaction processing. Or if secid is a log sequence number it's possible that ordering by this column may be equivalent to ordering by tstamp but integers that form the primary key should be quicker to sort than long date/time columns. Ben.

Ben Thompson wrote: > No. IDS often seems to return data in the order in which it was inserted > which for a log file can be date/time order but it can't be guaranteed. > Ben. Ben, But this data is *really* stored in a timeseries column via the timeseries datablade, the virtual table is a faithful representation of the base table and therefore *should* present the data in the same order, i.e. chronologically ascending. You can't look upon this in the 'standard' way. Niall, You're correct, there's no explicit statement in the documentation but it does say : <quote> The virtual table interface takes the data encapsulated in the TimeSeries data type and produces a virtual relational table containing the same data. </quote> I know it's open to interpretation buy I've always taken that to mean that the data in the virutal table will be in the same order as in the TS column, i.e. chronologically ascending. In my experience (arguably limited thus far) I have found the data in virtual tables to be in timeseries order. Not conclusive I know, but I hope that helps a bit anyway :-) It should be a prettty quick support call if you want a 'better' answer. Rich