Results 1 to 6 of 6
  1. #1
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11

    Unanswered: Storing images in a database

    An odd question from me, I know, but this time, I assure you a twist.

    I have a group that wants to store images in either a database or a file share, in order to make a certain website able to run on a load balanced web farm. These images are around 1KB each in size, and have a lifespan of exactly one use (think of graphs). I went a-googling, and found no shortage of articles that say "don't do it, but here's how you can do it", but I did not find any real hard statements as to why to not do it. Needless to say, this is hurting the case for not putting these images in the database. I have found an article that says images over 8KB will have worse performance, but I can not use that factoid here. For the moment, I have the developers leaning toward the fileshare, because they will be writing transaction logs all day, which will be more work than the fileshare needs to do....I think. The best I can do, is cut the text/image datatype overhead, and have them create the table as varbinary(2000), but even then, I don't like the look of the idea.

    Anyone out there have good articles/whitepapers that detail the differences between writing single-use images to a fileshare vs. a SQL database? <baiting=blatant>Failing that, do any of the Microsoft development team have an opinion?</baiting>

    Alternatively, what is the general opinion of keeping session state information in a database (because I bet that will be my next battle). I see .NET includes a session state server, but not being a programmer, I can not create an opbjective test.

  2. #2
    Join Date
    Dec 2002
    Posts
    1,245
    No white papers from me, but about two years' experience storing images on a database. We currently house 6.5 million images in our database. Some comments on the experience:

    1. For some reason, the vendor chose to partition the filegroup into 128 separate files. Knowing what I know about the underlying mechanics (which admittedly isn't everything), there was no good reason to do this. However, I inherited the situation and I'm stuck with it for now. I don't think that it contributes anything (positively or negatively) to performance.

    2. The images are stored in one database and the metadata concerning the images is stored in a separate database on the server. Again, I am not certain whether this contributes to performance, but in this case it does make sense since it would simplify the process if I ever needed to separate the two databases onto discrete disk subsystems (currently they are not segregated).

    3. We have never gotten any complaints from users regarding performance or image quality.

    4. I have had a separete vendor (a company makes a product which consumes the images automatically through a web service interface) comment enthusiasticlly about the speed of our disk solution.

    We run SQL 2000 (SP4) on Windows 2000 (SP4); a 2-way server with 4 GB RAM. The server is connected to an Dell/EMC Clariion CX 500. I have two RAID-1 disks for log files and a single RAID-5 disk for data. The data is added continuously through a batch process and accessed randomly by both batch processes and user interactions.

    In short, I'm frankly pretty happy with the solution. It certainly makes things a heck of lot easier when it comes time to upgrade hardware/migrate applications. In fact, we just helped another agency retire their legacy system which used pointers in a database to image files that were stored on a web server; what a nightmare that was.

    Regards,

    hmscott
    Have you hugged your backup today?

  3. #3
    Join Date
    Nov 2005
    Location
    San Francisco, CA
    Posts
    506
    Quote Originally Posted by hmscott

    3. We have never gotten any complaints from users regarding performance or image quality.

    hmscott
    Hmm,thats interesting...well ,would you please specify the image size that you are dealing with?Is it very small ,or any size??
    Success is the ability to go from one failure to another with no loss of enthusiasm.
    - Sir Winston Churchill
    Joydeep

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Joydeep - were you spying on me?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Dec 2002
    Posts
    1,245
    Quote Originally Posted by rudra
    Hmm,thats interesting...well ,would you please specify the image size that you are dealing with?Is it very small ,or any size??
    Wow, talk about resurrecting a thread.

    I'm estimating here:

    Total size of the table: 234,076,752 (KBytes)
    Total number of rows: 8,020,074 (up from 6 million last year)

    Avg Image size: 29.2 KB

    The photos are all supposed to be pretty standard (dimension and quality wise). They are mostly front head and side head shots.

    Regards,

    hmscott
    Have you hugged your backup today?

  6. #6
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Happy (belated) birthday to you
    Happy (belated) birthday to you
    Happy (belated) birthday to my thread
    Happy (belated) birthday to you

    Well, in the interest of complete disclosure, I suppose I should close the story. The developers went with the fileshare on a clustered server. The cleanup (i.e.delete old images) routine took a while longer to come about, and was only implemented (hastily) after a few diskspace alarms went off.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •