Re: [sqlite] PRAGMA user_version

On 9/2/2011 11:02 PM, Walter wrote: Is there any way to get the user_version from an Attached database PRAGMA attachedName.user_version; -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org

[sqlite] PRAGMA user_version

Hi All Is there any way to get the user_version from an Attached database PRAGMA user_version only get it from the Opened database. If not perhaps a request for it. Thank you Walter ___ sqlite-users mailing list sqlite-users@sqlite.org

Re: [sqlite] sqlite3_open_v2 performance degrades as number of opens increase

On 2 Sep 2011, at 6:34pm, Terry Cumaranatunge wrote: > This does suggest that we should re-evaluate our design and not open as many > databases from a single process. The original motivation was to limit the > size the database for performance reasons, which resulted in a large number > of

Re: [sqlite] sqlite3_open_v2 performance degrades as number of opens increase

Thanks everyone for your responses.   We modified the sqlite findReusableFd function call to return 0 immediately (i.e. no file descriptor to reuse) and it significantly improved the performance. The time to open 20K db's went down from 75 secs to 24 secs. The modification to findReusableFd 

Re: [sqlite] Track DML (from certain clients only)

On 02 Sep 2011 at 16:58, Stephan Beal wrote: > On Fri, Sep 2, 2011 at 5:32 PM, Frans Knibbe wrote: > >> If SQLite could log this statement including the comment, it would still be >> possible to distinguish sources. Or reject the logging of

Re: [sqlite] Speeding up index creation

On 09/02/2011 07:32 PM, Filip Navara wrote: On Fri, Sep 2, 2011 at 11:04 AM, Filip Navara wrote: *snip* The time to create an index on my 266 Mb experimental database is more than 9 minutes. *snip* I erroneously measured the time with DEBUG build, so I've rerun the

Re: [sqlite] Speeding up index creation

On 09/02/2011 09:24 PM, Simon Slavin wrote: On 2 Sep 2011, at 10:04am, Filip Navara wrote: The time to create an index on my 266 Mb experimental database is more than 9 minutes. The database is available at http://www.emclient.com/temp/mail_index.zip and the command I use to create the index

Re: [sqlite] Track DML (from certain clients only)

On Fri, Sep 2, 2011 at 5:32 PM, Frans Knibbe wrote: > If SQLite could log this statement including the comment, it would still be > possible to distinguish sources. Or reject the logging of statement with a > trigger similar to the one you proposed.. > Why not just write

Re: [sqlite] Track DML (from certain clients only)

You will, of course, be slowing down your app to do this but if all you're wanting to do is log SQL statements then sqlite3_profile sounds like the way to go (although it is subject to change). All you would do is open your own database connection inside your callback and insert the SQL

Re: [sqlite] Speeding up index creation

On 2 Sep 2011, at 4:38pm, Filip Navara wrote: > With SQLite 3.7.7 and older the index creation takes eons since the > file is in WAL mode and the journal file grows uncontrollably. Yeah. It was 5.1Gig, and the underlying database was only 266Meg. Heh. Simon.

Re: [sqlite] Speeding up index creation

On Fri, Sep 2, 2011 at 5:23 PM, Simon Slavin wrote: > > On 2 Sep 2011, at 3:24pm, Simon Slavin wrote: > >> On 2 Sep 2011, at 10:04am, Filip Navara wrote: >> >>> The time to create an index on my 266 Mb experimental database is more >>> than 9 minutes. The database is

Re: [sqlite] Track DML (from certain clients only)

Hello Michael, I am sorry for being unclear, I will try to explain: What would work is each application writing its own DML to a trace table. For example, Application A might do this (in pseudocode): SqlStatement = "insert into tab values (1, 'hello')"; SqlLite.Execute(SqlStatement);

Re: [sqlite] Speeding up index creation

On 2 Sep 2011, at 3:24pm, Simon Slavin wrote: > On 2 Sep 2011, at 10:04am, Filip Navara wrote: > >> The time to create an index on my 266 Mb experimental database is more >> than 9 minutes. The database is available at >> http://www.emclient.com/temp/mail_index.zip and the command I use to >>

Re: [sqlite] Fine tuning of Sqlite

Thanks for your reply Pavel.I hope that the provided example has some sense for sqlite but with the version used and from the java layer I was not able to grab connections with such config...removing the journal mode off in my code and i get an usable connection Regards Jerome

Re: [sqlite] Track DML (from certain clients only)

Maybe I don't understand your setup completely...but can't you have your applications send a string identifying who they are? Like the 1st argument to whatever function you're setting up? I don't think sqlite3_trace is going to let you stop or modify the SQL insert or suchall you could

Re: [sqlite] Fine tuning of Sqlite

>> Do you set of PRGAMAS uncompatible ? e;g: >> setting synchronous=true + journal_mode=off is a non sense for sqlite >> so engine can't deliver connections > > I think I now see what you want: a table saying if you have PRAGMA A set to > THIS, then PRAGMA B has no effect. Personally I don't

Re: [sqlite] sqlite3_open_v2 performance degrades as number of opens increase

On Fri, Sep 02, 2011 at 06:30:57AM -0500, Terry Cumaranatunge scratched on the wall: > Hello, > > We have an application that creates many small databases (over 100K) to be > able to control the size of the database and provide more deterministic > performance. At process startup, it opens many

