Re: [sqlite] hoe to create index to a big table - resolution
Hi Thanks Gerd and Dan and Ryan. I received answers that helped me understand and solve my problem by redirecting temp data to a directory rather than in-memory. I tried Dan's proposal to SETENV TMPDIR and afterwards I could successfully create index. I will try Gerd's pragma proposal as well Thanks Rinat -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of GB Sent: Monday, June 30, 2014 9:15 PM To: sqlite-users@sqlite.org Subject: Re: [sqlite] hoe to create index to a big table? Do you possibly have redirected temporary storage to memory, either by compile switch or #pragma temp_store? If so, try explicitly setting #pragma temp_store=1, this will force temporary data to be stored on disk. Gerd. Hadashi, Rinat schrieb am 30.06.2014 10:30: > I work with a very big table, on Linux. > I fail to create index getting the following error: > SQL error near line 1: database or disk is full > > Any advice? > > Rinat Hadashi > > > - > Intel Israel (74) Limited > > This e-mail and any attachments may contain confidential material for > the sole use of the intended recipient(s). Any review or distribution > by others is strictly prohibited. If you are not the intended > recipient, please contact the sender and delete all copies. > ___ > 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 - Intel Israel (74) Limited This e-mail and any attachments may contain confidential material for the sole use of the intended recipient(s). Any review or distribution by others is strictly prohibited. If you are not the intended recipient, please contact the sender and delete all copies. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] hoe to create index to a big table?
I work with a very big table, on Linux. I fail to create index getting the following error: SQL error near line 1: database or disk is full Any advice? Rinat Hadashi - Intel Israel (74) Limited This e-mail and any attachments may contain confidential material for the sole use of the intended recipient(s). Any review or distribution by others is strictly prohibited. If you are not the intended recipient, please contact the sender and delete all copies. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Where can I find JDBC for 3.8.2 or later?
I am looking for a JAVA sqlite JDBC version 3.8.2 or later, for Linux Rinat Hadashi - Intel Israel (74) Limited This e-mail and any attachments may contain confidential material for the sole use of the intended recipient(s). Any review or distribution by others is strictly prohibited. If you are not the intended recipient, please contact the sender and delete all copies. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Reading compressed database files
Fixed data. I write the databases once in a different flow, and then I only read them. -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Simon Slavin Sent: Wednesday, May 28, 2014 4:18 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] Reading compressed database files On 28 May 2014, at 8:25am, Hadashi, Rinat <rinat.hada...@intel.com> wrote: > My databases are very big (almost 100 GB). > I am looking for a compression solution. Are these databases with fixed data which can be opened read-only ? Or do you have to be able to make changes to them ? It makes a huge difference in how easy it is to do this. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users - Intel Israel (74) Limited This e-mail and any attachments may contain confidential material for the sole use of the intended recipient(s). Any review or distribution by others is strictly prohibited. If you are not the intended recipient, please contact the sender and delete all copies. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Reading compressed database files
Hi Roger Where can I learn how to characterize my database? I work read-only, single connection. I have 13 tables, of which 2 are huge, 2 are medium and the rest are very small. My huge tables have 3 columns: numeric, numeric and varchar with millions of rows. I keep an index on the numeric columns. Does that bring up any column ordering suggestion? Thanks Rinat -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Roger Binns Sent: Wednesday, May 28, 2014 12:06 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] Reading compressed database files -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 28/05/14 00:25, Hadashi, Rinat wrote: > My databases are very big (almost 100 GB). I am looking for a > compression solution. > > Did anyone have an experience with reading a compressed database? It would be helpful if you characterise your data and queries. For example if the size is due to blobs, then careful ordering of columns, or moving them to separate tables will likely be very useful. You can get compression external to SQLite by using a compressing filesystem like NTFS or btrfs (make sure to pick an appropriate page size), or by internal compression with cerod: http://www.hwaci.com/sw/sqlite/cerod.html Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1 iEYEARECAAYFAlOFpvsACgkQmOOfHg372QRSRACfcDqTprcD//n9yYXcGPl9yQfo sTIAoLkIaQHR4JAwk1LbuRzCyQsx/5aN =tYeT -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users - Intel Israel (74) Limited This e-mail and any attachments may contain confidential material for the sole use of the intended recipient(s). Any review or distribution by others is strictly prohibited. If you are not the intended recipient, please contact the sender and delete all copies. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Reading compressed database files
Hi My databases are very big (almost 100 GB). I am looking for a compression solution. Did anyone have an experience with reading a compressed database? What was the degradation in performance of queries? Thanks Rinat Hadashi - Intel Israel (74) Limited This e-mail and any attachments may contain confidential material for the sole use of the intended recipient(s). Any review or distribution by others is strictly prohibited. If you are not the intended recipient, please contact the sender and delete all copies. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Load time performance
Hi, I work in READ ONLY mode. My application connects the DB only once, at the beginning. I can't really work with local files. (I log to any machine and get my files from the network.) Perhaps there are some intermediate files generated in the first load that I can prepare in advance? Rinat -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Stephen Chrzanowski Sent: Thursday, May 22, 2014 4:05 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] Load time performance You shouldn't access a SQLite database remotely, except maybe for read only access, but that could come under fire. Any network file action can't guarantee that write locks happen. If you attempt to access a file in READ-ONLY mode, you'll be greeted with a 1-5 second delay (I don't remember what the delay is right now) per connection. If you're using one connection to the DB, then you'll experience the delay. If you've got one connection going for the life of your application, you'll get hit with the first delay, but all subsequent queries will work. On Thu, May 22, 2014 at 8:12 AM, Hadashi, Rinat <rinat.hada...@intel.com>wrote: > Hi > > The first time I access a database takes significantly more time than > subsequent accesses. > I am looking for ideas to shorten the time required for the first access. > > I work on Linux, my db. file is "somewhere" in the file system, not > locally on the machine from which I am running sqlite3 > > Thanks > > Rinat Hadashi > > - Intel Israel (74) Limited This e-mail and any attachments may contain confidential material for the sole use of the intended recipient(s). Any review or distribution by others is strictly prohibited. If you are not the intended recipient, please contact the sender and delete all copies. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Load time performance
Hi The first time I access a database takes significantly more time than subsequent accesses. I am looking for ideas to shorten the time required for the first access. I work on Linux, my db. file is "somewhere" in the file system, not locally on the machine from which I am running sqlite3 Thanks Rinat Hadashi - Intel Israel (74) Limited This e-mail and any attachments may contain confidential material for the sole use of the intended recipient(s). Any review or distribution by others is strictly prohibited. If you are not the intended recipient, please contact the sender and delete all copies. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
