viele INSERTs beschleunigen?

Hallo,
da ich keine sqlite-Gruppe gefunden habe, hoffe ich, dass sich hier
auch jemand mit dieser Datenbank auskennt. Oder dass das Problem gar
nicht Datenbankspezifisch ist.

Ich habe ein Tabelle mit dieser Struktur:

create table t(id int primary key, x float, y float, z float);

Diese Tabelle soll mit einigen Millionen Zeilen gefüllt werden, wobei
ich diese Aufrufe (id ist weitgehend zufällig, nicht auf- oder
absteigend) der C-API benutze:

sprintf(cmd,"insert into t values(%d,%f,%f,%f)",id,x,y,z);
sqlite_exec(db,cmd,callback,pArg,&errmsg);

Problematisch ist, dass es jeweils ca. 5 Sekunden dauert, 1000
Einträge vorzunehmen. Kann man dies irgendwie beschleunigen, z.B.
indem man blockweise mehrere Zeilen auf einmal einträgt? Allerdings
unterstützt sqlite kein "insert into t values (),(),...".

Oder bringt es etwas, id zunächst nicht als Key zu definieren und
diesen erst anzulegen, wenn die Tabelle gefüllt ist?

Oder ist sqlite einfach nur furchtbar ineffizient und die einzige
Lösung ist, ein anderes Datenbanksystem zu verwenden?

Robert

Re: viele INSERTs beschleunigen?

Andreas
--
Andreas Kretschmer
Linux - weil ich es mir wert bin!
GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net
Deutsche PostgreSQL User Group: http://pgug.de

Re: viele INSERTs beschleunigen?

> Unterstützt es COPY? Das ist unter PG *deutlich* schneller bei
> Masseninserts als INSERT.

ja, COPY wird unterstützt. Scheint das zu sein, was ich brauche.
Danke!

Robert

Re: viele INSERTs beschleunigen?

So, mit COPY habe ich jetzt 1000000 Zeilen in wenigen Sekunden
gespeichert.

Nun brauche ich SELECTs wie
select * from t where (abs(x)<1 and abs(y)<1);

Um die Suche zu optimieren, habe ich Indices für x,y,z erzeugt. Obiges
SELECT gibt sämtliche ca. 100 Ergebnisse in 1 Sekunde.

select * from t where (abs(x)<1 and abs(y)<1) ORDER BY id; geht
ähnlich schnell, aber:
select * from t where (abs(x)<1 and abs(y)<1) ORDER BY x; ist nach ein
paar Minuten noch immer nicht fertig. Warum?

Bringt bei Abfragen wie "where (abs(x)<1)" ein Index auf x überhaupt
etwas? Dazu müsste SQL ja die Monotonie der ABS-Funktion bekannt sein.
Oder kann man einen Index auf "abs(x)" anlegen?

Wie du siehst, ich bin SQL-Anfänger und hoffe auf ähnlich hilfreiche
Tips wie den COPY-Befehl

Robert

On 6 Feb., 15:37, r-.schm...@web.de wrote:
> > Unterstützt es COPY? Das ist unter PG *deutlich* schneller bei
> > Masseninserts als INSERT.
>
> ja, COPY wird unterstützt. Scheint das zu sein, was ich brauche.
> Danke!
>
> Robert

Re: viele INSERTs beschleunigen?

Am 06.02.2007 16:18 schrieb r-.schmidt@web.de:
> So, mit COPY habe ich jetzt 1000000 Zeilen in wenigen Sekunden
> gespeichert.
>
> Nun brauche ich SELECTs wie
> select * from t where (abs(x)<1 and abs(y)<1);
>
> Um die Suche zu optimieren, habe ich Indices für x,y,z erzeugt. Obiges
> SELECT gibt sämtliche ca. 100 Ergebnisse in 1 Sekunde.
>
> select * from t where (abs(x)<1 and abs(y)<1) ORDER BY id; geht
> ähnlich schnell, aber:
> select * from t where (abs(x)<1 and abs(y)<1) ORDER BY x; ist nach ein
> paar Minuten noch immer nicht fertig. Warum?
>

Weil kein Index auf x liegt? Wenn Du mal bei Tante G in dieser Gruppe
nach "Performance" suchst, wirst Du immer wieder darauf treffen.

