I've never seen an application that would run faster in ANY database vs custom code. Databases are for generic query problems...not the end-all to "store my data" when speed is a concern.
I've pointed out a few times on this list where people are concerned for speed and showed things like 30X faster using your own. I've done network data acquisition like this before and I'll guarantee that you will never keep up with a data burst on a gigabit network. I don't even think winpcap can do that let alone a database. I don't think you need to run your trigger every minute. Just run it every insert. I think the delete will be notably faster than the insert and you won't notice the difference vs running every 60 seconds. What you will want to do is only do a commit every so often...I think you stated you're doing a commit every packet which would be dog slow. -----Original Message----- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Hemant Shah Sent: Thursday, September 09, 2010 8:57 AM To: General Discussion of SQLite Database Subject: EXTERNAL:Re: [sqlite] EXTERNAL: In memory database and locking. How do I setup trigger to run every minute? I thought about writing hash code, but thought sqlite or other in memory database would work. The in memory database seems to keep up with the in coming traffic. Hemant Shah E-mail: hj...@yahoo.com --- On Thu, 9/9/10, Black, Michael (IS) <michael.bla...@ngc.com> wrote: From: Black, Michael (IS) <michael.bla...@ngc.com> Subject: Re: [sqlite] EXTERNAL: In memory database and locking. To: "General Discussion of SQLite Database" <sqlite-users@sqlite.org> Date: Thursday, September 9, 2010, 7:48 AM Have you considered doing your cleanup during a trigger? I assume you're already using transactions for your inserts. I wouldn't think it would be much slower doing it every insert as you'd be deleting a much smaller set every time. This is really a LOT faster if you just hash your info and then periodically walk the hash table to delete old stuff. A database is never going to keep up with a gigabit interface. -----Original Message----- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Hemant Shah Sent: Wednesday, September 08, 2010 10:55 PM To: sqlite-users@sqlite.org Subject: EXTERNAL:[sqlite] In memory database and locking. Folks, I am trying to write an application that reads packets from the network and inserts it into sqlite database, I have a unique key which is combination of couple of columns. I want to find re-transmitted packets so I rely on the fact that if I violate unique key constraint then I have found the duplicate packet. Also, I just want to compare it with packets received within last minute. One of the column is timestamp. I am using C API and statically link sqlite 3.7.2 with my application. Here is what I am doing. When I start my application it creates the database and table and then forks two processes. One process reads packets from network and inserts information about it in the database, if insert fails then it has found re-transmission and it executes the select statement to get the information about previous packet and print information about both packets. The other process wakes up every 60 seconds and deletes all row whose timestamp columns is less then (current timestamp - 60). The timestamp is number of seconds since epoch. The first process is constantly inserting rows into the database, so the other process cannot delete any rows. When I use :memory: for database I do not get any error but it does not delete any rows as the memory footprint of my program keeps on increasing.If I use a file for database I get error that database is locked. Both of these processes are sibling and have same database handle. When I read the documentation I found that in-memory database always uses EXCLUSIVE lock. How do I solve this problem? Thanks. Hemant Shah E-mail: hj...@yahoo.com _______________________________________________ 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 _______________________________________________ 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
