The queries are identical and you should have similar performance on each assuming that you have the correct indexes defined.
I presume you have indexes on Message(RemoteContactID, Id) and Attachment(MessageId)? If you do not, then if you only have indexes on Message(Id), the first will be fast(er) for trivial table sizes because it will do a full table scan of the attachments table, look up the record (using the index) in Message, apply the filter, and return the results. Performance will deteriorate when you get a significant number of records in Attachment. Conversely, the second (slow) query can only be solved by (without appropriate indexes) scanning the Message table and for each hit scanning the Attachment table. Table Scans are a slow process for non-trivial tables (and a particularly bad idea in inner-loops). >-----Original Message----- >From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users- >boun...@sqlite.org] On Behalf Of Andrew Arnott >Sent: Sunday, 25 May, 2014 16:22 >To: General Discussion of SQLite Database >Subject: [sqlite] Two equivalent queries with very different perf > >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 _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