> Bringt bei Abfragen wie "where (abs(x)<1)" ein Index auf x überhaupt
> etwas? Dazu müsste SQL ja die Monotonie der ABS-Funktion bekannt sein.
> Oder kann man einen Index auf "abs(x)" anlegen?

Nicht jede DB unterstützt funktionale Indizes, sqlite wohl eher nicht.
Selbst wenn es das könnte: Du müsstest natürlich einen Index auf
'abs(x)' legen, nicht auf x.

Im vorliegenden Fall dürfte aber nicht das WHERE der limitierende
Faktor sein (das ist ja in beiden Fällen identisch), sondern das ORDER
BY. Und *das* dürfte durchaus von einem Index auf x profitieren.

>
> Wie du siehst, ich bin SQL-Anfänger und hoffe auf ähnlich hilfreiche
> Tips wie den COPY-Befehl

Auch Anfängern stehen die üblichen Informationsquellen außerhalb des
Usenet offen, vulgo Bücher, (Online-)Dokumentation etc.

Re: viele INSERTs beschleunigen?

Andreas
--
Andreas Kretschmer
Linux - weil ich es mir wert bin!
GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net
Deutsche PostgreSQL User Group: http://pgug.de

Re: viele INSERTs beschleunigen?

Andreas
--
Andreas Kretschmer
Linux - weil ich es mir wert bin!
GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net
Deutsche PostgreSQL User Group: http://pgug.de

Re: viele INSERTs beschleunigen?

> > Um die Suche zu optimieren, habe ich Indices für x,y,z erzeugt. Obiges
> > SELECT gibt sämtliche ca. 100 Ergebnisse in 1 Sekunde.
>
> Dürfte bei der Suche nicht wirklich helfen, weil Du ja nach abs(x)
> suchst. Aber vielleicht löst er das auch selber auf und such schon im
> Index nach abs(x).

Ich habe keinen Index nach abs(x) definiert, und nach der sqlite-Doku
ist das auch nicht möglich.

> > select * from t where (abs(x)<1 and abs(y)<1) ORDER BY id; geht
> > ähnlich schnell, aber:
> > select * from t where (abs(x)<1 and abs(y)<1) ORDER BY x; ist nach ein
> > paar Minuten noch immer nicht fertig. Warum?
>
> Kann sqlite EXPLAIN? Bist Du sicher, der Index ist auf x und nicht auf
> abs(x)?

Die Indices existieren, wie sqlite hier nochmal bestätigt:
sqlite> .indices t
ixx
ixy
ixz

Erzeugt wurden sie mit "create index ixx on t(x)".

Hier folgen die Ausgaben von "explain select * from t where(abs(x)<1
and abs(y)<1) order by id;" und "explain select * from t
where(abs(x)<1 and abs(y)<1) order by x;" Ich kann dort leider nicht
erkennen, welche Indices verwendet werden. Ich muss doch für "ORDER
BY" nicht explizit angeben, dass der Index ixx benutzt werden soll,
oder doch?

sqlite> explain select * from t where(abs(x)<1 and abs(y)<1) order by
id;
0|ColumnName|0|0|id
1|ColumnName|1|0|x
2|ColumnName|2|1|y
3|ColumnName|3|0|int
4|ColumnName|4|0|float
5|ColumnName|5|0|float
6|Integer|0|0|
7|OpenRead|0|3|t
8|VerifyCookie|0|711|
9|Rewind|0|26|
10|Column|0|2|
11|Function|1|0|ptr(0x8050618)
12|Integer|1|0|1
13|Ge|1|25|
14|Column|0|1|
15|Function|1|0|ptr(0x8050618)
16|Integer|1|0|1
17|Ge|1|25|
18|Column|0|0|
19|Column|0|1|
20|Column|0|2|
21|SortMakeRec|3|0|
22|Column|0|0|
23|SortMakeKey|1|0|+
24|SortPut|0|0|
25|Next|0|10|
26|Close|0|0|
27|Sort|0|0|
28|SortNext|0|32|
29|SortCallback|3|0|
30|Goto|0|28|
31|Pop|1|0|
32|SortReset|0|0|
33|Halt|0|0|

