Thanks for your suggestion Igor Tandetnik: Scope creep expanded the original query to the actual trading instruments and the refactored code has evolved to::
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. Found proper index is key for performance... Symbol,Timestamp _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
