Sql Ignoring spaces: blob

Showing posts with label blob. Show all posts

Monday, March 26, 2012

Images and PDF's in the same DB or in another DB?

Hello,
I must save up to 20GB of Image and Pdf's in a sqlserver db.
Is it better to hold the table with these BLOB's in a seperate DB or
in the same db where all the other tables are present (100 tables)?
Are there a performance problem if the BLOB table ist in the same as
the other data?
RobertThis is a multi-part message in MIME format.
--040906000104030808000509
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
Same DB.
If you're concerned about IO performance at all you can always create
another filegroup consisting of a file on a different disk (or RAID
group) and create the image table on that filegroup. But having the
image data reside on the same disk as all the other tables shouldn't
affect query performance of those other tables (at least not in any
noticeable way).
Putting it in a separate filegroup would really only give you a little
more flexibility with backup/restore strategies (as you could back up &
restore the image data separately to the data in all your other tables,
which may or may not be handy for you).
--
*mike hodgson* |/ database administrator/ | mallesons stephen jaques
*T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
*E* mailto:mike.hodgson@.mallesons.nospam.com |* W* http://www.mallesons.com
robert madrian wrote:
>Hello,
>I must save up to 20GB of Image and Pdf's in a sqlserver db.
>Is it better to hold the table with these BLOB's in a seperate DB or
>in the same db where all the other tables are present (100 tables)?
>Are there a performance problem if the BLOB table ist in the same as
>the other data?
>Robert
>
>
--040906000104030808000509
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
<tt>Same DB. 
 
If you're concerned about IO performance at all you can always create
another filegroup consisting of a file on a different disk (or RAID
group) and create the image table on that filegroup. But having the
image data reside on the same disk as all the other tables shouldn't
affect query performance of those other tables (at least not in any
noticeable way). 
 
Putting it in a separate filegroup would really only give you a little
more flexibility with backup/restore strategies (as you could back up
& restore the image data separately to the data in all your other
tables, which may or may not be handy for you). 
</tt>
<div class="moz-signature">
<title></title>
<meta http-equiv="Content-Type" content="text/html; ">
-- 
 mike
hodgson | database administrator
| mallesons stephen jaques 
T +61 (2) 9296 3668 | F +61
(2) 9296 3885 | M +61
(408) 675 907 
E
<a href="http://links.10026.com/?link=mailto:mike.hodgson@.mallesons.nospam.com">
mailto:mike.hodgson@.mallesons.nospam.com</a> | W <a href="http://links.10026.com/?link=/">http://www.mallesons.com">
http://www.mallesons.com</a> 
</div>
 
 
robert madrian wrote:
<blockquote cite="miduJkNqFOZFHA.2688@.TK2MSFTNGP09.phx.gbl" type="cite">
<pre wrap="">Hello,
I must save up to 20GB of Image and Pdf's in a sqlserver db.
Is it better to hold the table with these BLOB's in a seperate DB or
in the same db where all the other tables are present (100 tables)?
Are there a performance problem if the BLOB table ist in the same as
the other data?
Robert
</pre>
</blockquote>
</body>
</html>
--040906000104030808000509--|||The only reason I could imagine for putting the blobs, etc in a separate DB
is IF you wish to backup/restore them separately from the other database...
The price/penalty you pay for separating them is that you would have to use
DTC to do a single transaction which updates both databases.
I would put them together... ( SQL 2005 will allow separate backup restore
of filegroups anyway. ) so put them on a separate filegroup now, but in the
same database.
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"robert madrian" <office@.madrian.at> wrote in message
news:uJkNqFOZFHA.2688@.TK2MSFTNGP09.phx.gbl...
> Hello,
> I must save up to 20GB of Image and Pdf's in a sqlserver db.
> Is it better to hold the table with these BLOB's in a seperate DB or
> in the same db where all the other tables are present (100 tables)?
> Are there a performance problem if the BLOB table ist in the same as
> the other data?
> Robert
>|||In addition, try to describe the contents about the LOB as much as possible
with other data. I also typically segragate the LOBs from the primary data
into a segmented table and relate them 1 to 1 with the original table. This
way, I only have the JOIN ID and the LOB itself, in a table itself, in a
filegroup and file itself.
Why, because although SQL Server 2000 supports LOBS, it does a really lousy
job managing the space, especially if there are a lot of inserts and
deletes. The space reclaimation is lousy. Also, lets face it, LOBs have
nothing to do with the "descriptive" data of the database. It is just
convenient, high priced, storage and retreival. It should be segmented away
anyway.
Sincerely,
Anthony Thomas
"Mike Hodgson" <mike.hodgson@.mallesons.nospam.com> wrote in message
news:uVFN%23ROZFHA.3280@.TK2MSFTNGP09.phx.gbl...
Same DB.
If you're concerned about IO performance at all you can always create
another filegroup consisting of a file on a different disk (or RAID group)
and create the image table on that filegroup. But having the image data
reside on the same disk as all the other tables shouldn't affect query
performance of those other tables (at least not in any noticeable way).
Putting it in a separate filegroup would really only give you a little more
flexibility with backup/restore strategies (as you could back up & restore
the image data separately to the data in all your other tables, which may or
may not be handy for you).
--
mike hodgson | database administrator | mallesons stephen jaques
T +61 (2) 9296 3668 | F +61 (2) 9296 3885 | M +61 (408) 675 907
E mailto:mike.hodgson@.mallesons.nospam.com | W http://www.mallesons.com
robert madrian wrote:
Hello,
I must save up to 20GB of Image and Pdf's in a sqlserver db.
Is it better to hold the table with these BLOB's in a seperate DB or
in the same db where all the other tables are present (100 tables)?
Are there a performance problem if the BLOB table ist in the same as
the other data?
Robert

