Re: [sqlite] Performance Issue on Large Table

I guess is this slowness is because of triggers (most probably) amplified by extra indexes. Lets discuss indexes first: if most of queries uses BibleID as SARG along with Book & Chapter then INX_Verses_BID & INX_Verses_BID_B_C are not required. These are unnecessary adding slowness to write

Re: [sqlite] Performance Issue on Large Table

Am 23.02.2020 um 20:23 schrieb Richard Damon: An amount of 140 tables in such a "BibleVersions.db" is not uncommon and can be managed by SQLite in a good performance. I'm not sure that form of division would be good. One basic rule of database normalization is that you don't break-up data

Re: [sqlite] Performance Issue on Large Table

On 2/23/20 3:06 PM, R.Smith wrote: On 2020/02/23 21:23, Richard Damon wrote: On 2/23/20 8:31 AM, Olaf Schmidt wrote: An amount of 140 tables in such a "BibleVersions.db" is not uncommon and can be managed by SQLite in a good performance. I'm not sure that form of division would be good.

Re: [sqlite] Performance Issue on Large Table

On 23 Feb 2020, at 7:23pm, Richard Damon wrote: > I'm not sure that form of division would be good. One basic rule of database > normalization is that you don't break-up data based on the value of one of > the fields because you can't make the table to lookup data from be taken from > a field

Re: [sqlite] Performance Issue on Large Table

On 2020/02/23 21:23, Richard Damon wrote: On 2/23/20 8:31 AM, Olaf Schmidt wrote: An amount of 140 tables in such a "BibleVersions.db" is not uncommon and can be managed by SQLite in a good performance. I'm not sure that form of division would be good. One basic rule of database

Re: [sqlite] Performance Issue on Large Table

Chip Beaulieu wrote: > I also recently vacuumed the file. The issue really showed up after the FTS5 > was > setup on the table. I suspect it’s got something to do with the triggers more > than > the indexes. I am definitely open to suggestions. I’ve not been able to find > much > info on

Re: [sqlite] Performance Issue on Large Table

On 2/23/20 8:31 AM, Olaf Schmidt wrote: Am 21.02.2020 um 02:24 schrieb Chip Beaulieu: I have a table with 4.5 million records with full text indexing.  > Reads are very fast, but deleting / inserting / updating takes on average about 50 seconds per record. I often do batches of 30,000 deletes

Re: [sqlite] Performance Issue on Large Table

Am 21.02.2020 um 02:24 schrieb Chip Beaulieu: I have a table with 4.5 million records with full text indexing. > Reads are very fast, but deleting / inserting / updating takes on average about 50 seconds per record. I often do batches of 30,000 deletes / inserts at a time. The last batch took

Re: [sqlite] Performance Issue on Large Table

> On Feb 20, 2020, at 5:24 PM, Chip Beaulieu wrote: > > CREATE INDEX `INX_Verses_BID` ON `Verses` (`BibleID`) > CREATE INDEX `INX_Verses_BID_B_C` ON `Verses` (`BibleID`,`Book`,`Chapter`) > CREATE INDEX `INX_Verses_BI_B_C_V` ON `Verses` > (`BibleID`,`Book`,`Chapter`,`VerseNumber`) I believe

Re: [sqlite] Performance Issue on Large Table

On 21-2-2020 02:24, Chip Beaulieu wrote: I have a table with 4.5 million records with full text indexing. Reads are very fast, but deleting / inserting / updating takes on average about 50 seconds per record. I often do batches of 30,000 deletes / inserts at a time. The last batch took 10

[sqlite] Performance Issue on Large Table

I have a table with 4.5 million records with full text indexing. Reads are very fast, but deleting / inserting / updating takes on average about 50 seconds per record. I often do batches of 30,000 deletes / inserts at a time. The last batch took 10 hours to complete. Here are the details:

Re: [sqlite] Performance issue with left join in 3.24 compared with 3.22

Thanks Richard! Changing the inner join to a cross join works as well in that case, though is it enough to always disable the left join optimization ? I have other variants of the query with different/more left joined tables/subqueries, and varying filtering conditions, as the query is

Re: [sqlite] Performance issue with left join in 3.24 compared with 3.22

