Re: [sqlite] planning to use sqlite for a project involving huge data
On May 2, 2006 09:42 pm, E Tse wrote: Hi all, I am planing to use sqlite for a project involving huge data. The planned sqlite file (which will contain a single table) will grow to more than 4g in size. Has anyone try that before and what problems have you encountered? Some operating systems only allow files of either 2G or 4G maximum size due to the limitation of file access being limited to 32bits. You may note a 32bit long == 2G file size and a 32bit unsigned long == 4G file size. Take a look at the fseek() or fopen() commands for your operating system, that may help give you an idea if the operating system can handle your request of greater than 4G or not.
Re: [sqlite] planning to use sqlite for a project involving huge data
Wednesday, May 3, 2006, 06:42:34, E Tse wrote: I am planing to use sqlite for a project involving huge data. The planned sqlite file (which will contain a single table) will grow to more than 4g in size. Has anyone try that before and what problems have you encountered? Be prepared, that your planned design might become subject of change. I've a 1 GB file here, initially populated mainly by one big table containing 25 millon records (3 floats, 5 integers). For *my* problem splitting the table in 200 smaller ones caused a big performance gain. All things are relevant in the case of big tables. File system issues (as mentioned), but also index creating (when, if any; for which entities), statement optimizing (the requests themselves as well as the handling of possible huge answer sets from SELECT etc.). Without some knowledge regarding your problem domain I'm afraid no reasonable analysis is possible besides very general remarks. Caring for the latter ones is not difficult, but the solution of your problem lies most of the time in finding a suiting structure. That said, also my splitting was in no way arbitrary, it followed from certain conditions of the problem behind. Micha --
Re: [sqlite] planning to use sqlite for a project involving huge data
On 5/2/06, E Tse [EMAIL PROTECTED] wrote: I wish sqlite can be enhanced to represent itself as a collection of files, See the database 'attach' statement http://sqlite.org/lang_attach.html .
Re: [sqlite] planning to use sqlite for a project involving huge data
Hi Jay, Thanks for your reply. I don't think I can use the attach statement. I thot attach statement can combine different databases, but not on a database with a single table. I think at best it could split a large db file by splitting the tables within the database (if there are multiple tables). I would be nice if sqlite implement the underlying file engine not with a single file, but optionally with a different engine which is implemented by multiple smaller files (by filesize). This might help the underlying OS to manage file caches better, IMO. Thanks, Eric On 5/3/06, Jay Sprenkle [EMAIL PROTECTED] wrote: On 5/2/06, E Tse [EMAIL PROTECTED] wrote: I wish sqlite can be enhanced to represent itself as a collection of files, See the database 'attach' statement http://sqlite.org/lang_attach.html .
[sqlite] planning to use sqlite for a project involving huge data
Hi all, I am planing to use sqlite for a project involving huge data. The planned sqlite file (which will contain a single table) will grow to more than 4g in size. Has anyone try that before and what problems have you encountered? I heard that the initial disk caching time might be a problem, and to solve it we can open the file as a normal binary file and read the file in chunks. can I just do incremental file seeks (instead of actually reading it)? I wish sqlite can be enhanced to represent itself as a collection of files, for example, my_database_1 my_database_2 ... etc Thanks, Eric
