Re: [sqlite] Attach Database and Database pooling

Never mind, there was an error in my code. On 5/26/16, 11:52 AM, "sqlite-users-boun...@mailinglists.sqlite.org on behalf of Michael Hari" wrote: >Hi All, > > > >I’m trying to preallocate about 20 sqlite

[sqlite] Attach Database and Database pooling

Hi All, I’m trying to preallocate about 20 sqlite connections each with the same “ATTACH DB” command. I’m getting “database x already in use” errors. Is this not possible to do? Can each independent X connection not have the same attached DB command? If not, what’s the preferred approach?

[sqlite] ATTACH DATABASE statement speed

On 24 Aug 2015, at 2:48am, Simon Slavin wrote: > My suspicion here is that there's a design fault in ZFS. To correct myself here, what I meant to write was that there was a fault in the implementation of ZFS that Paolo is using, not in the basic design of ZFS itself. Simon.

[sqlite] ATTACH DATABASE statement speed

On 24 Aug 2015, at 2:32am, Roger Binns wrote: > On 08/19/2015 05:56 PM, Paolo Bolzoni wrote: >> I left running the pragma quick check during the night and finished >> in 2 hours and 46 minutes, so it is about 8 times slower than in >> ext4. Zfs is an advanced filesystem plenty of features, but

[sqlite] ATTACH DATABASE statement speed

-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 08/23/2015 06:48 PM, Simon Slavin wrote: > Paolo is using Linux which does not do read-ahead optimization like > some versions of Windows. Therefore if he really is using an SSD > then fragmentation is not an issue. You are confusing things. The

[sqlite] ATTACH DATABASE statement speed

-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 08/19/2015 05:56 PM, Paolo Bolzoni wrote: > I left running the pragma quick check during the night and finished > in 2 hours and 46 minutes, so it is about 8 times slower than in > ext4. Zfs is an advanced filesystem plenty of features, but this >

[sqlite] ATTACH DATABASE statement speed

On Wed, Aug 19, 2015 at 6:53 PM, Simon Slavin wrote: > On 19 Aug 2015, at 10:46am, Paolo Bolzoni > wrote: > Just by itself, the above information is significant to the SQLite team. > Perhaps when you have had a chance to confirm it a new thread can be started > called 'SQLite database on

[sqlite] ATTACH DATABASE statement speed

On Wed, Aug 19, 2015 at 11:44 AM, Simon Slavin wrote: > Hmm. Would it be possible to format an external drive in ZFS and try the > operations on files stored on that ? As you might have guessed from the timezone I am not at home atm, so I do not have spare external disks. However, I do have

[sqlite] ATTACH DATABASE statement speed

I see. Thanks nameless person known as sqlite-mail (npkasm for short), what you say makes sense. However it does not explain why the pragma checks are so slow. Anyhow, npkasm, I will keep in mind for the future. Good point indeed. On Wed, Aug 19, 2015 at 3:59 PM, sqlite-mail wrote: > Hello ! >

[sqlite] ATTACH DATABASE statement speed

> > is there any target date when the preRelease branch gets over to a actual > release? > > Is a really hard show stopper for our development at the moment. We checked > everything for compatibility before merge your current trunk to Visual > Studio 2015 and we forget about the SQLite design

[sqlite] ATTACH DATABASE statement speed

Hi, is there any target date when the preRelease branch gets over to a actual release? Is a really hard show stopper for our development at the moment. We checked everything for compatibility before merge your current trunk to Visual Studio 2015 and we forget about the SQLite design tool. :(

[sqlite] ATTACH DATABASE statement speed

@Simon I tried zpool scrub on both my disks and it returned nothing, I also tried executing stress[1] on the disk and no error appeared in the log or in stress itself. However, coping the sqlite db on an external disk connected via usb3 and formatted with Ntfs actually does the pragma quick_check

[sqlite] ATTACH DATABASE statement speed

On 19 Aug 2015, at 10:46am, Paolo Bolzoni wrote: > As you might have guessed from the timezone I am not at home atm, so I > do not have spare external disks. > However, I do have an expendable 16BG usb stick so I tried on that. > > First I formatted it using zfs and I did the Pragma

[sqlite] ATTACH DATABASE statement speed

Hello ! The problem with foreign keys most of the time is not the the referenced table/field (normally primary key that do no need extra index) but the dependent table/field when they do not have a proper index, any time you update/delete a record on the referenced table a linear scan is

[sqlite] ATTACH DATABASE statement speed

On 19 Aug 2015, at 3:27am, Paolo Bolzoni wrote: > coping the sqlite db on an external disk connected via usb3 > and formatted with Ntfs actually does the pragma quick_check in little > more than 20 seconds and pragma integrity_check in 5 minutes. Those times are completely typical for SQLite.

[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] 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 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] 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] ATTACH DATABASE statement speed

