[sqlite] Prevent database file from being overwritten by other processes

ITE, which explains why *any* process can overwrite the contents. Is there an easy way of configuring / changing this so that SHARED_WRITE is not acquired? Will SQLite even function? Is it just easier to create a hidden copy and work on that? Thanks for the advice Fabian

[sqlite] Attach via prepared statement / Multiple attach

Thanks for your reply, James! > Parameterized queries in general let you replace any data value > (and not metadata). For instance, you can replace a column value, but > not a column name. Using that as a guide, I'm a little suprised that > > ATTACH DATABASE ? AS ? > > works, because

[sqlite] Attach via prepared statement / Multiple attach

Hi everyone, I am currently using > ATTACH DATABASE ? AS ?; to dynamically attach databases to a connection. In some cases, the same physical database file is attached under different names. This all seems to work perfectly, although the documentation for the ATTACH statement specifies the name

Re: [sqlite] In-memory DB slower than disk-based?

this off can make a noticeable difference in scalability. I'm not sure if this will affect page cache throughput specifically, but these two things are both fairly easy to try. -Fabian ___ sqlite-users mailing list sqlite-users@sqlite.org http://s

Re: [sqlite] Building multiple indexes in one scan over a table

On 6/10/2014 2:25 PM, Simon Slavin wrote: On 10 Jun 2014, at 7:22pm, Fabian Giesen <fabi...@radgametools.com> wrote: 1. Bulk insertion of relatively large amounts of data (low hundreds of megabytes to tens of gigabytes); a typical profile generates on the order of 10MB of samplin

[sqlite] Building multiple indexes in one scan over a table

operation in SQLite? If not, how hard would it be to add? Thanks, -Fabian ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] ORDER BY DESC after GROUP BY not using INDEX - intentionally?

Thanks. I think using GROUP BY without aggregates is a strange way to remove duplicates, anyway. Not intentional. SQLite simply fails to recognize that by using the GROUP BY in descending order it could avoid the ORDER BY clause. This is an optimization that we have never considered because it

[sqlite] ORDER BY DESC after GROUP BY not using INDEX - intentionally?

test3 USING COVERING INDEX test3_idx Regards Fabian ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite keeps on searching endlessly

sqlite> select * from sqlite_stat1; t||3 sqlite> analyze t; sqlite> select * from sqlite_stat1; t|i|3 1 1 Fabian ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite keeps on searching endlessly

that the query planner has accurate information about your index. Fabian ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Comparing two tables column by column

Wouldn't SELECT * FROM table1 EXCEPT SELECT * FROM table2 solve this problem? I think it does for the example provided. Not sure if it would work in real-world environment. -Ursprüngliche Nachricht- Von: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] Im

Re: [sqlite] FTS Find Tokens at Record Start

You might want to check the following: SELECT word FROM fts WHERE fts MATCH '^token' Beginning with 3.7.9 this should only return records that have 'token' at the beginning of the record. See changelog of 3.7.9: If a search token (on the right-hand side of the MATCH operator) in FTS4 begins

[sqlite] FTS: Phrase queries

Ever since I started using FTS extensively, I frequently ran into this limitation: ** TODO: Strangely, it is not possible to associate a column specifier ** with a quoted phrase, only with a single token. Not sure if this was ** an implementation artifact or an intentional decision when

Re: [sqlite] FTS: Phrase queries

2011/11/14 nobre > > Comment from the source: > > ** TODO: Strangely, it is not possible to associate a column specifier > ** with a quoted phrase, only with a single token. Not sure if this was > ** an implementation artifact or an intentional decision when

[sqlite] FTS: Phrase queries

When I have a basic FTS query that needs to be restricted to a column, I can write it in two ways: 1.) WHERE column MATCH 'apple' 2.) WHERE table MATCH 'column:apple' But when I have a phrase query, I can only write it in one way: 1.) WHERE column MATCH '"apple juice"' The problem is that when

[sqlite] JOIN vs IN