On 6/26/18, Eric Grange wrote: > I am experiencing a massive performance issue on a query with a left join > in 3.24, now taking 40 seconds, whereas in 3.22 it was just a few > milliseconds. > The problematic query looks like > > select d.key_field, count(*) nb > from low_volume_table

Re: [sqlite] Performance issue with left join in 3.24 compared with 3.22

Also ran a few index to "force" the query plan, but with limited success: - the "indexed by" clause does not result in the optimizer using the index first, it just uses the indexes in the later steps of the query plan. - using "not indexed" still results in the same table scan of

[sqlite] Performance issue with left join in 3.24 compared with 3.22

Hi, I am experiencing a massive performance issue on a query with a left join in 3.24, now taking 40 seconds, whereas in 3.22 it was just a few milliseconds. The problematic query looks like select d.key_field, count(*) nb from low_volume_table b join mid_volume_table c on

Re: [sqlite] Performance issue in different versions

I realized that the amount of memory used for the page cache is different. And I found that is the root cause. Sorry for my careless mistake. Thank you. -- Sent from: http://sqlite.1065341.n5.nabble.com/ ___ sqlite-users mailing list

Re: [sqlite] Performance issue in different versions

Yup, absolutely you are right. I just ran a new test using the same upper bound on the amount of memory used for the page cache, then I found a reasonable result. Thank you, Dan. I did notice the cache_size change before but you made me realize it. Thanks a lot. -- Sent from:

Re: [sqlite] Performance issue in different versions

On 02/01/2018 04:05 PM, Nick wrote: I update sqlite in my Android mobile phone from 3.9.2 to 3.16.2. And I find the default page_size in 3.9.2 is 1024 while in 3.16.2 is 4096 (changed since 3.12.0). I think SQLITE_DEFAULT_PAGE_SIZE has great effect on the performance so I use speedtest1.c to

Re: [sqlite] Performance issue in different versions

