On Sun, May 25, 2014 at 4:22 PM, Andrew Arnott <andrewarn...@gmail.com>wrote:
> 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 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 });
>
I suspect the problem with the slow one is that the subquery has to be
re-run and count the matching rows for every matching RemoteContactId. You
might be able to speed it up with a simple "LIMIT 1" in the sub-query, like
so: (SELECT COUNT(Id) FROM Attachment a WHERE a.MessageId = m.Id LIMIT 1).
Since you only care if the number is greater than zero, 1 will do just as
well as any other non-zero number.
SDR
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
