SQLite query

[MKe]

Hoi, ik ben met een query bezig op een zeer grote (1000000 regels) tabel in SQLite. Maar het is erg traag
De tabel ziet er als volgt uit:

Code: [Selecteer]

sqlite> select * from CALLS limit 20;
snp         sample      call                                                                                       
----------  ----------  ----------                                                                                 
1           1           A                                                                                           
1           2           NON                                                                                         
1           3           A                                                                                           
1           4           T/A                                                                                         
1           5           T/A                                                                                         
1           6           A                                                                                           
1           7           NON                                                                                         
1           8           A                                                                                           
1           9           A                                                                                           
1           10          A                                                                                           
1           11          NON                                                                                         
1           12          A                                                                                           
1           13          A                                                                                           
1           14          NON                                                                                         
1           15          A                                                                                           
1           16          A                                                                                           
1           17          A                                                                                           
2           1           NON                                                                                         
2           2           G                                                                                           
2           3           G
Ik wil hierbij de SNP getallen hebben waarbij de call van sanmple1 niet gelijk is aan sample 3. Een query is denk ik duidelijker:

Code: [Selecteer]

SELECT B.snp from CALLS AS A, CALLS AS B WHERE A.sample =1 AND B.sample=3 AND A.snp=B.snp AND A.call!='NON' AND B.call!='NON' AND A.call!=B.call;
Het werkt, maar het probleem is dat het vreselijk traag is. Heeft iemand een idee hoe dit sneller kan? Ik heb alle 3 velden in de tabel al geïndexeerd.

[profoX]

Kan je uitweiden over de implementatie en DB schema/structuur?
Eventueel voldoende sample data en broncode?
Wat bedoel je met "vreselijk traag"? meerdere uren? enkele minuten?

Ik weet niet zeker of aan de query veel kan aangepast worden.
Werkt het met een join even traag?

Er zijn best veel factoren die kunnen bijdragen aan de performance van de query.
Kijk ook eens naar de output van http://www.sqlite.org/eqp.html

Ben zelf geen SQLite expert maar met sample data en implementatie broncode
wil ik eventueel wel eens kijken of ik het nier ergens sneller zou krijgen.

[SeySayux]

Werkt het met een join even traag?

Volgens de SQL-standaard zouden een komma met een WHERE-clause en een JOIN identiek hetzelfde moeten zijn, geen idee of SQLite zich hier aan houdt.

Het is in ieder geval betere stijl om een JOIN te gebruiken in plaats van de komma.

[MKe]

Hoi,

Ik heb dit alles nog in de shell van sqlite3 gedaan, dus nog niet vanuit een programma.
Met langzaam bedoel ik echt langzaam. Ik had de query gestart in de sqlite3 shell afgelopen dinsdag middag en vanochtend was hij nog bezig en was misschien op een kwart. Dit op een xeon machine met 12 Gb ram

Ik heb al verschillende dingen geprobeerd, waaronder inderdaad een join. Dit maakt het niet zichtbaar sneller, al heb ik niet meer 36 uur gewacht voordat ik het afbrak.
De tabel is :

Code: [Selecteer]

CREATE TABLE CALLS (
                   snp INTEGER,
                   sample INTEGER,
                   call TEXT);
CREATE INDEX snpindex ON CALLS (snp);
CREATE INDEX sampleindex ON CALLS (sample);
CREATE INDEX callsindex ON CALLS (call);
De gegevens zijn gemakkelijk zelf te genereren. Er zij 17 samples die steeds weer bij elke snp terug komen en heel veel snps (1 miljoen). Bij calls is er een variatie van strings te zien, zoals bij de output in mijn eerdere post. Ik probeer in mijn query de snps te vinden waarbij sample 1 en sample 3 verschillen in de call kolom en geen van beide 'NON' hebben in call.
Bij de query probeer

[Rachid]

Ik weet niet of je query veel sneller kan als je al een INDEX hebt.
SQLite klinkt niet als de beste oplossing voor dergelijke problemen. Zou het misschien sneller worden als je er een VIEW voor maakt?
Anders kun je wat extra info opslaan, zodat je het makkelijk kunt ophalen (soort eigengemaakte index).
Bijv. een extra table waar je de ID'sin zet WHEN A != B (A=3 AND B=3). Je moet dan bij een INSERT of UPDATE zelf even uitrekenen of je het ID in de extra table moet zetten.