Suppose I have two tables, and I want to have look for a value in the first table, but display the columns from the second table. The most obvious way would be joining them on rowid. But I don't need to SELECT any columns from the first table, and it's a FTS4 table (which always joins a bit slower

Re: [sqlite] VACUUM

2011/11/10 Richard Hipp > > Because when you are inserting the Nth row, SQLite has no idea of how many > more rows will follow or how big the subsequent rows will be, so it has no > way to reserve contiguous space sufficient to hold them all. The result is > that parts of the

[sqlite] VACUUM

I'm trying to understand the following: Why is it that when I create a new database, and fill it with rows, why can it not be commited to disk the same way as VACUUM would do it? Currently I'm trying to manually vacuum the database by inserting rows sorted by table and by rowid, but as soon as the

Re: [sqlite] FTS4: Datatypes

It seems I finally have some sort of a solution: "As of SQLite version 3.7.9, FTS4 supports a new option - "content" -designed to extend FTS4 to support the creation of full-text indexes where: + +* The indexed documents are not stored within the SQLite database + at all (a

[sqlite] FTS4: Datatypes

Ever since I started using FTS, I'm always confronted with the problem that I need two tables: one FTS table with the TEXT columns, and one normal table with the INTEGER columns for numerical values. This causes all kinds of problems (keeping the rowid's in sync, complex queries, etc.). >From a

Re: [sqlite] INDEX Types

2011/11/9 GB > Maybe you could try to use a pagesize that matches the size of a disk > allocation unit or memory page. For Windows since a typical NTFS partition > has a clustersize of 4KiB - which happens to also be the size of a memory > page - a pagesize of 4096 Byte seems to

Re: [sqlite] INDEX Types

2011/11/9 Black, Michael (IS) OK...you're right...a reboot kills it. > I'm glad someone was able to reproduce this on Linux, ruling out the possibility it's a Windows-issue. > However, reboot again and add "select count(*) from a;" as the first line > of gendat2.sql >

Re: [sqlite] INDEX Types

2011/11/9 Nico Williams > > I don't get it. You're reading practically the whole file in a random > manner, which is painfully slow, so why can't you read the file in one > fell swoop (i.e., sequential reads)?? > I'm only reading the whole file when the number of

Re: [sqlite] INDEX Types

2011/11/9 Nico Williams > > What's wrong with reading the whole file into memory at boot time as a > way to prime the cache? Rebooting always takes some time, mostly the > time to read all sorts of files. > > It's a desktop application, I cannot pre-cache anything before

[sqlite] SQLite Error (19)

I'm in the process of converting a normal table to a FTS virtual table, and I'm noticing different behaviour if I try to insert duplicate rowid's. I'm using the 'INSERT OR IGNORE' statement, and on the normal table I can try to insert millions of duplicates very quickly, but on the FTS table it's

Re: [sqlite] INDEX Types

2011/11/9 Luuk > On 09-11-2011 17:23, Black, Michael (IS) wrote: > >> time sqlite3> 19.307u 0.082s 0:19.40 99.8%0+0k 0+0io 0pf+0w >> >> time sqlite3< index2.sql >> 19.266u 0.092s 0:19.37 99.8%0+0k 0+0io 0pf+0w >> >> > > $ time sqlite3 > real0m21.094s > user

Re: [sqlite] INDEX Types

2011/11/9 Black, Michael (IS) > Are you sure you're using BEGIN/COMMIT on your transactions? > Yes > I just used my benchmark data and inserted another 100,000 rows into the > database in 2.3 seconds. That is because you immediately insert those additional rows,

Re: [sqlite] INDEX Types

2011/11/9 Simon Slavin > > Didn't someone recently note that entering the first million records was > fast, but if he then closed and reopened the database, entering the next > 100,000 records was slow ? > > Yes, and there is still no real explanation for it, other than

Re: [sqlite] INDEX Types

