Quantcast

SQLite3 Pros / Cons

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
15 messages Options
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

SQLite3 Pros / Cons

Clyde Eisenbeis
I'm new to SQLite ... started using it a few months ago.  I was
unaware of SQLite3 until I joined the SQLite mailing list.

What are the pros / cons of SQLite3?

If I switched from "using System.Data.SQLite" to SQLite3, are all of
the functions in a .dll I could download and use?
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: SQLite3 Pros / Cons

Simon Slavin-3

On 4 Feb 2017, at 7:57pm, Clyde Eisenbeis <[hidden email]> wrote:

> I'm new to SQLite ... started using it a few months ago.  I was
> unaware of SQLite3 until I joined the SQLite mailing list.
>
> What are the pros / cons of SQLite3?

You might find this useful:

<https://sqlite.org/whentouse.html>

Pay special attention to the middle session which tells you when to use something else.

Simon.

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: SQLite3 Pros / Cons

Clemens Ladisch
In reply to this post by Clyde Eisenbeis
Clyde Eisenbeis wrote:
> What are the pros / cons of SQLite3?

http://www.sqlite.org/whentouse.html

> If I switched from "using System.Data.SQLite" to SQLite3, are all of
> the functions in a .dll I could download and use?

http://www.sqlite.org/howtocompile.html
http://www.sqlite.org/download.html


Regards,
Clemens
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: SQLite3 Pros / Cons

Drago, William @ CSG - NARDA-MITEQ-2
In reply to this post by Clyde Eisenbeis
> -----Original Message-----
> From: sqlite-users [mailto:[hidden email]] On
> Behalf Of Clyde Eisenbeis
> Sent: Saturday, February 04, 2017 2:58 PM
> To: SQLite mailing list <[hidden email]>
> Subject: [sqlite] SQLite3 Pros / Cons
>
> I'm new to SQLite ... started using it a few months ago.  I was unaware of
> SQLite3 until I joined the SQLite mailing list.
>
> What are the pros / cons of SQLite3?
>
> If I switched from "using System.Data.SQLite" to SQLite3, are all of the
> functions in a .dll I could download and use?

You're probably better off sticking with System.Data.SQLite because it will be easier to make your code compatible with other databases. It's also simpler in my opinion than using the SQLite3 .dll.

--
Bill Drago
Staff Engineer
L3 Narda-MITEQ
435 Moreland Road
Hauppauge, NY 11788
631-272-5947 / [hidden email]



> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
CONFIDENTIALITY, EXPORT CONTROL AND DISCLAIMER NOTE:This e-mail and any attachments are solely for the use of the addressee and may contain information that is privileged or confidential. Any disclosure, use or distribution of the information contained herein is prohibited. In the event this e-mail contains technical data within the definition of the International Traffic in Arms Regulations or Export Administration Regulations, it is subject to the export control laws of the U.S.Government. The recipient should check this e-mail and any attachments for the presence of viruses as L-3 does not accept any liability associated with the transmission of this e-mail. If you have received this communication in error, please notify the sender by reply e-mail and immediately delete this message and any attachments.

Effective immediately my new email address is [hidden email]. Please update your records.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: SQLite3 Pros / Cons

Random Coder
On Feb 4, 2017, at 12:47 PM, Drago, William @ CSG - NARDA-MITEQ <[hidden email]> wrote:

>> -----Original Message-----
>> From: sqlite-users [mailto:[hidden email]] On
>> Behalf Of Clyde Eisenbeis
>> Sent: Saturday, February 04, 2017 2:58 PM
>> To: SQLite mailing list <[hidden email]>
>> Subject: [sqlite] SQLite3 Pros / Cons
>>
>> I'm new to SQLite ... started using it a few months ago.  I was unaware of
>> SQLite3 until I joined the SQLite mailing list.
>>
>> What are the pros / cons of SQLite3?
>>
>> If I switched from "using System.Data.SQLite" to SQLite3, are all of the
>> functions in a .dll I could download and use?
>
> You're probably better off sticking with System.Data.SQLite because it will be easier to make your code compatible with other databases. It's also simpler in my opinion than using the SQLite

And in case it's not obvious: System.Data.Sqlite _is_ sqlite3
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: SQLite3 Pros / Cons

Joe Mistachkin-3

Random Coder wrote:
>
> And in case it's not obvious: System.Data.Sqlite _is_ sqlite3
>

To clarify and expand upon the above reply:

System.Data.SQLite is an ADO.NET based managed wrapper around the
SQLite.

It includes the SQLite core library, compiled with a few extra
options and loadable extensions, and some extra code to help it
integrate better with the .NET Framework.  The file name for the
native portions of System.Data.SQLite (also known as the "interop
assembly") is typically "SQLite.Interop.dll" (i.e. this file name
is used instead of "sqlite3.dll" by the P/Invoke integration).

--
Joe Mistachkin @ https://urn.to/r/mistachkin

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Transactions

Michele Pradella
In reply to this post by Clyde Eisenbeis
Hi all, I have a question about transactions and SQLite:

Do you think transactions are useful only when you have to do a sequence
of statements that depends on each other and you need a way to rollback
all statements if something goes wrong? or you can use transactions even
with not interdependent statements for performance reason? and if yes do
you think there's a trade-off about the number of query number in each
transaction?

I'm think about 1000 INSERT query to execute, is transaction works
better because you do not have to change index at any insert but just
one time on commit?
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Transactions

Clemens Ladisch
Michele Pradella wrote:
> I have a question about transactions and SQLite:

http://www.sqlite.org/faq.html#q19


Regards,
Clemens
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: SQLite3 Pros / Cons

Clyde Eisenbeis
In reply to this post by Joe Mistachkin-3
This is good information!

I posted "[sqlite] Retrieve INTEGER PRIMARY KEY" a few days ago.  The
only solution proposed appears to use sqlite3.

On Sat, Feb 4, 2017 at 5:34 PM, Joe Mistachkin <[hidden email]> wrote:

>
> Random Coder wrote:
>>
>> And in case it's not obvious: System.Data.Sqlite _is_ sqlite3
>>
>
> To clarify and expand upon the above reply:
>
> System.Data.SQLite is an ADO.NET based managed wrapper around the
> SQLite.
>
> It includes the SQLite core library, compiled with a few extra
> options and loadable extensions, and some extra code to help it
> integrate better with the .NET Framework.  The file name for the
> native portions of System.Data.SQLite (also known as the "interop
> assembly") is typically "SQLite.Interop.dll" (i.e. this file name
> is used instead of "sqlite3.dll" by the P/Invoke integration).
>
> --
> Joe Mistachkin @ https://urn.to/r/mistachkin
>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: SQLite3 Pros / Cons

Jens Alfke-2

> On Feb 5, 2017, at 5:27 AM, Clyde Eisenbeis <[hidden email]> wrote:
>
> I posted "[sqlite] Retrieve INTEGER PRIMARY KEY" a few days ago.  The
> only solution proposed appears to use sqlite3.

I think you’re confusing sqlite3 the library with its C API.

You’re _already_ using the sqlite3 library, in the form of a .NET library that wraps around it providing a C# API.
What you’re saying here is that the solution for your problem requires calling the C API, because there’s no C# API equivalent to it.
I don’t know if that’s true or not … but it’s going to be easier to discuss the issue if you use terminology that makes sense to us.

—Jens
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Transactions

Hick Gunter
In reply to this post by Michele Pradella
Yes, putting a large number of inserts that affect the same table(s) into ona bulk transaction can be a huge speedup, because the operations can take place in memory without having to reach the disk surface until commit time.

The optimal number of inserts/transaction depends on your hardware setup and who else needs access to CPU and I/O resources.

-----Ursprüngliche Nachricht-----
Von: sqlite-users [mailto:[hidden email]] Im Auftrag von Michele Pradella
Gesendet: Sonntag, 05. Februar 2017 09:42
An: SQLite mailing list <[hidden email]>
Betreff: [sqlite] Transactions

Hi all, I have a question about transactions and SQLite:

Do you think transactions are useful only when you have to do a sequence of statements that depends on each other and you need a way to rollback all statements if something goes wrong? or you can use transactions even with not interdependent statements for performance reason? and if yes do you think there's a trade-off about the number of query number in each transaction?