Images - Store in a database or file system?

I have recently designed and built my first database using SQL server 2005 express. I have included an image (BLOB) column in one of the database tables. This is a bad idea according to some experts, and some say it is OK!

I am currently carrying out a trial with just 3 pictures via Visual Basic 2005 express forms, and there is no problem so far as the images are displayed for each record. But I anticipate between 300 - 1000 images for the table, and this could pose real problems for SQL server 2005 express and Visual Basic 2005 express, I guess.

I have just been reading that the cost of storing large images in the database is too high! I have also read it's better to store images (BLOB) into the file system because it is cheaper to store them no matter how many there are.

But the question is how I can reference an image in this path: C:\Picture\Product\Grocery\00052745.jpg in the database table, so that when I select a record Visual Basic 2005 forms the image is displayed accordingly, similar as when stored directly in the database table? Your help very much appreciated.

hi Paul,

PaulN wrote:

I have recently designed and built my first database using SQL server 2005 express. I have included an image (BLOB) column in one of the database tables. This is a bad idea according to some experts, and some say it is OK!

yes, this often result in "religious wars" bewteen parts.. I'm with the "all in the db" part , with just one caveat (regarding MSDE and SQLExpress), depending on the "limited" database maximum size of 2gb for MSDE and 4gb for SQLExpress.. this obviously could "limit" the quantity and size of the lobs stored in the database, thus penalizing an architecture of a project..

well.. 4gb are not that "limited" if you store little jpges, but could eventually become a real limit if you have to store a "tipycal" ripped movie of about 700mb..

I am currently carrying out a trial with just 3 pictures via Visual Basic 2005 express forms, and there is no problem so far as the images are displayed for each record. But I anticipate between 300 - 1000 images for the table, and this could pose real problems for SQL server 2005 express and Visual Basic 2005 express, I guess.

actually that's not that bad... it's real a "limited" quantity you should not have problem with..

I have just been reading that the cost of storing large images in the database is too high! I have also read it's better to store images (BLOB) into the file system because it is cheaper to store them no matter how many there are.

this is true.. standard disk space is absolutely cheap. but you incour in other kind of problems.. see later..

But the question is how I can reference an image in this path: C:\Picture\Product\Grocery\00052745.jpg in the database table, so that when I select a record Visual Basic 2005 forms the image is displayed accordingly, similar as when stored directly in the database table? Your help very much appreciated.

thi is the problem with "external" resources... I mean that it's a traditional scenario for this kind of stuff, but this includes an important access tradeoff.. if you have "all your things" within the database, you only need to query it for returning the desired output (including the lob's data).. all is incapsulated within the database... you do not have "particular" security problems as your database permissions schema already includes lobs.. on the contrary, if your "table" just stores the "path" to the actual lob jpeg file, that file should be available among the whole lan (if your scenario involves remote connections), meaning it must reside on a "share" where all potential (remote) users can access it, with read-only and/or read/write permissions on it (depending on the nature) in order to allow both access (read) and modifications (write)...

more, all the automated tasks as you already pointed out are out of scope, you have to load pictures from a stream loaded with the lob's data from the (remote) folder... this last part is not that bad (streaming data), but all the remaining part is (accessing physical NTFS permissions, share, and disk subsystem), IMVHO...

I do prefer to streams lob's data out of a (say) datareader object anyway from the database not directly relying on wizard's bindings as usually data have to be "purged/checked" before presentation (but this is not mandatory, it depends on your design and the actual prj), but all remains "incapsulated" in the db... just a few stored procedures to read/write data in and out and the job is done...

regards|||

I agree with Andrea.

For small photos, store them in the database. You won't 'lose' photos quite as easily as if they are just 'floating' around the file system somewhere. And backups are much easier.

However, if I were dealing with something like a Contract Photo Agency, with hundreds of thousands of photos, in high resolution (meaning multi-MB photo/file sizes), I would investigate something like 'FileNET', or other of the content management 'systems'.

|||Another good ressource is: To blob or not to blob: http://research.microsoft.com/research/pubs/view.aspx?msr_tr_id=MSR-TR-2006-45

Jens K. Suessmeyer.

http://www.sqlserver2005.de
sql

Monday, March 26, 2012

Images and PDF's in the same DB or in another DB?

Images and PDF's in the same DB or in another DB?

Images - Store in a database or file system?

Sql Ignoring spaces

Blog Archive

About Me