Images in a table

Question

Images in a table

Lori B

Ten Centuries

Points: 1149
More actions
July 8, 2004 at 11:31 am

#83307

I am trying to get my head around how images are stored in sql server. I have a table that has
708750 rows of barcodes in it. The database is 137 GB total size. The table is 125GB and I am
trying to explain to the db owner why this table
is so large and what we can do to make it smaller. We are also noticing that backing up this db is getting
to be a chore because of the size. I could make a new filegroup and place the table with the image data
into this filegroup, but am unsure that I would make the backup faster.
Someone help make sure that I have this correct...
Why the table is so big...
The db owner thinks that since we have used an image field, that the table should not be so large since
it is a special field. If each barcode image(tiff, jpeg, whatever) is about 170k, then even though
we are using an image field to hold the barcode image, it still has to take up the 170k worth of
space in the database for the image. An image field does not necessarily mean that the size of the image
is not accounted for by the database, the image field simply allows the hex that translates
into the image to be stored in the database.
What I can do about it...
If possible, we could make the barcode images smaller and then shrink the database.

Opinions??? Am I way off?

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Jim P. SSCrazy Eights Points: 8725 More actions · Answer 1

We just had this discussion in http://qa.sqlservercentral.com/forums/shwmessage.aspx?forumid=5&messageid=125190

Storing the images in the database is considered bad juju. There is nothing that can be really done to compress them. Best suggestion is create links to the images and store the links in the DB. That way, only new images are backed up off disk and the DB size shrinks considerably.

Note that you will probably have to rebuild the database to shrink it once the images are out of it.

----------------
Jim P.

A little bit of this and a little byte of that can cause bloatware.

Lori B Ten Centuries Points: 1149 More actions · Answer 2

Thanks Jim!! I thought that I had it down but wanted to make sure. I think I will go the business owner and tell him that this is "bad juju". I like that!!

Bas Schouten SSC Veteran Points: 278 More actions · Answer 3

Im very curious why you would store barcodes as images ... a barcode is nothing else but a alphanumeric string represented in a special font, so actually that barcode could be stored as 7, 11, 13 ... or so characters.

Or am i missing something here?

Bas Schouten

CBIS BV

Frank Kalis SSC Guru Points: 111183 More actions · Answer 4

Then again, I disagree!

There is no binary answer to this question. Each approach has its pros and cons and in some environments it might be advisable to use the db approach.

See this document http://www.microsoft.com/resources/documentation/sql/2000/all/reskit/en-us/part3/c1161.mspx and learn from Microsofts implementation of Terraserver.

...btw, the use of an image column DOES NOT make a db big. The size of the table you observe now will be roughly the same when using the filesystem.

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

lionfan91 SSCrazy Eights Points: 8794 More actions · Answer 5

I'm with Frank on this one -- I think it depends on the situation whether or not you store files/images in the DB or externally. I've got a web app that lets people upload any type of document in multiple functions within the app. I store every file in a single "tblFile" and then have JOIN tables from records like "tblDocument" and "tblDiagram" to "tblFile". This works great for me because using ASP and Persits ASPUpload I can easily get my binary files into and out of the DB and export them in their native format/application using ASP's Response.BinaryWrite method. Additionally, someone can upload multiple files with the same filename -- try that if you're storing them in the filesystem. Also when using ASP, my web app doesn't have to worry about having permissions to write to the filesystem. When it comes to backups, even though the backups may be big, I only have to backup the DB, not a file storage location. And what happens if a file within the file storage gets deleted but the pointer within the DB still exists (or vice versa)? Broken links. If you ask me, storing files externally to the DB is "bad juju", maybe not from a technical standpoint but certainly from a functional standpoint. My 2¢...

Jim P. SSCrazy Eights Points: 8725 More actions · Answer 6

When it comes to backups, even though the backups may be big,
 I only have to backup the DB, not a file storage location.
  And what happens if a file within the file storage gets
 deleted but the pointer within the DB still exists (or vice versa)?
  Broken links.  If you ask me, storing files externally to the DB
 is "bad juju", maybe not from a technical standpoint but certainly
 from a functional standpoint.  My 2¢...

milzs,

You have valid points. I'm talking mostly from my own experiences with both locally developed and delivered packages. The locally developed stored in the DB. We couldn't replicate, backups were a chore.... Big time non-fun. It has since been replaced. I admit some of the problems were in the design...didn't really use relationships.

The delivered package is storing externally...document imaging...and is being moved as I type. We are less than 500,000 from being done. Yes we are having a few docs that can't be found at the moment. But the images total out over 6.5 Million (Yes, that is 65 with 5 zeroes behind it). The image files make up about 675GB and growing daily.

If we tried to backup a DB that size nightly it would never happen. By doing it at the file level, we can do differential on the images during the week and full on the DB itself nightly. And we can even catch transaction logs hourly during the day.

I am also only throwing in my $0.02

----------------
Jim P.

A little bit of this and a little byte of that can cause bloatware.

vhanda SSCrazy Points: 2180 More actions · Answer 7

Wait a sec, Bas is absolutely right: a barcode is just a special font representation, really, of standard alphanumeric data. Never mind the image-in-DB vs. image-in-file debate for now. Unless there is a very specific business reason to store the actual barcode image (none pops to mind), Lori should be able to just store the text (as char or varchar) data represented by the barcode. We've built several barcode-enabled applications and have never encountered the need to store the actual image. Yes, 2d barcodes can be used to encode binary data, but this is usually in the 2k-4k range.

Vik

Jim P. SSCrazy Eights Points: 8725 More actions · Answer 8

The point about the barcodes is valid. It is just a font, but the font needs to be available to all client machines...usually loaded at the client level.

In addition there are over 30 different barcodes out there. It sort of depends on the client.

----------------
Jim P.

A little bit of this and a little byte of that can cause bloatware.

Lori B Ten Centuries Points: 1149 More actions · Answer 9

This is all really good info. I have never had an application or database with barcodes and did not realize that this should be a font. But, I guess I can see that if they don't want to have to install a special font to everyone who needs all the barcodes then the image of the barcode would be easier to give to everyone. We are a food distributor and have a lot of barcodes. I will go ask about why they are not using a font.