[sqlite] about attach database
hi,all There are two ways to open a database. 1.sqlite3_open 2.ATTACH DATABASE Because there are so many data base. So we used attach database to open them. But the efficiency of the programming is not ideal. which one is faster? Is the efficiency between the two methods great? best regards. wqg
[sqlite] about attach database
On 12/15/15, ??? <2004wqg2008 at 163.com> wrote: > hi,all > There are two ways to open a database. > 1.sqlite3_open > 2.ATTACH DATABASE > > Because there are so many data base. So we used attach database to open > them. > But the efficiency of the programming is not ideal. > > which one is faster? > Is the efficiency between the two methods great? > I think both methods are about the same speed. Have you measured a difference between them? They both do about the same amount of work, I think. -- D. Richard Hipp drh at sqlite.org
[sqlite] about attach database
I mean only compare the two ways of get the database handl. 1.sqlite3_open 2.ATTACH DATABASE Do not consider the next operation, such as select,update and so on. At 2015-12-16 10:51:31, "Richard Hipp" wrote: >On 12/15/15, ??? <2004wqg2008 at 163.com> wrote: >> hi,all >> There are two ways to open a database. >> 1.sqlite3_open >> 2.ATTACH DATABASE >> >> Because there are so many data base. So we used attach database to open >> them. >> But the efficiency of the programming is not ideal. >> >> which one is faster? >> Is the efficiency between the two methods great? >> > >I think both methods are about the same speed. Have you measured a >difference between them? They both do about the same amount of work, >I think. > >-- >D. Richard Hipp >drh at sqlite.org >___ >sqlite-users mailing list >sqlite-users at mailinglists.sqlite.org >http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] about attach database
On 12/15/15, ??? <2004wqg2008 at 163.com> wrote: >I mean only compare the two ways of get the database handl. > 1.sqlite3_open > 2.ATTACH DATABASE I think they both do about the same amount of work. -- D. Richard Hipp drh at sqlite.org
[sqlite] about attach database
After testing the Sqlite3_open and ATTACH DATABASE, I found that the attach database is slower than sqlite3_open. there is attachment after the mail which includ the speed information ( millisecond ). At 2015-12-16 10:59:27, "Richard Hipp" wrote: >On 12/15/15, ??? <2004wqg2008 at 163.com> wrote: >>I mean only compare the two ways of get the database handl. >> 1.sqlite3_open >> 2.ATTACH DATABASE > >I think they both do about the same amount of work. > >-- >D. Richard Hipp >drh at sqlite.org >___ >sqlite-users mailing list >sqlite-users at mailinglists.sqlite.org >http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] about attach database
You cannot attach to this list. If you can just paste into the body of the email or provide a link to the information *Jim Dodgen* On Tue, Dec 15, 2015 at 9:33 PM, ??? <2004wqg2008 at 163.com> wrote: > > After testing the Sqlite3_open and ATTACH DATABASE, > I found that the attach database is slower than sqlite3_open. > there is attachment after the mail which includ the speed > information ( millisecond ). > > > > At 2015-12-16 10:59:27, "Richard Hipp" wrote: > >On 12/15/15, ??? <2004wqg2008 at 163.com> wrote: > >>I mean only compare the two ways of get the database handl. > >> 1.sqlite3_open > >> 2.ATTACH DATABASE > > > >I think they both do about the same amount of work. > > > >-- > >D. Richard Hipp > >drh at sqlite.org > >___ > >sqlite-users mailing list > >sqlite-users at mailinglists.sqlite.org > >http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > >
[sqlite] about attach database
After testing the Sqlite3_open and ATTACH DATABASE, I found that the attach database is slower than sqlite3_open. there is attachment after the mail which includ the speed information ( millisecond ).
[sqlite] about attach database
??? <2004wqg2008 at 163.com> wrote: > > After testing the Sqlite3_open and ATTACH DATABASE, > I found that the attach database is slower than sqlite3_open. > there is attachment after the mail which includ the speed > information ( millisecond ). Your attachment was discarded (attachment not allowed in this mailing list). Anyway, I remember observing that: - sqlite3_open_v2(...) is lazy. In other words, it does not parse the schema of the DB until the first query is performed after opening the database. - ATTACH is not lazy. The schema is parsed as soon as you attach a database. That could explain the difference in speed. Would there be a way to make ATTACH lazy by the way? Regards Dominique
[sqlite] about attach database
On Tue, Dec 15, 2015 at 11:19 PM, Dominique Pell? wrote: > ??? <2004wqg2008 at 163.com> wrote: > > > > > After testing the Sqlite3_open and ATTACH DATABASE, > > I found that the attach database is slower than sqlite3_open. > > there is attachment after the mail which includ the speed > > information ( millisecond ). > > > Your attachment was discarded (attachment not allowed in this > mailing list). > > Anyway, I remember observing that: > > - sqlite3_open_v2(...) is lazy. In other words, it does not parse the > schema of the DB until the first query is performed after opening > the database. > - ATTACH is not lazy. The schema is parsed as soon as you > attach a database. > > That could explain the difference in speed. > Would there be a way to make ATTACH lazy by the way? > Why would that be of benefit to you? Are you intending to attach a database and never use it? It seems to me the same amount of time will be taken either way. When it comes to opening a database, there may be a need to do some connection specific configuration prior to actually opening the database file and parsing the schema. I believe this is the reason why open defers that processing until later, giving you a chance to finish configuring your connection before locking it down. Once that configuration is complete, there is no advantage to deferring the open of the database. I say no advantage ... maybe I just can't think of one. Why do you think there would be an advantage to deferring the open & schema processing of an attached database? -- Scott Robison
[sqlite] about attach database
Scott Robison wrote: > On Tue, Dec 15, 2015 at 11:19 PM, Dominique Pell? gmail.com >> wrote: > >> ??? <2004wqg2008 at 163.com> wrote: >> >> > >> > After testing the Sqlite3_open and ATTACH DATABASE, >> > I found that the attach database is slower than sqlite3_open. >> > there is attachment after the mail which includ the speed >> > information ( millisecond ). >> >> >> Your attachment was discarded (attachment not allowed in this >> mailing list). >> >> Anyway, I remember observing that: >> >> - sqlite3_open_v2(...) is lazy. In other words, it does not parse the >> schema of the DB until the first query is performed after opening >> the database. >> - ATTACH is not lazy. The schema is parsed as soon as you >> attach a database. >> >> That could explain the difference in speed. >> Would there be a way to make ATTACH lazy by the way? >> > > Why would that be of benefit to you? Are you intending to attach a database > and never use it? It seems to me the same amount of time will be taken > either way. > > When it comes to opening a database, there may be a need to do some > connection specific configuration prior to actually opening the database > file and parsing the schema. I believe this is the reason why open defers > that processing until later, giving you a chance to finish configuring your > connection before locking it down. Once that configuration is complete, > there is no advantage to deferring the open of the database. > > I say no advantage ... maybe I just can't think of one. Why do you think > there would be an advantage to deferring the open & schema processing of an > attached database? Laziness can be useful in some cases. I have an application that opens hundred or so of database connections. Being able to open all connections at start-up is simple. Since it's lazy, it's also fast and does not use memory to store schemas until the databases are actually used later. In my application, queries happen in only few connections after start-up out of all opened connections. For many connections, queries happen much later or sometimes do not even happen. Laziness is thus useful to make start-up fast and simple, without application having to implement laziness itself. I see that the original message from ??? says "Because there are so many database [...]", so it seems to be the same scenario as in my application in which laziness is quite useful. I'm not 100% sure but I'm quite confident that laziness is the explanation for performance discrepancy between sqlite3_open*() and ATTACH. If laziness was useless, why would it then be already implemented for sqlite3_open_v2(...)? Having said all that, reading https://www.sqlite.org/c3ref/open.html I see no mention of the fact that sqlite3_open*() is lazy. Is it documented somewhere? Regards Dominique
[sqlite] about attach database
On 12/16/2015 12:51 PM, ??? wrote: > After testing the Sqlite3_open and ATTACH DATABASE, > I found that the attach database is slower than sqlite3_open. > there is attachment after the mail which includ the speed > information ( millisecond ). Hi, This mailing list strips attachments, so you'll need to include the information inline. One possible explanation: When you run an ATTACH statement, SQLite opens the new database file and reads the schema from the sqlite_master table. Whereas sqlite3_open() just opens the db file (reading the schema is deferred until it is first required in this case). So an apples/apples comparison might be to open/ATTACH the database and then run a simple query that forces SQLite to read the database schema if it has not already - say "SELECT * FROM sqlite_master". Dan.
[sqlite] about attach database
Thanks for everyone. You are right. According to you help, I understand the problem. Just open or attach database , open operation is faster than attach database. if add a query statement after open or attach database. The time which they cost almost the same. Best regards. what Dominique said is right. As following: Anyway, I remember observing that: - sqlite3_open_v2(...) is lazy. In other words, it does not parse the schema of the DB until the first query is performed after opening the database. - ATTACH is not lazy. The schema is parsed as soon as you attach a database. That could explain the difference in speed. Would there be a way to make ATTACH lazy by the way? Regards Dominique At 2015-12-16 18:27:34, "Dan Kennedy" wrote: >On 12/16/2015 12:51 PM, ??? wrote: >> After testing the Sqlite3_open and ATTACH DATABASE, >> I found that the attach database is slower than sqlite3_open. >> there is attachment after the mail which includ the speed >> information ( millisecond ). > >Hi, > >This mailing list strips attachments, so you'll need to include the >information inline. > >One possible explanation: When you run an ATTACH statement, SQLite opens >the new database file and reads the schema from the sqlite_master table. >Whereas sqlite3_open() just opens the db file (reading the schema is >deferred until it is first required in this case). > >So an apples/apples comparison might be to open/ATTACH the database and >then run a simple query that forces SQLite to read the database schema >if it has not already - say "SELECT * FROM sqlite_master". > >Dan. > > >___ >sqlite-users mailing list >sqlite-users at mailinglists.sqlite.org >http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] about attach database
On 16 Dec 2015, at 8:37am, Dominique Pell? wrote: > Having said all that, reading https://www.sqlite.org/c3ref/open.html > I see no mention of the fact that sqlite3_open*() is lazy. > Is it documented somewhere? Not in the official SQLite documentation. But it is easy to prove. Just open a database that doesn't exist. Nothing is done about it until your first write command. Only then are the files created. While discussing lazy it's worth noting that transactions are (by default) lazy too. The default BEGIN is BEGIN DEFERRED. It does nothing to the files and places no locks. Only when a read or write is done inside that transaction does the database get locked. Simon.
[sqlite] about attach database
On Wed, Dec 16, 2015 at 1:37 AM, Dominique Pell? wrote: > Scott Robison wrote: > > > Why would that be of benefit to you? Are you intending to attach a > database > > and never use it? It seems to me the same amount of time will be taken > > either way. > > > > When it comes to opening a database, there may be a need to do some > > connection specific configuration prior to actually opening the database > > file and parsing the schema. I believe this is the reason why open defers > > that processing until later, giving you a chance to finish configuring > your > > connection before locking it down. Once that configuration is complete, > > there is no advantage to deferring the open of the database. > > > > I say no advantage ... maybe I just can't think of one. Why do you think > > there would be an advantage to deferring the open & schema processing of > an > > attached database? > > Laziness can be useful in some cases. I have an application > that opens hundred or so of database connections. Being able to open > all connections at start-up is simple. Since it's lazy, it's also fast and > does > not use memory to store schemas until the databases are actually > used later. In my application, queries happen in only few connections > after start-up out of all opened connections. For many connections, > queries happen much later or sometimes do not even happen. Laziness > is thus useful to make start-up fast and simple, without application having > to implement laziness itself. > > I see that the original message from ??? says "Because there are so > many database [...]", so it seems to be the same scenario as in my > application in which laziness is quite useful. I'm not 100% sure but I'm > quite confident that laziness is the explanation for performance > discrepancy between sqlite3_open*() and ATTACH. > > If laziness was useless, why would it then be already implemented > for sqlite3_open_v2(...)? > As I indicated above, in the case of SQLite, it isn't about lazy. It is about deferring opening the database to give the programmer a chance to do any further configuration of the connection that must be done prior to creating or opening the actual database file and reading / parsing the schema (pragma auto_vaccum, pragma encoding, perhaps sqlite3_db_config, maybe others). Those are operations that may require a connection that has not yet processed a schema. In any other case of 'lazy' loading (which I agree can be a valuable technique and I have used it myself), it can be implemented in your own code. By tracking what databases you've attached and only attaching them on first use, rather than attaching them all in the beginning. -- Scott Robison