I'm think about 1000 INSERT query to execute, is transaction works better because you do not have to change index at any insert but just one time on commit?
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


___________________________________________
 Gunter Hick
Software Engineer
Scientific Games International GmbH
FN 157284 a, HG Wien
Klitschgasse 2-4, A-1130 Vienna, Austria
Tel: +43 1 80100 0
E-Mail: [hidden email]

This communication (including any attachments) is intended for the use of the intended recipient(s) only and may contain information that is confidential, privileged or legally protected. Any unauthorized use or dissemination of this communication is strictly prohibited. If you have received this communication in error, please immediately notify the sender by return e-mail message and delete all copies of the original communication. Thank you for your cooperation.


_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Transactions

Jens Alfke-2

> On Feb 6, 2017, at 12:07 AM, Hick Gunter <[hidden email]> wrote:
>
> The optimal number of inserts/transaction depends on your hardware setup and who else needs access to CPU and I/O resources.

Too many transactions can definitely be a problem! It depends on the OS, but the filesystem flush at the end of the commit can cause the hardware disk controller to block for “a long time” (tens or hundreds of ms) while it writes all the blocks from its internal cache to the physical storage medium. This can be bad for real-time threads that are dependent on disk I/O.

Ten years ago, back when I worked at Apple and was first using SQLite, I was too eager about running transactions. In some cases there’d be multiple events in a second that triggered a database write in a transaction; when this happened down in my humble process, it could cause iTunes playback to stutter and video capture to lose frames. Fortunately this was caught early on by internal testers, and I tweaked my insertion code to batch up changes into larger more widely-spaced transactions before release.

[Disclaimer: Some of this may be not be true anymore. SSDs have different performance characteristics than hard disks, and OS kernels have advanced. But it’s still true that achieving durability is expensive and has trade-offs.]

—Jens
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Transactions

Nathan Bossett
In reply to this post by Michele Pradella
On Sun, Feb 05, 2017 at 09:41:48AM +0100, Michele Pradella wrote:

> Do you think transactions are useful only when you have to do a sequence
> of statements that depends on each other and you need a way to rollback
> all statements if something goes wrong? or you can use transactions even
> with not interdependent statements for performance reason? and if yes do
> you think there's a trade-off about the number of query number in each
> transaction?
>
> I'm think about 1000 INSERT query to execute, is transaction works
> better because you do not have to change index at any insert but just
> one time on commit?

Anecdotally, I can say that I've experimented and see huge speedups in
building up a database on both HDD's with a few tens of megabytes of cache
and on SSD's by batching up individual INSERTs and UPDATEs into groups
through a transaction.

I've experimented with batching various numbers of INSERTS when
building up a new database from another local data source and wound
up with 100 or 1000, which may not be optimal but was fast enough.

-Nathan
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Transactions

James K. Lowden
In reply to this post by Jens Alfke-2
On Mon, 6 Feb 2017 09:38:20 -0800
Jens Alfke <[hidden email]> wrote:

> In some cases there?d be multiple events in a second that triggered a
> database write in a transaction; when this happened down in my humble
> process, it could cause iTunes playback to stutter and video capture
> to lose frames.

You should turn that into a war story for "Coders at Work" or
something.  It's fascinating, and emblematic of our times, that
something like iTunes had (or has) DBMS interaction amidst low-level
operations like capture and playback.  

--jkl
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Transactions

Jens Alfke-2

> On Feb 6, 2017, at 11:08 AM, James K. Lowden <[hidden email]> wrote:
>
> It's fascinating, and emblematic of our times, that
> something like iTunes had (or has) DBMS interaction amidst low-level
> operations like capture and playback.  

Oh, it didn’t use a database! It was just streaming audio data from the filesystem. (And Final Cut was just streaming video frames to the filesystem.)

My point is that the disk-controller flush invoked by SQLite’s commit caused the entire filesystem to become unavailable for tens-to-hundreds of milliseconds at a time, and when I started doing that multiple times a second, it would sometimes starve iTunes’ audio threads of data, causing dropouts.

—Jens
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Loading...