[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(@"
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(@"
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
Re: [sqlite] Windows Phone 8.1
I'd love to see it as well. Now that my app is ready for submission to the Windows Phone store, I find that the Sqlite available through Tim Heuer's blog prevents my app from being accepted by the store because this build of sqlite uses disallowed APIs. -- 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 On Wed, Apr 23, 2014 at 8:54 PM, Ryan Finnesey wrote: > Thank you this is very helpful. > > Can anyone share the process to get an "official" build released? Would be > great for the community to have it available on nuget. > > Cheers > Ryan > > > -Original Message- > From: sqlite-users-boun...@sqlite.org [mailto: > sqlite-users-boun...@sqlite.org] On Behalf Of Andrew Arnott > Sent: Monday, April 21, 2014 10:24 AM > To: General Discussion of SQLite Database > Cc: Lane Williams > Subject: Re: [sqlite] Windows Phone 8.1 > > I found this, which offers a private build of a Universal compatible > version of SQLite. > > http://timheuer.com/blog/archive/2014/04/17/universal-windows-apps-nuget-sdk-references-sqlite.aspx > > -- > 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 > > > On Mon, Apr 21, 2014 at 6:59 AM, Andrew Arnott >wrote: > > > +1. > > > > Windows Phone 8.1 (Appx) is a new platform. It's much closer to the > > WinRT than it is to Windows Phone 8.x (Silverlight). So I suspect the > > closest matching SQLite SDK would be the existing WinRT one. But as to > > how to make it available to add as an SDK Reference to WinPhone 8.1 > > Appx projects I don't know. I was hoping to find the answer here. > > > > -- > > 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 > > > > > > On Fri, Apr 18, 2014 at 5:50 PM, Ryan Finnesey > wrote: > > > >> Hi Lane > >> > >> I was wondering if you have found a solution. I am running into the > >> same issue with Azure Mobile Services and a Windows Phone 8.1 App. > >> > >> Cheers > >> Ryan > >> > >> > >> -Original Message- > >> From: sqlite-users-boun...@sqlite.org [mailto: > >> sqlite-users-boun...@sqlite.org] On Behalf Of Lane Williams > >> Sent: Sunday, April 13, 2014 4:53 PM > >> To: sqlite-users@sqlite.org > >> Subject: [sqlite] Windows Phone 8.1 > >> > >> I have used the SQLite on several Windows Store and Windows Phone > >> projects including the latest version 3.8.4.3 on a Windows Phone 8 > >> project from VS 2013, they all work Great. > >> > >> However the 3.8.4.3 version will not recognize in my latest Windows > >> Phone > >> 8.1 project. I am trying to use the new "Universal Apps" method in > >> VS 2013, the Windows 8.1 will load (3.8.4.3) but the Windows Phone > >> 8.1 does not show the SQLite as an option to load. > >> > >> Is an update in the works to support the latest Windows Phone 8.1 > >> platform. Is there a suggested work around for using the current > >> version of SQLite with Windows Phone 8.1. > >> > >> Thanks, > >> Lane > >> ___ > >> 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 > >> > > > > > ___ > 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 > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Windows Phone 8.1
I found this, which offers a private build of a Universal compatible version of SQLite. http://timheuer.com/blog/archive/2014/04/17/universal-windows-apps-nuget-sdk-references-sqlite.aspx -- 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 On Mon, Apr 21, 2014 at 6:59 AM, Andrew Arnott wrote: > +1. > > Windows Phone 8.1 (Appx) is a new platform. It's much closer to the WinRT > than it is to Windows Phone 8.x (Silverlight). So I suspect the closest > matching SQLite SDK would be the existing WinRT one. But as to how to make > it available to add as an SDK Reference to WinPhone 8.1 Appx projects I > don't know. I was hoping to find the answer here. > > -- > 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 > > > On Fri, Apr 18, 2014 at 5:50 PM, Ryan Finnesey wrote: > >> Hi Lane >> >> I was wondering if you have found a solution. I am running into the same >> issue with Azure Mobile Services and a Windows Phone 8.1 App. >> >> Cheers >> Ryan >> >> >> -Original Message- >> From: sqlite-users-boun...@sqlite.org [mailto: >> sqlite-users-boun...@sqlite.org] On Behalf Of Lane Williams >> Sent: Sunday, April 13, 2014 4:53 PM >> To: sqlite-users@sqlite.org >> Subject: [sqlite] Windows Phone 8.1 >> >> I have used the SQLite on several Windows Store and Windows Phone >> projects including the latest version 3.8.4.3 on a Windows Phone 8 project >> from VS 2013, they all work Great. >> >> However the 3.8.4.3 version will not recognize in my latest Windows Phone >> 8.1 project. I am trying to use the new "Universal Apps" method in VS >> 2013, the Windows 8.1 will load (3.8.4.3) but the Windows Phone 8.1 does >> not show the SQLite as an option to load. >> >> Is an update in the works to support the latest Windows Phone 8.1 >> platform. Is there a suggested work around for using the current version >> of SQLite with Windows Phone 8.1. >> >> Thanks, >> Lane >> ___ >> 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 >> > > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Windows Phone 8.1
+1. Windows Phone 8.1 (Appx) is a new platform. It's much closer to the WinRT than it is to Windows Phone 8.x (Silverlight). So I suspect the closest matching SQLite SDK would be the existing WinRT one. But as to how to make it available to add as an SDK Reference to WinPhone 8.1 Appx projects I don't know. I was hoping to find the answer here. -- 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 On Fri, Apr 18, 2014 at 5:50 PM, Ryan Finnesey wrote: > Hi Lane > > I was wondering if you have found a solution. I am running into the same > issue with Azure Mobile Services and a Windows Phone 8.1 App. > > Cheers > Ryan > > > -Original Message- > From: sqlite-users-boun...@sqlite.org [mailto: > sqlite-users-boun...@sqlite.org] On Behalf Of Lane Williams > Sent: Sunday, April 13, 2014 4:53 PM > To: sqlite-users@sqlite.org > Subject: [sqlite] Windows Phone 8.1 > > I have used the SQLite on several Windows Store and Windows Phone projects > including the latest version 3.8.4.3 on a Windows Phone 8 project from VS > 2013, they all work Great. > > However the 3.8.4.3 version will not recognize in my latest Windows Phone > 8.1 project. I am trying to use the new "Universal Apps" method in VS > 2013, the Windows 8.1 will load (3.8.4.3) but the Windows Phone 8.1 does > not show the SQLite as an option to load. > > Is an update in the works to support the latest Windows Phone 8.1 > platform. Is there a suggested work around for using the current version > of SQLite with Windows Phone 8.1. > > Thanks, > Lane > ___ > 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 > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite on Windows Phone 8 fails on "VACUUM" command
Hi Joe, Thanks for the workaround. It works! Is there a place where a bug should be filed to track this? -- 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 On Tue, Mar 11, 2014 at 3:30 PM, Joe Mistachkin wrote: > > Andrew Arnott wrote: > > > > I tried adding sqlite3_set_directory to the sqliteWP8.cpp file but it got > > too intense for my limited C++ knowledge. > > > > If the Windows Phone 8 wrapper for SQLite does not properly set the > temporary > directory, queries that requires a temporary file (e.g. VACUUM) may fail. > > Until the wrapper package is changed to do this, the workaround would be to > open a database connection and then immediately execute the following > query: > > "PRAGMA temp_store_directory = 'C:\some\temp\directory';" > > Using the ApplicationData.LocalFolder property for the temporary directory > value should work fine. > > -- > Joe Mistachkin > > ___ > 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
Re: [sqlite] SQLite on Windows Phone 8 fails on "VACUUM" command
Hi Joe, Thanks for your reply. After making that change (and also adding the SQLite3.SetDirectory method definition itself since that too was inside an #if) the app crashes because WP8 doesn't support the TemporaryFolder property. 'Windows.Storage.ApplicationData.Current.TemporaryFolder' threw an exception of type 'System.NotImplementedException' When I replaced the use of TemporaryFolder with just LocalFolder, the line again threw an exception, but with one I couldn't figure out how to workaround: System.NotSupportedException occurred _HResult=-2146233067 _message=DllImport cannot be used on user-defined methods. HResult=-2146233067 Message=DllImport cannot be used on user-defined methods. Source=Dart.WinPhone8Lib StackTrace: at SQLite.SQLite3.SetDirectory(UInt32 directoryType, String directoryPath) at SQLite.SQLiteConnection..ctor(String databasePath, SQLiteOpenFlags openFlags, Boolean storeDateTimeAsTicks) InnerException: I tried adding sqlite3_set_directory to the sqliteWP8.cpp file but it got too intense for my limited C++ knowledge. -- 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 On Mon, Mar 10, 2014 at 10:57 PM, Joe Mistachkin wrote: > > Andrew Arnott wrote: > > > > The exception raised when I send "VACUUM" as a sql statement is: > > SQLite.SQLiteException occurred > > _HResult=-2146233088 > > _message=SQL logic error or missing database > > HResult=-2146233088 > > Message=SQL logic error or missing database > > Source=Dart.WinPhone8Lib > > StackTrace: > >at SQLite.SQLiteCommand.ExecuteNonQuery() > > InnerException: > > > > Thanks for any help you can offer! > > > > In the "SQLite.cs" file, there is the following: > > #if NETFX_CORE > SQLite3.SetDirectory(/*temp directory type*/2, > Windows.Storage.ApplicationData.Current.TemporaryFolder.Path); > #endif > > Can you please try removing the surrounding "#if" from this code and try it > again? > > -- > Joe Mistachkin > > ___ > 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
Re: [sqlite] SQLite on Windows Phone 8 fails on "VACUUM" command
I've created a trivial repro project, which you can download here: http://1drv.ms/N2D4gP The exception raised when I send "VACUUM" as a sql statement is: SQLite.SQLiteException occurred _HResult=-2146233088 _message=SQL logic error or missing database HResult=-2146233088 Message=SQL logic error or missing database Source=Dart.WinPhone8Lib StackTrace: at SQLite.SQLiteCommand.ExecuteNonQuery() InnerException: Thanks for any help you can offer! -- 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 On Mon, Mar 10, 2014 at 8:06 PM, Andrew Arnott wrote: > I expect I could build a miniature repro if that would be useful to anyone. > On Mar 10, 2014 8:05 PM, "Andrew Arnott" wrote: > >> It raises an error, which the sqlite.cs file that everyone uses turns >> into an exception. >> On Mar 10, 2014 6:01 PM, "Simon Slavin" wrote: >> >>> >>> On 11 Mar 2014, at 12:56am, Andrew Arnott >>> wrote: >>> >>> > When I upgraded from the SQLite for WinPhone8 SDK v3.8.1 to 3.8.3.1, >>> the >>> > VACUUM statement quit working. Any idea why? >>> >>> What does it do instead of work ? >>> >>> Simon. >>> ___ >>> 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
Re: [sqlite] SQLite on Windows Phone 8 fails on "VACUUM" command
I expect I could build a miniature repro if that would be useful to anyone. On Mar 10, 2014 8:05 PM, "Andrew Arnott" wrote: > It raises an error, which the sqlite.cs file that everyone uses turns into > an exception. > On Mar 10, 2014 6:01 PM, "Simon Slavin" wrote: > >> >> On 11 Mar 2014, at 12:56am, Andrew Arnott wrote: >> >> > When I upgraded from the SQLite for WinPhone8 SDK v3.8.1 to 3.8.3.1, the >> > VACUUM statement quit working. Any idea why? >> >> What does it do instead of work ? >> >> Simon. >> ___ >> 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
Re: [sqlite] SQLite on Windows Phone 8 fails on "VACUUM" command
It raises an error, which the sqlite.cs file that everyone uses turns into an exception. On Mar 10, 2014 6:01 PM, "Simon Slavin" wrote: > > On 11 Mar 2014, at 12:56am, Andrew Arnott wrote: > > > When I upgraded from the SQLite for WinPhone8 SDK v3.8.1 to 3.8.3.1, the > > VACUUM statement quit working. Any idea why? > > What does it do instead of work ? > > Simon. > ___ > 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
[sqlite] SQLite on Windows Phone 8 fails on "VACUUM" command
When I upgraded from the SQLite for WinPhone8 SDK v3.8.1 to 3.8.3.1, the VACUUM statement quit working. Any idea why? I'd be quite happy to 'rollback' to 3.8.1, but I can't find a way to download prior versions of the SDK. Thanks. -- 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
