On 14 Sep 2016, at 11:57am, SinhaK <[email protected]> wrote:
> create table 'Stream0' ( TokenNo int NOT NULL,OrderId integer NOT NULL,SIDE > int NOT NULL,PRICE int NOT NULL,QTY int NOT NULL,PRIMARY KEY (OrderId)); > > Index On Table : > > CREATE INDEX DataFilterIndex ON 'Stream0'(TokenNo , SIDE, Price,Qty); > > [...] > > "select TokenNo,Price ,sum(QTY) from 'Stream0' where TokenNo=?1 and Side=66 > group by Price order by Price desc limit 5"; Thank you for your very detailed description of your problem, which has saved a lot of question/answer. The above INDEX is a very good index for this SELECT. The only improvement would be to run ANALYZE on your database file. You only need to do this once, when your database file has some plausible data in it (NOT just after creating blank table). The results of the ANALYZE command are stored in the database file so you do not need to include the ANALYZE command in your program's code. > rc= sqlite3_exec(db, "PRAGMA synchronous = OFF", NULL, NULL, &zErrMsg); > rc= sqlite3_exec(db, "PRAGMA count_changes = false", NULL, NULL, &zErrMsg); > rc= sqlite3_exec(db, "PRAGMA journal_mode = OFF", NULL, NULL, &zErrMsg); Messing with "synchronous" and "journal_mode" in a multi-accessed network environment can cause problems up to and including corrupt databases. For testing purposes, please remove those two PRAGMAs from your program and see whether this fixes your problem. You might put them back later once you understand the problem but the situation will be clearer if you're using the default setup. The count_changes PRAGMA exists for historical reasons and should not be used in new code. A few of the configuration changes I see you've listed do not significantly speed up SQLite and can make it difficult for you to identify problems. You might find that using default configuration makes your problems disappear. (I understand that speed is critical in HFT but the amount of time SQLite spends in software is very small compared to the time it spends waiting for data to be read/written.). If you feel like enabling them again, you might do benchmark testing on each one to find out whether it makes a difference. > if the packet processing is delayed due to any reason there can be a packet > drop and a delayed packet is of no use in High Frequency trading environment. > There can be a minimum packet flow of 5 mbps where each packet is of a > maximum 45 bytes size. Packet drop is a hardware issue. There is nothing inside SQLite which understands that you're accessing its database file across a network. As far as SQLite is concerned, you are accessing a file stored on the computer which is running the program doing the SQLite calls. If you're seeing dropped packets then it may be more useful to look for problems in your network driver and network hardware. Simon. _______________________________________________ sqlite-users mailing list [email protected] http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