Re: [sqlite] sqlite3_open_v2 performance degrades as number of opens increase

> Does anyone know why the performance degrades this way and what can be done > to resolve the problem? Do you have by any chance shared cache turned on? I believe when shared cache is used SQLite searches through all open databases to understand if the one you want to open is already opened and

Re: [sqlite] Speeding up index creation

On 2 Sep 2011, at 10:04am, Filip Navara wrote: > The time to create an index on my 266 Mb experimental database is more > than 9 minutes. The database is available at > http://www.emclient.com/temp/mail_index.zip and the command I use to > create the index is > > CREATE INDEX

Re: [sqlite] Track DML (from certain clients only)

Hello Michael, Thanks for the quick response. Yes, I could add a field to put the source string in. But I would not like to bother applications with having to insert rows in the DML table. I would prefer that to happen automatically because I want to prevent entanglement of separate modules.

Re: [sqlite] sqlite3_open_v2 performance degrades as number of opens increase

I profiled the sqlite3 test program below and I get this for 25,000 files Flat profile: Each sample counts as 0.01 seconds. % cumulative self self total time seconds secondscalls ms/call ms/call name 61.15 17.6117.6125000 0.70 0.70

Re: [sqlite] Fine tuning of Sqlite

Hi Simon, thanks for your answer comments below >> I want to setup a benchmark injecting different configurations of the >> sqlite engine and doing different queries (insert/select ) into >> different transactional contexts over dummy data.. >> For such job I need to inject different

Re: [sqlite] sqlite3_open_v2 performance degrades as number of opens increase

I assume you've overridden the system default for 1024 files in ulimit for # of open files? I don't see the times you're seeing using this program...though my databases are empty which probably makes a difference. I do see the gradual increase in time...I think this is almost all due to the OS

Re: [sqlite] sqlite3_open_v2 performance degrades as number of opens increase

My first thought would be to check the amount of memory being used by your many connections. Each connection will consume a non-trivial amount of resources (page cache, file handles, OS file cache, etc.) It's certainly plausible that your overall system performance is reduced as you run out of

Re: [sqlite] sqlite3_open_v2 performance degrades as number of opens increase

On 2 Sep 2011, at 12:30pm, Terry Cumaranatunge wrote: > We have an application that creates many small databases (over 100K) to be > able to control the size of the database and provide more deterministic > performance. At process startup, it opens many of these databases in a loop > to keep

Re: [sqlite] Speeding up index creation

On Fri, Sep 2, 2011 at 11:04 AM, Filip Navara wrote: *snip* > The time to create an index on my 266 Mb experimental database is more > than 9 minutes. *snip* I erroneously measured the time with DEBUG build, so I've rerun the experiment with several SQLite versions:

Re: [sqlite] Fine tuning of Sqlite

On 2 Sep 2011, at 8:08am, jerome moliere wrote: > I want to setup a benchmark injecting different configurations of the > sqlite engine and doing different queries (insert/select ) into > different transactional contexts over dummy data.. > For such job I need to inject different PRAGMAs

[sqlite] sqlite3_open_v2 performance degrades as number of opens increase

Hello, We have an application that creates many small databases (over 100K) to be able to control the size of the database and provide more deterministic performance. At process startup, it opens many of these databases in a loop to keep them open for faster transaction response times. The

Re: [sqlite] Track DML (from certain clients only)

If you can add a field that you can put a source string in you can do this: create table dml (dmlstuff text, source text); create trigger before insert on dml for each row when new.source not like 'p2p' begin select raise(rollback,'Not p2p source'); end; sqlite> insert into dml

[sqlite] Track DML (from certain clients only)

Hello all, I am trying to use SQLite as a data store for a P2P (peer to peer) system. There could be several applications reading from and writing to the database. One of these is the P2P module. It reads local additions to the database (only INSERTs are allowed, rows are never DELETEd or

[sqlite] Speeding up index creation

Hi, I'm experimenting with the new code in SQLite's trunk and measuring the improvements in index creation. The I/O required to create index and also the disk space requirements has reduced significantly, so the CREATE INDEX is now dominated by CPU time. Almost no memory is used for cache though,

Re: [sqlite] Fine tuning of Sqlite

I'll give you some elements regarding your questions but there 's no immediate link with my current problem... 2011/9/2 Simon Slavin : > > On 2 Sep 2011, at 7:39am, jerome moliere wrote: > >> I must give some real clues to my customers to fine tune Sqlite > > 1) What

Re: [sqlite] Fine tuning of Sqlite

Hi Simon, thanks for your reply but try to forget the general introduction of my context I want to setup a benchmark injecting different configurations of the sqlite engine and doing different queries (insert/select ) into different transactional contexts over dummy data.. For such job I

Re: [sqlite] Fine tuning of Sqlite

On 2 Sep 2011, at 7:39am, jerome moliere wrote: > I must give some real clues to my customers to fine tune Sqlite 1) What problem(s) are you trying to solve ? 2) Must your system be set up for multi-process (or multi-user) access, or can we ignore all problems concerning those ? 3) Are you

[sqlite] Fine tuning of Sqlite

Hi all sqlite users, I'm using Sqlite for an application using 150 Mb databases running on an embedded device (Honeywell Dolphin 9900) into an OSGi Java context. I must give some real clues to my customers to fine tune Sqlite, so I discovered among different ways to have better results PRAGMAs...