On Apr 18, 2004, at 7:31 PM, Greg Obleshchuk wrote:
Hi Richard,
You know that is the first clear and concise explanation of why not to store large blobs in a database that I have heard anywhere.
Indeed. But I wonder if most all databases do it the same way? Or do all file-based dbs do it the same way? etc.
Nice explanation though. I am currently working on a web-based image gallery application powered by Oracle and am storing file names in the db while the images themselves are stored on the disk. Some said why don't I just stick the images in the db and I said no... the OS would be better and more flexible at managing the physical files. Now I have a more scientific sounding answer to back my assertion.
;-)
Greg ----- Original Message ----- From: D. Richard Hipp Cc: [EMAIL PROTECTED] Sent: Monday, April 19, 2004 9:50 AM Subject: Re: [sqlite] row size limit
[EMAIL PROTECTED] wrote:According to the FAQ on sqlite.org, the row size is arbitrarily limited to 1MB, which can be increased to 16MB by changing a #define in the source code.
My question is, why even limit the row size? Is there a way the code can modified so that there is no limit for the row size (other than the available disk/memory space)?
The current file format allocates 24 bits for storing the number of
bytes in a particular row. (See http://www.sqlite.org/fileformat.html
for details.) So the currect version of SQLite will never allow more
than 16MB in one row. The proposed version 3.0.0 of SQLite uses a
variable-length integer to store the row size and can thus accomodate
up to 2^64 bytes in a single row. In theory.
But a limit of 1MB will probably still be enforced. Why is this?
SQLite stores large rows by breaking the data up into 1K chunks and storing each chunk in a separate page of the database. Filesystems do much the same thing in an operating system. But filesystems have an advantage over databases in that they can access the many chunks that make up a file in parallel, whereas SQLite has to access them one at a time.
Suppose you have a 1MB row in SQLite and you want to read the whole thing. SQLite must first ask for the 1st 1K page and wait for it to be retrieved. Then it asks for the 2nd 1K page and waits for it. And so forth for all 1000+ pages. If each page retrieval requires 1 rotation of the disk, that's about 8.5 seconds on a 7200RPM disk drive.
The filesystem code inside the operating system can do the same task
in parallel. If you ask the operating system to read in all of a
1MB file for you, it can request many separate blocks from the disk
controller at once. The blocks might arrive out of order, but the
OS can reassemble them into the correct order before returning the
result up to the user-space process. Using this approach, only a few
rotations of the disk platter would be required to retrieve a 1MB
file, instead of thousands. The retrival will be 100s of times faster.
The moral of the story: If you have large amounts of data you want to store, it is best to store that data in a separate file and then write the name of that file into the database. -- D. Richard Hipp -- [EMAIL PROTECTED] -- 704.948.4565
--------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
--------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
