Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Best way to store images Expand / Collapse
What is best way to store images
Poll ResultsVotes
Store them in binary type in SQL SERVER
 
12.5%
2
Copy them in a folder and only store their 'names' in SQL SERVER
 
87.5%
14
Member Votes: 16, Anonymous Votes: 0. You don't have permission to vote within this poll.
Author
Message
Posted Saturday, March 9, 2013 2:59 AM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, July 15, 2014 6:01 AM
Points: 61, Visits: 276
Hi.
I recognized if we store an image in binary type in SQL SERVER they take more space. For example a 700kb image take about 900kb space.

With this condition which way is better to store images?


___________________________________
Computer Enterprise Masoud Keshavarz
I don't care about hell.
If I go there I've played enough Diablo to know how to fight my way out.
Post #1428860
Posted Saturday, March 9, 2013 8:24 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 12:18 AM
Points: 36,751, Visits: 31,202
masoudk1990 (3/9/2013)
Hi.
I recognized if we store an image in binary type in SQL SERVER they take more space. For example a 700kb image take about 900kb space.

With this condition which way is better to store images?


Generally speaking, I'll allow storing of small images (such as small badge photographs) in a database and store large binaries in a file-path reference only.

In all practicality, I'll usually only use the file-path reference method even for small binaries for two reasons.

1. The data normally originates in a file. If we use the file-path reference method, you can avoid the bit of pain it takes to load a binary into the database. This is especially important if changes to the binary are made. No database action is required, really. Just overwrite the file and you're done.

2. Since the data normally does originate as a file, most people end up wanting to keep the file even if they don't use it for other things. It seems a bit wasteful to me to have the data stored in two places.

3. Of course, there's the front end and related security aspects. It's sometimes a whole lot easier on both if the binary data is stored in the database. Of course, if the binary data will never change, it seems a waste to constantly backup potentially voluminous static data over and over. It also makes restores take much longer should a restore need to be accomplished. If the binary data were stored in files instead of the database, restores would be much quicker. It will also distribute the I/O to possibly more than one machine (a file server and SQL Server) which would usually enhance performance.

So the answer is and although I lean toward the file-path reference method, "It Depends". Every situation is a bit different and I wouldn't rely on any rule of thumb here. Sit down and make a list of requirements vs advantages/disadvantages of each method and then make a decision.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1428897
Posted Saturday, March 9, 2013 9:09 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 11:00 PM
Points: 21,212, Visits: 14,910
Jeff Moden (3/9/2013)
masoudk1990 (3/9/2013)
Hi.
I recognized if we store an image in binary type in SQL SERVER they take more space. For example a 700kb image take about 900kb space.

With this condition which way is better to store images?

...

So the answer is and although I lean toward the file-path reference method, "It Depends". Every situation is a bit different and I wouldn't rely on any rule of thumb here. Sit down and make a list of requirements vs advantages/disadvantages of each method and then make a decision.


Agreed. I have done it both ways. The file path presents an issue with double-hop sometimes and can be a pain to troubleshoot. One thing I sometimes do is to separate the images into a separate filegroup for backup purposes (when stored in the database). If they are static, you could even turn the filegroup into a read-only filegroup. The backup of that filegroup becomes less painful and requires less overall maintenance.

That said, I too have often found it easier to just store the filepath in the database - especially with frequently changing images or developer design.

In the end, It Depends.




Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #1428902
Posted Monday, March 11, 2013 5:26 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, October 31, 2013 3:44 AM
Points: 314, Visits: 4,128

This is more of a question that it is a solution but, Jeff, with the advent of filestream technology in more recent versions of sql server have you used that approach or is that more or less what you are refering to by the "file path reference approach"?
Post #1429180
Posted Monday, March 11, 2013 5:55 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 12:18 AM
Points: 36,751, Visits: 31,202
OTF (3/11/2013)

This is more of a question that it is a solution but, Jeff, with the advent of filestream technology in more recent versions of sql server have you used that approach or is that more or less what you are refering to by the "file path reference approach"?


