Re: [sqlite] How to speed up read-only databases?
Julian Bui <[EMAIL PROTECTED]> writes:
>
> Hey MikeW,
>
> The article you posted seems like something I might want to try. I am
> currently using JDBC to embed sqlite in my java app. Do you know if there
> are equivalent statements for java?
>
> Please let me know if you do.
>
> Thanks,
> Julian
>
Just issue the PRAGMA command(s) like any other SQL commands,
http://www.sqlite.org/pragma.html
presumably with a
connection.createStatement("PRAGMA temp_store = 2") etc.
Regards,
MikeW
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to speed up read-only databases?
Hey MikeW, The article you posted seems like something I might want to try. I am currently using JDBC to embed sqlite in my java app. Do you know if there are equivalent statements for java? Please let me know if you do. Thanks, Julian On Mon, Oct 27, 2008 at 7:58 AM, MikeW <[EMAIL PROTECTED]> wrote: > Christophe Leske <[EMAIL PROTECTED]> writes: > > > How about the cache size? or does this only pertain to databases which > > get inserts? > > > > Christophe Leske > > Speedup tip: > http://article.gmane.org/gmane.comp.db.sqlite.general/41990 > > You may have seen it ! > > MikeW > > ___ > 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] How to speed up read-only databases?
> How many memory has your embedded project? You can create a new > in-memory database and copy there your database data. > That´s what i am currently doing, but we are using too much memory this way, we are out of specs. -- Christophe Leske www.multimedial.de - [EMAIL PROTECTED] http://www.linkedin.com/in/multimedial Lessingstr. 5 - 40227 Duesseldorf - Germany 0211 261 32 12 - 0177 249 70 31 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to speed up read-only databases?
At 13:47 15/10/2008, you wrote: >Hi there, > >i am using a 120MB database in an embedded project (a DVD-ROM project) >and was wondering what I can do to speed up its reading using diverse >PRAGMA statements. >The database is locked, meaning that no data is being inserted or >deleted from it. I am solely after speeding up its reading performance. > >Indices have been set, would augmenting the cache size for Sqlite do >something? How many memory has your embedded project? You can create a new in-memory database and copy there your database data. >Grateful for any info, > >-- >Christophe Leske ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to speed up read-only databases?
> Speedup tip: > http://article.gmane.org/gmane.comp.db.sqlite.general/41990 > Hello Mike, first of all, thank you for your tips. Yes, i saw that posting, and i am already using it in my code. But thanks again, -- Christophe Leske www.multimedial.de - [EMAIL PROTECTED] http://www.linkedin.com/in/multimedial Lessingstr. 5 - 40227 Duesseldorf - Germany 0211 261 32 12 - 0177 249 70 31 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to speed up read-only databases?
Christophe Leske <[EMAIL PROTECTED]> writes: > How about the cache size? or does this only pertain to databases which > get inserts? > > Christophe Leske Speedup tip: http://article.gmane.org/gmane.comp.db.sqlite.general/41990 You may have seen it ! MikeW ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to speed up read-only databases?
Regarding: ... my criteria for a lean and slim SQlite3 db access ... I can use in my DVD-ROM project. I don't know for sure, but I'd suspect that any time saved by using prepared statements would be vastly overwhelmed by even one extra DVD-rom seek. I imagine you VACUUM the database before burning it to disk, right? Setting as large a cache size as practical might help to keep some indices in RAM. If by chance this is reasonable for your application, you could even copy some tables to a database in temporary space on the target system's hard drive, and ATTACH this db to your DVD-rom DB. If there are some long strings (or blobs) in the database, then it may help to define key columns first, and put the lengthier columns last -- or in their own separate tables. Even if you have plenty of space on the DVD rom, there might even been a performance improvement by using a compressed file system so as to reduce seeks. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to speed up read-only databases?
> Or you could have a look at the Perl SQLite functionality: > http://search.cpan.org/~msergeant/DBD-SQLite-0.31/lib/DBD/SQLite.pm > > Yes, Perl is weird initially if you have only (say) written VB !! > Improves the résumé though ... and your ninja status ! > Perl is fine, no problem, but it does not satisfy my criteria for a lean and slim SQlite3 db access (read "command line interpreter") I can use in my DVD-ROM project. -- Christophe Leske www.multimedial.de - [EMAIL PROTECTED] http://www.linkedin.com/in/multimedial Lessingstr. 5 - 40227 Duesseldorf - Germany 0211 261 32 12 - 0177 249 70 31 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to speed up read-only databases?
Christophe Leske <[EMAIL PROTECTED]> writes: > > Yes, but I am using Adobe Director as a production environment. This is > a single threaded application, which also doesn´t allow for threaded > calls to a database. Plus, i got no access to the source code of the > so-called Xtra (=DLL) which emits the call to the DB. > > All i got is an Xtra which spawns a new thread in which the command line > executable is run. I need the thread in order to keep my application > running smoothly which otherwise stalls. > > Regading the pre-recording of statements: can this be achieved somehow > if the parameters of the call change all the time? > Or you could have a look at the Perl SQLite functionality: http://search.cpan.org/~msergeant/DBD-SQLite-0.31/lib/DBD/SQLite.pm Yes, Perl is weird initially if you have only (say) written VB !! Improves the résumé though ... and your ninja status ! Regards, MikeW ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to speed up read-only databases?
Christophe Leske wrote: > John Stanton schrieb: > >>The sqlite3.exe program is set up as a utility and maintenance tool, not >>a production environment and is designed to that end. If you want >>maximum performance it is not the way to go; instead embed the Sqlite >>calls inside your application and optimize access. If you are >>performing ad-hoc DB tasks then it or one of the many similar function >>Ssqlite tools are appropriate. One is the Firefox plug in. >> >>You can imagine that having to compile the SQL for over and over instead >>of storing and re-using the compiled code adds considerably to overhead >>on frequently run jobs. > > Yes, but I am using Adobe Director as a production environment. This is > a single threaded application, which also doesn´t allow for threaded > calls to a database. Plus, i got no access to the source code of the > so-called Xtra (=DLL) which emits the call to the DB. You cab build your own command line interfsce embedding Sqlite. > > All i got is an Xtra which spawns a new thread in which the command line > executable is run. I need the thread in order to keep my application > running smoothly which otherwise stalls. > > Regading the pre-recording of statements: can this be achieved somehow > if the parameters of the call change all the time? This is where sqlite_bind functions. > > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to speed up read-only databases?
John Stanton schrieb: > The sqlite3.exe program is set up as a utility and maintenance tool, not > a production environment and is designed to that end. If you want > maximum performance it is not the way to go; instead embed the Sqlite > calls inside your application and optimize access. If you are > performing ad-hoc DB tasks then it or one of the many similar function > Ssqlite tools are appropriate. One is the Firefox plug in. > > You can imagine that having to compile the SQL for over and over instead > of storing and re-using the compiled code adds considerably to overhead > on frequently run jobs. Yes, but I am using Adobe Director as a production environment. This is a single threaded application, which also doesn´t allow for threaded calls to a database. Plus, i got no access to the source code of the so-called Xtra (=DLL) which emits the call to the DB. All i got is an Xtra which spawns a new thread in which the command line executable is run. I need the thread in order to keep my application running smoothly which otherwise stalls. Regading the pre-recording of statements: can this be achieved somehow if the parameters of the call change all the time? -- Christophe Leske www.multimedial.de - [EMAIL PROTECTED] http://www.linkedin.com/in/multimedial Lessingstr. 5 - 40227 Duesseldorf - Germany 0211 261 32 12 - 0177 249 70 31 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to speed up read-only databases?
The sqlite3.exe program is set up as a utility and maintenance tool, not a production environment and is designed to that end. If you want maximum performance it is not the way to go; instead embed the Sqlite calls inside your application and optimize access. If you are performing ad-hoc DB tasks then it or one of the many similar function Ssqlite tools are appropriate. One is the Firefox plug in. You can imagine that having to compile the SQL for over and over instead of storing and re-using the compiled code adds considerably to overhead on frequently run jobs.. JS Christophe Leske wrote: > John, > > thanks for your suggestions, but i am wondering if any of your > suggestions can be applied to the sqlite3.exe command line application? > > > >>Prepare your statements only once and then use bind. Do not use >> > > How is this done? Can the command line executable be modified in such a way? > > >>sqlite3_exec. Do not open and close the DB for each read, instead open >>once and let the cache work. > > That´s already done. > >>Avoid row scans by defining indices. > > Already there. > >> Use >>the new index selection functionality to force the use of the best >>index. > > Would you care to provide more information about this? A simple link > would be enough. > > >>Place large and less frequently accessed columns at the end of >>the Sqlite rows. >> > > Ok, thanks. Will do. However, i read out the whole row all the time. > BTW, does it help to specify which coloumns i would like instead of all? > I would like to get all minus one coloumn in general. > >>When you obey these rules you will get very good read perfprmance from >>Sqlite. The cacheing is important if you are using a slow disk or flash >>memory. Look at shared cache mode if you have multiple users > > Nope, just one from DVD. > > Thanks again, > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to speed up read-only databases?
John, thanks for your suggestions, but i am wondering if any of your suggestions can be applied to the sqlite3.exe command line application? > Prepare your statements only once and then use bind. Do not use > How is this done? Can the command line executable be modified in such a way? > sqlite3_exec. Do not open and close the DB for each read, instead open > once and let the cache work. That´s already done. > Avoid row scans by defining indices. Already there. > Use > the new index selection functionality to force the use of the best > index. Would you care to provide more information about this? A simple link would be enough. > Place large and less frequently accessed columns at the end of > the Sqlite rows. > Ok, thanks. Will do. However, i read out the whole row all the time. BTW, does it help to specify which coloumns i would like instead of all? I would like to get all minus one coloumn in general. > When you obey these rules you will get very good read perfprmance from > Sqlite. The cacheing is important if you are using a slow disk or flash > memory. Look at shared cache mode if you have multiple users Nope, just one from DVD. Thanks again, -- Christophe Leske www.multimedial.de - [EMAIL PROTECTED] http://www.linkedin.com/in/multimedial Lessingstr. 5 - 40227 Duesseldorf - Germany 0211 261 32 12 - 0177 249 70 31 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to speed up read-only databases?
Prepare your statements only once and then use bind. Do not use sqlite3_exec. Do not open and close the DB for each read, instead open once and let the cache work. Avoid row scans by defining indices. Use the new index selection functionality to force the use of the best index. Place large and less frequently accessed columns at the end of the Sqlite rows. When you obey these rules you will get very good read perfprmance from Sqlite. The cacheing is important if you are using a slow disk or flash memory. Look at shared cache mode if you have multiple users. JS MikeW wrote: > Christophe Leske <[EMAIL PROTECTED]> writes: > > >>Hi there, >> >>i am using a 120MB database in an embedded project (a DVD-ROM project) >>and was wondering what I can do to speed up its reading using diverse >>PRAGMA statements. >>The database is locked, meaning that no data is being inserted or >>deleted from it. I am solely after speeding up its reading performance. >> >>Indices have been set, would augmenting the cache size for Sqlite do >>something? >> >>Grateful for any info, >> > > Did you say that the PRAMGA locking_mode=EXCLUSIVE was set ? > The doc suggests this speeds things up ... > > You are not clear about which aspect is slow - are you already using > sqlite3_bind_x() and placeholders (?) in your SQL statements ? > Doing a sqlite3_prepare every time you do a query can slow things > down. > Sorry if you are already using all code-level best practise !! > > MikeW > > > > ___ > 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] How to speed up read-only databases?
> When you say 'sqlite.exe' I presume you are referring to 'sqlite3.exe' ? > (http://www.sqlite.org/sqlite.html) > Yes. And I am using v3.6.4. > Using the correct INDEX can speed queries up vastly, so if you can > identify how you are accessing the data, and then set that/those > columns as INDEXed, that will help ... > Have done that. It almost doubles my database, but it is worth it. How about the cache size? or does this only pertain to databases which get inserts? > (Techie note > http://20bits.com/2008/05/13/interview-questions-database-indexes/) > Christophe Leske www.multimedial.de - [EMAIL PROTECTED] http://www.linkedin.com/in/multimedial Lessingstr. 5 - 40227 Duesseldorf - Germany 0211 261 32 12 - 0177 249 70 31 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to speed up read-only databases?
<[EMAIL PROTECTED]> writes: > > Hello Mike, > > Did you say that the PRAMGA locking_mode=EXCLUSIVE was set ? > The doc suggests this speeds things up ... > > I have that set, yes. > > You are not clear about which aspect is slow - are you already using > sqlite3_bind_x() and placeholders (?) in your SQL statements ? > Doing a sqlite3_prepare every time you do a query can slow things > down. > > I am using the sqlite.exe command line executable in a separate thread to query my database. I don't have > code level control over the access to it. > > I was thus wondering if there is anything i can do on a query level (as input to the command line executable) to > speed things up. > > Any help is much appreciated, > > christophe Leske > When you say 'sqlite.exe' I presume you are referring to 'sqlite3.exe' ? (http://www.sqlite.org/sqlite.html) If not, update your software ! Using the correct INDEX can speed queries up vastly, so if you can identify how you are accessing the data, and then set that/those columns as INDEXed, that will help ... http://www.sqlite.org/lang_createindex.html Best choice for index could be a) field that directly identifies record, eg. serial number b) field that allows quick rejection of most non-matching records, reducing amount of data to search by other methods, eg. surname Of course, creating an index uses more space in the DB, so there is always a tradeoff. (Techie note http://20bits.com/2008/05/13/interview-questions-database-indexes/) MikeW ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to speed up read-only databases?
Hello Mike, Did you say that the PRAMGA locking_mode=EXCLUSIVE was set ? The doc suggests this speeds things up ... I have that set, yes. You are not clear about which aspect is slow - are you already using sqlite3_bind_x() and placeholders (?) in your SQL statements ? Doing a sqlite3_prepare every time you do a query can slow things down. I am using the sqlite.exe command line executable in a separate thread to query my database. I don't have code level control over the access to it. I was thus wondering if there is anything i can do on a query level (as input to the command line executable) to speed things up. Any help is much appreciated, christophe Leske ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to speed up read-only databases?
Christophe Leske <[EMAIL PROTECTED]> writes: > > Hi there, > > i am using a 120MB database in an embedded project (a DVD-ROM project) > and was wondering what I can do to speed up its reading using diverse > PRAGMA statements. > The database is locked, meaning that no data is being inserted or > deleted from it. I am solely after speeding up its reading performance. > > Indices have been set, would augmenting the cache size for Sqlite do > something? > > Grateful for any info, > Did you say that the PRAMGA locking_mode=EXCLUSIVE was set ? The doc suggests this speeds things up ... You are not clear about which aspect is slow - are you already using sqlite3_bind_x() and placeholders (?) in your SQL statements ? Doing a sqlite3_prepare every time you do a query can slow things down. Sorry if you are already using all code-level best practise !! MikeW ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to speed up read-only databases?
That's a tricky one. Databases like random access, but DVD-ROM not so much :) Maybe delete indexes to force a full table scan? On Wed, Oct 15, 2008 at 5:47 AM, Christophe Leske <[EMAIL PROTECTED]> wrote: > Hi there, > > i am using a 120MB database in an embedded project (a DVD-ROM project) > and was wondering what I can do to speed up its reading using diverse > PRAGMA statements. > The database is locked, meaning that no data is being inserted or > deleted from it. I am solely after speeding up its reading performance. > > Indices have been set, would augmenting the cache size for Sqlite do > something? > > Grateful for any info, > > -- > Christophe Leske -- Cory Nelson http://www.int64.org ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
