Storing images in Sql database

Hi, I currently have a web server with a illustrations folder containing
some 100,000 images of all types (gif/jpeg...). The folder is presenting
a great management problem since it is really hard to load the entire
content of that folder remotely, and it may take minutes to open that
folder up. However, we've already installed a ASP.Net Handler in that
folder to generate thumbnails, which enables us change the storage
option if we wanted.

So, the question is, would it be optimal to place all those images in a
Sql 2005 Database and retrieve them using an Asp.Net Web handler when
demanded? How much slower will that option be? and would it consume a
lot of system memory?
0
Max
3/27/2010 4:03:29 AM
sqlserver.server 1327 articles. 0 followers. Follow

8 Replies
846 Views

Similar Articles

[PageSpeed] 39

On Sat, 27 Mar 2010 12:03:29 +0800, Max Zhao <interarticle@gmail.com>
wrote:

>Hi, I currently have a web server with a illustrations folder containing
>some 100,000 images of all types (gif/jpeg...). The folder is presenting
>a great management problem since it is really hard to load the entire
>content of that folder remotely, and it may take minutes to open that
>folder up. However, we've already installed a ASP.Net Handler in that
>folder to generate thumbnails, which enables us change the storage
>option if we wanted.
>
>So, the question is, would it be optimal to place all those images in a
>Sql 2005 Database and retrieve them using an Asp.Net Web handler when
>demanded? How much slower will that option be? and would it consume a
>lot of system memory?

Hi

As you are on SQL Server 2005 then your options to store the images
does not include using FILESTREAM, but even if you are using SQL 2008
you may not want to use it if the images are small.

Check out
http://databases.aspfaq.com/database/should-i-store-images-in-the-database-or-the-filesystem.html

John
0
John
3/27/2010 8:13:41 AM
Well, that folder's 12 Gb in size, contains around 190,000 files, and 
maintaining the images is becoming excessively complicated.
Is it worth it to load a 12-Gb folder into a 12-Gb database?
0
Max
3/27/2010 12:26:38 PM
Max Zhao (interarticle@gmail.com) writes:
> Well, that folder's 12 Gb in size, contains around 190,000 files, and 
> maintaining the images is becoming excessively complicated.
> Is it worth it to load a 12-Gb folder into a 12-Gb database?
 
This means that the images are on average 60 KB. That is, far below
the limit where reading images from SQL Server can be a performance
issue. On the other, hand 190000 files in a regular file-system folder
is not a recipe for speed. 

I would say that it is a good idea to put the images into the database.


-- 
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

0
Erland
3/27/2010 3:47:46 PM
Split the 100,000 images into different repertories and your problem will be 
solved.
-- 
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Blog/web site: http://coding-paparazzi.sylvainlafontaine.com
Independent consultant and remote programming for Access and SQL-Server 
(French)


"Max Zhao" <interarticle@gmail.com> wrote in message 
news:uMGL3JWzKHA.1796@TK2MSFTNGP02.phx.gbl...
> Hi, I currently have a web server with a illustrations folder containing
> some 100,000 images of all types (gif/jpeg...). The folder is presenting
> a great management problem since it is really hard to load the entire
> content of that folder remotely, and it may take minutes to open that
> folder up. However, we've already installed a ASP.Net Handler in that
> folder to generate thumbnails, which enables us change the storage
> option if we wanted.
>
> So, the question is, would it be optimal to place all those images in a
> Sql 2005 Database and retrieve them using an Asp.Net Web handler when
> demanded? How much slower will that option be? and would it consume a
> lot of system memory? 


0
Sylvain
3/27/2010 7:37:25 PM
I agree with that.  And even if you were on SQL 2008 and had FILESTREAM 
available Paul Randall (i think it was) did some performance analysis and 
found that files needed to be somewhere upwards of several hundred K or even 
around 1MB in size before it was faster to use FILESTREAM than direct TSQL 
database BLOB access.


