SQLITE select dauert 2 min auf dem iphone :-(

[raegtime]

Hallo, ich hoffe mir kann hier einer nen Tipp geben wie ich mein Suche schneller machen kann... im Mom. dauert es auf dem iPhone knapp ne Minute bis die Anfrage ein Ergebnis liefert und das ist eindeutig zu lange..

Ich habe ne Tabelle mit ca. 1000 Bildern... die zu verschiedenen Kategorien passen können im Programm wähle ich genau 4 Kategorien aus und dann sollen mir die dazu passenden Bilder angezeigt werden (UND verknüpft). Beispiel... Bild 1 = modern UND neu UND grün UND cool ....

Zum Aufbau meiner Tabellen:

Code:

CREATE TABLE age ( picid SMALLINT NOT NULL DEFAULT "0", catid SMALLINT NOT NULL );

CREATE TABLE categories ( name TEXT NOT NULL, droplist TINYINT NOT NULL );

CREATE TABLE gender ( picid SMALLINT NOT NULL, catid SMALLINT NOT NULL DEFAULT "0" );

CREATE TABLE pictures ( filename TEXT NOT NULL, picid SMALLINT NOT NULL DEFAULT "0", kind SMALLINT NOT NULL DEFAULT "0" );

CREATE TABLE products ( picid SMALLINT NOT NULL, catid SMALLINT NOT NULL DEFAULT "0" );

CREATE TABLE style ( picid SMALLINT NOT NULL, catid SMALLINT NOT NULL DEFAULT "0" );

und hier die SQLite Verknüpfung:

Code:

SELECT pictures . filename
FROM pictures , (
SELECT tab3 . picid
FROM (
SELECT tab2 . picid
FROM (
SELECT tab1 . picid
FROM (
SELECT gender . picid
FROM gender , categories
WHERE categories . oid = gender . catid AND gender . catid = 24 ) AS tab1 , products
WHERE tab1 . picid = products . picid AND catid = 5 ) AS tab2 , age
WHERE tab2 . picid = age . picid AND catid = 28 ) AS tab3 , style
WHERE tab3 . picid = style . picid AND catid = 34 ) AS endtab
WHERE endtab . picid = pictures . picid

ich verknüpfe nur integer bzw smallint werte und durchsuche auch nur nach solchen... am ende gibts zwar noch einen String vergleich aber der ist nicht sonderlich groß (zeitintentiv) denke ich...

vlt. kann mir auch einer nen besseres Tabellenlayout oder nen optimierten selectbefehl sagen ... ich glaube dass die ganzen subselects auf dem iphone sehr langsam sind...

 

[Cromon]

Ich habe zwar nicht wirklich eine Ahnung vom Themengebiet, aber allgemein als Programmierer würde ich dir folgendes Vorschlagen:
Verlasse dich nie auf "denke ich". Das sind die grössten Fehlerquellen.

Versuche also mal ob du das ganze auch ohne den Stringvergleich mal laufen lassen kannst und schaue dann, ob es Einfluss auf die Performance hat. Wer weiss, vielleicht hast du dich ja getäuscht. Erst wenn man es in der Laufzeit sieht sollte man sich etwas sicher sein.

 

[Yaslaw]

Ich kenne SQLITE nicht, jedoch haben die meisten SQL-Sprachen viele Gemeinsamkeiten.

Ich hab mir dein SQL mal angeschaut - was willst du damit überhaubt?

Beginnen wir mal im Kern deiner x-Fach verschachtelten Lösung (tab1)

SELECT gender.picid
FROM gender, categories
WHERE
	categories.oid = gender.catid 
	AND gender.catid = 24

Wozu brauchst du categories? eine unnötige Verknüpfung.
Das kürze ich mal auf

SELECT gender.picid
FROM gender
WHERE gender.catid = 24

Die ganze Verschachtelung ist unnötig. mach es mit einem einfachen Select. Um die picid zu bestimmen, die alle 4 Anforderungen erfüllt, reicht das folgende Query

SELECT
	tab1.picid
FROM
	(SELECT picid FROM gender WHERE catid = 24) AS tab1,
	(SELECT picid FROM products WHERE catid = 5) AS tab2,
	(SELECT picid FROM age WHERE catid = 28) AS tab3,
	(SELECT picid FROM style WHERE catid = 34) AS endtab,
WHERE
	tab1.picid = tab2.picid
	AND tab1.picid = tab3.picid	
	AND tab1.picid = tab3.endtab

Jetzt noch die Daten der Tabelle pictures damit filtern und fertig ists.

SELECT
	filename	
FROM
	pictures	
WHERE
	picid IN (
				SELECT
					tab1.picid
				FROM
					(SELECT picid FROM gender WHERE catid = 24) AS tab1,
					(SELECT picid FROM products WHERE catid = 5) AS tab2,
					(SELECT picid FROM age WHERE catid = 28) AS tab3,
					(SELECT picid FROM style WHERE catid = 34) AS endtab,
				WHERE
					tab1.picid = tab2.picid
					AND tab1.picid = tab3.picid	
					AND tab1.picid = tab3.endtab)

Auf alle Felder picid und alle Felder catid solltest du noch indexe legen!

 

[raegtime]

Erst einmal danke!

also zu Cromon.. den Stringvergleich habe ich weggelassen und die Dauer hat sich nicht wirklich verändert... :-(

@Yaslaw...

dein Select funktioniert am ende genauso sie meiner (also gibt das gleiche Ergebnis) das ist schon mal gut, aber die Geschwindigkeit hat sich leider auch nicht verändert. Ich habe aber auch noch keine Indexe erstellt... müssen die beim Anlegen der Datenbank nach den inserts (Daten) erstellt werden oder kann man die auch schon beim erstellen der Tabelle festlegen... dass eine Spalte geindext wird?!

 

[Yaslaw]

Google sagt: http://www.sqlite.org/lang_createindex.html

Ich weiss jetzt nicht ob SQLITE einen Index Only Scan erlaubt oder nicht. Wenn ja, dann kann man ein Index über beide Felder (picid und catid) legen. Da wir keine anderen Felder verwenden, greifft der Otimizer (oder wie das Zeugs in SQLITE jeisste) nur auf den Index und gar nicht erst auf die Tabelle zu.

CREATE INDEX idx_gender_1 ON gender(catid, picid);

Wenn kein Index Only Scwn möglich ist, wird wahrscheinlich eine Lösung mit 2 Indexen schneller sein.

CREATE INDEX idx_gender_picid ON gender(picid);
CREATE INDEX idx_gender_catid ON gender(catid);

Nach besser ists wahrscheinlich, auf dei Tabellen gleich ein Primary-Key zu legen.

CREATE TABLE gender ( 
	picid SMALLINT NOT NULL, 
	catid SMALLINT NOT NULL DEFAULT "0", 
	PRIMARY KEY (picid, catid));

Nachtrag:
Und selbstverständlich auch ein Primary Key auf pictures.id