Poor performance searching from multiple DBs

classic Classic list List threaded Threaded
5 messages Options
Reply | Threaded
Open this post in threaded view
|

Poor performance searching from multiple DBs

chinasky65

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/
---------------------------------------------------------------

Reply | Threaded
Open this post in threaded view
|

Re: Poor performance searching from multiple DBs

Jay Sprenkle
>
> 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";

 Did you put an index on field5?
Reply | Threaded
Open this post in threaded view
|

Re: Re: Poor performance searching from multiple DBs

chinasky65
In reply to this post by chinasky65





>> 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 ).

>> select * from main where field5= "AABBCCDD";



> Did you put an index on field5?

Yes I use:

CREATE UNIQUE INDEX main_index ON main (field2,field5,field7);



Is a quite complex index used for eliminating duplicates, is it OK?



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/
---------------------------------------------------------------

Reply | Threaded
Open this post in threaded view
|

Re: Re: Poor performance searching from multiple DBs

Jay Sprenkle
> >> 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 ).
>
> >> select * from main where field5= "AABBCCDD";
>
>
>
> > Did you put an index on field5?
>
> Yes I use:
>
> CREATE UNIQUE INDEX main_index ON main (field2,field5,field7);

It's not the first field in the index, so I don't think it will use it
for searching on "WHERE field5 = 'xxx'  ".

Try adding an index on field5 only

 CREATE UNIQUE INDEX main_indexf5 ON main (field5);

>
>
>
> Is a quite complex index used for eliminating duplicates, is it OK?

I don't see any problem.
Reply | Threaded
Open this post in threaded view
|

Re: Re: Re: Poor performance searching from multiple DBs

chinasky65
In reply to this post by chinasky65

       

>It's not the first field in the index, so I don't think it >will use it

>for searching on "WHERE field5 = 'xxx'  ".



>Try adding an index on field5 only



> CREATE UNIQUE INDEX main_indexf5 ON main (field5);





>I don't see any problem.



Thanks Jay.

You are right.

The query in a PC change from 34 Secs to 0,036 secs.

Nice.



Thanks for your suggestions.

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/
---------------------------------------------------------------