Storing images in Sql database
in [Server Configuration]
|
Prev: Converting SQL Server 2005 to SQL Express 2008
Next: Never mind, Re: Failed to generate a user instance of SQL Server.Only an integrated connection can generate a user instance.
From: TheSQLGuru on 27 Mar 2010 21:32 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(a)sommarskog.se> wrote in message news:Xns9D48AADC6C282Yazorman(a)127.0.0.1... > Max Zhao (interarticle(a)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(a)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 >
From: Plamen Ratchev on 27 Mar 2010 21:41 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
From: Uri Dimant on 28 Mar 2010 03:08 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(a)gmail.com> wrote in message news:uMGL3JWzKHA.1796(a)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?
From: John Bell on 28 Mar 2010 14:35
On Sat, 27 Mar 2010 20:32:28 -0500, "TheSQLGuru" <kgboles(a)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 |