The pragma integrity_check is still running... Maybe my disk sucks for some reason? The output of iostat looks normal to me though.. $ iostat -dmN encplate 1 5 Linux 4.1.4-1-ARCH (slyrogue) 08/17/2015 _x86_64_(8 CPU) Device:tpsMB_read/sMB_wrtn/sMB_read

[sqlite] ATTACH DATABASE statement speed

Dear list, I have a program that materialize the subset of a database in a second database for future use. To do so I create the database object on the OUTPUT database, create tables, create the indexes, and vacuum it just in the case I reused an old file. After I attach the INPUT db and I copy

[sqlite] ATTACH DATABASE statement speed

On 17 August 2015 at 14:52, Paolo Bolzoni wrote: > After I attach the INPUT db and I copy the useful lines. > > The input db is about 13GB, so not really large, however the step on > this sql statement (where ? is of course binded to the db name) > > ATTACH DATABASE ? AS indb; > > requires

[sqlite] ATTACH DATABASE statement speed

On 17 Aug 2015, at 9:22am, Paolo Bolzoni wrote: > The pragma integrity_check is still running... Maybe my disk sucks for > some reason? I wonder whether the hard disk is faulty or the file is on a bad sector. If the other tests show nothing, can you duplicate the input database file ? Do

[sqlite] ATTACH DATABASE statement speed

On 17 Aug 2015, at 8:17am, Simon Slavin wrote: > Also, out of interest, can you run "PRAGMA integrity_check" on all the > database files involved ? Also out of interest, instead of just ATTACHing the input database, 1) Close the output database 2) Reopen the output database 3) ATTACH the

[sqlite] ATTACH DATABASE statement speed

On 17 Aug 2015, at 7:52am, Paolo Bolzoni wrote: > I have a program that materialize the subset of a database in a second > database for future use. > > To do so I create the database object on the OUTPUT database, create > tables, create the indexes, and vacuum it just in the case I reused an

Re: [sqlite] ATTACH DATABASE question

Am Thu, 27 Sep 2012 20:31:36 -0700 schrieb john_prov...@yahoo.com: > I’m confused about how to attach a database file to an existing > database. I open my main database ok, then to attach the second > database, I try to execute the following SQL: > > ATTACH DATABASE

Re: [sqlite] ATTACH DATABASE question

john_prov...@yahoo.com wrote: > I’m confused about how to attach a database file to an existing database. I > open my main database ok, then to attach the second > database, I try to execute the following SQL: > > ATTACH DATABASE ‘c:\mydata\my_attach_database.db’ AS ‘attached_db’ > > Calling

[sqlite] ATTACH DATABASE question

I’m confused about how to attach a database file to an existing database. I open my main database ok, then to attach the second database, I try to execute the following SQL: ATTACH DATABASE ‘c:\mydata\my_attach_database.db’ AS ‘attached_db’ Calling “SQLite3_Prepare_v2” on this statement

Re: [sqlite] ATTACH DATABASE .tables .schema ?

I'm a huge fan of the command line tool. At work we use it extensively when prototyping. We're probably amongst the less technically sophisticated of users, so the ability to quickly script an approach using a few text files is something we value hugely. It's a great way of weaning people off

Re: [sqlite] ATTACH DATABASE .tables .schema ?

On Sun, Jun 12, 2011 at 05:29:32PM -0400, ap scratched on the wall: > When I attach additional DB's with ATTACH DATABASE how can I view the schema > or tables in the (non main) databases? > > > > ATTACH DATABASE 'C:\temp\test.db' AS T > > .schema T > > .tables T > > Produce no results.

[sqlite] ATTACH DATABASE .tables .schema ?

When I attach additional DB's with ATTACH DATABASE how can I view the schema or tables in the (non main) databases? ATTACH DATABASE 'C:\temp\test.db' AS T .schema T .tables T Produce no results. Thanks. ___ sqlite-users mailing list

[sqlite] Attach Database---Insert Into no work

I have a need to create a new SQL Lite database. My only means to do so is with a utility "WinSQL". It connects to any existing database using a DSN set up in the ODBC administrator that is in the control panel. My work flow that I have worked out dies when attempting to copy records from an

Re: [sqlite] Attach database problem

It solves the problem. I put all in one connection, made attach and everything is fine. Thanks Igor, you save me a hours! 2010/11/16 Igor Tandetnik : > Mihailo wrote: >> I have one database1 with dataTable1 and other database2 with dataTable2. >>

Re: [sqlite] Attach database problem

Mihailo wrote: > I have one database1 with dataTable1 and other database2 with dataTable2. > conn1 and conn2. > > conn1->exec("UPDATE dataTable1 set spt_activtrigger = 3 where sim_id > in ( select sim_id from dataTable2 where )); > conn2->exec("UPDATE dataTable2 set

[sqlite] Attach database problem

I have one database1 with dataTable1 and other database2 with dataTable2. conn1 and conn2. conn2->query("attach '".$_SESSION['QPO_dataBase1Path']."' as dst1"); //$_SESSION['QPO_dataBase1Path'] is path to database1, adding database1 to database2 conn1.beginTransaction(); conn2.beginTransaction();

Re: [sqlite] ATTACH DATABASE with connection pooling

On Mon, Jul 19, 2010 at 4:02 PM, Jay A. Kreibich wrote: > On Mon, Jul 19, 2010 at 01:27:52AM -0400, Sam Carleton scratched on the wall: >> Is there any way to use the PRAGMA database_list in a query?  What I would >> like to do is: >> >> SELECT * FROM (PRAGMA database_list) WHERE

Re: [sqlite] ATTACH DATABASE with connection pooling

On Mon, Jul 19, 2010 at 01:27:52AM -0400, Sam Carleton scratched on the wall: > Is there any way to use the PRAGMA database_list in a query? What I would > like to do is: > > SELECT * FROM (PRAGMA database_list) WHERE name = 'EventDB'; One of the virtual tables examples in "Using SQLite"

Re: [sqlite] ATTACH DATABASE with connection pooling

On 19 Jul 2010, at 6:27am, Sam Carleton wrote: > Is there any way to use the PRAGMA database_list in a query? I don't think so. It's not standard SQL, it's a freak of SQLite. Do what you want to do in software. If you're just checking to see if a known database is attached, you could try

Re: [sqlite] ATTACH DATABASE with connection pooling

Is there any way to use the PRAGMA database_list in a query? What I would like to do is: SELECT * FROM (PRAGMA database_list) WHERE name = 'EventDB'; Sam On Sun, Jul 18, 2010 at 11:56 PM, Simon Slavin wrote: > > On 19 Jul 2010, at 4:48am, Sam Carleton wrote: > > > It

Re: [sqlite] ATTACH DATABASE with connection pooling

On 19 Jul 2010, at 4:48am, Sam Carleton wrote: > It would appear that the ATTACH/DETACH DATABASE is connection specific, > correct? (It is logical now that I think about it.) Right. You could have a.db open in two connections. One connection might also ATTACH b.db. That wouldn't do

[sqlite] ATTACH DATABASE with connection pooling

I am working with an Apache module that is using the Apache DBD connection pool system. The application always need to connect to two DB files, the "System DB" and the "Event DB". The System DB is the default, the "Event DB" is always connected via an ATTACH DATABASE called EventDB. The system

Re: [sqlite] ATTACH Database

questions on the SQLite engine. -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Carlton Guc Sent: Monday, June 29, 2009 9:54 PM To: sqlite-users@sqlite.org Subject: [sqlite] ATTACH Database ATTACH database I have a unique problem

[sqlite] ATTACH Database

ATTACH database I have a unique problem where ATTACH database seems to work fine when I'm using a GUI tool, however when I pass the SQL string to the SQLite engine using SqLiteCommand under .NET, it doesn't seem to work.I get an error saying that D2.Table is not found. SQL String: ATTACH

[sqlite] "attach database"

Hallo, found this interesting feature - but I'm not quite sure, how it's working, and manual doesn't contain a good example. Perhaps someone using this feature could give me an example, how should it look like in the case, when I want to: 1. Copy the entire contents of existing database from

[sqlite] ATTACH DATABASE documentation

Hi, Just a helpful suggestion -- the documentation for the ATTACH DATABASE command should indicate that it can't be used within a transaction. This turns out to be true, but unless I missed something, the only way you find out about it is when you run into the error that is generated at runtime.

Re: [sqlite] ATTACH DATABASE how-to

[EMAIL PROTECTED] wrote: Hi all, The situation is: i have two DBs - one in memory and one in the filesystem. I need to fill some tables in the second DB from the fist DB. So, how can I ATTACH DATABASE from memory? (unfortunately ATTACH DATABASE ":memory:" AS mem; fails ...) It works for me.

Re: [sqlite] ATTACH DATABASE how-to

[EMAIL PROTECTED] writes: > The situation is: > i have two DBs - one in memory and one in the filesystem. I > need to fill some tables in the second DB from the fist DB. > So, how can I ATTACH DATABASE from memory? > (unfortunately ATTACH DATABASE ":memory:" AS mem; fails ...) You need to ATTACH

[sqlite] ATTACH DATABASE how-to

Hi all, The situation is: i have two DBs - one in memory and one in the filesystem. I need to fill some tables in the second DB from the fist DB. So, how can I ATTACH DATABASE from memory? (unfortunately ATTACH DATABASE ":memory:" AS mem; fails ...) Regards E. -

[sqlite] Attach database

While trying to attach a readonly database to another one, I get the message « root page number less than 2 “, It reproduces itself almost each time. I precise the database which has to be attached is a readonly one. Do you have some idea ? Thanks a lot, and excuse me for my english.