I'm using C# with SQLite.cs and SQLiteAsync.cs. I started with the slow
version and upon realizing perf was *really* bad I experimented with
different forms and discovered the fast version worked great. On Windows
Phone 8.1, the slow one took ~20 seconds to execute while the fast one was
well under a second. The "Attachment" table contains binary data that can
be over a MB in size.
It isn't clear why the slow one would be so much slower. In my naïve
opinion the query analyze in SQLite should recognize and treat these two as
the same but obviously the execution strategy is vastly different. Is there
an opportunity here to optimize SQLite for the slow query form?
var fast = await this.Database.QueryAsync<Entities.Message>(@"
SELECT DISTINCT m.Id FROM Message m
INNER JOIN Attachment a ON a.MessageId = m.Id
WHERE m.RemoteContactId = ?
",
new object[] { this.RemoteParty.Id });
var slow = await this.Database.QueryAsync<Entities.Message>(@"
SELECT m.Id FROM Message m
WHERE m.RemoteContactId = ? AND (
SELECT COUNT(Id) FROM Attachment a
WHERE a.MessageId = m.Id
) > 0", new object[] { this.RemoteParty.Id });
--
Andrew Arnott
"I [may] not agree with what you have to say, but I'll defend to the death
your right to say it." - S. G. Tallentyre
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