-- 
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net


"Erland Sommarskog" <esquel@sommarskog.se> wrote in message 
news:Xns9D48AADC6C282Yazorman@127.0.0.1...
> Max Zhao (interarticle@gmail.com) writes:
>> Well, that folder's 12 Gb in size, contains around 190,000 files, and
>> maintaining the images is becoming excessively complicated.
>> Is it worth it to load a 12-Gb folder into a 12-Gb database?
>
> This means that the images are on average 60 KB. That is, far below
> the limit where reading images from SQL Server can be a performance
> issue. On the other, hand 190000 files in a regular file-system folder
> is not a recipe for speed.
>
> I would say that it is a good idea to put the images into the database.
>
>
> -- 
> Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
>
> Links for SQL Server Books Online:
> SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
> SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
> SQL 2000: 
> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
> 


0
TheSQLGuru
3/28/2010 1:31:52 AM
Here is a great publication on the topic from Microsoft Research:
http://research.microsoft.com/apps/pubs/default.aspx?id=64525

Extract from there:
"As expected from the common wisdom, objects smaller than 256K are best stored in a database while objects larger than
1M are best stored in the filesystem. Between 256K and 1M, the read:write ratio and rate of object overwrite or
replacement are important factors."

-- 
Plamen Ratchev
http://www.SQLStudio.com
0
Plamen
3/28/2010 1:41:37 AM
Hi
Also  you might improve performamnce to retrieve the images by seting you 
table as below
sp_tableoption tblname, 'text in row', ON



"Max Zhao" <interarticle@gmail.com> wrote in message 
news:uMGL3JWzKHA.1796@TK2MSFTNGP02.phx.gbl...
> Hi, I currently have a web server with a illustrations folder containing
> some 100,000 images of all types (gif/jpeg...). The folder is presenting
> a great management problem since it is really hard to load the entire
> content of that folder remotely, and it may take minutes to open that
> folder up. However, we've already installed a ASP.Net Handler in that
> folder to generate thumbnails, which enables us change the storage
> option if we wanted.
>
> So, the question is, would it be optimal to place all those images in a
> Sql 2005 Database and retrieve them using an Asp.Net Web handler when
> demanded? How much slower will that option be? and would it consume a
> lot of system memory? 


0
Uri
3/28/2010 7:08:16 AM
On Sat, 27 Mar 2010 20:32:28 -0500, "TheSQLGuru"
<kgboles@earthlink.net> wrote:

>I agree with that.  And even if you were on SQL 2008 and had FILESTREAM 
>available Paul Randall (i think it was) did some performance analysis and 
>found that files needed to be somewhere upwards of several hundred K or even 
>around 1MB in size before it was faster to use FILESTREAM than direct TSQL 
>database BLOB access.

You are probably thinking of
http://www.sqlskills.com/BLOGS/PAUL/post/SQL-Server-2008-FILESTREAM-whitepaper-published-on-MSDN.aspx

It's not just FILESTREAM datatype, but wether to access that data
using T-SQL, win32. but as the OP is using SQL 2005 filestream is out
of the equation. 

What no-one has mentioned so far is the effect on the cache.

Sylvain  has also mentioned that organising the files into a number of
drectories will improve the directory browsing issues. Hopefully the
files are on NTFS and not FAT

John
0
John
3/28/2010 6:35:39 PM
Reply:

Similar Artilces:

XML as a database that can be written to
Has anyone had any luck using XML as a database that not only can be read, but also written to? What features are available in .NET to do this? I need to do all of this from a Web based application. Am I thinking File System Object? Thanks. mjc wrote: > Has anyone had any luck using XML as a database that not only can be read, > but also written to? That's doable, but you better use real database instead. XML wasn't meant to be used as a database, it's just a data format. -- Oleg Tkachenko [XML MVP] http://blog.tkachenko.com Yes, it can be done and I think its an id...