2011/11/9 Black, Michael (IS) > Hmmm...appears to be the same for this case which, I must say, I find > surprising. > > Thanks for actually benchmarking it. I'm also a bit surprised, because I always thought SQLite handled INTEGER more efficiently than TEXT. I also did

[sqlite] INDEX Types

I'm having an issue where inserts on an un-cached database are very slow. The reason probably is that a large part of the existing index needs to be read from disk, to be able to insert new rows to the index. The length of the values in the indexed column are around 60 bytes, so I'm thinking about

Re: [sqlite] Slow INDEX

ead of in a single step, when commiting your transactions, thus slowing > down disk writes. > > > Fabian-40 wrote: > > > > 2011/11/2 Black, Michael (IS) > > I do not use WAL, since I have turned 'journal_mode' off (to improve > > insert > > performa

Re: [sqlite] Slow INDEX

2011/11/2 Nico Williams > > Incidentally, it pays to make the SQLite3 page size match the > filesystem preferred block size. > > I already have the page_size set to 4096, which should match the default NTFS cluster size. But note that this can still fail you when the file

Re: [sqlite] Slow INDEX

2011/11/2 Nico Williams > > But that's NOT what SQLite3 is doing. SQLite3 is doing random I/O. > And the OS sees the random I/O pattern and concludes it's better to > not read the whole file in. So for those 10K inserts you pay -worst > case- 10K I/Os. At ~12ms per

Re: [sqlite] Slow INDEX

2011/11/2 Black, Michael (IS) > Then, when you restart the file is on system disk and it is flushing each > insert to system disk on the WAL file slowing things down dramaticalliy. > I do not use WAL, since I have turned 'journal_mode' off (to improve insert performance), and as far as I know

Re: [sqlite] Slow INDEX

2011/11/2 Simon Slavin > > So compare your 'restart-and-INSERT' test with one where you restart, log > in, then do a few random operations for a minute: start your web browser > and load a page. Open and close a few documents or control panels. Sit > and do nothing for a

Re: [sqlite] Slow INDEX

2011/11/2 Paul Corke > > A stateful antivirus that does lots of heavy processing when you first > open the file? > > I'm running these tests on a completey clean Win7 install, without any anti-virus (or other software) installed. I should note that it runs virtualized

Re: [sqlite] Slow INDEX

2011/11/2 Mr. Puneet Kishor > > ahh, so you *are* getting expected behavior, just not what *you* expected. > Did you have a different number in mind instead of a factor of 300? And, if > so, why? To read an (un-cached) 150mb file from disk, doesn't take 30 seconds. And

Re: [sqlite] Slow INDEX

2011/11/2 Mr. Puneet Kishor > > Others will have better answers, but methinks that when you reboot the > computer, the operating system's caches are flushed out, which slows the > operation. Try working with the db for a bit (SELECT, repeat INSERTs, etc.) > and notice if the

[sqlite] Slow INDEX

I asked a similar question before, but received no response, so maybe it's a very stupid question, but if so, feel free to say so. I create a database, create one table with a TEXT column, insert 1 million rows in 10 secs, create an index in 10 secs, VACUUM the database, and close the database.

Re: [sqlite] Slow inserts with UNIQUE

2011/10/30 Black, Michael (IS) > > #1 What version? > 3.7.8, using System.Data.Sqlite wrapper > #2 How long to insert the 1M? > 10 seconds > > #3 What's the average string size? > 55 characters > > #5 How long to create the index? > 10 seconds > #6 How long to

Re: [sqlite] Slow inserts with UNIQUE

2011/10/29 Simon Slavin > > When you insert the 10,000 strings are you doing it inside a transaction ? > > BEGIN TRANSACTION; > INSERT ... > INSERT ... > INSERT ... > COMMIT; > > Yes, I use transactions, prepared statements, cache_size, journal_mode and synchronous

[sqlite] Slow inserts with UNIQUE

I have a table with one TEXT column. I insert 1 million rows of short strings, and then create an UNIQUE INDEX. The speed is very acceptable. Then I insert another 10.000 short strings, and the performance is very bad, it almosts take longer than inserting the initial million(!) rows to fill the

Re: [sqlite] Database file structure

2011/10/23 Simon Slavin > > In that case, try defragging your file sometime. May make a big > difference. > > If you mean Windows defrag, it would be pointless, since it doesn't change the database structure? If you mean VACUUM, it will generate the exact same structure as

Re: [sqlite] UNIQUE constraint

2011/10/23 Simon Slavin > > In this example, the indexed column is a text column. The text fields > could have been very long, and checking long text fields for uniqueness can > involve comparing every byte. Nevertheless, I do not understand the results > you quoted. I

Re: [sqlite] Database file structure

2011/10/23 Simon Slavin > > My immediate question is why this is two rows in two separate tables rather > than one row in one table. After all, if tables always have the same rows > in, they might as well be the same row in one table. I would love to have those rows into

Re: [sqlite] UNIQUE constraint

> > > No, a UNIQUE index and a regular index are implemented the exact same way. > It's just that, at INSERT and UPDATE time, after finding a proper place to > insert the new value, an additional check is made that the place isn't > already occupied. > So the only overhead for UNIQUE is that

[sqlite] Database file structure

I have two tables, both containing 1 million rows, which frequently need to be joined by rowid. Right now, the insert loop is like this: For I = 1 to 1000 INSERT INTO TABLE1 ... INSERT INTO TABLE2 ... Next When I look at the structure of the created database-file, the rows for the

[sqlite] UNIQUE constraint

I have a column with a normal INDEX, and I would like to turn it into an UNIQUE index, but I'm a bit worried about the performance implications for inserts. Can someone give some insight into how UNIQUE is implemented in SQLite, does it create extra tables compared to a normale index, are there

[sqlite] Stand-Alone INDEX

I have a very simple table: it just contains one single (text) column with an index. This table contains million of hash-values, and because of the index, SQLite is storing all the data twice. Behind the scenes it creates a second table, containing all the same values in sorted order, causing the

[sqlite] FTS: Reduce tokens

Using the default tokenizer, everything that is not an alphanumeric character or an underscore, will generate a new token. I have a lot of columns that contains e-mail addresses or URL's, and most of them have characters like '.', '@' and '/'. Is there a simple way to make FTS see them as one

Re: [sqlite] FTS vs INDEX

2011/10/19 Scott Hess > > To be clear, how it works is that new insertions are batched into a > new index tree, with index trees periodically aggregated to keep > selection efficient and to keep the size contained. So while the > speed per insert should remain pretty stable

Re: [sqlite] FTS vs INDEX

2011/10/19 Alexey Pechnikov > FTS use index multi-tree and de-facto has _no_ insert speed degradation. > Thanks, that's good to hear! It makes me wonder why SQLite doesn't use that same multi-tree mechanism for regular indexes, but that's a whole different question.

Re: [sqlite] FTS vs INDEX

thread has the benchmark data > > > > Michael D. Black > > Senior Scientist > > Advanced Analytics Directorate > > Advanced GEOINT Solutions Operating Unit > > Northrop Grumman Information Systems > > > From: sqlite-users

[sqlite] FTS vs INDEX

Did anyone do some benchmarks how the insert-speed of FTS compares to a TEXT INDEX column? I don't need many of the extra features of FTS, because I always need to look up rows by prefix or exact match, and both can be implemented efficiently via TEXT INDEX too. But if the overhead is comparable,

Re: [sqlite] Fast JOIN

2011/10/19 Fabian <fabianpi...@gmail.com> > > > Maybe there is just no easy way to do what I want, I will take a good night > sleep about it ;) > > I think the correct query would be: SELECT table1.data1, table2.data2 FROM ( SELECT table1.rowid, table1.data1 WHERE table

Re: [sqlite] Fast JOIN

> > > Assuming that is a typo, does the following not do what you want? > > SELECT table1.data1, table2.data2 > FROM > ( > SELECT table1.rowid, table1.data1 > WHERE table1.data1 = 10 > ORDER BY table1.rowid DESC > OFFSET 0 LIMIT 250 > ) AS table1 > JOIN table2 > ON table1.rowid = table2.rowid >

[sqlite] Fast JOIN

I'm working on a pagination system where two tables need to be joined. At first my query looked like this: SELECT table1.data1, table2.data2 FROM table1 JOIN table2 ON table1.rowid = table2.rowid WHERE table1.data1 = 10 ORDER BY table1.rowid DESC OFFSET 0 LIMIT 250 I got very good advice on this

Re: [sqlite] Limit COUNT

2011/10/16 Frank Missel <i...@missel.sg> > > But it sounds a bit like Fabian both wants to have the total number of > records available and at the same time limit the count. > > No, I only want to have a capped total available. If I would go with Simons solution, I

Re: [sqlite] Limit COUNT

2011/10/16 Frank Missel > > What do you want to attain with the count? > > I want to allow users to paginate through a result set. The pages are retreived through LIMIT/OFFSET, but to calculate the total number of pages, I have execute a separate COUNT() query (without LIMIT)

[sqlite] Limit COUNT

How can you limit a count-query? I tried: SELECT COUNT(*) FROM table LIMIT 5000 But it ignores the LIMIT clause. I think the workaround would be counting the results of a sub-query, but I'm trying to understand whats wrong with the syntax above. The goal is to make the engine stop iterating

Re: [sqlite] Slow JOIN on ROWID

2011/10/14 Simon Slavin > > So that should never happen, right ? ANALYZE is meant to make things > faster, not slower. So is that an actual fixable bug or is it one of those > extremely unlikely situations that is hard to fix ? > > So, OP, try issuing these commands > >

Re: [sqlite] Slow JOIN on ROWID

> > Do you have an sqlite_stat1 table in the database (created by > running ANALYZE)? What is the output of the shell command > How about the contents of the "sqlite_stat1" table? What does > the shell command ".dump sqlite_stat1" show? > > This is the output with a fresh database, where ANALYZE

Re: [sqlite] Slow JOIN on ROWID

2011/10/14 Dan Kennedy > > Your EXPLAIN output shows that it is doing a linear scan of > table1. Which is different from what I get here with the same > schema and query. When I run them here, both queries (with and > without the "ORDER BY rowid") use the same query plan.

Re: [sqlite] Slow JOIN on ROWID

2011/10/14 Dan Kennedy > > Good question. Can you enter the following commands into > the shell tool and post the complete output (no "QUERY PLAN" > this time): > > .version > .schema > .explain > > EXPLAIN SELECT * FROM table1 WHERE data=10 ORDER BY rowid LIMIT 250

Re: [sqlite] Slow JOIN on ROWID

2011/10/14 Dan Kennedy > > If SQLite cannot use an index to for an ORDER BY in a SELECT > query and has to do an external sort, the EXPLAIN QUERY PLAN > output will have something like this in it: > > 0|0|0|USE TEMP B-TREE FOR ORDER BY > > Since my EXPLAIN does not show

Re: [sqlite] Slow JOIN on ROWID

2011/10/14 Petite Abeille > > Hurray! Now you must have the finest query ever to grace the intraweb! A > true work of beauty :)) > > Here in Belgium we have a saying: Who doesnt honor the petite, is not worth the big ;) ___

