Hello, I have this Database:
CREATE TABLE other( field1 VARCHAR(15), field2 VARCHAR(2), field3 VARCHAR(255), field4 VARCHAR(255)); CREATE TABLE main ( field1 VARCHAR(15), field5 VARCHAR(8), field2 VARCHAR(2), field6 VARCHAR(2), INS_82 VARCHAR(2), field7 VARCHAR(255)); CREATE TABLE main_long ( field1 VARCHAR(15), field5 VARCHAR(8), field2 VARCHAR(2), field6 VARCHAR(2), INS_82 VARCHAR(2), field7 VARCHAR(255), field7_2 VARCHAR(255)); CREATE UNIQUE INDEX main_index ON main (field2,field5,field7); CREATE UNIQUE INDEX mainl_index ON main_long(field2,field5,field7); CREATE UNIQUE INDEX other ON other(field3); I get some data from a binary file I convert then in ASCI HEX, and I insert them in the DB using INSERT INTO command. I make a huge number of insertions in the main table (about 5 M every DB file) For performance reasons (the insertions starts very quick, but the speed slows down in a constant way), and for other considerations is convenient to use a new DB every 5 M records. So every 5 M records the program create a new DB. PROBLEM I have to search for field5 in some DB files (30 or more for a total of 50 GB of space and 150 M of records ). The DBs have the same structure. I want to find the string (if exist) in all DBs. The query is like this: select * from main where field5= "AABBCCDD"; When I search in only one file the first query is slow (more than 1 minute). The second query is more fast (about 3 secs). But I have to search for this filed5 in all DB that I have, so: I open a DB, I make the query, and I close the DB, this process for every DB. The result is that the search is very long and I can't optimize the second query because I open and close the DBs. Can someone help me to resolve these problems? In particular the low speed of the search is the biggest problem. Using some MDB and the same search techique I'm able to search in seconds over 50 GB of data ( more tahn 30 MDB files). The version of sqlite is 3.2.1 and the OS is Windows. Sorry for my poor English. Thanks in advance, China --------------------------------------------------------------- Scegli il tuo dominio preferito e attiva la tua email! Da oggi l'eMail di superEva e' ancora piu' veloce e ricca di funzioni! http://webmail.supereva.it/new/ --------------------------------------------------------------- --------------------------------------------------------------- Scegli il tuo dominio preferito e attiva la tua email! Da oggi l'eMail di superEva e' ancora piu' veloce e ricca di funzioni! http://webmail.supereva.it/new/ ---------------------------------------------------------------
