Re: [sqlite] Patch Etiquette

Ziemowit Laski wrote: > [...] > I DO care about being acknowledged as the author of the patch, however. But you are not the author. You reported the problem, but the actual patches that got applied (http://www.sqlite.org/cgi/src/info/50e60cb44fd3687d,

Re: [sqlite] Bug using aggregate functions

Radovan Antloga wrote: > select min(A) > from TEST > where B is null > and A > 3; > > if you replace min(A) with * you get empty result set as expected > but with min or max or avg you get one record This is just how aggregate functions in SQL work. When you're using GROUP BY, you get exactly

Re: [sqlite] Bulk Insert in Sqlite3

On 7 February 2017 at 15:11, Simon Slavin wrote: > > On 7 Feb 2017, at 6:56am, Niti Agarwal wrote: > > > Thanks for your reply. The length matters as I am appending 100 rows at a > > time in a sql statement. It is making very fast as compared to

Re: [sqlite] Bulk Insert in Sqlite3

On 7 Feb 2017, at 6:56am, Niti Agarwal wrote: > Thanks for your reply. The length matters as I am appending 100 rows at a > time in a sql statement. It is making very fast as compared to single sql > insert in For loop. > Copied the code below for reference. Here the

Re: [sqlite] Bulk Insert in Sqlite3

Thanks for your reply. The length matters as I am appending 100 rows at a time in a sql statement. It is making very fast as compared to single sql insert in For loop. Copied the code below for reference. Here the list size is 100 Any better way to do this? Like I read about *bind*...not sure how

Re: [sqlite] Why does a query run 50x slower across a network?

There are several reasons why networks are much slower than local disks (think of SATA vs. Ethernet, SATA Bus vs. Network latency, no client side caching etc.). This is especially true for random access patterns like those SQLite uses. So to minimize file access, (like already suggested by

Re: [sqlite] Bulk Insert in Sqlite3

Hi Niti, There's on need to build a giant SQL string; a transaction can span multiple statements. To bind in golang place a ? within your SQL query and provide the values as additional arguments to the Exec/Query function. eg, after using db.Begin() to create a transaction tx, err :=

Re: [sqlite] Bulk Insert in Sqlite3

On 7 Feb 2017, at 5:36am, Niti Agarwal wrote: > Need to insert close to 10 Million records to sqlite3 in around 30 mins. This number of records requires so much space the temporary data will not fit inside a cache. Consider using a counter so that the transaction is

[sqlite] Bulk Insert in Sqlite3

Hi, We are using Sqlite3 with Golang to do bulk insert. Need to insert close to 10 Million records to sqlite3 in around 30 mins. Currently I am saving 100 Records under one transaction with below settings: PRAGMA synchronous = NORMAL; PRAGMA journal_mode = WAL; PRAGMA

Re: [sqlite] SQLITE_ENABLE_UPDATE_DELETE_LIMIT (Was: Patch Etiquette)

Hello Jan + Maintainers, Here is my approach to the SQLITE_ENABLE_UPDATE_DELETE_LIMIT problem. [If the attachment does not arrive intact, please let me know.] It is different from Jan's in that it operates strictly on the Makefile level. The idea is simple: When compiling parse.y with

Re: [sqlite] Patch Etiquette

Hi Jens, Yes, I did notice the commit that mentions my name, and am glad for the attribution. What I was getting at, though, is that such attributions should be part of SQLite policy and should happen automatically and every time. To your question, I don't particularly care if I have write

[sqlite] SQLITE_ENABLE_UPDATE_DELETE_LIMIT (Was: Patch Etiquette)

Hi Jan, Thank you for the link. I'll take a look. --Zem [P.S. It appears I cannot reply to the whole list directly from the https://www.mail-archive.com/sqlite-users webpage. Is this because I'm not (yet) a members of the list?] ___ sqlite-users

[sqlite] Bug using aggregate functions

Hi, I discovered a bug using sqlite 3.15.2. It is simple to reproduce. Try this SQL-s: CREATE TABLE TEST( A integer primary key, B integer); -- insert some test data insert into TEST (A,B) values (1, 1); insert into TEST (A,B) values (2, null); -- check count(*) select count(*) from TEST

Re: [sqlite] Exposing compile-time limits?

> > We do strive to contain unnecessary growth in the number of > interfaces. Remember that we are promising to support every interface > until 2050. That argues for keeping the number of APIs to a minimum. > Sounds good to me. Thanks for clarifying!

Re: [sqlite] Retrieve INTEGER PRIMARY KEY

Why do you say 'there is no equivalence' ? Have you read the link I posted in the reply to your question nearly 3 days ago? Last_insert_rowid() https://www.sqlite.org/c3ref/last_insert_rowid.html select @@identity and 'select last_insert_rowid()' perform the same action - retrieving the last

Re: [sqlite] Why does a query run 50x slower across a network?

Besides my own experience, although it is a bit older and I couldn't find it in this email list, It is still in my archive, so there you have (copy-paste) of related email of 2014-09-08 in this list, send by jose isaias cabrera <...@cinops.xerox.com> in response of a query: Re: [sqlite] Does

Re: [sqlite] Bug using aggregate functions

This is the way SQL (not only SQLite) interprets these queries. Basically you ask information about an empty set: - count(*) = 0, as expected - min(A) is undefined, which is translated in SQL by 'null' value; since 'null' is not 'nothing', you get a 1-line result comprising 'null' ! Regards

Re: [sqlite] Transactions

> On Feb 6, 2017, at 11:08 AM, James K. Lowden 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

Re: [sqlite] Transactions

On Mon, 6 Feb 2017 09:38:20 -0800 Jens Alfke 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

Re: [sqlite] Why does a query run 50x slower across a network?

On Mon, 06 Feb 2017 13:12:22 +0100 a...@zator.com wrote: > In respect to the Windows environment I've appreciated that the use > of UNC convention over a network (LAN) behaves much slower that > "mapping" the logical unit as a drive letter D, E, .. Z in the local > host. That's bizarre. By

Re: [sqlite] Transactions

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

Re: [sqlite] Patch Etiquette

> On Feb 5, 2017, at 12:14 PM, Ziemowit Laski wrote: > > HOWEVER, one thing bothers me: You did not acknowledge my authorship of it. > AFAICT, you NEVER seem to acknowledge third party contributions. Clearly, > I'm not user 'drh’. Like with other open-source projects, I

Re: [sqlite] Bug using aggregate functions

Sorry to post this to quick. I just checked this with Firebird and I get same result. I did not expect that. Sorry once again !! Radovan Antloga je 06.02.2017 ob 18:34 napisal: Hi, I discovered a bug using sqlite 3.15.2. It is simple to reproduce. Try this SQL-s: CREATE TABLE TEST( A

Re: [sqlite] Transactions

> On Feb 6, 2017, at 12:07 AM, Hick Gunter 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

[sqlite] Bug using aggregate functions

Hi, I discovered a bug using sqlite 3.15.2. It is simple to reproduce. Try this SQL-s: CREATE TABLE TEST( A integer primary key, B integer); -- insert some test data insert into TEST (A,B) values (1, 1); insert into TEST (A,B) values (2, null); -- check count(*) select count(*) from TEST

Re: [sqlite] Too many emails.

On 2/6/17, Taylor Matson wrote: > > Hello could you please take me off the mailing list? Thanks! > You are not on the mailing list, at least not under the email address shown above. Perhaps you have subscribed using a different email address which is then forwarded to

[sqlite] Too many emails.

Hello could you please take me off the mailing list? Thanks! Sent from my Verizon, Samsung Galaxy smartphone ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Retrieve INTEGER PRIMARY KEY

I'm writing WPF / C# code. This is part of a function that is called whenever the user enters data that is saved. The primary key is used whenever the user changes that record ... to ensure the correct data is changed. On Mon, Feb 6, 2017 at 10:11 AM, Simon Slavin wrote:

Re: [sqlite] Retrieve INTEGER PRIMARY KEY

On 6 Feb 2017, at 1:30pm, Clyde Eisenbeis wrote: > What is the correct nomenclature for using last_insert_rowid() for > SQLite? For OLE DB I've used ... oledbCmd.CommandText = "Select > @@Identity" ... int iKeyID = (int)oledbCmd.ExecuteScalar(). last_insert_row() is an SQL

Re: [sqlite] Why does a query run 50x slower across a network?

On Mon, Feb 6, 2017 at 4:27 PM, Bart Smissaert wrote: > Would a "server app" be an option, so run SQLite on the remote location and > return the dataset? > Didn't sound like it was, from David's description, but in case I'm guessing wrong, then

Re: [sqlite] Why does a query run 50x slower across a network?

Would a "server app" be an option, so run SQLite on the remote location and return the dataset? RBS On Mon, Feb 6, 2017 at 10:28 AM, dandl wrote: > We have an application we converted from Access to Sqlite. Mostly it's > been a great success, but we have two queries that runs

Re: [sqlite] Retrieve INTEGER PRIMARY KEY

What is the correct nomenclature for using last_insert_rowid() for SQLite? For OLE DB I've used ... oledbCmd.CommandText = "Select @@Identity" ... int iKeyID = (int)oledbCmd.ExecuteScalar(). On Mon, Feb 6, 2017 at 7:24 AM, Clemens Ladisch wrote: > Clyde Eisenbeis wrote: >>

Re: [sqlite] Retrieve INTEGER PRIMARY KEY

There is no record locking in SQLite -Ursprüngliche Nachricht- Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im Auftrag von Clyde Eisenbeis Gesendet: Montag, 06. Februar 2017 14:19 An: SQLite mailing list Betreff: Re:

Re: [sqlite] Retrieve INTEGER PRIMARY KEY

Clyde Eisenbeis wrote: > Perhaps there is no equivalence to OLE DB ... oledbCmd.CommandText = > "Select @@Identity" ... int iKeyID = (int)oledbCmd.ExecuteScalar()? This has *nothing* to do with OLE DB; @@Identity is an SQL Server specific thing. SQLite's is last_insert_rowid(), and both have

Re: [sqlite] Retrieve INTEGER PRIMARY KEY

In this case, there is only one record added ... no one else has access to this database. In the past, I have locked a record, so no one else can access that record while it is being modified. Is locking an option in SQLite? Perhaps there is no equivalence to OLE DB ... oledbCmd.CommandText =

Re: [sqlite] Exposing compile-time limits?

On 2/5/17, Richard Newman wrote: > Hello folks, > > `sqlite3_limit` allows callers to discover the run-time value of limits > such as `SQLITE_LIMIT_VARIABLE_NUMBER`. > > Callers can also *set* each limit, so long as the value is smaller than a > compile-time define, in this

Re: [sqlite] Why does a query run 50x slower across a network?

In respect to the Windows environment I've appreciated that the use of UNC convention over a network (LAN) behaves much slower that "mapping" the logical unit as a drive letter D, E, .. Z in the local host. Altought unfortunately this doesn't seem very handy in all situations. -- Adolfo. > >

Re: [sqlite] Exposing compile-time limits?

Richard Newman wrote: > `sqlite3_limit` allows callers to discover the run-time value of limits > such as `SQLITE_LIMIT_VARIABLE_NUMBER`. > > Callers can also *set* each limit, so long as the value is smaller than a > compile-time define, in this case `SQLITE_MAX_VARIABLE_NUMBER`. > > But callers

Re: [sqlite] Retrieve INTEGER PRIMARY KEY

> Le 5 févr. 2017 à 18:26, Clyde Eisenbeis a écrit : > > To clarify further, the SQLite function is attached. Attachments to this mailing list are stripped. Better inline in the email itself if short or send privately to the recipient. :) -- Best Regards, Meilleures

Re: [sqlite] Patch Etiquette

2017-02-05 21:14 GMT+01:00 Ziemowit Laski: > I have another patch in the works which will make > SQLITE_ENABLE_UPDATE_DELETE_LIMIT directly accessible from the amalgamation, > but am reluctant to submit it. Well, I submitted such a patch already, some half year ago. You can find the full thread

Re: [sqlite] Why does a query run 50x slower across a network?

On 6 Feb 2017, at 10:28am, dandl wrote: > I'm hoping we've done something really dumb and obvious, but we can't see it. > CREATE INDEX [order_header_type_idx] ON [order_header] ([transaction_type], > [sale_type_id]); Nothing really dumb, but this might help. Create another

[sqlite] Exposing compile-time limits?

Hello folks, `sqlite3_limit` allows callers to discover the run-time value of limits such as `SQLITE_LIMIT_VARIABLE_NUMBER`. Callers can also *set* each limit, so long as the value is smaller than a compile-time define, in this case `SQLITE_MAX_VARIABLE_NUMBER`. But callers have no good way of

[sqlite] Patch Etiquette

Hello SQLite maintainers, I'm glad that my patch below has been incorporated (with some changes) into future releases: [50e60cb4] Modify the ICU extension to use a static initializer, as VC++ complains about a dynamic initialization. Maybe the

Re: [sqlite] Retrieve INTEGER PRIMARY KEY

To clarify further, the SQLite function is attached. Retrieving the primary key is commented (used by System.Data.OleDb function). On Sun, Feb 5, 2017 at 10:40 AM, J Decker wrote: > http://data.sqlite.org/c3ref/last_insert_rowid.html > > there is an api call to get it; or you

Re: [sqlite] Why does a query run 50x slower across a network?

I'm in a different environment (linux with database on nfs share) but found the same behaviour. I came to the conclusion that the latency of network file system operations combined with database fragmentation was largely responsible for the reduced performance. SQLite is very seek heavy, unlike a

[sqlite] Why does a query run 50x slower across a network?

We have an application we converted from Access to Sqlite. Mostly it's been a great success, but we have two queries that runs 50x slower across a gigabit LAN than on a local file system and we don't know why. Performance on Access was perfectly acceptable, and on Sqlite is not and we can't

Re: [sqlite] Transactions

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