Re: [sqlite] Slow JOIN on ROWID

2011/10/14 Simon Slavin > > If you have an index on the data column then that's the index SQLite would > used for that query. Once it has picked that index it no longer has access > to the rowid index. > > CREATE INDEX tdr ON table (data, rowid) > > Thanks! That explains

Re: [sqlite] Slow JOIN on ROWID

2011/10/14 Petite Abeille > > Much? Really? I get the broadly same execution time for either variant: > > 0|0|0|SCAN TABLE mail_header USING INTEGER PRIMARY KEY (~2192503 rows) > > 0|0|0|SCAN TABLE mail_header (~2192503 rows) > > I get SELECT mail_header.rowid FROM

Re: [sqlite] Slow JOIN on ROWID

> > > Exactly. > > I still don't have optimal performance in the query (although it's much better now), and it seems to be related to ORDER BY. When I execute: SELECT * FROM table WHERE data = 10 LIMIT 250 OFFSET 50 It's very fast, but it's get much slower (10 times) when I add an ORDER BY

Re: [sqlite] Slow JOIN on ROWID

2011/10/12 Petite Abeille > > Now the join is performed only 250 times, adding just a small overhead > compare the the bare bone query without the join. > > The short of it: minimize the amount of work upfront :) > > Thank you very much! This approach solved the

