On 25 May 2012, at 3:04am, IQuant <[email protected]> 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.
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. Simon. _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
