Re: Random Access Files in databases


"Chris Berg" <spam.spam.eggs@xxxxxxx> wrote in message
news:7m4dm1pd5afcv1l72gnq60pnbuuk1kn4ud@xxxxxxxxxx
>I am writing an application that maintains a lot of binary files. Each
> file has to be accessed randomly, that is, the file pointer slides
> back and forth to get data, and occasionally data is appended to the
> end of the file or data is overwritten in the middle. There can be
> several hundred files open at the same time, accessed by concurrent
> threads. (Q: "What on earth is he up to now?" A: Well, it is a sort of
> server application, but it is too complicated to go into detail. Bear
> with me).
>
> Now, I wonder if I can replace all the data files with file entries in
> a database. I am not very experienced in programming databases, and I
> am certainly a novice in evaluating in advance what the performance
> implications may be.

I am not a Database design guru either, but it might be wise to break
the files into individual records and store each record as a row in a table,
rather than making the whole file be a row in a table.

In other words, are you really moving around the file on a byte by byte
basis, or is it more like you want to get a String, so you move to a
location and read in in the string, and then you want to get an integer, so
you move to a different location and read in the integer, etc.?

Aren't there natural boundaries that you can break up the files into?

Anyway, here's the answers to your question (to the best of my
knowledge) but I think you might be approaching this the wrong way (though
it's hard to say without knowing more about what the application does).

> 1) Can I make random access to the data in a database file the same
> way as in a disk file?

I'm pretty sure the SQL language does have ways to seek around in BLOB
data values. They might be treating the BLOBs like strings though, and using
"substring-like syntax" to do this seeking.

> 2) - or do I have to read the whole file entry into ram before
> accessing it?

I think some underlying SQL implementations may do this as part of the
query described above.

> 3) Can I do it concurrently with several hundred files?

Quite probably, some servers have limits on number of connections that
can be open at a time.

> 4) Is MySQL a good choice?

The documentation is relatively good and the community is decent, though
some SQL purist are quick to point out that MySQL doesn't implement the SQL
standard properly.

> 5) I reckon it will be somewhat slower, but how much?

You'll have to benchmark it yourself. Anything you read here will
probably be wild guesses, especially since we have no idea what your program
does.

- Oliver


.

Relevant Pages

  • Re: return multiple rows from sql statement
    ... strings from input values is almost certainly a safe path to SQL ... Also, being a MySQL function, it knows what MySQL needs or uses. ... All characters that are entered in the fields make their way into the database unaltered. ... The insert of what surprisinlgly was NOT a syntax error, but a string called "mysql_insert_id" into an integer field resulted in the value zero being put in. ...
    (comp.lang.php)
  • Re: return multiple rows from sql statement
    ... Concatening SQL ... strings from input values is almost certainly a safe path to SQL ... All characters that are entered in the fields make their way into the database unaltered. ... The insert of what surprisinlgly was NOT a syntax error, but a string called "mysql_insert_id" into an integer field resulted in the value zero being put in. ...
    (comp.lang.php)
  • Re: Establish connection with and transferring data to Access
    ... If you Google for SQL +"Insert into" you'll a dozen tutorials. ... The specific problem with your code is that you need to quote string ... Dim vConnection As New ADODB.Connection ... Doug's code opens and writes to an existing database. ...
    (microsoft.public.word.vba.general)
  • Re: return multiple rows from sql statement
    ... strings from input values is almost certainly a safe path to SQL ... Also, being a MySQL function, it knows what MySQL needs or uses. ... All characters that are entered in the fields make their way into the database unaltered. ... The insert of what surprisinlgly was NOT a syntax error, but a string called "mysql_insert_id" into an integer field resulted in the value zero being put in. ...
    (comp.lang.php)
  • Re: Can databases form automatic calculations?
    ... Basically I would like to know if there is some way to get a database ... All my product sales are ... Anyone know the MySQL function syntax for this if it is possible? ... The user interface is the SQL language which can be invoked ...
    (comp.databases)