[sqlite] .NET System.Data.SQLite, how trap SQLITE_BUSY_SNAPSHOT (WAL) and retry
Hi, I am using WAL. If I use a new db connection for every write (as usually suggested practice) and connection pooling is enabled, my writes are quite a bit faster. Good so far. However, I also see some db locks occurring, specifically: (517) SQLITE_BUSY_SNAPSHOT For some reason, the .net wrapper only traces that to the console, but it does not throw. 1) How can I make the sqlite .NET wrapper throw an exception so I can deal with it. 2) Retry the same write again. Ideally, SQLite should retry itself, but I am not sure if this is supported. Thanks Andy ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] System.Data.Sqlite.dll v93 performance issues...
Guys, I do not know if this is the right forum, but we found a reproducible perf issue with System.Data.Sqlite.dll. A simple query like this: Select * from Dxlog where ContestNR = 0 order by contestnr, TS limit 24 offset 143098 Takes about 60ms to fetch the first record from the reader, and then only 0.5ms for the rest of the 24 rows. When I go back to version 92, it takes only 2ms for the first fetch. I can provivide more details if needed, but for now, I need to know these two things: 1) Where can I find the older builds, especially the statically linked one for v92. 2) Where can I create a bug for this? Thanks Andy ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] DMV available in Sqlite?
If not anything available, I could consider porting my db from sqlite to MSSQL and just change the data access. Wonder if anyone has attempted this and if this would give me the information what I am looking for (like: is the db designed correctly wrt indexes etc.). Thanks, Andy From: Andreas Hofmann [mailto:andreas.hofm...@ku7t.org] Sent: Thursday, July 24, 2014 12:33 PM To: 'General Discussion of SQLite Database' Subject: DMV available in Sqlite? Hi, I know how to use SQL server DMV for analyzing query performance. I am wondering if something similar to the MS SQL Server DMV is available for Sqlite (http://msdn.microsoft.com/en-us/library/ms188754.aspx)? Anyone? Thanks Andy ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] DMV available in Sqlite?
Hi, I know how to use SQL server DMV for analyzing query performance. I am wondering if something similar to the MS SQL Server DMV is available for Sqlite (http://msdn.microsoft.com/en-us/library/ms188754.aspx)? Anyone? Thanks Andy ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Fine tuning table indexes
Hi, I would like to fine tune table indexes. I want to make sure I got indexes for the columns or combined columns of all (most) where clauses. The issue is that the application builds dynamic SQL strings all over the place and it may not be easy to find them all without reading the code very closely. I would rather find out what indexes are needed/suggested by profiling the app. Is there a tool that can collect profiling information at run time and suggest missing indexes? If not, I am thinking about parsing the SQL strings myself, not a difficult thing to do, but I was wondering if I reinvent the wheel here. Thanks Andy ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Need pointers for better build integration and support of schema updates
Sorry if some of these are no brainers, I am a sqlite newbie with some MS SQL experience. Our application uses 3 Sqlite dbs and System.Data.Sqlite.dll (dropped by installer). Functionality allows users to start a new instance of one of the databases at run time. Hence we ship empty databases in a binary format, created by one of our developers. The process of making changes to the DDL is basically using a sqlite admin tool, make the changes, then update the installer with the new db file. I am looking for a better way how to automate this and have better source control integration. At my other job, we got MS SQL ddl source files as text files in source control, verify them at build time, and then ship the scripts. I would like accomplish these (or at least some of these) for the Sqlite dbs: - text files for sql in source control - build them from command line during msbuild for syntax verification - ship text files in the installer (I know how to do that) - run the text files against a newly created db at run time in order "install" the schema and data (I think this is just a simple execute the sql ddl agains the db, right?) - support schema updates. If V2 of the product ships, all dbs that are older should automatically be migrated to V2 when loaded. Is this as simple as writing the ddl as IF COLUMN EXISTS etc. statements? - version detection. At run time need to detect that a given db is of version v1. simple column in dbversion table? Any pointers would be appreciated. Thanks Andy ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Could not load file or assembly 'System.Data.SQLite.dll'or one of its dependencies on Windows XP. Ideas?
Even more information. I used fuslogvw.exe to see the binding errors. It seems that the assembly of System.SQLite.Data.dll was loaded fine: LOG: GAC Lookup was unsuccessful. LOG: Attempting download of new URL file:///C:/Program Files/N1MMLogger+/System.Data.SQLite.DLL. LOG: Assembly download was successful. Attempting setup of file: C:\Program Files\N1MMLogger+\System.Data.SQLite.dll LOG: Entering run-from-source setup phase. LOG: Assembly Name is: System.Data.SQLite, Version=1.0.86.0, Culture=neutral, PublicKeyToken=db937bc2d44ff139 LOG: Binding succeeds. Returns assembly from C:\Program Files\N1MMLogger+\System.Data.SQLite.dll. LOG: Assembly is loaded in default load context. But the native image was not: OG: Initial PrivatePath = NULL LOG: Dynamic Base = NULL LOG: Cache Base = NULL LOG: AppName = N1MMLogger.net.exe Calling assembly : N1MMLogger.net, Version=1.0.0.0, Culture=neutral, PublicKeyToken=null. === LOG: Start binding of native image System.Data.SQLite, Version=1.0.86.0, Culture=neutral, PublicKeyToken=db937bc2d44ff139. WRN: No matching native image found. LOG: IL assembly loaded from C:\Program Files\N1MMLogger+\System.Data.SQLite.dll. Anyone know why this is happening? Thanks Andreas -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Andreas Hofmann Sent: Wednesday, November 13, 2013 7:59 PM To: 'General Discussion of SQLite Database' Subject: Re: [sqlite] Could not load file or assembly 'System.Data.SQLite.dll' or one of its dependencies on Windows XP. Ideas? As a followup question: Should Windows XP be supported? Is there anything special that is needed? As I understand it, the dll is supposed to have .net interop and sqlite all inside. It works fine in W7, but not in XP... Any way to troubleshoot what is missing? Would ildasm tell me? Thanks Andreas -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Andreas Hofmann Sent: Wednesday, November 13, 2013 5:19 PM To: sqlite-users@sqlite.org Subject: [sqlite] Could not load file or assembly 'System.Data.SQLite.dll' or one of its dependencies on Windows XP. Ideas? Deploying the same app on W7 32, W7 64, and XP 32. Using XCOPY deployment. W7 are fine, in XP it complains about this: System.IO.FileNotFoundException: Could not load file or assembly 'System.Data.SQLite.dll' or one of its dependencies. The specified module could not be found. File name: 'System.Data.SQLite.dll' at N1MMLogger.Net.DBInterface..ctor() at N1MMLogger.Net.AdminDatabase..ctor(Boolean IsReorganize) in C:\Users\andreash\Downloads\N1MMSource\N1MM Logger on .NET\N1MM Logger.Net\Classes\DBInterface.vb:line 270 However the file is there. 907,264 System.Data.SQLite.dll, verion 1.0.86.0, SQLite version: 3.7.17. I assume this is the interop file that also has the native sqlite inside. As I mentioned, I deployed the same folder (also after deploying .NET 4.0). Any ideas? Thanks Andreas ___ 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] Could not load file or assembly 'System.Data.SQLite.dll' or one of its dependencies on Windows XP. Ideas?
As a followup question: Should Windows XP be supported? Is there anything special that is needed? As I understand it, the dll is supposed to have .net interop and sqlite all inside. It works fine in W7, but not in XP... Any way to troubleshoot what is missing? Would ildasm tell me? Thanks Andreas -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Andreas Hofmann Sent: Wednesday, November 13, 2013 5:19 PM To: sqlite-users@sqlite.org Subject: [sqlite] Could not load file or assembly 'System.Data.SQLite.dll' or one of its dependencies on Windows XP. Ideas? Deploying the same app on W7 32, W7 64, and XP 32. Using XCOPY deployment. W7 are fine, in XP it complains about this: System.IO.FileNotFoundException: Could not load file or assembly 'System.Data.SQLite.dll' or one of its dependencies. The specified module could not be found. File name: 'System.Data.SQLite.dll' at N1MMLogger.Net.DBInterface..ctor() at N1MMLogger.Net.AdminDatabase..ctor(Boolean IsReorganize) in C:\Users\andreash\Downloads\N1MMSource\N1MM Logger on .NET\N1MM Logger.Net\Classes\DBInterface.vb:line 270 However the file is there. 907,264 System.Data.SQLite.dll, verion 1.0.86.0, SQLite version: 3.7.17. I assume this is the interop file that also has the native sqlite inside. As I mentioned, I deployed the same folder (also after deploying .NET 4.0). Any ideas? Thanks Andreas ___ 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] Could not load file or assembly 'System.Data.SQLite.dll' or one of its dependencies on Windows XP. Ideas?
Deploying the same app on W7 32, W7 64, and XP 32. Using XCOPY deployment. W7 are fine, in XP it complains about this: System.IO.FileNotFoundException: Could not load file or assembly 'System.Data.SQLite.dll' or one of its dependencies. The specified module could not be found. File name: 'System.Data.SQLite.dll' at N1MMLogger.Net.DBInterface..ctor() at N1MMLogger.Net.AdminDatabase..ctor(Boolean IsReorganize) in C:\Users\andreash\Downloads\N1MMSource\N1MM Logger on .NET\N1MM Logger.Net\Classes\DBInterface.vb:line 270 However the file is there. 907,264 System.Data.SQLite.dll, verion 1.0.86.0, SQLite version: 3.7.17. I assume this is the interop file that also has the native sqlite inside. As I mentioned, I deployed the same folder (also after deploying .NET 4.0). Any ideas? Thanks Andreas ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
