Re: [sqlite] Bulk load strategy

> -Original Message- > From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On > Behalf Of Gerry Snyder > Sent: Wednesday, May 17, 2017 9:14 AM > To: SQLite mailing list > Subject: Re: [sqlite] Bulk load strategy > > If the

Re: [sqlite] Bulk load strategy

On 17 May 2017, at 4:06pm, Joseph L. Casale wrote: > CREATE TABLE AdObject ( >IdINTEGER PRIMARY KEY NOT NULL, >DistinguishedName TEXTNOT NULL COLLATE NOCASE, >SamAccountNameTEXTCOLLATE NOCASE > ); > CREATE UNIQUE INDEX

Re: [sqlite] Bulk load strategy

Can you send ore details about your data and the updates and indexes you are using? On 5/17/17, Joseph L. Casale wrote: >> From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] >> On >> Behalf Of Clemens Ladisch >> Sent: Wednesday, May 17, 2017 6:04

Re: [sqlite] Bulk load strategy

On Wed, May 17, 2017 at 3:52 AM, Joseph L. Casale wrote: > I am trying to bulk load about a million records each with ~20 related > records > into two tables. I am using WAL journal mode, synchronous is off and > temp_store > is memory. The source data is static and

Re: [sqlite] SAVEPOINT with multiple databases

One other minor thing of note is that attaching and detaching can only happen outside of a transaction. So you can't add or remove attached databases in the middle of a transaction, and transaction commit/rollback, or savepoint release/rollback will never leave you with a different set of

Re: [sqlite] Bulk load strategy

> From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On > Behalf Of Richard Hipp > Sent: Wednesday, May 17, 2017 8:54 AM > To: SQLite mailing list > Subject: Re: [sqlite] Bulk load strategy > > Can you send ore details about your data

Re: [sqlite] Bulk load strategy

The unique index on DistinguishedName though is what gets used for that sub query of the insert, so most definitely keep that one index for the whole load. (The others can be left out until the end though) Otherwise, as was mentioned, journal_mode = off, synchronous = off, a large cache_size,

Re: [sqlite] SAVEPOINT with multiple databases

Thank you, David. Now it totally makes sense to me. I realize this is SQL not SQLite question. Thank you, Roman From: sqlite-users [sqlite-users-boun...@mailinglists.sqlite.org] on behalf of David Raymond [david.raym...@tomtom.com] Sent: Wednesday,

Re: [sqlite] Bulk load strategy

> -Original Message- > From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On > Behalf Of Simon Slavin > Sent: Wednesday, May 17, 2017 10:05 AM > To: SQLite mailing list > Subject: Re: [sqlite] Bulk load strategy > I’m

Re: [sqlite] Bulk load strategy

Am 17.05.2017 um 19:08 schrieb David Raymond: The unique index on DistinguishedName though is what gets used for that sub query of the insert, so most definitely keep that one index for the whole load. (The others can be left out until the end though) I once had a similar scenario, and

Re: [sqlite] Bulk load strategy

On 17 May 2017, at 7:07pm, Joseph L. Casale wrote: > So I have one query which if I expect if I encounter will be painful: > > UPDATE AdAttribute > SET Value = @NewValue > WHERE Type = @Type > AND Value = @Value; > > I may pass member or memberOf to

Re: [sqlite] Bulk load strategy

On 17 May 2017, at 5:05pm, Simon Slavin wrote: > Fastest way to do bulk inserts would be to delete all the indexes which don’t > play any part in identifying duplicates, then do the inserting, then remake > the indexes. I forgot: once you’ve remade the indexes run

Re: [sqlite] Bulk load strategy

The key point I was thinking of for keeping that index was that it was perfect for speeding up the foreign key check / subquery for this part. I wasn't thinking at all in terms of unique enforcement. INSERT OR IGNORE INTO AdAttribute (Type, Value, AdObjectId) VALUES (@Type,

