Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 Web Image Storage - SQL Server or File System
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

apollo11
Starting Member

USA
6 Posts

Posted - 11/28/2001 :  22:01:00  Show Profile  Reply with Quote

When designing a Web application (such as a product catalog) where you need to save/retireve an image (jpg or gif) for each item, what is the best approach to use for storing the images?

1. Store images in SQL Server 7 table
2. Store URL in table, and actual image as a file outside of SQL Server

Images need to be uploaded via web browser, and saved to the SQL Server
For each product, need to display on Web page both text and image.
Number of products could range from hundreds to thousands to hundreds of thousands.
Web Server is MSFT IIS4 or IIS5 with ASP or ASP.NET

Can anyone point me to sample code for uploading images to SQL Server and for displaying image from SQL Server on a web page.

TIA.




Best Regards,
Jim Underwood

Merkin
Funky Drop Bear Fearing SQL Dude!

Australia
4970 Posts

Posted - 11/28/2001 :  22:26:09  Show Profile  Visit Merkin's Homepage  Reply with Quote
Hi

Most definitely not SQL Server. Do it on the file system. I can't stress it strongly enough. Pulling all that image data out of your SQL Server will have a big performance hit.

Also, if you are dealing with lots of images, your database size will grow to be very big.

A nice result of storing them on the filesystem is if you need to spread your images over multiple drives it is easy. One of the applications I work with has about 40,000 images, some of them very hi res (4mb each). We set up a separate path for each image type so we can split them across different drives when we need to.

Hope that helps


Damian
Go to Top of Page

apollo11
Starting Member

USA
6 Posts

Posted - 11/29/2001 :  00:19:33  Show Profile  Reply with Quote
quote:

Most definitely not SQL Server. Do it on the file system. I can't stress it strongly enough. Pulling all that image data out of your SQL Server will have a big performance hit.



Thanks for your kind response.

First, let me apologize for not searching the archives properly. Looks like you get this question at least once a month. Guess it was my turn.

It seems very clear from all the prior posts that it is a bad idea to store images in SQL Server.

The main issue now is what is the limit on number of files per directory to not have a performance problem?

BTW, this is one of the best forums on the Internet, from both a content and am implementation perspective. The UI is great!


Best Regards,
Jim Underwood
Go to Top of Page

AjarnMark
SQL Slashing Gunting Master

USA
3246 Posts

Posted - 11/29/2001 :  13:36:01  Show Profile  Visit AjarnMark's Homepage  Reply with Quote
quote:
The main issue now is what is the limit on number of files per directory to not have a performance problem?



I think you'll find the answer to this really depends on levels of traffic, size of graphics, etc. I would encourage you to get a stress testing tool (such as WAST from Microsoft: http://webtool.rte.microsoft.com) and have it track your perfmon readings for drive statistics among others. If you are expecting a lot of traffic, then you'll probably want to commit the resources like Merkin talks about and have a dedicated drive array(s).

Also, you've probably already thought about this, but I would encourage you to separate the path and filename into separate fields in your SQL to make it easier to update if you move images. You might want to break it down further into Drive Letter/Server Name, path, filename, or whatever works for you. This is just for ease of future maintenance.

-------------------
It's a SQL thing...
Go to Top of Page

Merkin
Funky Drop Bear Fearing SQL Dude!

Australia
4970 Posts

Posted - 11/29/2001 :  17:05:39  Show Profile  Visit Merkin's Homepage  Reply with Quote
Agree on the separate file path. What we actually do is have a table of file "types", we assign a path to each type. Then depending on the type of file we know where to look for it.



Damian
Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2019 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.02 seconds. Powered By: Snitz Forums 2000