Re: [sqlite] Importing from single-insert-statement SQL dump is 61 times slower than importing from SQL dump with one statement per row

On Tuesday, 30 May, 2017 10:33, R Smith said: > Keith, I think the OP meant he inserted the values using one single > statement, not one single transaction, as in he did one ginormous INSERT > INTO t(v1, v2...) VALUES (1, 2),(2,3),(..),,(1, > 297829872); -

Re: [sqlite] Does prepare do arithmetic?

On 5/30/17, James K. Lowden wrote: > > I didn't know sqlite3_column_text converted the result. I thought the > result was constant, and the function returned a string > representation of it. > It might be a good optimization to change that, so that sqlite3_column_text

Re: [sqlite] Does prepare do arithmetic?

On Mon, 29 May 2017 16:18:17 +0200 R Smith wrote: > Actually what Richard is saying is that you SHOULD do the arithmetic > yourself when you can, because you can boldly make assumptions about > the code that you write, but he, or the SQLite engine to be precise, > cannot be

Re: [sqlite] SELECT WHERE with RTREE and second condition slow

> Then what is "FROM Pois_bb, Pois WHERE...Pois_bb.Id = Pois.Id"? > That's joining two tables together. This is just because of the rtree, which is in Pois_bb (http://www.sqlite.org/rtree.html), has nothing to do with the second condition "styleid IN .." Am 30.05.2017 um 18:29 schrieb

Re: [sqlite] SELECT WHERE with RTREE and second condition slow

Thomas Flemming Tue, 30 May 2017 09:43:15 -0700 >> Try putting a "+" symbol before "styleid". Like this: >> >> AND +styleid IN (1351,1362,1371,1374,1376,1542,1595,1597,1643,1762) > THATS IT !! :-))) > > 50ms with +, and 15000ms without the + > > How is that possible? Hello, best

Re: [sqlite] SELECT WHERE with RTREE and second condition slow

If you scroll down in my previous reply I put the explain query plan outputs in with the queries. Guess I should have mentioned that. (Re-copied them below) It was using the index on StyleId, thinking that was going to be faster. What Dr Hipp suggested in adding the unary + operator does is

Re: [sqlite] SELECT WHERE with RTREE and second condition slow

> Try putting a "+" symbol before "styleid". Like this: > > AND +styleid IN (1351,1362,1371,1374,1376,1542,1595,1597,1643,1762) THATS IT !! :-))) 50ms with +, and 15000ms without the + How is that possible? Am 30.05.2017 um 17:36 schrieb Richard Hipp: On 5/27/17, Thomas

Re: [sqlite] SELECT WHERE with RTREE and second condition slow

On 5/27/17, Thomas Flemming wrote: > Hi, > > I have a table Pois with points of interest (geogr. coordinate, label, > styleid) where I do regional querys using a rtree-index: > > SELECT Pois.* FROM Pois_bb, Pois WHERE y0 < -14.8600 AND y1 > -15.12862 >

Re: [sqlite] Importing from single-insert-statement SQL dump is 61 times slower than importing from SQL dump with one statement per row

Keith, I think the OP meant he inserted the values using one single statement, not one single transaction, as in he did one ginormous INSERT INTO t(v1, v2...) VALUES (1, 2),(2,3),(..),,(1, 297829872); - 180MB or so worth... Probably lots of data in few rows, because he is not

Re: [sqlite] Importing from single-insert-statement SQL dump is 61 times slower than importing from SQL dump with one statement per row

On 2017/05/30 2:01 PM, Hick Gunter wrote: If you stuff all 18MB of your data into a single INSERT, then SQlite will need to generate a single program that contains all 18MB of your data (plus code to build rows aout of that). This will put a heavy strain on memory requirements and offset any

Re: [sqlite] SELECT WHERE with RTREE and second condition slow

but the query is not in two tables. its two select-where in the same table. Am 30.05.2017 um 17:18 schrieb Hick Gunter: Just like any other join, but with tables in the desired order and the word CROSS added SELECT ... FROM CROSS JOIN ... -Ursprüngliche Nachricht- Von: sqlite-users

Re: [sqlite] Importing from single-insert-statement SQL dump is 61 times slower than importing from SQL dump with one statement per row

I find quite the opposite. Using a DUMP file to create a database where the first test uses the standard dump (which does the load in a single transaction) is enormously faster than the second one, where the BEGIN TRANSACTION and COMMIT have been commented out, and thus each insert is

Re: [sqlite] SELECT WHERE with RTREE and second condition slow

Just like any other join, but with tables in the desired order and the word CROSS added SELECT ... FROM CROSS JOIN ... -Ursprüngliche Nachricht- Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im Auftrag von Thomas Flemming Gesendet: Dienstag, 30. Mai 2017

Re: [sqlite] SELECT WHERE with RTREE and second condition slow