Re: [sqlite] Bulk load strategy

> -Original Message- > From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On > Behalf Of Simon Slavin > Sent: Wednesday, May 17, 2017 2:02 PM > To: SQLite mailing list > Subject: Re: [sqlite] Bulk load strategy > I forgot: once

Re: [sqlite] SQLite in memory

From the SQLite shell (CLI), have you tried dot commands ".backup" to file and ".restore" to a new :memory: DB? That assumes a few things like access to the filesystem and sufficient user memory quota to hold the disk version of the DB. Does that work? The shell dot commands and their syntax is

[sqlite] SQLite in memory

Hi, I'm trying to assess if the performance of my application is dependent on disk access from sqlite. To rule this out I wanted to make sure that the SQLite DB is completely accessed from memory and there are no disk accesses. Is it possible to obtain this effect by using pragmas such as

Re: [sqlite] Request for ISO Week in strftime()

Sorry, re-sending to list. Point of Clarification: The ISO Week begins as day 1 on Monday and ends as day 7 on Sunday, hump day (colloq.) is Thursday. There may be other repercussions in terms of week counts if this has not been implemented correctly. I haven't checked, I do not use this

Re: [sqlite] sqlite3_create_function xFinal argument called when there's an error?

Nelson, Erik - 2 wrote: > for aggregate functions, is xFinal called if there's an error? Yes; it's always called when SQLite cleans up the context. Regards, Clemens ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org

[sqlite] Request for ISO Week in strftime()

The current '%W' week substitution appears to be US-specific. I would like to make a feature request for a '%V' (or similar) substitution that inserts the ISO-8601 week number. -- Mark Lawrence ___ sqlite-users mailing list

Re: [sqlite] Bulk load strategy

On 5/17/17, Joseph L. Casale wrote: > I am trying to bulk load about a million records each with ~20 related > records > into two tables. I am using WAL journal mode, synchronous is off and > temp_store > is memory. The source data is static and the database will only

Re: [sqlite] Bulk load strategy

Joseph L. Casale wrote: > I am deferring index creation to after the load. > The load proceeds along quickly to about 150k records where I encounter > statements > which perform modifications to previous entries. Without an index, searching for a previous entry is likely to involve a scan

Re: [sqlite] Function sqlite3_prepare_v2 of C API does not work

Thanks for your responses, the original reporters confirmed that it is possible to insert row in a table with parent colums when making them UNIQUE (https://github.com/sqlitebrowser/sqlitebrowser/issues/463), so my issue (https://github.com/sqlitebrowser/sqlitebrowser/issues/901) is resolved too.

[sqlite] Bulk load strategy

I am trying to bulk load about a million records each with ~20 related records into two tables. I am using WAL journal mode, synchronous is off and temp_store is memory. The source data is static and the database will only be used as a means to generate reporting and is not vital. I am deferring

Re: [sqlite] sqlite3_create_function xFinal argument called when there's an error?

Nelson, Erik - 2 wrote: > should any result be set in xFinal? Like sqlite3_result_error? Or the > previously-returned xStep error is sufficient? The sqlite3_result_xxx() documentation documents what happens when you call it multiple times (i.e., the later call overrides the earlier value).

Re: [sqlite] sqlite3_create_function xFinal argument called when there's an error?

> From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On > Behalf Of Clemens Ladisch > Sent: Wednesday, May 17, 2017 2:36 AM > To: sqlite-users@mailinglists.sqlite.org > Subject: Re: [sqlite] sqlite3_create_function xFinal argument called when > there's an error? > > Nelson,

Re: [sqlite] Bulk load strategy

> From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On > Behalf Of Clemens Ladisch > Sent: Wednesday, May 17, 2017 6:04 AM > To: sqlite-users@mailinglists.sqlite.org > Subject: Re: [sqlite] Bulk load strategy > > Without an index, searching for a previous entry is likely to