On 2/1/18, Nick wrote: > I update sqlite in my Android mobile phone from 3.9.2 to 3.16.2. > > There are many test cases in speedtest1.c and case 270 is a DELETE case > which is the most time-consuming one. > There is a result. (different version + different

Re: [sqlite] Performance issue in different versions

Um, I am a OS application developer and we just upgraded the source code on our developing engine. I am sure I used the same compile-options. SQLITE_SECURE_DELETE is not set. -- Sent from: http://sqlite.1065341.n5.nabble.com/ ___ sqlite-users

Re: [sqlite] Performance issue in different versions

Nick wrote: > I update sqlite in my Android mobile phone from 3.9.2 to 3.16.2. How? Your own copy, or the system one? Did you use the same configuration? Especially SQLITE_SECURE_DELETE? Regards, Clemens ___ sqlite-users mailing list

[sqlite] Performance issue in different versions

I update sqlite in my Android mobile phone from 3.9.2 to 3.16.2. And I find the default page_size in 3.9.2 is 1024 while in 3.16.2 is 4096 (changed since 3.12.0). I think SQLITE_DEFAULT_PAGE_SIZE has great effect on the performance so I use speedtest1.c to test it. There are many test cases in

Re: [sqlite] Performance issue for version 3.9.2 compared with 3.16.2

On 10/17/17, advancenOO wrote: > In order to profile sqlite 3.9.2 and sqlite 3.16.2, I run speedtest1.c on my > mobile phone. > It is weird to find that 3.9.2 is better than 3.16.2, especially in the > following case: > “270, 1 DELETEs, numeric BETWEEN,

[sqlite] Performance issue for version 3.9.2 compared with 3.16.2

In order to profile sqlite 3.9.2 and sqlite 3.16.2, I run speedtest1.c on my mobile phone. It is weird to find that 3.9.2 is better than 3.16.2, especially in the following case: “270, 1 DELETEs, numeric BETWEEN, indexed" 3.9.2 uses 0.7s while 3.16.2 uses 1.8s; My relevant

Re: [sqlite] performance issue on a read only database

Hi all, I tried the sugestion made by Clemens using PRAGMA locking_mode = EXCLUSIVE. This works fine for me. Thanks a lot. 2017-06-13 20:46 GMT+02:00 David Raymond : > "Also please note that SQLite does a 'lazy open'. When you create your > connection to the database

Re: [sqlite] performance issue on a read only database

"Also please note that SQLite does a 'lazy open'. When you create your connection to the database file, SQLite doesn’t actually open the file. Instead the file handling is done the first time SQLite needs the data from the file. So the first SELECT after a new connection is made takes longer

Re: [sqlite] performance issue on a read only database

On 13 Jun 2017, at 10:53am, rv.gauth...@free.fr wrote: > Is there a way (pragma, compile flags, ...) to gain these 14 ms for the first > query ? In SQLite, it is transactions which take all the time. Individual commands such as SELECT are fast. What takes the time is the locking, journal

Re: [sqlite] performance issue on a read only database

On Tue, 13 Jun 2017 11:53:05 +0200 rv.gauth...@free.fr wrote: > Hi all, > > we are using sqlite3 on an embedded application. > The database is opened in read only mode and can't be modified by the > application. > > I noticed that the first SELECT statement after a BEGIN TRANSACTION > takes

Re: [sqlite] performance issue on a read only database

rv.gauth...@free.fr wrote: > I noticed that the first SELECT statement after a BEGIN TRANSACTION takes at > least 14 ms. > All subsequent queries in the same transaction are taking near 0 ms. > > If I omit the BEGIN TRANSACTION, all queries are taking at least 14 ms. Because then you get an

[sqlite] performance issue on a read only database

Hi all, we are using sqlite3 on an embedded application. The database is opened in read only mode and can't be modified by the application. I noticed that the first SELECT statement after a BEGIN TRANSACTION takes at least 14 ms. All subsequent queries in the same transaction are taking

Re: [sqlite] performance issue through Dll upgrade

On 1 Dec 2016, at 7:24am, Stephan Stauber wrote: > SQLite 3.8.5: to INSERT 380.000 records into a in > inMemory Database it takes 10 seconds > SQLite 3.10.0 to INSERT 380.000 records into a in > inMemory Database it takes 35

[sqlite] performance issue through Dll upgrade

sorry for the delayed response. with 3.15.2 it is even worse ( it takes 1246s to INSERT 380.000 records) with 3.8.5 it took only 10s Best regards Stephan ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org

Re: [sqlite] performance issue through Dll upgrade

On 12/01/2016 02:24 PM, Stephan Stauber wrote: Hello, we have following performance Issue since we upgraded from SQLite 3.8.5 to SQLite 3.10.0: SQLite 3.8.5: to INSERT 380.000 records into a in inMemory Database it takes 10 seconds SQLite 3.10.0 to

[sqlite] performance issue through Dll upgrade

Hello, we have following performance Issue since we upgraded from SQLite 3.8.5 to SQLite 3.10.0: SQLite 3.8.5: to INSERT 380.000 records into a in inMemory Database it takes 10 seconds SQLite 3.10.0 to INSERT 380.000 records into a in inMemory Database

Re: [sqlite] performance issue, v.3.10 compared to v.3.14 (inner joins)

On 8/16/16, Raja Kajiev wrote: > > *The output of .fullschema command:* > sqlite> .fullschema -indent > CREATE TABLE 'CLIPPED_UPDATE'( > CREATE TABLE 'DATASET'( > CREATE TABLE 'EXSET'( > CREATE TABLE 'EXSET_DATASET'( > /* No STAT tables available */ No indexes. No

Re: [sqlite] performance issue, v.3.10 compared to v.3.14 (inner joins)

I can send whatever required else if the following bits are not enough. *The output of .fullschema command:* sqlite> .fullschema -indent CREATE TABLE 'CLIPPED_UPDATE'( 'name' TEXT, 'edition' INTEGER NOT NULL, 'coverage_id' INTEGER NOT NULL, 'update_no' INTEGER NOT NULL, 'data' BLOB

Re: [sqlite] performance issue, v.3.10 compared to v.3.14 (inner joins)

On 8/10/16, Raja Kajiev wrote: > The issue is: one of requests performed in my app was executed in ~7 > seconds in v.3.10, but in v.3.14 execution time is ~13-15 seconds. That's > really itching in my case. > The sql request in question includes "inner join" constructs. > I

[sqlite] performance issue, v.3.10 compared to v.3.14 (inner joins)

The issue is: one of requests performed in my app was executed in ~7 seconds in v.3.10, but in v.3.14 execution time is ~13-15 seconds. That's really itching in my case. The sql request in question includes "inner join" constructs. I also remember that in version prior to 3.10 the execution time

[sqlite] Performance issue with CTE

Just to let you know, the solution using SELECT * FROM (query with offset/limit) works perfectly well. Thanks a lot for the suggestion!

[sqlite] Performance issue with CTE

I think the doc is right. I overcame the problem by using a construct like: SELECT field1, field2? WHERE PKEY IN (SELECT PKEY ? WHERE OFFSET n LIMIT m) That executes a sub query. But your solution looks actually better, as it is: SELECT * FROM (SELECT field1, field2? WHERE OFFSET n LIMIT m)

[sqlite] Performance issue with CTE

Thanks. I know about the technique your mentioned, but the point is not about the use of offset or not. The same issue will happen but using a key. See my other reply above.

[sqlite] Performance issue with CTE

On Thu, 1 Oct 2015 13:40:23 +0200, Clemens Ladisch wrote: > OFFSET is inefficient because the database still has to compute all the > rows before skipping over them. > > To do paging, remember the first and last date values on the page, and > for the previous/next page, just continue from there:

[sqlite] Performance issue with CTE

Philippe Riand wrote: > I have a table with 500,000+ records. The table has a date column, > that I?m using to sort my queries (the columns has an index). Simple > queries on the table work very well, using ORDER BY, LIMIT & OFFSET. > I?m actually extracting ?pages? of rows that I?m displaying in

[sqlite] Performance issue with CTE

Op 1 okt 2015, om 04:10 heeft Philippe Riand wrote: > I have a table with 500,000+ records. The table has a date column, > that I?m using to sort my queries (the columns has an index). Simple > queries on the table work very well, using ORDER BY, LIMIT & OFFSET. > I?m actually extracting

[sqlite] Performance issue with CTE

I have a table with 500,000+ records. The table has a date column, that I?m using to sort my queries (the columns has an index). Simple queries on the table work very well, using ORDER BY, LIMIT & OFFSET. I?m actually extracting ?pages? of rows that I?m displaying in a web page. Great!. Now,

[sqlite] Performance issue

On 2015-04-13 09:49 AM, Jeff Roux wrote: > Hi everyone, > > I have copied the original database on my personnal website in a tbz > archive here : > > http://nice-waterpolo.com/misc/db/ > > There is only one index on timestamp,protocol. Hi Jeff, I am not sure what is wrong your side, but the

[sqlite] Performance issue

Hi everyone, I have copied the original database on my personnal website in a tbz archive here : http://nice-waterpolo.com/misc/db/ There is only one index on timestamp,protocol. Thanks. 2015-04-08 14:38 GMT+02:00 R.Smith : > > > On 2015-04-08 01:57 PM, Dominique Devienne wrote: > >> No

[sqlite] Performance issue

On 2015-04-08 01:57 PM, Dominique Devienne wrote: >> No Dominique, it's not that - >> > Perhaps. But that still doesn't get to my point. With a LIMIT clause, in > such a GROUP BY ORDER BY returning a large result set, would SQLite: > 1) sort the whole result-set and then keep only the first

[sqlite] Performance issue

On Wed, Apr 8, 2015 at 2:09 PM, Richard Hipp wrote: > On 4/8/15, Dominique Devienne wrote: > > With a LIMIT clause, in > > such a GROUP BY ORDER BY returning a large result set, would SQLite: > > 1) sort the whole result-set and then keep only the first top-N rows? > > 2) or instead do a

[sqlite] Performance issue

On Wed, Apr 8, 2015 at 1:24 PM, R.Smith wrote: > On 2015-04-08 11:35 AM, Dominique Devienne wrote: > >> On Wed, Apr 8, 2015 at 11:16 AM, Jeff Roux wrote: >> >>> time echo 'SELECT ... FROM flows WHERE timestamp>=1383770600 AND \ >>> timestamp<=1384770600 AND protocol IN (17, 6) GROUP BY

[sqlite] Performance issue

On 2015-04-08 11:35 AM, Dominique Devienne wrote: > On Wed, Apr 8, 2015 at 11:16 AM, Jeff Roux wrote: > >> # For mysql, I use: >> /etc/init.d/mysql stop; /etc/init.d/mysql start; \ >> time echo 'SELECT SUM(nbBytesDecompOut+nbBytesCompIn) as vol, portLan \ >> as item FROM flows WHERE

[sqlite] Performance issue

On 8 Apr 2015, at 10:16am, Jeff Roux wrote: > SELECT SUM(nbBytesDecompOut+nbBytesCompIn) as vol, portLan \ > as item FROM flows WHERE timestamp>=1383770600 AND \ > timestamp<=1384770600 AND protocol IN (17, 6) GROUP BY portLan \ > ORDER BY vol DESC LIMIT 6 If you want us to

[sqlite] Performance issue

On Wed, Apr 8, 2015 at 11:16 AM, Jeff Roux wrote: > # For mysql, I use: > /etc/init.d/mysql stop; /etc/init.d/mysql start; \ > time echo 'SELECT SUM(nbBytesDecompOut+nbBytesCompIn) as vol, portLan \ > as item FROM flows WHERE timestamp>=1383770600 AND \ > timestamp<=1384770600 AND

[sqlite] Performance issue

Thanks everyone for your answers, I made some changes to the database according to the information you gave me. It improved the performance of the query by about 20% (the request now takes 4 seconds instead of 5). Here are some more information, regarding all the suggestions I received: - The

[sqlite] Performance issue

On 4/8/15, Dominique Devienne wrote: > With a LIMIT clause, in > such a GROUP BY ORDER BY returning a large result set, would SQLite: > 1) sort the whole result-set and then keep only the first top-N rows? > 2) or instead do a partial-sort of the first top-N rows only, SQLite must examine all

[sqlite] Performance issue

In case of SELECTing "all available" I recommend invoking a different statement without the timestamp-part instead of providing some min and max values for timestamp. This avoids tricking the query planner into some wrong decisions (if you have an index with protocol as the first column). And

[sqlite] Performance issue

On 2015-04-01 10:20 AM, Jeff Roux wrote: > Here is the complete schema: > > sqlite> .schema > CREATE TABLE application (id INTEGER CONSTRAINT applpk PRIMARY KEY, > shortname VARCHAR(64), name VARCHAR(256)); > > CREATE TABLE flows (idx INTEGER PRIMARY KEY, ipLan INTEGER, ipWan INTEGER, > flags

[sqlite] Performance issue

Here is the complete schema: sqlite> .schema CREATE TABLE application (id INTEGER CONSTRAINT applpk PRIMARY KEY, shortname VARCHAR(64), name VARCHAR(256)); CREATE TABLE flows (idx INTEGER PRIMARY KEY, ipLan INTEGER, ipWan INTEGER, flags INTEGER, portLan INTEGER, portWan INTEGER, tos INTEGER,

[sqlite] Performance issue

Thanks everyone for the answers. I won't be able to make some tests today, I will come back to you soon with additional information. Just to say that, in the worst case, the WHERE clause selects the entire data, i.e 100 entries. The user can select a time range of 1 hour, 3 hours, 1 day, and

[sqlite] Performance issue

From what I see, I assume that timestamp gives the highest selectivity. Taking into account that protocol is SELECTed for and portLan is GROUPed BY, I'd try an index (timestamp, protocol, portLan) (not sure if portLan helps here, but it's worth a try, I think). Don't forget to ANALYZE, of

[sqlite] Performance issue

On 2015-03-30 11:46 AM, Jeff Roux wrote: > Hi everyone, > > I have a daemon that collects information and stores it in a SQLite > database. The table has 1 million rows. > > This daemon is running on a HP server with 12 cores, 32 GB of RAM, > and a SSD drive. I have performance issues with some

[sqlite] Performance issue

Simon, here is the list of the indexes that were already defined on the table: CREATE INDEX idxLanWan ON flows(ipLan, ipWan, portWan, portLan); CREATE INDEX idxProto ON flows(protocol); CREATE INDEX idxTos ON flows(tos); CREATE INDEX idxTsLanWan ON flows(timestamp, ipLan, ipWan, portWan,

[sqlite] Performance issue

Hi everyone, I have a daemon that collects information and stores it in a SQLite database. The table has 1 million rows. This daemon is running on a HP server with 12 cores, 32 GB of RAM, and a SSD drive. I have performance issues with some requests. For instance, the following request takes

[sqlite] Performance issue

On 30 Mar 2015, at 10:46am, Jeff Roux wrote: > This daemon is running on a HP server with 12 cores, 32 GB of RAM, > and a SSD drive. I have performance issues with some requests. For > instance, the following request takes more than 5 seconds to > accomplish with SQlite3 (in that particular

[sqlite] Performance issue

On Mon, Mar 30, 2015 at 6:44 AM, Jeff Roux wrote: > Simon, > > here is the list of the indexes that were already defined on the table: > CREATE INDEX idxLanWan ON flows(ipLan, ipWan, portWan, portLan); > CREATE INDEX idxProto ON flows(protocol); > CREATE INDEX idxTos ON flows(tos); > CREATE

[sqlite] Performance issue

ounces at mailinglists.sqlite.org [mailto:sqlite-users- >bounces at mailinglists.sqlite.org] On Behalf Of Jeff Roux >Sent: Monday, 30 March, 2015 03:46 >To: sqlite-users at mailinglists.sqlite.org >Subject: [sqlite] Performance issue > >Hi everyone, > >I have a daemon that c

Re: [sqlite] Performance issue when copying data from one sqlite database to another

ll: Pontus Bergsten <pontus_bergs...@yahoo.se>; General Discussion of SQLite Database <sqlite-users@sqlite.org> Skickat: onsdag, 15 oktober 2014 0:29 Ämne: Re: [sqlite] Performance issue when copying data from one sqlite database to another On Tue, Oct 14, 2014 at 5:24

Re: [sqlite] Performance issue when copying data from one sqlite database to another

On Wed, 15 Oct 2014 01:26:10 +0200, Kees Nuyt wrote: > http://knuyt.demon.nl/sqlite.org/faq.html#q19 Oops, make that http://www.sqlite.org/faq.html#q19 ___ sqlite-users mailing list sqlite-users@sqlite.org

Re: [sqlite] Performance issue when copying data from one sqlite database to another

On Tue, 14 Oct 2014 21:24:40 + (UTC), Pontus Bergsten wrote: >Hi, >I have the following problem setup: [...] > INSERT INTO Dest.TheTable (field1, ..., fieldN) FROM Main.TheTable WHERE time > BETWEEN t1 AND t2 > > Is there any technique that can be used for tuning

Re: [sqlite] Performance issue when copying data from one sqlite database to another

On Tue, Oct 14, 2014 at 5:24 PM, Pontus Bergsten wrote: > When the application is executed on Windows on a desktop computer, the > copying works fine and the performance is fairly ok, even when saving to > USB. However, when the same code is executed on the embedded

Re: [sqlite] Performance issue when copying data from one sqlite database to another

On 14 Oct 2014, at 10:24pm, Pontus Bergsten wrote: > INSERT INTO Dest.TheTable (field1, ..., fieldN) FROM Main.TheTable WHERE time > BETWEEN t1 AND t2 > And here is the performance problem: When the application is executed on > Windows on a desktop computer, the

Re: [sqlite] Performance issue when copying data from one sqlite database to another

On the embedded unit write caching disabled? 2014-10-14 23:24 GMT+02:00 Pontus Bergsten : > Hi, > I have the following problem setup: > We use sqlite3 in an embedded signal logger application. The "Main" > in-memory signal signal database consists of some minor signal

Re: [sqlite] Performance issue when copying data from one sqlite database to another

Depends on how safe/robust you want the copying to be, but if you can simply rerun if something goes wrong, you might look into stuff like: pragma journal_mode = MEMORY; pragma synchronous = off; But make sure you understand the consequences first by reading about these commands:

[sqlite] Performance issue when copying data from one sqlite database to another

Hi, I have the following problem setup: We use sqlite3 in an embedded signal logger application. The "Main" in-memory signal signal database consists of some minor signal definition tables + two large tables (shards) with the actual signal data. The sharding technique is used in order to

Re: [sqlite] Performance issue using SQLite

Jun 2014 21:53:58 +0800 >From: 163 <sdu...@163.com> >To: "sqlite-users@sqlite.org" <sqlite-users@sqlite.org> >Subject: [sqlite] Performance issue using SQLite >Message-ID: <790e34bd-c627-4dd6-872e-2b358a6d1...@163.com> >Content-Type: text/plain; char

Re: [sqlite] Performance issue using SQLite

163 wrote: > I met a performance issue when using SQLite 3.8.4.3. I found it would > be quite slow trying to select count(*) using a where statement with > several OR condition. For example: > > select ctrl_no from T_CTRL_CLRRULE where CTRL_NO='%s' and ((PRODUCT_ID='%s' > and OPERATE_TYPE='%s')

[sqlite] Performance issue using SQLite

> Hi Experts, > I met a performance issue when using SQLite 3.8.4.3. I found it would be > quite slow trying to select count(*) using a where statement with several OR > condition. For example: > > sprintf (sql_str, "select ctrl_no from T_CTRL_CLRRULE where CTRL_NO='%s' and >

Re: [sqlite] Performance issue with JOIN and large IN operator

On Wed, May 1, 2013 at 2:11 PM, jic wrote: > "Richard Hipp" wrote... > > Dr. Hipp, > > will this fix break the work-around you provided, > > " > to put a "+" sign in front of the "elements.id" identifier in the ON > clause: > > SELECT count(*) FROM elements JOIN tags ON

Re: [sqlite] Performance issue with JOIN and large IN operator

"Richard Hipp" wrote... On Wed, May 1, 2013 at 11:24 AM, Richard Hipp wrote: On Wed, May 1, 2013 at 8:30 AM, Martin Altmayer < martin.altma...@googlemail.com> wrote: Hi, I have a query that runs more than 400x slower in 3.7.16.2 than in 3.7.11. This seems to be

Re: [sqlite] Performance issue with JOIN and large IN operator

On Wed, May 1, 2013 at 11:24 AM, Richard Hipp wrote: > > > On Wed, May 1, 2013 at 8:30 AM, Martin Altmayer < > martin.altma...@googlemail.com> wrote: > >> Hi, >> >> I have a query that runs more than 400x slower in 3.7.16.2 than in 3.7.11. >> > > This seems to be caused by the

Re: [sqlite] Performance issue with JOIN and large IN operator

On Wed, May 1, 2013 at 8:30 AM, Martin Altmayer < martin.altma...@googlemail.com> wrote: > Hi, > > I have a query that runs more than 400x slower in 3.7.16.2 than in 3.7.11. > This seems to be caused by the use of transitive constraints in version 3.7.16. Your work-around (until an official fix

[sqlite] Performance issue with JOIN and large IN operator

Hi, I have a query that runs more than 400x slower in 3.7.16.2 than in 3.7.11. Instead of posting the original query, I post a simplified version which still experiences the problem with a factor of over 100x: SELECT COUNT(*) FROM elements JOIN tags ON elements.id = tags.element_id WHERE

Re: [sqlite] Performance issue on view

An Correction! /***/ DROP TRIGGER IF EXISTS movement_trg02; CREATE TRIGGER movement_trg02 AFTER INSERT ON movement FOR EACH ROW BEGIN REPLACE INTO movement SELECT t1.id , t1.account_id , t1.payment , t1.amount ,

Re: [sqlite] Performance issue on view

fr, "General Discussion of SQLite Database" <sqlite-users@sqlite.org> Enviadas: Terça-feira, 1 de Junho de 2010 17:12:02 Assunto: Re: [sqlite] Performance issue on view Create a new table to do this and add a trigger on op to make the sum. - Mensagem original - De: &

Re: [sqlite] Performance issue on view

Create a new table to do this and add a trigger on op to make the sum. - Mensagem original - De: "Stéphane MANKOWSKI" <steph...@mankowski.fr> Para: sqlite-users@sqlite.org Enviadas: Terça-feira, 1 de Junho de 2010 16:57:16 Assunto: [sqlite] Performance is

Re: [sqlite] Performance issue on view

> PS: I don't want to compute "balance" attribute by code and save it in op > balance due to the fact that I am using an undo/redo mechanism. >From my experience this is the only way to go - calculate the balance in your application then store it in database along with transaction as "balance

[sqlite] Performance issue on view

Hi, In the this database file (http://skrooge.org/files/test.wrk), I created a table named "op" containing banking transactions. A transaction has: An unique id An account A date An amount I created a view named "v_op" with one more computed attribute named

Re: [sqlite] Performance issue on records retrieval :: 100, 000 records

On 16 Jul 2009, at 6:22pm, MADHAVAN VINOD wrote: > Suppose, if I don't have index, is this the desired behavior of Sqlite > to take this much time to fetch just 10 records or am I missing > something here. Your first post states quite clearly '5) No INDEX created.'. Without any index on your

Re: [sqlite] Performance issue on records retrieval :: 100, 000 records

gt; From: sqlite-users-boun...@sqlite.org > [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Michal Seliga > Sent: Thursday, July 16, 2009 8:37 PM > To: General Discussion of SQLite Database > Subject: Re: [sqlite] Performance issue on records retrieval :: 100,000 > record

Re: [sqlite] Performance issue on records retrieval :: 100, 000 records

...@sqlite.org] On Behalf Of Michal Seliga Sent: Thursday, July 16, 2009 8:37 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] Performance issue on records retrieval :: 100,000 records MADHAVAN VINOD wrote: > > 5) No INDEX created. > > The retrieval logic is su

Re: [sqlite] Performance issue on records retrieval :: 100, 000 records

MADHAVAN VINOD wrote: > > 5) No INDEX created. > > The retrieval logic is such that to retrieve the oldest 10 records along > with some additional constraints (say a, b and c are columns and the > constraints are like a=1 AND b < c). > > > > So my WHERE clause is like "CurrTime <=

[sqlite] Performance issue on records retrieval :: 100, 000 records

Hi All, Description of my setup: My database contains 1) One table 2) 20 fields (contains date field to store the inserted time) 3) 100,000 records 4) database size is 21MB. 5) No INDEX created. 6) Sqlite version 3.5.9. The retrieval logic is such that to retrieve

