[sqlite] Improving SQLite performance over a network
Hello, I am using SQLite in a Windows environment. The file storing the database is on a Windows 2008 server.All other file access to this server is reasonably fast. However, when doing simple SELECTS and INSERTS/UPDATESit is noticeably slower on a network. I am talking about 50 records. Now I have read that setting some of the PRAGMAS can improve speed, but at the cost of data loss should powerfail or some other hardware condition occur. I am willing to risk this if the performance of the SQLite database accesscan be increased considerably. Here is what I am doing in code (This is C#) string connectionString = ""URI=file:mydb.db; Default Timeout=15; PRAGMA busy_timeout = 600" SQLiteConnection sqConn = new SQLiteConnection(connectionString);sqConn.Open(); Then immediately after opening the database, I'm? running this code string sqlPragma = "PRAGMA cache_size = 16000; PRAGMA synchronous = OFF; PRAGMA journal_mode = MEMORY;"; SQLiteCommand sqlite_command = OMConnection.sqConn.CreateCommand(); sqlite_command.CommandText = sqlPragma; sqlite_command.ExecuteNonQuery(); This sets up the pragmas for the cache size, synchronous mode, journal mode. I do not notice any difference in performance with the pragmas vs. without them.? Am I doing something wrong?Are there any other documented PRAGMAS which might help improve performance or are there any other measuresI can use to get faster performance over a network file vs. local file? ThanksMike
[sqlite] SQlite database access over wireless network
Hello, I am trying to troubleshoot a problem that I'm having with a program usingSQLite over a wireless network.? I know that wireless networks can havetheir own sets of issues that cause database access problems. What is happening is that something that takes 1 or 2 seconds on a wirednetwork will take 15-20 seconds over wireless.? I am using System.Data.SQLite, which is based on version SQLite 3. 8.8.3 (version 1.0.96.0)with C# Visual Studio. The program is installed and runs off a network. The database is also on a network. The reads are somewhat slower running on a wired network than when run on a local machine.However, when run over wireless it is significantly slower.? These are mostly reads onfairly simple queries.? Are there any pragmas that may be used to increase the latency or to allow thereads to process faster across a network??? Or are there any other tips or tricksthat may be used to speed up access? This is a multi-user database with about 2-10 usersreading/writing data. Most of the time, the client application sits idle.? The SQLite connectionsare opened only on demand, then closed after I/O operations. However this opening and closingis kept to a minimum. ThanksMike
[sqlite] Including PRAGMA statements on connection string
Hellom, I am using the c# variant of sqlite (system.sqlite.data.org) and am using a connection string similar to this: public static string OMconnectionString = "datasource=database.db; Default Timeout=20; PRAGMA busy_timeout = 600; PRAGMA synchronous=Off"; I then use the following statements to open the connection to the database: OMConnection.sqConn = new SQLiteConnection(OMConnection.OMconnectionString); OMConnection.sqConn.Open(); However, I do not seem to notice any effect when including the PRAGMA synchronous=Off.? I have tried this without the PRAGMA statement,but still do not see any effect.? What is the proper way to do this in sqlite with a connection string?
Re: [sqlite] Network and concurrency
I still get the crashes even after adding the pragma. My connection string looks like: public static string OMconnectionString = "URI=file:omm.db; PRAGMA busy_timeout = 15000" Is there anything else I can try? Mike On Wednesday, November 26, 2014 10:34 AM, Simon Slavin <slav...@bigfraud.org> wrote: On 26 Nov 2014, at 4:20pm, Mike McWhinney <elja...@sbcglobal.net> wrote: > Are there any other PRAGMA or connection string > statements that can help with this concurrency issue? If you haven't set a busy timeout then SQlite won't retry when there is a network clash, it will immediately return an error code. If you have the ability to use PRAGMAs then I suggest you set your timeout <http://www.sqlite.org/pragma.html#pragma_busy_timeout> to 6 milliseconds for testing. If this makes your problem go away that should identify your problem. Simon ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Network and concurrency
Hello, I am continuing to have sporadic problems with SQLite and a WinForms application that I'm developing. The program is a Winforms app using the System.Data.SQLite class. I am running this off a network. There are a small number of users which read and write to a database stored on a network. There are locking errors sometimes or sometimes when performing an operation the program will simply become locked up and eventually crash because the UI thread gets locked up. I tried to put some of these calls on separate threads, but for some reason the lockups still occur. Are there any other PRAGMA or connection string statements that can help with this concurrency issue? Thanks Mike ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Locking errors on network
I am using Sqlite.NET client in C#. How would I go about defining a sqlite busy timeout handler? Thanks Mike On Monday, November 10, 2014 1:35 PM, Tim Streater <t...@clothears.org.uk> wrote: On 10 Nov 2014 at 18:22, Mike McWhinney <elja...@sbcglobal.net> wrote: > So SQLite shouldn't be used at all on a network? Aren't there any other > provisions to handled the locking errors if/when > they occur? You tried setting a timeout as pointed to here? https://www.sqlite.org/faq.html#q5 (not that this would avoid any potential corruption issue). -- Cheers -- Tim ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Locking errors on network
So SQLite shouldn't be used at all on a network? Aren't there any other provisions to handled the locking errors if/when they occur? On Monday, November 10, 2014 12:10 PM, Roger Binns <rog...@rogerbinns.com> wrote: -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 11/10/2014 09:41 AM, Mike McWhinney wrote: > Please let know if there are any other solutions to this database > locking problem as used on a network. Yes. Do not do it. See the FAQ: https://www.sqlite.org/faq.html#q5 Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1 iEYEARECAAYFAlRg/6wACgkQmOOfHg372QQAfgCeLCZ7I4uC/3p+bNSuGQN0uTUB 6LEAoLjp4/yJzVJSWzGDq7cam8pezRma =jie1 -END PGP SIGNATURE- ail_ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Locking errors on network
Hello,
I continue to have "database is locked errors" when running an application
which accesses a SQLite dabase on a network.
I have tried many of the connection string options: pooling, changing default
time out.
I just recently tried a block of code which does a BeginTransaction and Commit.
Before I did not use this. However it does not seem to have an effect
on the locking problem.
string sql;
sql = "UPDATE APPOINTMENTS SET " +
// "PatientID=" + myPatientID + "," +
"TimeStampIsSeenByMA='" + nowString + "'" +
" WHERE (" +
"PatientID=" + myPatientID + " AND Date=" + "'" +
dateString + "'" + ")";
SQLiteCommand command = new SQLiteCommand(sql, OMConnection.sqConn);
int numRowsAffected = 0;
SQLiteTransaction tran;
tran = OMConnection.sqConn.BeginTransaction(false);
numRowsAffected = command.ExecuteNonQuery();
command.Dispose();
tran.Commit();
Please let know if there are any other solutions to this database locking
problem as used on a network.
Thanks
Mike
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Random locking errors using Sqlite.NET
Hello, I continue to have random errors on SQLite.NET (ADO provider). I have the version 3.8.6 (System.Data.SQLite.dll with version 1.0.94.0). I am getting random locking errors during the execution of my program. The program resides on a network and each client has a mapped share with full read/write access to the folder as well as the .db file used by SQLite. In my connection string I have the following: public static string OMconnectionString = "URI=file:oslermedicine.db; Default Timeout=10; Pooling=True; Max Pool Size=100;"; What might I be doing wrong? I am disposing of all objects (data readers, tables, commands) after use. Thanks Mike ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Fw: Database is locked exceptions
I thought the SQLite mailing list included traffic that discussed System.Data.SQLite I am not sure if there are any forums specifically for the ADO .NET provider and support. I will google to see if I can find the proper reference. Mike On Thursday, October 30, 2014 12:00 PM, Simon Slavin <slav...@bigfraud.org> wrote: On 30 Oct 2014, at 4:13pm, Mike McWhinney <elja...@sbcglobal.net> wrote: > I have continued to receive the locking erros. The latest connection string I > have as follows: > > public static string OMconnectionString = "URI=file:oslermedicine.db; busy > timeout=1; Pooling=True; Max Pool Size=100;"; > > > > Are there any other parameters that will increase the timeout when the > database gets locked? I'm sorry, but the library you're using uses parameters which don't exist in SQLite, and I don't know enough about how it works. Perhaps somebody else has a suggestion. Or perhaps you can contact the authors of that web page and ask them. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Fw: Database is locked exceptions
I have continued to receive the locking erros. The latest connection string I have as follows: public static string OMconnectionString = "URI=file:oslermedicine.db; busy timeout=1; Pooling=True; Max Pool Size=100;"; Are there any other parameters that will increase the timeout when the database gets locked? Thanks Mike On Wednesday, October 29, 2014 5:26 PM, Mike McWhinney <elja...@sbcglobal.net> wrote: I will let you know if this fixes the problem. So far I have not had any locking issues. I will see tomorrow when it receives more usage and traffic from other users Mike On Wednesday, October 29, 2014 4:52 PM, Simon Slavin <slav...@bigfraud.org> wrote: On 29 Oct 2014, at 9:00pm, Mike McWhinney <elja...@sbcglobal.net> wrote: > What is the exact connection string parameter? I am a little confused. From > this website > > http://www.devart.com/dotconnect/sqlite/docs/Devart.Data.SQLite~Devart.Data.SQLite.SQLiteConnection~ConnectionString.html > > It shows that the Default Command Timeout is the parameter and that it is > expressed in seconds. Hmm. Well, that's confusing. The web page you cited says that this parameter is not the parameter I thought it was. It lists a parameter "Default Command Timeout" which is not the same as the "default timeout" included in the string in your dump. Not only that, but it includes another parameter "Busy Timeout" which I /think/ is the one I wanted in the first place. I have no idea what "default timeout" is or how it is implemented. But you may as well leave it as it is. Instead can you please change "URI=file:mydb.db; default timeout=10; Pooling=True; Max Pool Size=100;" to "URI=file:mydb.db; default timeout=10; Busy Timeout=1; Pooling=True; Max Pool Size=100;" So make this change and see if your problem goes away. Whether it does or not can help us figure out what's wrong. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Database is locked exceptions
Hello,
I have written a program in C# Visual Studio Winforms. In general the SQLite
database is working quite well.
However, lately we have been plagued with database locking errors:
Here is the exeption:
database is locked
database is locked
at System.Data.SQLite.SQLite3.Step(SQLiteStatement stmt)
at System.Data.SQLite.SQLiteDataReader.NextResult()
at System.Data.SQLite.SQLiteDataReader..ctor(SQLiteCommand cmd,
CommandBehavior behave)
at System.Data.SQLite.SQLiteCommand.ExecuteReader(CommandBehavior
behavior)
at System.Data.SQLite.SQLiteCommand.ExecuteNonQuery(CommandBehavior
behavior)
at System.Data.SQLite.SQLiteCommand.ExecuteNonQuery()
at WindowsApplication1.Form1.logExceptionToTable()
at WindowsApplication1.Form1.MyExceptionHandler(Object sender,
ThreadExceptionEventArgs e)
at
System.Windows.Forms.Application.ThreadContext.OnThreadException(Exception
t)
at System.Windows.Forms.Control.WndProcException(Exception e)
at
System.Windows.Forms.Control.ControlNativeWindow.OnThreadException(Exception
e)
at System.Windows.Forms.NativeWindow.Callback(IntPtr hWnd, Int32 msg,
IntPtr wparam, IntPtr lparam)
at System.Windows.Forms.UnsafeNativeMethods.DispatchMessageW(MSG&
msg)
at
System.Windows.Forms.Application.ComponentManager.System.Windows.Forms.UnsafeNativeMethods.IMsoComponentManager.FPushMessageLoop(Int32
dwComponentID, Int32 reason, Int32 pvLoopData)
at
System.Windows.Forms.Application.ThreadContext.RunMessageLoopInner(Int32
reason, ApplicationContext context)
at
System.Windows.Forms.Application.ThreadContext.RunMessageLoop(Int32
reason, ApplicationContext context)
at System.Windows.Forms.Application.RunDialog(Form form)
at System.Windows.Forms.Form.ShowDialog(IWin32Window owner)
at System.Windows.Forms.Form.ShowDialog()
at
WindowsApplication1.LabTestPatientForm.enterTestDataButton_Click(Object
sender, EventArgs e)
at System.Windows.Forms.Control.OnClick(EventArgs e)
at DevComponents.DotNetBar.ButtonX.OnClick(EventArgs e)
at System.Windows.Forms.Control.WmMouseUp(Message& m,
MouseButtons button, Int32 clicks)
at System.Windows.Forms.Control.WndProc(Message& m)
at DevComponents.DotNetBar.ButtonX.WndProc(Message& m)
at
System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message&
m)
at
System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m)
at System.Windows.Forms.NativeWindow.Callback(IntPtr hWnd, Int32 msg,
IntPtr wparam, IntPtr lparam)
The database resides on a network on a Windows Server 2008 R2 system. The users
have full read/write
permissions to the database file.
The code that I'm using to access the database is:
OMConnection.sqConn = new SQLiteConnection(
"URI=file:mydb.db; default timeout=10; Pooling=True; Max Pool Size=100;";
sqConn.Open();
string sql = "SELECT " +
"*" +
" FROM APPOINTMENTTYPES";
SQLiteCommand cmd = OMConnection.sqConn.CreateCommand();
cmd.CommandText = sql;
cmd.CommandType = CommandType.Text;
SQLiteDataReader dr = cmd.ExecuteReader();
DataTable myTable = new DataTable();
myTable.Load(dr);
//put the categories into the
for (int rowIndex = 0; rowIndex < myTable.Rows.Count; rowIndex++)
{
dataSet1.Tables["ReasonForVisit"].Rows.Add("");
dataSet1.Tables["ReasonForVisit"].Rows[rowIndex]["ReasonForVisitName"] =
myTable.Rows[rowIndex]["Name"].ToString().Trim();
dataSet1.Tables["ReasonForVisit"].Rows[rowIndex]["Duration"] =
myTable.Rows[rowIndex]["Duration"];
}
myTable.Dispose();
OMConnection.sqConn.Close();
OMConnection.sqConn.Dispose();
OMConnection.sqConn = null;
The thing is, the database locking errors are random and do not always occur. I
am using the
c# ADO provider, version 1.0.94.0 (version 3.8.6 of SQLite).
One thing is that I have tried a method where I open the database and leave it
open through out the life
of the program (open it when I start the program and close on exit). Should I
perhaps change this so that
I open the database, perform the query, then close it right away?
Thanks
Mike
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
