> On 25 May 2012, at 3:04am, IQuant <sql...@iquant.co.cc> wrote:
>
> > update TICKDATA set IQ_A = ROUND(ASK - (
> > select t2.ASK from TICKDATA t2
> > where t2.SYMBOL = TICKDATA.SYMBOL and t2.TIMESTAMP <
> > TICKDATA.TIMESTAMP ORDER BY T2.SYMBOL, t2.timestamp DESC LIMIT 1),4),
> > IQ_B = ROUND(BID - (
> > select t2.BID from TICKDATA t2
> > where t2.SYMBOL = TICKDATA.SYMBOL and t2.TIMESTAMP <
> > TICKDATA.TIMESTAMP ORDER BY T2.SYMBOL, t2.timestamp DESC LIMIT 1),4),
> > IQ_T = ROUND(TRADEPRICE - (
> > select t2.TRADEPRICE from TICKDATA t2
> > where t2.SYMBOL = TICKDATA.SYMBOL and t2.TIMESTAMP <
> > TICKDATA.TIMESTAMP ORDER BY T2.SYMBOL, t2.timestamp DESC LIMIT 1),4),
> > IQ_X = (select t2.timestamp from TICKDATA t2
> > where t2.SYMBOL = TICKDATA.SYMBOL and t2.TIMESTAMP <
> > TICKDATA.TIMESTAMP ORDER BY T2.SYMBOL, t2.timestamp DESC LIMIT 1);
> >
> > The consolidated tick database is 1.5GB / 32M records and the above
> > query takes 5 minutes to run.
> On 24 May, 2012 20:11, Simon Slavin said:
> You appear to be doing the same sub-select four times. I don't know whether
> SQLite optimizes these into one or not, but if you're using a programming
> language I bet you could speed the query up a great deal by doing that
> subselect first, and substituting the results into the UPDATE command.
It doesn't optimize correlated subqueries even with the same subselect criteria
-- however the btree indexes are already positioned after the first so the next
three just traverse the same tree already in memory.
However, here are two ways of moving the correlated queries from the inner loop
to an outer loop:
-- need unique index on TICKDATA (SYMBOL, TIMESTAMP)
-- the view and the trigger can be defined in the database schema once
CREATE VIEW TICKMAX
AS
SELECT ASK, BID, TRADEPRICE, TIMESTAMP, SYMBOL
FROM TICKDATA
WHERE TIMESTAMP = MAX(TIMESTAMP)
GROUP BY SYMBOL;
CREATE TRIGGER TICKMAXUPDATE INSTEAD OF UPDATE ON TICKMAX FOR EACH ROW
BEGIN
UPDATE TICKMAX
SET IQ_A = ROUND(ASK - OLD.ASK, 4),
IQ_B = ROUND(BID - OLD.BID, 4),
IQ_T = ROUND(TRADEPRICE - OLD.TRADEPRICE, 4),
IQ_X = OLD.TIMESTAMP
WHERE SYMBOL = OLD.SYMBOL
AND TIMESTAMP < OLD.TIMESTAMP;
END;
-- everytime you want to do an update, run this:
UPDATE TICKMAX SET ASK = ASK + 1;
Of course, if you are using a programming language you could do something like
this:
cr.execute('BEGIN IMMEDIATE;')
for row in cr.execute('''SELECT ASK, BID, TRADEPRICE, TIMESTAMP, SYMBOL
FROM TICKDATA
WHERE TIMESTAMP = MAX(TIMESTAMP)
GROUP BY SYMBOL;'''):
cr.execute('''UPDATE TICKMAX
SET IQ_A = ROUND(ASK - :ASK, 4),
IQ_B = ROUND(BID - :BID, 4),
IQ_T = ROUND(TRADEPRICE - :TRADEPRICE, 4),
IQ_X = :TIMESTAMP
WHERE SYMBOL = :SYMBOL
AND TIMESTAMP < :TIMESTAMP;''', row)
cr.execute('COMMIT;)
In other words, select the values at the maximum timestamp for each symbol,
then for each row returned execute the update statement updating the rest of
the rows for that symbol ... both the trigger and the above code move the
selection of the updating values from the inner loop to an outer loop.
Of course, they may not make much of a difference at all. The latter two will
do exactly count(distinct symbol) less row updates, but that is still pretty
much every row in the database. You will have to try it and see if the
performance increase (if any) is worth the additional complexity.
---
() ascii ribbon campaign against html e-mail
/\ www.asciiribbon.org
> -----Original Message-----
> From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
> boun...@sqlite.org] On Behalf Of
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] Update Query
>
>
>
>
> Simon.
> _______________________________________________
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
