Re: [sqlite] installation problem
Hi arthur I am not a windows expert but I guess that installer checks some key in the windows registry...May be trying to find the values checked and set them manually using regedit ? Regards Jerome --Message d'origine-- De: Artur Ligmann Expéditeur : sqlite-users-boun...@sqlite.org À: sqlite-users@sqlite.org Répondre à: General Discussion of SQLite Database Objet: [sqlite] installation problem Envoyé: 15 sept. 2011 22:21 Hi, i was trying to install system.data.sqlite using the sqlite-netFx40-setup-bundle-x86-2010-1.0.74.0.exe installer but i got an error message telling me the visual studio 2010 sp1 redist can't be installed. Problem is this redist is already installed on my system. The installer stops afterwards What can I do ? regards ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users Envoyé avec BlackBerry® d'Orange ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] In memory strategy guide
Sorry for any inconveniance as I supposed the second strategy works like a charm while the first one fails... Thanks J.MOLIERE - Mentor/J auteur Eyrolles blog: http://romjethoughts.blogspot.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] In memory strategy guide
Hi all, I'm about trying to use SQLite as a pure in memory mode but I am still facing problems, I wrote a small Java program doing this: - opening a connection using the in memory syntax - create a stupid table - insert dummy values - dumping the table to a file - dropping the table - restoring the contents of my dumps - issuing a query on the table my query fails because the table does not exist (that 's what sqlite_master show me) So I guess should rather use such strategy, could you confirm/infirm it ? - connect as in memory - create the table mytable in memory - attach the database as stored in a file : attach database 'mydb' as mydb - import the data using a insert into mytable query using a select * from mydb.mytable - then use data available in memory in the database using select * from mytable PS: I got a previous DNS error from my gmail so I resent the message, I must apologize if you already received this message J.MOLIERE - Mentor/J auteur Eyrolles blog: http://romjethoughts.blogspot.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Fine tuning of Sqlite
2011/9/6 Alexey Pechnikov <pechni...@mobigroup.ru>: > 2011/9/6 jerome moliere <jerome.moli...@gmail.com>: >> Could you give me more details about corruption cases ? >> Is there a list of contexts where we can get corrupted tables ? > > http://www.sqlite.org/howtocorrupt.html Just one word : perfect sorry for not having found it by myself jerome ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Fine tuning of Sqlite
>> Indicies can be very >>> slow... may be the FTS4 table will be much faster for your queries. >> >> can you give more details about this concept ? what is the FTS4 table >> ? I am sorry don't know what is is !!! > > See > http://www.sqlite.org/fts3.html > The full-text index is very fast and scalable. You can use it instead of > a lot of btree-indicies when you can rewrite your search queries as FTS. > Thanks for the pointer ,I did not notice this feature There may be one candidate for such feature but it 's not involved in our current performance problem ... >>> Did you try to use in-memory database as temp storage and >>> copy set of records into main database in single transaction? >> >> it's one of the goal of the benchmark but this solution has av ery >> high level of database corruption isn't it ..so I prefer to keep it as >> a joker... > > Database corruption can't be the result of the coping from temp in-memory > database to main disk-database! Could you give me more details about corruption cases ? Is there a list of contexts where we can get corrupted tables ? Thanks for all your answers regards jerome ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Fine tuning of Sqlite
Hi once again I will answer to these questions (most of them) J.MOLIERE - Mentor/J auteur Eyrolles 2011/9/6 Alexey Pechnikov: > Jerome, can you show your database structure and queries? no definetely not I can't for legal reasons..sorry > Which FS is used? the only one available on Windows Mobile 6.5.. internal flash disk is used for storage because there 's no SD card available on the devices as bought by our customers Did you set corresponding pragmas > page_size, cache_size, journal_mode? page_size yes cache_size no because we are using a higher level cache (in Java) journal_mode is in the list of the pragmas to be tested through the benchmark setting this parameter to OFF speeds up transactions for around 20% Indicies can be very > slow... may be the FTS4 table will be much faster for your queries. can you give more details about this concept ? what is the FTS4 table ? I am sorry don't know what is is !!! > Do you use a prepared queries?.. yes of course we do Which type of storage are you > use? internal flash disk Can you group a set of inserts into single transaction? yes I previously refactored part of this code to do such job, I divided by 4 responses times ... But in the meanwhile customers requested changes and we added other inserts/selects... > Did you try to use in-memory database as temp storage and > copy set of records into main database in single transaction? it's one of the goal of the benchmark but this solution has av ery high level of database corruption isn't it ..so I prefer to keep it as a joker... > There are a lot of questions without answers. > thanks once gain ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Fine tuning of Sqlite
Hi Alexey, I thought I answered to those questions but once again : * my application runs on an embedded device (Honeywell 9900) , it's a very complex application (too much I think) and we are facing performance problems while inserting datas (about 500 objects to be inserted inducing about 2000 queries and the same amount of transactions because the application runs with NON EXCLUSIVE flag, using serializable default isolation level). As any application running on an embedded device it 's not well suited for multi users!!! The application stores data for ONE employee of the company (our customers)... We have indexes (too much I think) but as far as I know in our case indexing is one performance problem rather than a solution (indexing in an INSERT phase adds overheads..) But now my initial problem is solved , it was a problem with Xerial JDBC classes , sqlite manages well group of PRAGMA (where Xerial SQLiteConfig objects don't) I'd like to use this post to thank you because I did not see such a good group for a while , with people reacting quickly and with a lot of value added comments... So thank you guys ,keep this very special Open Source mind alive for a while I just post an entry on my blogger.com page.. For those interested : http://romjethoughts.blogspot.com/2011/09/sqlite-performance-tuning.html kind regards Jerome ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
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 Envoyé avec BlackBerry® d'Orange -Original Message- From: Pavel Ivanov <paiva...@gmail.com> Sender: sqlite-users-boun...@sqlite.org Date: Fri, 2 Sep 2011 10:42:06 To: General Discussion of SQLite Database<sqlite-users@sqlite.org> Reply-To: General Discussion of SQLite Database <sqlite-users@sqlite.org> Subject: 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 know of any incompatible pragmas. And concerning the given example synchronous=true and journal_mode=off is not a nonsense for SQLite. It will happily work exactly as you asked - without journal and using fsyncs. It may be or may be not a nonsense from the application point of view, or it's better to say application can easily break with such settings in certain situations. But it depends on the application and for some of them it could be pretty acceptable. Pavel On Fri, Sep 2, 2011 at 8:26 AM, Simon Slavin <slav...@bigfraud.org> wrote: > > 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 isn't it ? >> Setting up : >> synchronous , read_uncommitted, and so on > > I'm sorry but I can't help with this. I hope someone else reading this can. > >> 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. I do not know of anyone who has made a > table like that. You might be able to work down > > http://www.sqlite.org/pragma.html > > and make part of one yourself, but I could do no better. > > Simon. >___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
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 PRAGMAs isn't it ? >> Setting up : >> synchronous , read_uncommitted, and so on > > I'm sorry but I can't help with this. I hope someone else reading this can. > >> 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. I do not know of anyone who has made a > table like that. You might be able to work down > yes it's exactly what I want I just planned to use ethe Java layer instead of the direct setting of pRAGMA with SQL queries(but it should be the same while reading the code) regards jerome ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
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 <slav...@bigfraud.org>: > > 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 ? inserts are very slow in my context and we are doing a lot of network synchronizations inducing database inserts flash memory and windows mobile are not innocents in our troubles ... > > 2) Must your system be set up for multi-process (or multi-user) access, or > can we ignore all problems concerning those ? > the EXCLUSIVE MODE is well adapted to our case is not set up yet but it 's one ogf the goal of my benchmark to prove that it could have very signifcant influence in our whole performance results > 3) Are you using transactions to batch together data changes which relate to > one-another ? > no application is not designed in this way and I can't refactor it to use such very nice practice > 4) If speed is a problem, do you have indexes defined appropriate to your > commands ? > INSERT are our main problem , we are using a Java cache above the SQL queries so select queries are not our major problem thanks jerome > Simon. > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
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 need to inject different PRAGMAs isn't it ? Setting up : synchronous , read_uncommitted, and so on 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 Thanks jerome J.MOLIERE - Mentor/J auteur Eyrolles 2011/9/2 Simon Slavin <slav...@bigfraud.org>: > > 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 using transactions to batch together data changes which relate to > one-another ? > > 4) If speed is a problem, do you have indexes defined appropriate to your > commands ? > > Simon. > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[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... I wrote an OSGi application used to inject set of configurations and running different tests : inserts, inserts into many transactions, read, may be different threads in the future... It could work fine but it seems that most sets of configurations used induce null connections... Example given I can have connections with SYNCHRONOUS_MODE but I can't turn off JOIURNAL_MODE neither set the SHARED_CACHE and set the READ_UNCOMMITTED isolation level... So I wondered if theer was an (un)official matrix of (un)compatible PRAGMAs... I am using for my benchmark demo the Xerial JDBC driver (layer above org.sqlite JDBC driver) Thanks for your feedback kind regards J.MOLIERE - Mentor/J auteur Eyrolles ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users