(Solved) NextDatabaseRow extreme slow

[Cyllceaux]

I have a SQLite Database with 9GB.
I have a C Code which workes really fine.
I tried this statement:

Code: Select all

SELECT NR from DATEN_VIEW where CKATEGORIEN=0 and PVERBINDUNGEN=0 and CVERBINDUNGEN=0 and ORT='';

The C-Code gives me a resultset in 69ms

The result are 3500 numbers.


Than I tried this

Code: Select all

Protected start.q=ElapsedMilliseconds()
If DatabaseQuery(db,"SELECT NR from DATEN_VIEW where CKATEGORIEN=0 and PVERBINDUNGEN=0 and CVERBINDUNGEN=0 and ORT='';")
   Debug ElapsedMilliseconds()-start
   While NextDatabaseRow(db)

   Wend
   FinishDatabaseQuery(db)
EndIf
Debug ElapsedMilliseconds()-start

This runs in 55ms for DatabaseQuery and 142987ms for the first NextDatabaseRow

[Tenaja]

You cannot measure performance with the debugger on.

Turn off the debugger, replace the debug commands with message boxes, compile for exe, and run the exe.

[Cyllceaux]

ok... I changed the code and disabled the debugger:

Code: Select all

Protected start.q=ElapsedMilliseconds()
If DatabaseQuery(db,"SELECT NR from DATEN_VIEW where CKATEGORIEN=0 and PVERBINDUNGEN=0 and CVERBINDUNGEN=0 and ORT='';")
   MessageRequester("", Str(ElapsedMilliseconds()-Start))
   While NextDatabaseRow(db)

   Wend
   FinishDatabaseQuery(db)
EndIf
MessageRequester("", Str(ElapsedMilliseconds()-Start))

First: 44ms
Second: 144440ms

[Kiffi]

Do you also iterate through all rows in your C-Code?

[Cyllceaux]

yes.. Always

cause I need the NRs.

Code: Select all

CALL_SQLITE (prepare_v2 (db, sql, strlen (sql) + 1, & stmt, NULL));
while (1) {
    int s;
    s = sqlite3_step (stmt);
    if (s == SQLITE_ROW) {
            
    }
    else if (s == SQLITE_DONE) {
        break;
    }
    else {
        fprintf (stderr, "Failed.\n");
        exit (1);
    }
}

[skywalk]

I may have a similar slowdown.
Sqlite fixed a performance issue after the latest PB version. Download the latest sqlite3.dll.

[Cyllceaux]

I tried Version 3.35.1.

The DLL runs fine and fast. (like the binary)
I tried UseSQLiteDatabase("sqlite3.dll"). But it doesn't change anything.

I test if it worked with:

Code: Select all

"SELECT sqlite_version();"

and it shows the right version

[Cyllceaux]

I may have a similar slowdown.
Sqlite fixed a performance issue after the latest PB version. Download the latest sqlite3.dll.

I triede PB570... and the same code and file runs in 122773ms
this is really strange.

[skywalk]

Yes, I cannot conclude from your posts what is happening?
Please make a simplified database with SQL and post code that makes your queries.
You can quickly generate a table in PB.
Debug with a ":memory:" database first.
Then move onto "somefile.db" database.

[Cyllceaux]

this is not the Problem.

This DB has 9 GB Blob Data. If I reduce this to 3GB, it runs in 600ms
If I reduce to 300MB it runs in 55ms

The same DB in the C-Code always run in around 40ms

And NO... this is not a problem of the filesystem. The other applications can run this DB without any performance problems.

The Schema-Export.

Code: Select all