I'd have to include FileStream in the same bucket as the "File Path Reference" approach with some reservations. To be sure, I've not used FileStream, yet (too much on my plate for now), but I like what I see in the documentation. One of the things that would be important to me is that other applications should be able to use the files in the data containers that FileStream uses without having to go through anything having to do with SQL Server or FileStream. For example, I'd like folks to be able to get to the files using only, say, Windows Explorer and be able to open up a PDF or a JPG or whatever just by double-clicking on it. I also need to be able to "update" the content of a file simply by replacing it through something as simple as the Command Prompt. If that can't be done in FileStream data containers directly (and I haven't found such explicit information in the documentation, yet), then Filestream wouldn't work so well for the things I need to do.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1429203
Posted Monday, March 11, 2013 9:09 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, October 31, 2013 3:44 AM
Points: 314, Visits: 4,128
Jeff Moden (3/11/2013)
OTF (3/11/2013)

This is more of a question that it is a solution but, Jeff, with the advent of filestream technology in more recent versions of sql server have you used that approach or is that more or less what you are refering to by the "file path reference approach"?


I'd have to include FileStream in the same bucket as the "File Path Reference" approach with some reservations. To be sure, I've not used FileStream, yet (too much on my plate for now), but I like what I see in the documentation. One of the things that would be important to me is that other applications should be able to use the files in the data containers that FileStream uses without having to go through anything having to do with SQL Server or FileStream. For example, I'd like folks to be able to get to the files using only, say, Windows Explorer and be able to open up a PDF or a JPG or whatever just by double-clicking on it. I also need to be able to "update" the content of a file simply by replacing it through something as simple as the Command Prompt. If that can't be done in FileStream data containers directly (and I haven't found such explicit information in the documentation, yet), then Filestream wouldn't work so well for the things I need to do.


Thanks for the response.
Post #1429329
Posted Tuesday, March 12, 2013 5:09 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Friday, July 18, 2014 2:05 PM
Points: 3,943, Visits: 2,957
I've never used the filestream so I won't comment on it, but I have stored images (and lots of other file types, such as AutoCad) in Oracle and SQL Server in the past. I've regretted it every time. The database files become huge and fragmentation was always a concern. They're already in a physical file and they're presumably stored so the users can get them again, so I just leave them that way. Live and learn.


Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions
Post #1429682
Posted Tuesday, August 20, 2013 2:40 PM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Thursday, July 17, 2014 3:41 PM
Points: 522, Visits: 234
Storing files on File Server is better option if file size is huge.
Post #1486440
Posted Thursday, September 26, 2013 9:06 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 12:18 AM
Points: 36,751, Visits: 31,202
Ed Wagner (3/12/2013)
I've never used the filestream so I won't comment on it, but I have stored images (and lots of other file types, such as AutoCad) in Oracle and SQL Server in the past. I've regretted it every time. The database files become huge and fragmentation was always a concern. They're already in a physical file and they're presumably stored so the users can get them again, so I just leave them that way. Live and learn.


I've recently discovered a reason to store even large images in the database. We have call recordings that get stored on disk and then imported into SQL Server. Don't ask me why but I decided to doa reconciliation between the call files on disk and SQL Server. It turned out that people had moved the files several times, left some behind, moved some to different directories (some of which were never backed up), had lost many files in the process, etc, etc, ad infinitum.

None of that happened with the calls stored in the database even after several moves of the database. The cool part is that the files are right there with the keys to look them up. The "bad" part is that backups have gotten huge and, of course, a restore would take hours. There's a solution for that, though. These calls are, by their very nature, "read only". We're partitioning the call recording table by month with each partition in its own read-only file group. We'll only keep the current month and the previous 3 months in the PRIMARY file group to make restores a no brainer and to allow for "piece-meal" restores of the other file groups (again, 1 per month). This means instead of a 5.5 hour restore in a DR situation, we can get "back in business" in less than a half hour and take our sweet time restoring the older months and in an online fashion. Much like an archive bit in a file system, it allows us to really only backup the data that has changed (new call recordings) or that is super important even if it hasn't changed (previous 3 months).

I used to be one that said "Hell no, not in my database" to large binary "files" but I'm really starting to see some of the advantages to storing such data in the database when you combine that with some of the tools for handling larger tables. The added bonus is that the front-enders love it, management loves it (no lost or difficult to find recordings), and it has become a whole lot easier to manage the calls because they're guaranteed to all be in the same place.

One final thing... we keep the informational data for each call in a separate table and have a "sister" table for the recordings. This is kind of the best of both worlds because we can very quickly search the information table (very narrow row sizes and easy to index) and the recordings are "pulled up" only when the correct information is found. Of course, the recording table has the row ID of the informational table in it to provide the link between the two.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1499155
Posted Thursday, September 26, 2013 9:10 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 12:18 AM
Points: 36,751, Visits: 31,202
Snigdha Vartak (8/20/2013)
Storing files on File Server is better option if file size is huge.


When you make such a statement, it would be good if you provided a reason why.

Shifting gears, I used to think what you stated but, as you can see from my previous post, I'm having a serious change of heart about that. If the files are less than 2GB, they fit quite nicely in the database. Obviously, if they won't fit into 2GB per "file" then you will have to restort to the file system instead. If that's what you meant by "huge", then I agree but not because it's a "better" option... if the files are > 2GB, they won't fit into the MAX datatypes and that eliminates that particular option. Filestream of the old fashioned "store the file path" option would be a way around that.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1499156
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse