Database

6-Sep-11 11:55

I just spent 2 hours googleing best practices for storing picture's for a asp.net MVC project that I am starting.

There are two school's of thought. One is to store the Picture's in a database (for ease of backup's) Another is to use the site's file system. (For speed)

This project will be for my family.(Very Large Family I might add) I want to keep Pictures Referenced to the user that uploaded them. And only share some of them with other member's

My question: What do You think about storing the file's in the database? And why?

Thanks in advance

Any good resources or link's appreciated

Frazzle the name say's it all

Mycroft Holmes6-Sep-11 12:39

Mycroft Holmes

6-Sep-11 12:39

I use the file system but then I have 1000s of images, the economy of backing up the database and moving it to my dev environment alone dictates that I do not want the image files inside that backup. What do I care if the images are trashed on the server, I have less timely backups of them elsewhere, I certainly don't want to move them over the wire every time I take a backup of my data.

Never underestimate the power of human stupidity
RAH

David C# Hobbyist.6-Sep-11 15:04

6-Sep-11 15:04

Mycroft Holmes wrote:
I certainly don't want to move them over the wire every time I take a backup of my data.

That is a good point I don't think I will have that many images but you never know at design time If the project will be a hit with the User's.

Frazzle the name say's it all

PIEBALDconsult6-Sep-11 14:48

PIEBALDconsult

6-Sep-11 14:48

I put them in the database, they're safer there. In the file system, someone may overwrite* or delete a file. I don't backup my databases though. Unsure | :~

* Replace your favorite shot of Granny with a picture from your cousin's bachelor party (or vice versa).

David C# Hobbyist.6-Sep-11 15:00

6-Sep-11 15:00

PIEBALDconsult wrote:
* Replace your favorite shot of Granny with a picture from your cousin's bachelor party (or vice versa).

as I am almost a grandfather This would make me Upset!!!

Thank You for the reply I will consider this issue when I Finally Smile | :)

decide which way to go

Frazzle the name say's it all

Pradeep Shukla6-Sep-11 18:20

Pradeep Shukla

6-Sep-11 18:20

There are few questions that you should have answer to make this decision:

File System:

- Images sizes are smaller than 2 MB.
- No editing required on the images in future
- Faster access

Database:

- Image size is on a higher side 2+ MB.
- Easy maintenance and backup required

There is an another approach of FILESTREAM if you are planning to use sql server 2008.
http://msdn.microsoft.com/en-us/library/cc716724.aspx[^]

hope it helps.

Wendelius6-Sep-11 19:45

6-Sep-11 19:45

Here's an short article I wrote some tome ago concerning binary data in the database. Perhaps it would be of some help: How to store and fetch binary data into a file stream column[^]

The need to optimize rises from a bad design.My articles[^]

David C# Hobbyist.7-Sep-11 7:41

7-Sep-11 7:41

I just read your article. Interesting so basically all that gets stored in the database is a GUID and correct me if I'm wrong a Hash of the file size? And the file itself goes in the file system? I don't see the benefit in storing the info 2 times. Other than maybe so it can be strongly typed.

Maybe I missed the Point I will reread the article

Frazzle the name say's it all

Wendelius7-Sep-11 7:52

7-Sep-11 7:52

One of the main points is the transactionality. In case of an error you don't have to worry if the database contains a path to a non existent file or vice versa.

Second thing, backups. When you backup the database you also backup the files. No separate backups.

Thirdly, speed with larger files compared to storing all the binary info inside the database.

And the fourth thing, which I haven't covered very much yet is that you can actually stream the file better to the client when fetching.

I think those would be the main points. All comments are very welcome Smile | :)

The need to optimize rises from a bad design.My articles[^]

David C# Hobbyist.7-Sep-11 8:01

Regarding performance tunnig

7-Sep-11 8:01

Mika Wendelius wrote:
One of the main points is the transactionality. In case of an error you don't have to worry if the database contains a path to a non existent file or vice versa.

So it wil be Strongly Typed" This I like! Smile | :)

Mika Wendelius wrote:
Second thing, backups. When you backup the database you also backup the files. No separate backups.

This I like Alot! Smile | :)

Mika Wendelius wrote:
Thirdly, speed with larger files compared to storing all the binary info inside the database.

This should not be a problem, because I Intend to re-size the Images.

Mika Wendelius wrote:
And the fourth thing, which I haven't covered very much yet is that you can actually stream the file better to the client when fetching.

This on the other hand I Love. But I will have to learn a bit more about. Looks like Google time! Smile | :)

Frazzle the name say's it all

kankeyan5-Sep-11 20:11

kankeyan

5-Sep-11 20:11

WHich is more faster to compare values <> or != for performance tunning

thanks in advance

Mehdi Gholam5-Sep-11 20:15

Mehdi Gholam

5-Sep-11 20:15

It makes no difference, both are the same.

Its the man, not the machine - Chuck Yeager
If at first you don't succeed... get a better publicist
If the final destination is death, then we should enjoy every second of the journey.

Wendelius5-Sep-11 20:44

5-Sep-11 20:44

As said, from performance point of view, they are the same. However, since you didn't mention what database you're using, from syntax point of view there may be differences since not all of the databases understand both syntaxes.

The need to optimize rises from a bad design.My articles[^]

kankeyan5-Sep-11 22:41

kankeyan

5-Sep-11 22:41

we are using sql server 2008

RelationShip between to DataBase in SqlServer2008

Pradeep Shukla6-Sep-11 11:06

Pradeep Shukla

6-Sep-11 11:06

SQL Server understands both the operators...no issue at all

karimvand5-Sep-11 19:07

karimvand

5-Sep-11 19:07

RelationShip between to DataBase in SqlServer2008
this action in access down but sql I dont please help me.

Re: RelationShip between to DataBase in SqlServer2008

Mycroft Holmes5-Sep-11 19:51

Mycroft Holmes

5-Sep-11 19:51

First off get someone who has a better grasp of English to help you with your question b/c what you have written makes absolutely no sense (to me anyway).

Never underestimate the power of human stupidity
RAH

Re: RelationShip between to DataBase in SqlServer2008

kankeyan5-Sep-11 20:34

kankeyan

5-Sep-11 20:34

what you are trying to say ????

Re: RelationShip between to DataBase in SqlServer2008

Wendelius5-Sep-11 20:41

SSIS - How To Create This Package

5-Sep-11 20:41

Guessing that you mean one of these:
- foreign key between tables, see: http://msdn.microsoft.com/en-us/library/ms175464.aspx[^]
- or the ability to use external tables in Access. There's no exact match for this but Linked Server is very close. See: http://msdn.microsoft.com/en-us/library/ms188279.aspx[^]

The need to optimize rises from a bad design.My articles[^]

Kevin Marois5-Sep-11 14:11

Kevin Marois

5-Sep-11 14:11

This should be fairly simple, but I'm rather new to SSIS.

I want to copy data from one table to another. The column names are not the same, and in the target table I may need to expand some columns so I don't get truncation errors.

I'm not really sure what objects in SSIS to use.

Many thanks

Everything makes sense in someone's mind

Re: SSIS - How To Create This Package

Wendelius6-Sep-11 8:40