Images in a SQL 2000 database. Anyone use it? 5

[colttaylor]

I just read an article on how to store images from digital cameras and scanners in a field in an sql table. My imagination has run wild with possible uses for this technology. I already offer unlimited text notes on most records (users, items, etc.) but now I can store images to support each note, plus images directly on some of my records for portraits, item images, etc. Very cool stuff that should really impress the users.

My question involves what this cool stuff is going to cost me in terms of performance. Really, I have two issues...

Will the presence of tables containing large numbers of images have any effect on the response times of the tables which do not contain any images?

As the quantity of images in the database grow, will the tables with images slow down?

I plan to keep the images away from my high traffic tables. For example, sales transactions will have no images. Now that I think of it, Items are pretty high traffic so I will put item images in a child table.

Any advice and/or real-life experiences are greatly appreciated.

Thanks,
Colt.


If it's stupid but it works, it isn't stupid

 

[nigelrivett]

Blobs are stored separately from other datatypes so in itself it shouldn't cause much of a problem.
It will cause the database to grow more if you have it set to do so - this is an expensive process and you may notice the effects.
Reading and writing reows will take longer - will hold locks for longer, need to access more data from disk causing more disk reads and use up memory.
Backups (and restores) will take longer as there is more data.

A better solution is usually to store images in files and store the path in the database. This means that you can store them on the application server so saving a lot of network traffic and disk access on the database server.
Also means that you can easily move the images to another disk or split them between disks.

======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[SQLSister]

Usually we store the images in the file system somewhere and then just store the address of the image in the database. Seems to have fewer insert update issues that way and you don't need to build child tables.

Questions about posting. See faq183-874

 

[colttaylor]

Thanks! I will go with your advice(s) and store filepaths in SQL.

ONE MORE QUESTION

Anybody know of a graphic format (JPG/GIF/PNG) which allows comments to be stored inside of the file. Now that the images will be out in the file system, I want to tie them back to the associated record in the SQL database. I plan to store the database name, table name and identity value in one or more comment in the file, so that I can point back to the appropriate record when needed. Anybody ever do anything like this? Which file format should I use? and are there any vb.net graphic libraries which give read/write access to such file-embedded comments?
Thanks for the help!


If it's stupid but it works, it isn't stupid

 

[ESquared]

I am sure that GIF lets you put comments in.

As for actually putting comments in, why would you? You can always get this information at any time you want by querying your database. Or you could have an imageinfo table that gives these details, so even if no record currently points to a certain image, you still know what the image is for.

 

[chrissie1]

I dont know about gif (ill look it up sometimes)

but jpeg2000 (.jp2) is famous for it. that and some other things. One being the price, another the losless compression. but its very hard to find any documentation about it. Unless you buy it.

I think that it serves a purpose for QA.

Christiaan Baes
Belgium

What a wonderfull world - Louis armstrong

 

[MarvinJean]

I have an app that stores groups of .bmp images in a sql data base.

They are images of flaws on plastic film (used for filtering products) associated with a roll of film about 1 meter wide, 1600 or 3200 meters long. there can be several hundred to several thousand images for a roll. The images are in a seperate table linked to the data table.

I use Winzip command lines to zip all the .bmp's plus the set-up description into a zip file which is then moved to a sql image column. A display program selects the zipped file and returns it to its orig form for analysis when required.

Knowkedge base article 258038 describes how to write and retrieve it with sql using ADO stream object.

There are two advantages: It takes about 25% of the space the .bmp files require and it removes thousands of directory entries - which are very slow to search in win 2000 or winxp.

Marvin Jean

 

[Triplex]

Colttaylor,

"Thanks! I will go with your advice(s) and store filepaths in SQL."

-----

How can i store filepaths in SQL? what data types am I going to use?

Can you give me some syntax on how to do it?

Thanks,

Triplex

 

[colttaylor]

Storing a filepath? I would just use a varchar with an appropriate length like 120 characters. In this approach, SQL isn't handling the image itself, just storing directions on where the image is located on your harddrive (or maybe a url, out on the web). You would get the filename out of the table with something like...

select imgfilename from customers where cusid = 1000

...then use your programming environment to work with the resulting filename. How that is handled is up to your choice of programming environments such as vb, vb.net, delphi, etc.

If it's stupid but it works, it isn't stupid

 

[Triplex]

Colt,

Good Day... Thanks for your reply. I just want to clarify if I'm right. When you say storing a filepath, for example
I have a image file, let say image.jpg stored in c:\image\image.jpg, the data that I'm going to store in my SQL database, (let say I have a field called filepath with a varchar as datatype with a size of 120 char)is "c:\image\image.jpg", am right?

If yes, how can I call the imagefile if I want to view it? Take note... my report viewer is crystal report. Maybe this is not the proper forum.. but if you have an answer to my query, its a big help for me.

Thanks,
Triplex

 

[colttaylor]

Yes, you are right, and this technique has the inherent advantage of having the image files out in the operating system where they can be manipulated, replaced and used for other purposes without involving SQL.

I'm no expert at CR, but from what I can see from a few posts, you need to put some code in a beforeprint event which looks like...

Set Picture1.FormattedPicture = LoadPicture(strFile)

...where strFile is the location of your image file.
How strFile gets loaded in CR is beyond me, but that should be a normal operating procedure for a CR programmer.
Hope it helps!
Peace,
Colt

If it's stupid but it works, it isn't stupid

 

[NoCoolHandle]

I used to take the approach of Store to the Filesystem, but now more often than not if I am going to use the image in an application, I use the Database.

Why?
1. Security.. If it is on the Filesystem, others can modify,update,delete or view with less control...
Database offers a lawyer of abstraction and security above an NTFS (or some other) files system.
2. Performance..
Ok so you might get a little less performance in some situations, but if the image is being viewed often SQL will cache it and it will be faster to serve up to the client, more often than not reading from the file system will be a read from the file system (Expensive compared to Ram which is cheep)
3.Portability.. What?
Yep you move a files location from one drive to another and your app breaks. Move the data to a new filegroup and it is still functional. Shift data between disks, between servers.
4. Ease of use...
Database backups are fast and simple (especially if you use differential backups and filegroup back ups) just manage at the Database and allow the Sqlserver agent to manage automation of execution and notifications..
With new app platforms (like .Net) working with streams/images has become soooo easy that doing it any other way just doesn't make as much sence. (in my mind)

I have a web app running on my home system that stores all my digital images in a sql database. Because I have a nice camera, images are around 1 mb per. I have over 3 gig of images. Each is catagorized by a keyword system. A user can bring back the images by querying the server on any number of keywords which are "or" joined into a dynamic query.

The database server retrieves the images and the webapp dynamicly builds a thumbnail image (or images - 20 at a time)
Click on a thumb and it will bring it back scaled to 640px (no matter what it was stored at) modify the scale and the same image can be "blown up" to 2000000px (ok that takes some time - now the 1 meg image might easily be 1,000 mb - it will do it, but there is alot of network traffic for an image that size

Bottom line this system is running games, wordprocessing and when someone wants images the bottleneck is the network.

Dynamic scaleing allows you to build a response specificly for a client, giving them no more or no less than they want. If they have a slow connection the default is good, if their connectionis good, they can change the default to get a full sized image or one that is 1024 largest side.

Having a single location for backup (even though the filegroup spans 4 physical drives) makes it easy to maintain. No need for seperate directorys for thumbs, med size images, big images.

I am not saying this is the way to go, but think about the options. It is much harder to hack a database image than one on a filesystem. As I said in the beginning, I agreed 120% with the common opinion here a couple of years ago, but now...... I am definitly becoming a member of the other camp.

Just another view.


Rob

 

[ESquared]

You make some good points, Rob.

I'm just curious why the filesystem would be so much easier to hack than the database? If you've got security under control then the computer should be impenetrable.

Just think of that worm that came through in the last year... SQL server has its own vulnerabilities.

 

[NoCoolHandle]

<snip>
I'm just curious why the filesystem would be so much easier to hack than the database?
</snip>

The key with security here is the level of abstraction. If you can't see it, you it is harder to mess with it.

All image access is handled via storedprocs. No direct table premissions.

A long time ago someone stole a gehto blaster from my car. Why? They could see it. The lock didn't stop them becuase they could see where it was. Once they saw the goal, they went directly for it, breaking locks as they went.

I think filesystems tend to be ignored when it comes to images for web/intranet/extranet applications.

Just hack around Nasa..... Some very very cool images you won't find via their site. If it was in a DB couldn't be done...

I guess with the correct skill set your data would still be corruptable, but fortunatly with SQL tightend correctly you dont' need to worry too much.

None of my servers even have an sa account.. does'nt exist in sysxlogins.. You need to be a member of the correct NT group to get admin premisions. NT Admins cant get there.

In general SQL is very trackable, via the log, you can even roll back changes if they do occour (which they really shouldn't) .. Lumigent has a great tool for log exploration and allows you to pinpoint where to roll things back to and build from.

Sorry just random thoughts.. Hope some made sence.

Rob