>>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.
I guess it would depend on the system. I assume (and may ask) that MS SQL and Oracle use multi-threaded processes to access the information and that is the way they get around it. I know the MS is looking at replacing the file system with the SQL engine in Longhorn so they must have solved the issue. The speed that they can generate from queries would indicate that they have solved the issue. But perhaps the smaller systems PostgreSQL and/or MySQL don't. Greg ----- Original Message ----- From: Puneet Kishor To: SQLite Sent: Monday, April 19, 2004 10:41 AM Subject: Re: [sqlite] row size limit 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]