force it to go the way you want by using "cross join" to force the ordering of How would such "cross join" statemant look like? Am 30.05.2017 um 16:38 schrieb David Raymond: It looks like it thinks that using the index on StyleId is going to be the most beneficial, as opposed to the rtree

Re: [sqlite] SELECT WHERE with RTREE and second condition slow

It looks like it thinks that using the index on StyleId is going to be the most beneficial, as opposed to the rtree index. How it compares a normal index's stat1 to a virtual table's stat1 I don't know. In this case you can force it to go the way you want by using "cross join" to force the

Re: [sqlite] SELECT WHERE with RTREE and second condition slow

On May 30, 2017 10:07:45 AM EDT, Thomas Flemming wrote: >Style.Id doesn't need to be LONG, you're right. I changed it but it >doesn't >make a difference. >Pois.Id need to be LONG because the source for this column is really >containing 64-bit values Integers in SQLite are of

Re: [sqlite] SELECT WHERE with RTREE and second condition slow

-Original Message- From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Thomas Flemming Sent: Tuesday, May 30, 2017 8:08 AM To: sqlite-users@mailinglists.sqlite.org Subject: Re: [sqlite] SELECT WHERE with RTREE and second condition slow > > Do you know

Re: [sqlite] SELECT WHERE with RTREE and second condition slow

INTEGER and LONG can both store 64 bits in SQlite. The difference is that "INTEGER PRIMARY KEY" makes the column an alias for the rowid, whereas "LONG PRIMARY KEY" defines a second, possibly redundant index. -Ursprüngliche Nachricht- Von: sqlite-users

Re: [sqlite] SELECT WHERE with RTREE and second condition slow

> Do you know which SQLite version is being used by SQLite Expert > Professional 3.5? sqlite 3.10.0 I tried SQLite Expert Professional 4, using sqlite 3.18.0, but its the same slow. Style.Id doesn't need to be LONG, you're right. I changed it but it doesn't make a difference. Pois.Id need to

Re: [sqlite] SELECT WHERE with RTREE and second condition slow

Am Mon, 29 May 2017 14:27:56 +0100 schrieb Thomas Flemming: > Ok, here is a sample to try these queries: > > http://files.qvgps.com/0-tom-public/Geonames_World_2017.zip > (825mb, 12 mio records) Just a few quick observations ... First, I would replace all column declarations like LONG PRIMARY

Re: [sqlite] Importing from single-insert-statement SQL dump is 61 times slower than importing from SQL dump with one statement per row

If you stuff all 18MB of your data into a single INSERT, then SQlite will need to generate a single program that contains all 18MB of your data (plus code to build rows aout of that). This will put a heavy strain on memory requirements and offset any speed you hope to gain. The SOP is to put

Re: [sqlite] Sqlite Linux support

On 5/29/17, Radhakant Kumar wrote: > if I get same version on Linux > as Precompiled binaries it might help me. That is what the "Precompiled Binaries For Linux" on the https://sqlite.org/download.html page is for. -- D. Richard Hipp d...@sqlite.org

Re: [sqlite] 24hrs Formate Issue

On 5/30/17, rajesh mekala wrote: > > Hi all, > > > This is Rajesh. I am using SQLITE db. Actually in my database i have > one column.i want to convert my column value am/pm to 24hrs formate. > > > Ex: > > Step 1: I have one column. >Column name: Ts >

Re: [sqlite] Sqlite Linux support

Radhakant Kumar wrote: > On windows i got 3.18.0 and on Linux 3.6.20. Which Linux distribution are you using? Regards, Clemens ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org

Re: [sqlite] Importing from single-insert-statement SQL dump is 61 times slower than importing from SQL dump with one statement per row

Sarge Borsch wrote: > time xzdec something.sql.xz | sqlite3 something.db This measures only xzdec; it does not catch anything that sqlite3 does after xzdec has finished and closed the pipe. > IMO sqlite needs some optimisation for this case when there’s a huge > INSERT statement, because the

[sqlite] 24hrs Formate Issue

Hi all, This is Rajesh. I am using SQLITE db. Actually in my database i have one column.i want to convert my column value am/pm to 24hrs formate. Ex: Step 1: I have one column. Column name: Ts Column Have this value :2016-09-19 08:56:00 PM. My Query:

[sqlite] Sqlite Linux support

Hi , I am creating an application for windows and Linux . For windows system i have downloaded Precompiled binaries for command line shell and its serving my purpose. same i have done for Linux but it was not able to handle the Data validation(Joins and group) on same amount of data as in

[sqlite] Importing from single-insert-statement SQL dump is 61 times slower than importing from SQL dump with one statement per row

I compared speed of importing (into an empty SQLite DB) from 2 kinds of SQL dumps. Data is exactly the same in both cases, and xz-compressed size of SQL dump is near 18MB in both cases. First SQL dump has single big INSERT statement in single transaction. Second SQL dump has one INSERT statement