Compacting Databases
Hi, I create quarterly, read-only copies of databases for analysis and reporting. When I do this I re-index the tables to 100% FILLFACTOR for performance, and I shrink the databases to the minimal possible size to save disk space. Is it better to do the re-index first and the shrink second, or vice-versa? Optimally, I'd like to get each table's clustered index defragemented in contiguous pages in the disk file with no empty pages in between. I'm doing this on SQL 2008. -- Thank you, Daniel Jameson SQL Server DBA Children's Oncology Group www.child...

Exch 2003 store.exe terminates when ESM Replication Status selected
Exch 2003 SP1 on W2K SP4 - had been 5.5 until moving to Exch 2K swing server. 5.5 removed and Exch 2003 SP1 installed, seemingly OK. Weird problem - if I click on ESM->Administrative groups-> group ->Servers-> server->First Storage Group->Public Folder Store(server) and then click on Replication Status, after a delay I recieve a popup saying, "An error occurred while retrieving the requested information. The Microsoft Exchange Information Store service is not running or the Microsoft Exchange Server computer is unavailable. ID no: c103047d Exchange System Manager...

Image in Excel File is not getting copied
In a Excel File I have inserted one Image from Insert --> Picture --> Form File ... Now when programatically I use System.IO.File.Copy(srcFile, destFile) and make a copy of this excel file, then all the data in Excel File is copied but that image which I inserted is not shown in the destination file. What is the problem here? Thanks, Ashish ...

Multi Store Vouchers
I do not see a way for this to work. I want to be able to give out store credit to our customers but there does not seem a way to make this work for multiple stores. In a single store setup everything is stored in the Voucher table. These vouchers are uploaded during the HQ synchronization but each store does not have access to other stores voucher's. Does anyone have any insight on how I could get this resolved? Thanks, Dan I take it you are trying to issue Store Credit for returns, not actually trying to implement a voucher system for gift cards/certificates. If this is corre...

How to programmatically convert a database
Hello all, I have a client that wants to upgrade from Access 97 to 2003 and they need to convert approx 5000 existing db's. Obviously this would be a real pain to do interactively so I'm trying to write a small utility to do automatically. I seem to have it running using the following statement to do the actual conversion : Dim objEngine As JRO.JetEngine Set objEngine = CreateObject("JRO.JetEngine") objEngine.CompactDatabase "Provider=Microsoft.Jet.OLEDB.4. 0;Data Source=" & strSourceDB, _ "Provider=Microsoft.Jet.OLEDB.4.0;Jet OLEDB:Engine Type=5;D...

Stored Procedure error message
Hi, I have written scipt in Dex to create tables. After I have added chunk file, tables are creating properly. But the issue is after login into Great Plains I am getting the following error "The Stored Procedure GrantAccess() of form Sql maintenance : 69: pass through Sql returned the following results :DBMS :15151, Microsoft Dynamics GP:0" Any Idea Please... Jack Hi Friends, I got the solution my slef and issue has resolved.. Thanks, Jack "Jack" wrote: > Hi, > > I have written scipt in Dex to create tables. After I have added chunk file, > table...

Fixed image moves down spreadsheet.
I have a multipage spreadsheet that was created in Excel 2003 with an image inserted in the top cells. When it is opened in Excel 2007 everything looks normal until you go to print preview. In print preview the image moves down the page a few centimeters on each page. In a large spreadsheet it will be midway through the page sometimes. I am not seeing this problem when opened in Excel 2003. Is this a compatibility problem? Any thoughts on this? ...

HQ/Store date mismatch
We just deployed our 3rd store and eveyrthing was working fine, transactions were being uploaded and everything. Then, the store ops DB computer got moved, and I guess at some point someone changed the date on the computer to be a month in advance. Now, all of our transactions from 4/25/2006 to 4/27/2006 are actually in the DB as 5/25/2006 to 5/27/2006. I just discovered the problem and changed the date on the Store Ops machine, but I don't know what the best way to fix these transactions is, or if it is even possible. What I'd like to do is go change all of these transac...