sqlite> explain select * from nodes where(abs(x)<1 and abs(y)<1) order
by x
0|ColumnName|0|0|id
1|ColumnName|1|0|x
2|ColumnName|2|1|y
3|ColumnName|3|0|int
4|ColumnName|4|0|float
5|ColumnName|5|0|float
6|Integer|0|0|
7|OpenRead|0|3|t
8|VerifyCookie|0|711|
9|Integer|0|0|
10|OpenRead|1|92408|ixlat
11|Rewind|1|29|
12|RowKey|1|0|
13|IdxIsNull|0|28|
14|IdxRecno|1|0|
15|MoveTo|0|0|
16|Column|0|2|
17|Function|1|0|ptr(0x8050618)
18|Integer|1|0|1
19|Ge|1|28|
20|Column|0|1|
21|Function|1|0|ptr(0x8050618)
22|Integer|1|0|1
23|Ge|1|28|
24|Column|0|0|
25|Column|0|1|
26|Column|0|2|
27|Callback|3|0|
28|Next|1|12|
29|Close|0|0|
30|Close|1|0|
31|Halt|0|0|

Ich verstehe die deutlich längere Laufzeit für "ORDER BY x" nicht weil
a) die WHERE Bedingung nur auf 100 Zeilen zutrifft (100 Zeilen sollten
doch schnell zu sortieren sein, oder wird _erst_ sortiert und dann
ausgewählt?) und
b) ein INDEX für x existiert, aber keiner für id.

Robert

Re: viele INSERTs beschleunigen?