[sqlite] Slow JOIN on ROWID

Why is this very fast (20 ms): SELECT table1.data1, table1.data2 FROM table1 WHERE table1.data1 = 10 ORDER BY table1.rowid DESC LIMIT 250 OFFSET 50 0 0 0 SEARCH TABLE table1 USING INDEX data1idx (data1=?) (~3 rows) And this very slow (3500ms): SELECT

Re: [sqlite] FTS4: OFFSET Performance

2011/10/12 Dan Kennedy > > (INDEX 1) == lookup by rowid. > > Okay! Then maybe my problem is unrelated to FTS, I will create a new question. ___ sqlite-users mailing list sqlite-users@sqlite.org

Re: [sqlite] FTS4: OFFSET Performance

2011/10/12 Dan Kennedy > > Can we see the output of EXPLAIN for this query? > > Without selecting table2.data2: 0 0 0 SEARCH TABLE table1 USING INDEX data1idx (data1=?) (~3 rows) Including selecting table2.data2: 0 0 0 SEARCH TABLE table1 USING INDEX data1idx

Re: [sqlite] OFFSET Performance

2011/10/12 Igor Tandetnik > > See if this helps: http://www.sqlite.org/cvstrac/wiki?p=ScrollingCursor I tried to implement the method as suggested in the article, but it will only work for pagination where the user is only allowed to go 1 page back or 1 page forward (since