Copying Between Databases Without Linked Servers?
I have a SQL Server 2005 machine called "Dest" and a 2008 machine called "Source" and a 2008 machine called "Console". Each has the same SQL account with appropriate permissions and rights on all 3 to do anything. Without using a linked server, how can I sit at "Console" and execute a "select into" fromthe "Source" database to the "Dest" database? Thanks. "SnapDive" <SnapDive@community.nospam> wrote in message news:qcurk5dp3cem0l63d19io59nemtjv88534@4ax.com... >I have a SQL Server 200...

sample database
hi, can I install the sample database and use it as test database, on the same server where I have my datas ? Thank you Alessandra, CRM does not support two organisations on one server. I recommend that you look at VirtualPC or VMWare to set up a server that you can install the demo system on. Saves having to devote hardware to the task. Stephen "alessandra" <alessandra@discussions.microsoft.com> wrote in message news:66091D1A-C6C4-4C50-8026-497AFF786270@microsoft.com... > hi, > can I install the sample database and use it as test database, on the same > ser...

Outlook XP autofill To field
Outlook XP ... When typing in names in the To field of new messages Outlook will start to show names that have either already been typed, sent to or appear in the Contact list. Where is this "autofill" list kept? Is it user accessible? I'd like to get rid of some of the names that are not already in my Contacts. You can just use the down arrow key to highlight a name that is incorrect or whose address has changed and press delete. --� Milly Staples [MVP - Outlook] Post all replies to the group to keep the discussion intact. After searching google.groups.com and finding ...

Upgrading Great Plains 6.00 database from SQL Server 2000 to 2008
Hello, I have a client that uses GP 6.00g45 with SQL Server 2000 for the database. Does anyone know of any reason why that installation could not be upgraded to use SQL Server 2008? The only thing that comes to mind is different connection protocols. TIA, -- Greg Wilkerson Greg, You mean you want to run GP 6.0 on SQL 2008? Since GP 6.0 is not supported anymore, I suppose it really doesn't matter that's it's also not supported on SQL 2008? You would have to try it and see if it works. I would be curious to see if anyone has tried this, my guess would be that you...

Average Sales per Store
Need help modifying the RMS "Average Sales per Store" report that is found in CustomerSource. Would like to add filters for Department and Category if possible. I have tried to modify it myself, but I'm a beginner at this and I keep coming up short. One other thing that may be useful is if it can summarize by date also - would be nice to see how the values change over the course of a week or so. "Jason Kelton" wrote: > Need help modifying the RMS "Average Sales per Store" report that is found in > CustomerSource. Would like to add filters for D...

Converting Crosstab Table to Database
Does anyone if it is possible to convert a table (ie headings along the side and headings along the top) to a database format (eg Field names along the top and data within the table)? It is effectively the opposite of creating a PIVOT report from a database but I cannot find any function that allows this to happen without manually cutting and pasting. You can use the 'unpivot' technique described by John Walkenbach: http://j-walk.com/ss/excel/usertips/tip068.htm Shawn Ramraj wrote: > Does anyone if it is possible to convert a table (ie > headings along the side and h...

Store Credit multi Store
Good Day, Can someone give advice about how to set up Store Credits for a multi store enterprise. Currently the client use store credits/vouchers at store location only and can only redeem a Store Credit where it was issued. We would like to set it up so that they can redeem a store credit at any store location no matter where it was issued. Three stores connected via HQ. Thanks in Advance. I believe you can set up an account type and call it 'store credit' or something, then when you enter the new customer information for the person receiving store credit you simply assig...