Re: [sqlite] Performance issue in using 'LIKE' with Indexes.

On 8/07/2009 7:11 PM, aalap shah wrote: > Hi, > > I am a new user to sqlite3, I have a program that searches through a > database. I have a table with 1 column as varchar and I want to > perform a search on it. > I have created an index over that column. And I use a select query > with

Re: [sqlite] Performance issue in using 'LIKE' with Indexes.

On Jul 8, 2009, at 4:11 PM, aalap shah wrote: > Hi, > > I am a new user to sqlite3, I have a program that searches through a > database. I have a table with 1 column as varchar and I want to > perform a search on it. > I have created an index over that column. And I use a select query > with

[sqlite] Performance issue in using 'LIKE' with Indexes.

Hi, I am a new user to sqlite3, I have a program that searches through a database. I have a table with 1 column as varchar and I want to perform a search on it. I have created an index over that column. And I use a select query with "column_name LIKE 'a%' ". So my first question is will this

Re: [sqlite] Performance issue

Stergios Zissakis wrote: My question is: shouldn't sqlite's engine figure out what I am trying to do and sort the tables on the fly in an effort to optimize the query? When using no indexes, a .explain reveals 3 nested loops which take a long time to return results. Any help/ideas will be

[sqlite] Performance issue

Hello to everyone, This is my first post in the list I've got the following 3 tables: CREATE TABLE A ( int1 INTEGER, txt1 TEXT, int2 INTEGER, txt2 TEXT, PRIMARY KEY ( txt1 ) ); CREATE TABLE B ( txt1 TEXT, int1 INTEGER ); CREATE TABLE C ( txt1 TEXT, int1 INTEGER ); Each table

Re: [sqlite] performance issue

lete it! * - Original Message - From: [EMAIL PROTECTED] Date: Wednesday, July 18, 2007 1:36 pm Subject: [sqlite] performance issue > Hi > > I am using SQLite on MVL OS for ARM processor based embedded platform. > I am using SQLite versio

[sqlite] performance issue

Hi I am using SQLite on MVL OS for ARM processor based embedded platform. I am using SQLite version 3.3.13. We use SQLite APIs for DB operation. I am facing following issue. While testing I observed INSERT and UPDATE command is taking more time than SELECT queries. For example one select query

RE: [sqlite] Performance Issue with SQLite Inserts

PROTECTED] Subject: Re: [sqlite] Performance Issue with SQLite Inserts Soham Mehta wrote: > Thanks you for all of the ideas. Here are answers to the various questions: > > 1) Currently, I am committing after every 10,000 inserts. Initially, I was > running all of the inserts of a

  1   2   >