Re: [sqlite] OFFSET Performance

2011/10/12 Igor Tandetnik > > See if this helps: http://www.sqlite.org/cvstrac/wiki?p=ScrollingCursor Thanks! Very interesting! I already was aware that using a large OFFSET could potentially be slow, because SQLite internally reads all preceding rows, and just discards

Re: [sqlite] FTS4: OFFSET Performance

2011/10/12 Dan Kennedy > > Are you using 3.7.7 or newer? And specifying the order=DESC > option when creating the FTS4 table? If not, it might be a big > help in this case. > > > http://www.sqlite.org/fts3.**html#fts4order > >

Re: [sqlite] FTS4: OFFSET Performance

2011/10/12 Petite Abeille > > In any case, you can easily refactor the query into two steps: > > (1) First get your pagination > (2) Then join to your fts > > I tried implementing your refactoring, but immediately run into a problem. The user must be able to sort the

Re: [sqlite] FTS4: OFFSET Performance

> > > > How would I optimize the above queries, to take advantage of the > > LIMIT/OFFSET values, making them suitable for fast pagination? > > Are you sure the issue is with the fts table? Or is it the order by desc? > > If I omit the FTS table in the query, like so: SELECT table1.data1 FROM

[sqlite] FTS4: Use Cases

I have three types columns in my table, which I want to move to a FTS4 table, but I'm not sure whether that would make sense. 1.) This column contains hash-values. I always need to lookup 100% exact matches. Normally you would store them with 'TEXT INDEX' in a standard table. Would there be any

[sqlite] FTS4: OFFSET Performance

I have two tables, one normal table (table1), and one FTS4 table (table2). The ROWID's between the tables are in sync. I often run queries on table1, but every row returned must also include a value from table2 (data2), so I create a query like this: SELECT table1.data1, table2.data2 FROM table1

[sqlite] Exclusive transaction severly slows down the queries increasingly in time

application executes some queries on the table from time to time. Please help, Fabian __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com