Comments on Paul Kukiel - and what i'm interested in.: Reading and writing binary to a mySQL database wit...

One thing that might be worth your testing relates...

2009-12-02T04:26:22.412-05:00

One thing that might be worth your testing relates to the 64k buffer behavior. In past versions of CF inserting to SQL Server, I found that the full data stream was written to the database regardless of the buffer setting. Retrieval, however, was truncated if the data exceeded 64k without adjusting the buffer. No lost data, just inaccessible if it exceeded the buffer size.

If it's a busy site, you might also consider having a seperate "blobDSN" for use with the increased buffer setting. There is a performance impact associated with the buffer setting that you may not want to impose on every database transaction.

Nice post Paul. One huge adavantage of this is wh...

2009-12-01T17:44:01.584-05:00

Nice post Paul.

One huge adavantage of this is when running in a cluster with replicated databases, everything is replicated all text and binary content.

It is a big slower, but you can also cache the binary data on disk and only update the database when it changes.

@Chris Yes without a doubt this would cause an in...

2009-12-01T14:10:00.310-05:00

@Chris

Yes without a doubt this would cause an increase on load for the DB server. Servering a static image via apache or IIS I imagine the load its very tiny compared to retrieving it from a database.

In Aaron's list of cons, you could add more lo...

2009-12-01T13:37:20.452-05:00

In Aaron's list of cons, you could add more load on the database server too, couldn't you?

Aren't some of the new "No SQL" databases good for storing documents like images too? Just curious.

Pros: -You can secure the "files" on y...

2009-12-01T12:17:14.100-05:00

Pros:
-You can secure the "files" on your app much easier.
-if you have a multi-app server environment, you can share images/files/etc across the applications without duplicating files.

cons:
-i think there is a little bloat storing as blobs (not 1:1 file to blob IIRC)
-can't take advantage of webserver auto-caching the content
-more load on your app server versus the file server

Thats all I got right off the bat.

Well it would still use space on the database serv...

2009-12-01T11:13:02.479-05:00

Well it would still use space on the database server. Each "blob" is still the same size as the file its just stored in the database rather then the file system as a file.

Wouldn't it save server space? - if you have a...

2009-12-01T11:10:06.064-05:00

Wouldn't it save server space? - if you have a site that has users submit images - wouldn't writing them to the database be more efficient as far as space?

One advantage might be that you can detach a datab...

2009-12-01T11:00:31.401-05:00

One advantage might be that you can detach a database and move it to a new host without needing to copy the image files.

A disadvantage is that there is overhead to and from the database.

I'm sure there are many more pro's and con's and I'm interested to hear what others have to say.

What are the advantages (and disadvantages) to sav...

2009-12-01T10:54:49.973-05:00

What are the advantages (and disadvantages) to save the image in the database?