Andreas
--
q: why do so many people take an instant dislike to mysql?
a: it saves time (oicu in #postgresql)
Explaining the concept of referential integrity to a mysql user is like
explaining condoms to a catholic (Shadda in #postgresql)

Re: viele INSERTs beschleunigen?

> Also, ich hab mal eine Tabelle mit 100000 Zeilen und 4 Spalten (id,
> x,y,z) erstellt, wobei die Zahlen dort zwischen -1000 und +1000 FLOAT
> sind.

Die floats sind zufällig verteilt?

> Und dann Dein select, aber auf <100:
>
> Das dauert nun, wie man sieht, 58ms.


> Indexes:
> "idx_abs_x" btree (abs(x))
> "idx_abs_y" btree (abs(y))
> "idx_x" btree (x)
> "idx_y" btree (y)

Hast du diese Indexes vorher erzeugt oder sind sie ein Ergebnis des
SELECTs?

> So, Du schaust nun, welche Indexe Dein System unterstützt und ob es da
> ein Explain gibt und wie das das gegebenenfalls interpretiert wird.

Ja, wie ich um 17:26 schrieb, werden einfache Indexe (auf x oder y)
unterstützt, aber keine auf abs(x).
Dann müsste ich weitere Spalten abs_x, abs_y oder andere (je nachdem
wonach ich filtern möchte) anlegen, oder gibt es einen besseren Weg?

Robert

Re: viele INSERTs beschleunigen?

> > Um die Suche zu optimieren, habe ich Indices für x,y,z erzeugt. Obiges
> > SELECT gibt sämtliche ca. 100 Ergebnisse in 1 Sekunde.
>
> > select * from t where (abs(x)<1 and abs(y)<1) ORDER BY id; geht
> > ähnlich schnell, aber:
> > select * from t where (abs(x)<1 and abs(y)<1) ORDER BY x; ist nach ein
> > paar Minuten noch immer nicht fertig. Warum?
>
> Weil kein Index auf x liegt? Wenn Du mal bei Tante G in dieser Gruppe
> nach "Performance" suchst, wirst Du immer wieder darauf treffen.

Wie ich schrieb, existieren Indices auf x und y.

>
> > Bringt bei Abfragen wie "where (abs(x)<1)" ein Index auf x überhaupt
> > etwas? Dazu müsste SQL ja die Monotonie der ABS-Funktion bekannt sein.
> > Oder kann man einen Index auf "abs(x)" anlegen?
>
> Nicht jede DB unterstützt funktionale Indizes, sqlite wohl eher nicht.
> Selbst wenn es das könnte: Du müsstest natürlich einen Index auf
> 'abs(x)' legen, nicht auf x.
>
> Im vorliegenden Fall dürfte aber nicht das WHERE der limitierende
> Faktor sein (das ist ja in beiden Fällen identisch), sondern das ORDER
> BY. Und *das* dürfte durchaus von einem Index auf x profitieren.

Wie ich schrieb, werden ca. 100 Ergebnisse sortiert. Das ist
limitierend?

> Auch Anfängern stehen die üblichen Informationsquellen außerhalb des
> Usenet offen, vulgo Bücher, (Online-)Dokumentation etc.

Also, ich antworte auch Anfängern gerne bei Themen, mit denen ich mich
gut auskenne. Das erspart ihnen nicht nur langwieriges Suchen in oft
unübersichtlichen Dokumentationen, sondern ich lerne selbst etwas
dabei, vorausgesetzt ich lese mir genau durch, was das Problem ist und
gebe nicht nur auf Stichwörter wie "langsam" vollautomatisch "Google
Perfomance Index" von mir.

Re: viele INSERTs beschleunigen?

Andreas
--
q: why do so many people take an instant dislike to mysql?
a: it saves time (oicu in #postgresql)
Explaining the concept of referential integrity to a mysql user is like
explaining condoms to a catholic (Shadda in #postgresql)

Re: viele INSERTs beschleunigen?

> Gibt es denn keine Mailingliste oder so zu dem System? Nach dem, was ich
> bisher zu sqlite vernommen habe, soll es das halten, was der Name
> verspricht: klein und auch schnell auf Kosten von Features. Aber Dein
> Select ist nun nicht wirklich komplex, also das sollte es nicht
> überfordern.

Irgendwas stimmt hier wirklich nicht. Ich habe jetzt den Index auf x
gelöscht, und nun klappt das SELECT mit "ORDER BY x" genauso schnell
wie mit "ORDER BY id".
Da das wohl tatsächlich kein generelles SQL-Problem ist, werde ich
mich mal bei der sqlite-Mailinglist anmelden.

Vielen Dank für deine Tips!
Robert

Re: viele INSERTs beschleunigen?

> > Hast du diese Indexes vorher erzeugt oder sind sie ein Ergebnis des
> > SELECTs?
>
> Vorher. Ein SELECT erzeugt keine Indexe.
Ich dachte, vielleicht würde dein System intern welche anlegen, um
wiederholte SELECTs zu beschleunigen.

> > Dann müsste ich weitere Spalten abs_x, abs_y oder andere (je nachdem
> > wonach ich filtern möchte) anlegen, oder gibt es einen besseren Weg?
>
> Wenn funktionale Indexe nicht unterstützt werden mag das eine Lösung
> sein. Das das natürlich Scheiße weil Redundanz bedeutet ist klar, ode=
r?
Schon. Wobei die Redundanz das kleinere Problem ist (x,y,z sind später
nur ein kleiner Teil der benötigten Spalten). Unangenehmer wird sein,
dass weitere Tabellen von diesen Koordinaten abhängen, die ich dann
konsistent updaten muss.
Oh, ich sehe gerade, das sind die Kondome für Katholiken, ja?

> Wenn Du komplexere Dinge brauchst, die das RDBMS nicht kann, sollte man
> über einen Wechsel nachdenken. Schau mir in die .signature...

Ich habe inzwischen herausgefunden, daß sqlite maximal einen Index pro
query verwerten kann, was wahrscheinlich meine Probleme erklärt:
http://www.mail-archive.com/sqlite-users@sqlite.org/msg01641 .html

Welche kompakte Alternative gibt es zu sqlite, die als open-source für
Windows CE verfügbar ist? Funktionale Indices brauche ich nicht, da
ich abs(x)<1 als "x<1 and x>-1" ausdrücken kann, aber vorhandene
Indices sollten die Suchabfragen nach Möglichkeit beschleunigen, nicht
ausbremsen.

Robert

> Andreas
> --
> q: why do so many people take an instant dislike to mysql?
> a: it saves time (oicu in #postgresql)
> Explaining the concept of referential integrity to a mysql user is like
> explaining condoms to a catholic (Shadda in #postgresql)

Re: viele INSERTs beschleunigen?

r-.schmidt@web.de wrote:

> Da das wohl tatsächlich kein generelles SQL-Problem ist, werde ich
> mich mal bei der sqlite-Mailinglist anmelden.

Vielleicht schaust Du auch mal nebenan in de.comp.datenbanken.misc - evtl.
sitzen da auch ein paar, die sich mit SQLite auskennen...


Thomas
--
Ich grüße meine Mama, meinen Papa und ganz besonders meine Eltern