CREATE TABLE OPTIONEN(NAME TEXT NOT NULL UNIQUE  PRIMARY KEY, INHALT TEXT);
CREATE TABLE DATEN(NR INTEGER NOT NULL UNIQUE  PRIMARY KEY, UUID TEXT NOT NULL DEFAULT (lower(hex(randomblob(4))) || '-' || lower(hex(randomblob(2))) || '-4' || substr(lower(hex(randomblob(2))),2) || '-' || substr('89ab',abs(random()) % 4 + 1, 1) || substr(lower(hex(randomblob(2))),2) || '-' || lower(hex(randomblob(6)))), CREATED TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP, MODIFIED TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP, VERSION INTEGER NOT NULL DEFAULT 1, TYP TEXT NOT NULL, PARENT INTEGER REFERENCES DATEN(NR) ON DELETE CASCADE ON UPDATE CASCADE, PARENT_NULL INTEGER REFERENCES DATEN(NR) ON DELETE SET NULL ON UPDATE CASCADE, DATA BLOB, DATA2 BLOB, DATA3 BLOB, INHALT TEXT, INFO TEXT, ANREDE TEXT, VORNAME TEXT, NACHNAME TEXT, ZWEITNAME TEXT, SUFFIX TEXT, SPITZNAME TEXT, FIRMA TEXT, TITEL TEXT, NAME TEXT, PFAD TEXT, IBAN TEXT, BIC TEXT, BANK TEXT, STRASSE TEXT, PLZ TEXT, ORT TEXT, LAND TEXT, REGION TEXT, TELEFON TEXT, FAX TEXT, MOBIL TEXT, PAGER TEXT, MAIL TEXT, WEB TEXT, ABSENDER TEXT, EMPFAENGER TEXT, SEITE TEXT, VERTRAGSNUMMER TEXT, RECHNUNGSNUMMER TEXT, AUFTRAGSNUMMER TEXT, KUNDENNUMMER TEXT, NUMMER TEXT, ALBUM TEXT, INTERPRET TEXT, TRACK TEXT, GENRE TEXT, FORMAT TEXT, DATUM TEXT, START TEXT, ENDE TEXT, GEBURTSTAG TEXT, JAHRESTAG TEXT, FAELLIG TEXT, VON TEXT, BIS TEXT, ERINNERUNG TEXT, STATUS INTEGER, PROZENT INTEGER NOT NULL DEFAULT 0, PRIORITAET INTEGER, SUBTYP INTEGER, JAHR INTEGER, MONAT INTEGER, TAG INTEGER, LAENGE REAL, HOEHE REAL, BREITE REAL, GANZTAGS BOOLEAN, ERLEDIGT BOOLEAN, SICHTBAR BOOLEAN NOT NULL DEFAULT 1, GELOESCHT BOOLEAN NOT NULL DEFAULT 0, BETRAG REAL, MWST REAL, ERSTELLT TEXT, GEAENDERT TEXT, ZUGRIFF TEXT, HASH TEXT, SELECTED BOOLEAN, KATEGORIEN TEXT, CKATEGORIEN INTEGER DEFAULT 0, CVERBINDUNGEN INTEGER DEFAULT 0, PVERBINDUNGEN INTEGER DEFAULT 0, CHECK(TRIM(TYP)!=''), CHECK(PROZENT>=0 AND PROZENT<=100), CONSTRAINT KONTAKT_CHK CHECK(TYP!='KONTAKT' OR (TYP='KONTAKT' AND ((VORNAME IS NOT NULL AND TRIM(VORNAME)<>'') OR (NACHNAME IS NOT NULL AND TRIM(NACHNAME)<>'') OR (SPITZNAME IS NOT NULL AND TRIM(SPITZNAME)<>'') OR (FIRMA IS NOT NULL AND TRIM(FIRMA)<>'')))), CONSTRAINT ADRESSE_CHK CHECK(TYP!='ADRESSE' OR (TYP='ADRESSE' AND SUBTYP IS NOT NULL)), CONSTRAINT VERBINDUNG_CHK CHECK(TYP!='VERBINDUNG' OR (TYP='VERBINDUNG' AND SUBTYP IS NOT NULL)), CONSTRAINT NOTIZ_TITEL_CHK CHECK(TYP!='NOTIZ' OR (TYP='NOTIZ' AND TITEL IS NOT NULL AND TRIM(TITEL)<>'')), CONSTRAINT DATEI_CHK CHECK(TYP!='DATEI' OR (TYP='DATEI' AND NAME IS NOT NULL AND TRIM(NAME)<>'')), CONSTRAINT DOKUMENT_CHK CHECK(TYP!='DOKUMENT' OR (TYP='DOKUMENT' AND NAME IS NOT NULL AND TRIM(NAME)<>'')), CONSTRAINT SONG_CHK CHECK(TYP!='SONG' OR (TYP='SONG' AND PFAD IS NOT NULL AND TRIM(PFAD)<>'')), CONSTRAINT BILD_CHK CHECK(TYP!='BILD' OR (TYP='BILD' AND PFAD IS NOT NULL AND TRIM(PFAD)<>'')), CONSTRAINT AUFGABE_TITEL_CHK CHECK(TYP!='AUFGGABE' OR (TYP='AUFGGABE' AND TITEL IS NOT NULL AND TRIM(TITEL)<>'')), CONSTRAINT AUFGABE_DATUM_CHK CHECK(TYP!='AUFGGABE' OR (TYP='AUFGGABE' AND (START IS NULL OR FAELLIG IS NULL OR (START <= FAELLIG)))), CONSTRAINT TERMIN_TITEL_CHK CHECK(TYP!='TERMIN' OR (TYP='TERMIN' AND TITEL IS NOT NULL AND TRIM(TITEL)<>'')), CONSTRAINT TERMIN_DATUM_CHK CHECK(TYP!='TERMIN' OR (TYP='TERMIN' AND VON IS NOT NULL AND (BIS IS NULL OR (VON <= BIS)))), CONSTRAINT PROJEKT_CHK CHECK(TYP!='PROJEKT' OR (TYP='PROJEKT' AND NAME IS NOT NULL AND TRIM(NAME)<>'')), CONSTRAINT GRUPPE_CHK CHECK(TYP!='GRUPPE' OR (TYP='GRUPPE' AND NAME IS NOT NULL AND TRIM(NAME)<>'')), CONSTRAINT KONTO_CHK CHECK(TYP!='KONTO' OR (TYP='KONTO' AND NAME IS NOT NULL AND TRIM(NAME)<>'')), CONSTRAINT ZEITERFASSUNG_CHK CHECK(TYP!='ZEITERFASSUNG' OR (TYP='ZEITERFASSUNG'  AND VON IS NOT NULL AND BIS IS NOT NULL AND VON <= BIS)), CONSTRAINT ZEITERFASSUNG_DATUM_CHK CHECK(TYP!='JOURNAL' OR (TYP='JOURNAL' AND VON IS NOT NULL AND BIS IS NOT NULL AND VON <= BIS)), CONSTRAINT BUCHUNG_CHK CHECK(TYP!='BUCHUNG' OR (TYP='BUCHUNG' AND DATUM IS NOT NULL)), CONSTRAINT UUID_UIDX_ UNIQUE(UUID), CONSTRAINT STRICT_NR CHECK(NR IS NULL OR TYPEOF(NR) = 'null' OR TYPEOF(NR) = 'integer'), CONSTRAINT STRICT_UUID CHECK(UUID IS NULL OR TYPEOF(UUID) = 'null' OR TYPEOF(UUID) = 'text'), CONSTRAINT STRICT_CREATED CHECK(CREATED IS NULL OR TYPEOF(CREATED) = 'null' OR TYPEOF(CREATED) = 'text'), CONSTRAINT STRICT_MODIFIED CHECK(MODIFIED IS NULL OR TYPEOF(MODIFIED) = 'null' OR TYPEOF(MODIFIED) = 'text'), CONSTRAINT STRICT_VERSION CHECK(VERSION IS NULL OR TYPEOF(VERSION) = 'null' OR TYPEOF(VERSION) = 'integer'), CONSTRAINT STRICT_TYP CHECK(TYP IS NULL OR TYPEOF(TYP) = 'null' OR TYPEOF(TYP) = 'text'), CONSTRAINT STRICT_PARENT CHECK(PARENT IS NULL OR TYPEOF(PARENT) = 'null' OR TYPEOF(PARENT) = 'integer'), CONSTRAINT STRICT_PARENT_NULL CHECK(PARENT_NULL IS NULL OR TYPEOF(PARENT_NULL) = 'null' OR TYPEOF(PARENT_NULL) = 'integer'), CONSTRAINT STRICT_DATA CHECK(DATA IS NULL OR TYPEOF(DATA) = 'null' OR TYPEOF(DATA) = 'blob'), CONSTRAINT STRICT_DATA2 CHECK(DATA2 IS NULL OR TYPEOF(DATA2) = 'null' OR TYPEOF(DATA2) = 'blob'), CONSTRAINT STRICT_DATA3 CHECK(DATA3 IS NULL OR TYPEOF(DATA3) = 'null' OR TYPEOF(DATA3) = 'blob'), CONSTRAINT STRICT_INHALT CHECK(INHALT IS NULL OR TYPEOF(INHALT) = 'null' OR TYPEOF(INHALT) = 'text'), CONSTRAINT STRICT_INFO CHECK(INFO IS NULL OR TYPEOF(INFO) = 'null' OR TYPEOF(INFO) = 'text'), CONSTRAINT STRICT_ANREDE CHECK(ANREDE IS NULL OR TYPEOF(ANREDE) = 'null' OR TYPEOF(ANREDE) = 'text'), CONSTRAINT STRICT_VORNAME CHECK(VORNAME IS NULL OR TYPEOF(VORNAME) = 'null' OR TYPEOF(VORNAME) = 'text'), CONSTRAINT STRICT_NACHNAME CHECK(NACHNAME IS NULL OR TYPEOF(NACHNAME) = 'null' OR TYPEOF(NACHNAME) = 'text'), CONSTRAINT STRICT_ZWEITNAME CHECK(ZWEITNAME IS NULL OR TYPEOF(ZWEITNAME) = 'null' OR TYPEOF(ZWEITNAME) = 'text'), CONSTRAINT STRICT_SUFFIX CHECK(SUFFIX IS NULL OR TYPEOF(SUFFIX) = 'null' OR TYPEOF(SUFFIX) = 'text'), CONSTRAINT STRICT_SPITZNAME CHECK(SPITZNAME IS NULL OR TYPEOF(SPITZNAME) = 'null' OR TYPEOF(SPITZNAME) = 'text'), CONSTRAINT STRICT_FIRMA CHECK(FIRMA IS NULL OR TYPEOF(FIRMA) = 'null' OR TYPEOF(FIRMA) = 'text'), CONSTRAINT STRICT_TITEL CHECK(TITEL IS NULL OR TYPEOF(TITEL) = 'null' OR TYPEOF(TITEL) = 'text'), CONSTRAINT STRICT_NAME CHECK(NAME IS NULL OR TYPEOF(NAME) = 'null' OR TYPEOF(NAME) = 'text'), CONSTRAINT STRICT_PFAD CHECK(PFAD IS NULL OR TYPEOF(PFAD) = 'null' OR TYPEOF(PFAD) = 'text'), CONSTRAINT STRICT_IBAN CHECK(IBAN IS NULL OR TYPEOF(IBAN) = 'null' OR TYPEOF(IBAN) = 'text'), CONSTRAINT STRICT_BIC CHECK(BIC IS NULL OR TYPEOF(BIC) = 'null' OR TYPEOF(BIC) = 'text'), CONSTRAINT STRICT_BANK CHECK(BANK IS NULL OR TYPEOF(BANK) = 'null' OR TYPEOF(BANK) = 'text'), CONSTRAINT STRICT_STRASSE CHECK(STRASSE IS NULL OR TYPEOF(STRASSE) = 'null' OR TYPEOF(STRASSE) = 'text'), CONSTRAINT STRICT_PLZ CHECK(PLZ IS NULL OR TYPEOF(PLZ) = 'null' OR TYPEOF(PLZ) = 'text'), CONSTRAINT STRICT_ORT CHECK(ORT IS NULL OR TYPEOF(ORT) = 'null' OR TYPEOF(ORT) = 'text'), CONSTRAINT STRICT_LAND CHECK(LAND IS NULL OR TYPEOF(LAND) = 'null' OR TYPEOF(LAND) = 'text'), CONSTRAINT STRICT_REGION CHECK(REGION IS NULL OR TYPEOF(REGION) = 'null' OR TYPEOF(REGION) = 'text'), CONSTRAINT STRICT_TELEFON CHECK(TELEFON IS NULL OR TYPEOF(TELEFON) = 'null' OR TYPEOF(TELEFON) = 'text'), CONSTRAINT STRICT_FAX CHECK(FAX IS NULL OR TYPEOF(FAX) = 'null' OR TYPEOF(FAX) = 'text'), CONSTRAINT STRICT_MOBIL CHECK(MOBIL IS NULL OR TYPEOF(MOBIL) = 'null' OR TYPEOF(MOBIL) = 'text'), CONSTRAINT STRICT_PAGER CHECK(PAGER IS NULL OR TYPEOF(PAGER) = 'null' OR TYPEOF(PAGER) = 'text'), CONSTRAINT STRICT_MAIL CHECK(MAIL IS NULL OR TYPEOF(MAIL) = 'null' OR TYPEOF(MAIL) = 'text'), CONSTRAINT STRICT_WEB CHECK(WEB IS NULL OR TYPEOF(WEB) = 'null' OR TYPEOF(WEB) = 'text'), CONSTRAINT STRICT_ABSENDER CHECK(ABSENDER IS NULL OR TYPEOF(ABSENDER) = 'null' OR TYPEOF(ABSENDER) = 'text'), CONSTRAINT STRICT_EMPFAENGER CHECK(EMPFAENGER IS NULL OR TYPEOF(EMPFAENGER) = 'null' OR TYPEOF(EMPFAENGER) = 'text'), CONSTRAINT STRICT_SEITE CHECK(SEITE IS NULL OR TYPEOF(SEITE) = 'null' OR TYPEOF(SEITE) = 'text'), CONSTRAINT STRICT_VERTRAGSNUMMER CHECK(VERTRAGSNUMMER IS NULL OR TYPEOF(VERTRAGSNUMMER) = 'null' OR TYPEOF(VERTRAGSNUMMER) = 'text'), CONSTRAINT STRICT_RECHNUNGSNUMMER CHECK(RECHNUNGSNUMMER IS NULL OR TYPEOF(RECHNUNGSNUMMER) = 'null' OR TYPEOF(RECHNUNGSNUMMER) = 'text'), CONSTRAINT STRICT_AUFTRAGSNUMMER CHECK(AUFTRAGSNUMMER IS NULL OR TYPEOF(AUFTRAGSNUMMER) = 'null' OR TYPEOF(AUFTRAGSNUMMER) = 'text'), CONSTRAINT STRICT_KUNDENNUMMER CHECK(KUNDENNUMMER IS NULL OR TYPEOF(KUNDENNUMMER) = 'null' OR TYPEOF(KUNDENNUMMER) = 'text'), CONSTRAINT STRICT_NUMMER CHECK(NUMMER IS NULL OR TYPEOF(NUMMER) = 'null' OR TYPEOF(NUMMER) = 'text'), CONSTRAINT STRICT_ALBUM CHECK(ALBUM IS NULL OR TYPEOF(ALBUM) = 'null' OR TYPEOF(ALBUM) = 'text'), CONSTRAINT STRICT_INTERPRET CHECK(INTERPRET IS NULL OR TYPEOF(INTERPRET) = 'null' OR TYPEOF(INTERPRET) = 'text'), CONSTRAINT STRICT_TRACK CHECK(TRACK IS NULL OR TYPEOF(TRACK) = 'null' OR TYPEOF(TRACK) = 'text'), CONSTRAINT STRICT_GENRE CHECK(GENRE IS NULL OR TYPEOF(GENRE) = 'null' OR TYPEOF(GENRE) = 'text'), CONSTRAINT STRICT_FORMAT CHECK(FORMAT IS NULL OR TYPEOF(FORMAT) = 'null' OR TYPEOF(FORMAT) = 'text'), CONSTRAINT STRICT_DATUM CHECK(DATUM IS NULL OR TYPEOF(DATUM) = 'null' OR TYPEOF(DATUM) = 'text'), CONSTRAINT STRICT_START CHECK(START IS NULL OR TYPEOF(START) = 'null' OR TYPEOF(START) = 'text'), CONSTRAINT STRICT_ENDE CHECK(ENDE IS NULL OR TYPEOF(ENDE) = 'null' OR TYPEOF(ENDE) = 'text'), CONSTRAINT STRICT_GEBURTSTAG CHECK(GEBURTSTAG IS NULL OR TYPEOF(GEBURTSTAG) = 'null' OR TYPEOF(GEBURTSTAG) = 'text'), CONSTRAINT STRICT_JAHRESTAG CHECK(JAHRESTAG IS NULL OR TYPEOF(JAHRESTAG) = 'null' OR TYPEOF(JAHRESTAG) = 'text'), CONSTRAINT STRICT_FAELLIG CHECK(FAELLIG IS NULL OR TYPEOF(FAELLIG) = 'null' OR TYPEOF(FAELLIG) = 'text'), CONSTRAINT STRICT_VON CHECK(VON IS NULL OR TYPEOF(VON) = 'null' OR TYPEOF(VON) = 'text'), CONSTRAINT STRICT_BIS CHECK(BIS IS NULL OR TYPEOF(BIS) = 'null' OR TYPEOF(BIS) = 'text'), CONSTRAINT STRICT_ERINNERUNG CHECK(ERINNERUNG IS NULL OR TYPEOF(ERINNERUNG) = 'null' OR TYPEOF(ERINNERUNG) = 'text'), CONSTRAINT STRICT_STATUS CHECK(STATUS IS NULL OR TYPEOF(STATUS) = 'null' OR TYPEOF(STATUS) = 'integer'), CONSTRAINT STRICT_PROZENT CHECK(PROZENT IS NULL OR TYPEOF(PROZENT) = 'null' OR TYPEOF(PROZENT) = 'integer'), CONSTRAINT STRICT_PRIORITAET CHECK(PRIORITAET IS NULL OR TYPEOF(PRIORITAET) = 'null' OR TYPEOF(PRIORITAET) = 'integer'), CONSTRAINT STRICT_SUBTYP CHECK(SUBTYP IS NULL OR TYPEOF(SUBTYP) = 'null' OR TYPEOF(SUBTYP) = 'integer'), CONSTRAINT STRICT_JAHR CHECK(JAHR IS NULL OR TYPEOF(JAHR) = 'null' OR TYPEOF(JAHR) = 'integer'), CONSTRAINT STRICT_MONAT CHECK(MONAT IS NULL OR TYPEOF(MONAT) = 'null' OR TYPEOF(MONAT) = 'integer'), CONSTRAINT STRICT_TAG CHECK(TAG IS NULL OR TYPEOF(TAG) = 'null' OR TYPEOF(TAG) = 'integer'), CONSTRAINT STRICT_LAENGE CHECK(LAENGE IS NULL OR TYPEOF(LAENGE) = 'null' OR TYPEOF(LAENGE) = 'real'), CONSTRAINT STRICT_HOEHE CHECK(HOEHE IS NULL OR TYPEOF(HOEHE) = 'null' OR TYPEOF(HOEHE) = 'real'), CONSTRAINT STRICT_BREITE CHECK(BREITE IS NULL OR TYPEOF(BREITE) = 'null' OR TYPEOF(BREITE) = 'real'), CONSTRAINT STRICT_GANZTAGS CHECK(GANZTAGS IS NULL OR TYPEOF(GANZTAGS) = 'null' OR TYPEOF(GANZTAGS) = 'integer'), CONSTRAINT STRICT_ERLEDIGT CHECK(ERLEDIGT IS NULL OR TYPEOF(ERLEDIGT) = 'null' OR TYPEOF(ERLEDIGT) = 'integer'), CONSTRAINT STRICT_SICHTBAR CHECK(SICHTBAR IS NULL OR TYPEOF(SICHTBAR) = 'null' OR TYPEOF(SICHTBAR) = 'integer'), CONSTRAINT STRICT_GELOESCHT CHECK(GELOESCHT IS NULL OR TYPEOF(GELOESCHT) = 'null' OR TYPEOF(GELOESCHT) = 'integer'), CONSTRAINT STRICT_BETRAG CHECK(BETRAG IS NULL OR TYPEOF(BETRAG) = 'null' OR TYPEOF(BETRAG) = 'real'), CONSTRAINT STRICT_MWST CHECK(MWST IS NULL OR TYPEOF(MWST) = 'null' OR TYPEOF(MWST) = 'real'));
CREATE TABLE KATEGORIEN_DATEN(NR INTEGER NOT NULL UNIQUE  PRIMARY KEY, UUID TEXT NOT NULL DEFAULT (lower(hex(randomblob(4))) || '-' || lower(hex(randomblob(2))) || '-4' || substr(lower(hex(randomblob(2))),2) || '-' || substr('89ab',abs(random()) % 4 + 1, 1) || substr(lower(hex(randomblob(2))),2) || '-' || lower(hex(randomblob(6)))), CREATED TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP, MODIFIED TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP, VERSION INTEGER NOT NULL DEFAULT 1, NAME TEXT NOT NULL UNIQUE , SELECTED BOOLEAN NOT NULL DEFAULT 1, CHECK(TRIM(NAME)<>''), CONSTRAINT UUID_UIDX_ UNIQUE(UUID), CONSTRAINT STRICT_NR CHECK(NR IS NULL OR TYPEOF(NR) = 'null' OR TYPEOF(NR) = 'integer'), CONSTRAINT STRICT_UUID CHECK(UUID IS NULL OR TYPEOF(UUID) = 'null' OR TYPEOF(UUID) = 'text'), CONSTRAINT STRICT_CREATED CHECK(CREATED IS NULL OR TYPEOF(CREATED) = 'null' OR TYPEOF(CREATED) = 'text'), CONSTRAINT STRICT_MODIFIED CHECK(MODIFIED IS NULL OR TYPEOF(MODIFIED) = 'null' OR TYPEOF(MODIFIED) = 'text'), CONSTRAINT STRICT_VERSION CHECK(VERSION IS NULL OR TYPEOF(VERSION) = 'null' OR TYPEOF(VERSION) = 'integer'), CONSTRAINT STRICT_NAME CHECK(NAME IS NULL OR TYPEOF(NAME) = 'null' OR TYPEOF(NAME) = 'text'), CONSTRAINT STRICT_SELECTED CHECK(SELECTED IS NULL OR TYPEOF(SELECTED) = 'null' OR TYPEOF(SELECTED) = 'integer'));
CREATE TABLE DATEN_KATEGORIEN(NR INTEGER NOT NULL UNIQUE  PRIMARY KEY, UUID TEXT NOT NULL DEFAULT (lower(hex(randomblob(4))) || '-' || lower(hex(randomblob(2))) || '-4' || substr(lower(hex(randomblob(2))),2) || '-' || substr('89ab',abs(random()) % 4 + 1, 1) || substr(lower(hex(randomblob(2))),2) || '-' || lower(hex(randomblob(6)))), CREATED TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP, MODIFIED TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP, VERSION INTEGER NOT NULL DEFAULT 1, KATEGORIE INTEGER NOT NULL REFERENCES KATEGORIEN_DATEN(NR) ON DELETE CASCADE ON UPDATE CASCADE, ELEMENT INTEGER NOT NULL REFERENCES DATEN(NR) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT UUID_UIDX_ UNIQUE(UUID), UNIQUE(ELEMENT,KATEGORIE), CONSTRAINT STRICT_NR CHECK(NR IS NULL OR TYPEOF(NR) = 'null' OR TYPEOF(NR) = 'integer'), CONSTRAINT STRICT_UUID CHECK(UUID IS NULL OR TYPEOF(UUID) = 'null' OR TYPEOF(UUID) = 'text'), CONSTRAINT STRICT_CREATED CHECK(CREATED IS NULL OR TYPEOF(CREATED) = 'null' OR TYPEOF(CREATED) = 'text'), CONSTRAINT STRICT_MODIFIED CHECK(MODIFIED IS NULL OR TYPEOF(MODIFIED) = 'null' OR TYPEOF(MODIFIED) = 'text'), CONSTRAINT STRICT_VERSION CHECK(VERSION IS NULL OR TYPEOF(VERSION) = 'null' OR TYPEOF(VERSION) = 'integer'), CONSTRAINT STRICT_KATEGORIE CHECK(KATEGORIE IS NULL OR TYPEOF(KATEGORIE) = 'null' OR TYPEOF(KATEGORIE) = 'integer'), CONSTRAINT STRICT_ELEMENT CHECK(ELEMENT IS NULL OR TYPEOF(ELEMENT) = 'null' OR TYPEOF(ELEMENT) = 'integer'));
CREATE TABLE DATEN_OPTIONEN(NAME TEXT NOT NULL UNIQUE  PRIMARY KEY, INHALT TEXT, PARENT INTEGER NOT NULL REFERENCES DATEN(NR) ON DELETE CASCADE ON UPDATE CASCADE);
CREATE TABLE VERBINDUNGEN(PARENT INTEGER NOT NULL REFERENCES DATEN(NR) ON DELETE CASCADE ON UPDATE CASCADE, CHILD INTEGER NOT NULL REFERENCES DATEN(NR) ON DELETE CASCADE ON UPDATE CASCADE, PRIMARY KEY(PARENT,CHILD));
CREATE TABLE sqlite_stat1(tbl,idx,stat);
CREATE TABLE sqlite_stat4(tbl,idx,neq,nlt,ndlt,sample);
CREATE INDEX IDX_DATEN_TYP ON DATEN(TYP);
CREATE INDEX IDX_DATEN_PFAD ON DATEN(PFAD);
CREATE INDEX IDX_DATEN_NAME ON DATEN(NAME);
CREATE INDEX IDX_DATEN_TITEL ON DATEN(TITEL);
CREATE INDEX DATEN_KATEGORIEN_IDX ON DATEN_KATEGORIEN(ELEMENT);
CREATE INDEX IDX_DATEN_ERSTELLT ON DATEN(ERSTELLT);
CREATE INDEX IDX_DATEN_ORT ON DATEN(ORT);
CREATE TRIGGER DATEN_UPD AFTER UPDATE ON DATEN FOR EACH ROW BEGIN UPDATE DATEN SET MODIFIED = CURRENT_TIMESTAMP, VERSION = OLD.VERSION+1 WHERE NR = NEW.NR ; END;
CREATE VIEW KATEGORIEN_DATEN_VIEW AS SELECT K.*, (SELECT COUNT(KK.ELEMENT)  FROM DATEN_KATEGORIEN KK WHERE KK.KATEGORIE = K.NR ) AS ANZAHL  FROM KATEGORIEN_DATEN K
/* KATEGORIEN_DATEN_VIEW(NR,UUID,CREATED,MODIFIED,VERSION,NAME,SELECTED,ANZAHL) */;
CREATE INDEX IDX_DATEN_KATEGORIEN ON DATEN(KATEGORIEN);
CREATE INDEX IDX_DATEN_SICHTBAR ON DATEN(SICHTBAR);
CREATE INDEX IDX_DATEN_GELOESCHT ON DATEN(GELOESCHT);
CREATE INDEX IDX_DATEN_CKATEGORIEN ON DATEN(CKATEGORIEN);
CREATE INDEX IDX_DATEN_CVERBINDUNGEN ON DATEN(CVERBINDUNGEN);
CREATE INDEX IDX_DATEN_PVERBINDUNGEN ON DATEN(PVERBINDUNGEN);
CREATE VIEW DATEN_VIEW AS SELECT K.*, K.BETRAG - K.MWST * K.BETRAG / 100 AS NETTO, (strftime('%s',K.BIS)-strftime('%s',K.VON))/3600.0 AS DAUER  FROM DATEN K WHERE SICHTBAR = 1  AND GELOESCHT = 0
/* DATEN_VIEW(NR,UUID,CREATED,MODIFIED,VERSION,TYP,PARENT,PARENT_NULL,DATA,DATA2,DATA3,INHALT,INFO,ANREDE,VORNAME,NACHNAME,ZWEITNAME,SUFFIX,SPITZNAME,FIRMA,TITEL,NAME,PFAD,IBAN,BIC,BANK,STRASSE,PLZ,ORT,LAND,REGION,TELEFON,FAX,MOBIL,PAGER,MAIL,WEB,ABSENDER,EMPFAENGER,SEITE,VERTRAGSNUMMER,RECHNUNGSNUMMER,AUFTRAGSNUMMER,KUNDENNUMMER,NUMMER,ALBUM,INTERPRET,TRACK,GENRE,FORMAT,DATUM,START,ENDE,GEBURTSTAG,JAHRESTAG,FAELLIG,VON,BIS,ERINNERUNG,STATUS,PROZENT,PRIORITAET,SUBTYP,JAHR,MONAT,TAG,LAENGE,HOEHE,BREITE,GANZTAGS,ERLEDIGT,SICHTBAR,GELOESCHT,BETRAG,MWST,ERSTELLT,GEAENDERT,ZUGRIFF,HASH,SELECTED,KATEGORIEN,CKATEGORIEN,CVERBINDUNGEN,PVERBINDUNGEN,NETTO,DAUER) */;

I removed all the Blob-Data. Now it runs in 8ms

so... where is the difference in PB compared to plain C, Xojo, Valentina-Studio where I use the same SQL?

[captain_skank]

not helping i know, but...

i've always found database results to be rather slow when using PB & mysql wih both ODBC and the newer native mysql commands.

In particular, dumping output to lists or maps, and when populating a webgadget ( not so bad using a listicongadget though ).

I'd love to know how to speed this up.

[NicTheQuick]

It sounds like PB is preloading every row into memory while the C version is only prefetching it, meaning that it should be as slow as Purebasic if you actually read the blobs. On the other hand you are only selecting the column NR, so there should be no blob at all. Well, that's weird.

[skywalk]

Have you tried running your queries entirely from a sqlite wrapper in PB?
And use begin transaction..commit?
I have not hit your db sizes so I have not been pushed to full wrapper mode.
My last slowdown was fixed with a sqlite version.

[kpeters58]

Strange indeed - as if there was some lazy column loading. Don't think SQLite supports that...

Many people would suggest placing blob data in a separate table (I have even read of separate databases for that purpose).
Not that that would fix the observed issue at its root, but it may be a smart move, in particular if your CRM? displays the blob data (images?, documents?) only on 'secondary' forms.

[Cyllceaux]

Strange indeed - as if there was some lazy column loading. Don't think SQLite supports that...

Many people would suggest placing blob data in a separate table (I have even read of separate databases for that purpose).
Not that that would fix the observed issue at its root, but it may be a smart move, in particular if your CRM? displays the blob data (images?, documents?) only on 'secondary' forms.

Not a CRM but an Archive for Documents, Pictures, Files... and Everything I use in Outlook.

The First Call is a filter-call. So I only need the NR. Later I call for the blobs. These Calls are really fast. I can "display" each blob in less than a second. This is what it made this so strange.

I will rewrite the Database and separate the BLOBs. But this is not what I wanted. Maybe there is a internal problem in PB