[sqlite] ATTACH DATABASE statement speed

It really seems something strange happens at filesystem level. This is a simple copy of slightly less than 1gb. It needs 9 seconds including sync. % date && sudo rsync -Pr italy-latest.osm.pbf / && sync && date Tue Aug 18 19:22:23 JST 2015 sending incremental file list italy-latest.osm.pbf

[sqlite] pragma temp_store_directory is deprecated, what is the alternative?

Another issue to consider is security. Some programs, such as mine, needs to carefully control temporary files. Unless the user selects a specific directory for temporary files, the files are put in a subdirectory of the program directory, created at run-time, then is security erased when

[sqlite] ATTACH DATABASE statement speed

It seems my system CPUs overheats regularly and so the system throttle the CPU speed. It is definitely not good, I need to disassemble my laptop, clean everything, and probably replace the conductive cpu paste. However, this does not explain anything. sqlite3 appears IO bound, not CPU bound: in

[sqlite] ATTACH DATABASE statement speed

I think the ATTACH slowness was a misunderstanding caused by the optimization as it does not happen anymore now I compiled with -O0. The long time was actually deleting the table from the input db, this explains also why sqlite was making the journal file for the operation. I am aware it sounds

[sqlite] ATTACH DATABASE statement speed

Just another guess: Have you tried to increase the page chache drastically ? I can remeber that "PRAGMA quick_check" is pretty slow for bigger DBs without an increased page cache. Maybe something like: PRAGMA cache_size=50; PRAGMA quick_check; Marcus Am 18.08.2015 um 12:38 schrieb Paolo

[sqlite] ATTACH DATABASE statement speed

Hello ! Do you have foreign keys on your tables ? And if so do you have indexes on then ? A database with foreign keys and no indexes can run very slow for mas insert/update/delete ? Cheers ! ? > Tue Aug 18 2015 12:38:51 CEST from "Paolo Bolzoni" > Subject: Re: [sqlite] ATTACH

[sqlite] do temporary on-disk databases get cleaned up on abnormal process termination?

The docs say you have to close the DB handle to clean them up. I'm concerned that if a process is SIGKILLed or just exits abruptly that the temporary DBs will accumulate on disk. What mechanism is used to create the temporary files? If the file is unlinked after open, then process exit is

[sqlite] ATTACH DATABASE statement speed

I think I got it. As often when something is really weird, it is was not what I expected. In the input and output database I had a table of the same name and using: DROP TABLE IF EXISTS WaysNodes; sqlite3 was actually deleting the table of the input db, this was unexpected as I thought that

[sqlite] ATTACH DATABASE statement speed

On 18 Aug 2015, at 11:38am, Paolo Bolzoni wrote: > but after some seconds it drops terribly to less than 10MB/s This, along with some information from your previous posts, all goes to suggest you have a hardware problem of some kind. My guess is that your hard disk is becoming faulty,

[sqlite] ATTACH DATABASE statement speed

On 18 Aug 2015, at 7:30am, Paolo Bolzoni wrote: > Any other idea of what can I try? Perhaps my filesystem is misconfigured? The long time you quote is not standard for SQLite and I don't think anyone can help you solve it by knowing picky details of SQLite. I'm even surprised that it

[sqlite] pragma temp_store_directory is deprecated, what is the alternative?

On Sat, 15 Aug 2015 01:17:28 +0100 Simon Slavin wrote: > > BTW, Posix is almost silent on the question. It says TMPDIR will > > define the location of a temporary store, but not how. > > I'm okay if the documentation simply says something like ... > > For Darwin (Mac), it's always /tmp/ >

[sqlite] order by not working in combination with random()

Simon Slavin wrote: > On 18 Aug 2015, at 1:32am, Simon Davies > wrote: > >> sqlite> SELECT r FROM (SELECT random() AS r FROM myTable) ORDER BY r DESC; >> -6629212185178073901 >> -5293473521544706766 >> 2649466971390864878 >> -6185422953036640443 >> 1855956853707028764 > > Eek. Sorry, I should

[sqlite] ATTACH DATABASE statement speed

On 18 Aug 2015, at 4:14am, Paolo Bolzoni wrote: > In the input and output database I had a table of the same name and using: > > DROP TABLE IF EXISTS WaysNodes; > > sqlite3 was actually deleting the table of the input db, this was > unexpected as I thought that > without any prefix it

[sqlite] order by not working in combination with random()

On 18 Aug 2015, at 1:32am, Simon Davies wrote: > sqlite> SELECT r FROM (SELECT random() AS r FROM myTable) ORDER BY r DESC; > -6629212185178073901 > -5293473521544706766 > 2649466971390864878 > -6185422953036640443 > 1855956853707028764 Eek. Sorry, I should have tried it before posting.

[sqlite] order by not working in combination with random()

On 17 August 2015 at 21:50, Simon Slavin wrote: > > On 17 Aug 2015, at 9:46pm, Jeffrey Mattox wrote: > >> Could the random() be made part of an expression (that doesn't change the >> result) to fool the optimizer into only doing the random() once, like this: >> >> SELECT ( random() *