SQL 2000 Shrinking database
There was an Optimization plan set on the database with reorganise data and index pages and reove unused space from database files. As I have very little space left on the disk how do I get it to shrink without using up disk space Submitted via EggHeadCafe - Software Developer Portal of Choice Searching Active Directory For Users and Groups http://www.eggheadcafe.com/tutorials/aspnet/940ae9c2-8ec4-4542-9bc8-d7a24fe7e0e1/searching-active-director.aspx (Joseph Tait) writes: > There was an Optimization plan set on the database with reorganise data > and index pages and reove u...

How to image Server 2008?
Is it possible to image a Windows 2008 server as a backup strategy? I currently use Norton Ghost to automatically maintain a backup image on a desktop PC, and would like to do the same with a 2008 file server. I tried using Symantec Ghost Solution Suite but am unable to image the host server with it. Am I missing something? "Tom Hill" <tomhill@tech2go.ca> wrote in message = news:ext8se%23uKHA.4752@TK2MSFTNGP04.phx.gbl... > Is it possible to image a Windows 2008 server as a backup strategy? > I currently use Norton Ghost to automatically maintain a backup i...

Public Folder Store Present on Front-Ends
I ran the EXbpa and it said that it is recommended to dismount the Public folders stores on my front end severs. When I dismounted them mail started queueing up and did not start again until I mounted the stores. Why would this be when Microsoft recommends it and what does the PF store have to do with mail flow? I am running Exchange Server 2003 SP2 Tyson <Tyson@discussions.microsoft.com> wrote: >I ran the EXbpa and it said that it is recommended to dismount the Public >folders stores on my front end severs. When I dismounted them mail started >queueing up and did no...

Copying hotbuttons from store to store?
I have 10 hotbuttons set up in one of our stores and need to copy it to 8 other stores. Is there a certain file I can copy instead of remaking them for each store? Thanks ...

Custom views
I have vers 2002 SP2 When I open an prev backup outlook.pst file, everything seems to be there, including my custom print styles, EXCEPT MY CUSTOM VIEWS ARE MISSING FROM THE VIEW/CURRENT VIEW MENU! Are they not included in outlook.pst file? This has happened twice to me, once a couple of versions ago ...

Problem querying a database to send emails
I am trying to send one email with a attached report to people on a query ("Current_Case_Query_Within_15_Days"), the exact field in the query that contains the email address is "UserID" but I continue to get an error on that (UserID) field/part of the code. Can anyone help? I am new at this coding part of access. See my code below. Thanks in advance, Raymond Sub SendMessage() Dim qdf As DAO.QueryDefs Dim db As DAO.Database Dim rsRecip As DAO.Recordset Dim objOutlook As Outlook.Application Dim objOutlookMsg As Outlook....

New stores exported from HQ get original store's configuration....
In a headquarters / store operations environment, are configuration settings "centrally maintained data" that get exported from HQ when a new store is created from HQ? In other words, do the new stores get the configuration settings assigned to the first store in store ops manager - file - configuration - accounts/ordering/inventory/options/etc tabs....? Thanks for any insights. the short answer is no. But it does get some (finding this out as we go). Inventory yes, Ordering dependson what you mean, accounts yes you can send that down, options no. "convoluted" w...

anyone found a way to bridge Sansnap4 fine reader with pdf or other image file format?
This is probably not the forum but if any know of a better forum or groups to post. please let me know The Fujitsu scanSnap 2150 let's people scan in document to designate file folder in designated format. however to output to a different format required re-scan! has anyone found any software or tools to allow ABBYY's finereader to read in PDF or Image and allow me to extract tables to excel ...

Restoring Exchange Store files to a new server
We had a major system crash this week but I was able to grab the entire exchsrv directory from the server. What files do I need to copy to the new server to get all of my exchange mail/public folders/etc loaded on the new server? Thanks, CJ What version of Exchange? -- Ed Crowley MVP - Exchange "Protecting the world from PSTs and brick backups!" "Curious Joe" <joebob.johnson@gmail.com> wrote in message news:1160176822.798021.310710@m73g2000cwd.googlegroups.com... > We had a major system crash this week but I was able to grab the entire > exchsrv director...