Hello!
Sorry if this report shows up as a duplicate but didn?t figure out jet why
some of my emails not showing up or getting rejected.
____________
This is my first "bug" report here so please bear with me for blunders.
Using:
?sqlite-amalgamation-3081002.zip?
Build as:
?cl
sqlite3.c -O2 -DSQLITE_THREADSAFE=1 -DSQLITE_ENABLE_FTS4
-DSQLITE_API=__declspec(dllexport)
-link -dll -SUBSYSTEM:WINDOWS,"5.01" -out:sqlite3.dll?
I have 2 Tables:
CREATE TABLE `ART` (
`ID` integer NOT NULL,
`Kundennummer` integer,
`Rechnungsnummer` varchar,
`Rechnungsdatum` datetime,
`PDF` varchar,
PRIMARY KEY(ID)
);
and
CREATE VIRTUAL TABLE `OCR` using fts4 (
`ID` integer primary key NOT NULL,
`FullOCR` varchar
);
"PRAGMA journal_mode = OFF"
Both table has ~10.000 records but for testing more than 1000 is not
recommended - one query can take up to 4 hours -
?FullOCR? has list of words in rows averaging 700/words * ~8 chars per row.
The problem:
Query (for 1000 records)
498 Rows returned from: SELECT ID FROM ART WHERE NOT EXISTS (SELECT ID FROM
OCR WHERE OCR.ID = ART.ID); (took 34157ms)
(For 10.000 records it takes ~ 3.5 hours.)
- Result seems to be correct. -
498 Rows returned from: SELECT ART.ID FROM ART LEFT JOIN OCR ON OCR.ID =
ART.ID WHERE OCR.ID IS NULL; (took 47924ms)
(For 10.000 records it takes ~ 4 hours.)
- Result seems to be correct. -
498 Rows returned from: SELECT ID FROM ART WHERE ID NOT IN (SELECT ID FROM
OCR); (took 103ms)
(For 10.000 records it takes 1759ms)
Result seems to be correct as well.
I am not pretending to be an expert but it looks like that some serious
optimization flub is going on
with ?NOT EXISTS? and ?LEFT JOIN?, the timings are "horrific".
?NOT IN? looks just fine.
On MySQL all 3 queries timing is nearly identical ~1-2 seconds for 10.000
records.
MR,
Marta
