November 14, 2019 at 12:25 pm
In order to structure and layout 50GB of data which includes pdf files, videos, images, etc, which is the better way to implement? Using sql or not? Please can you tell me the pros and cons of it. Thank you. This is for an intranet site development!
November 14, 2019 at 3:25 pm
Is it only consisting of pdf, images, videos? If so, a relational database would be a poor choice.You might want to look to some other kinds of data storage that can index those files. For example, Azure has a tool for indexing videos, making them searchable. That's just one example. There are a bunch of others.
Is it a combination of things? Information about all those different files and the files themselves? If so, then the information about those files is likely to fit well into a relational database. You should still leave the files in traditional file storage. While there are ways to shove them into a database, you'll be quite unhappy with the results. Then you can bring in the other types of indexing and, where possible, combine it into your relational data.
There's tons of stuff that can be done. It's mainly about getting really good definitions so that you can make the right technical choices.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
November 14, 2019 at 4:11 pm
I do have to disagree with a database "being a poor choice" to support documents and images. At best, I'll agree to say "It Depends" but even that bothers me a bit, in this case.
I have more than 10 years of telephone call recordings stored as "Type 4" WAV files ("Type 4" is our acronym for a whole bunch of words that describe the compressed storage method. Most of the ~2TB database exists in a single partitioned table that contains these recordings. All of the rest of the stuff (call metadata, etc), takes just a couple of MB.
When I first reported to the company, I was aghast at the idea of storing such things in a database as most of my constituents across the world will likely be. The thing is... NO ONE will take care of data better than a DBA.
When I started working on using only file references, I actually found the table that did contain 1 file reference per wav file... 10% of the files were corrupt, 10% of the files were totally missing, and nearly 40% of the files had been moved to different directories. That's when I decided to continue to keep the WAV files in an SQL Server table. It also gave me impetus to do what should have been done from the git and partition the table. My full backups of the system vary from less than a minute at the beginning of a month to something less than 12 minutes at month end instead of the more than 10 hours it used to take 5 or 6 years ago... and I've not deleted any of the calls and the recorded call volume has increased almost 10-fold per month. There are some pretty easy methods for pulling all of this off and I've never lost a call file like they have on the file system.
So, bottom line is, "It Depends", as always. What I hope I've done is to provide food for thought that's quite a bit contrary to the typical/purported "Best Practices" recommendations and an example of where they actually sucked... a lot!
--Jeff Moden
Change is inevitable... Change for the better is not.
November 15, 2019 at 4:41 am
Thank you very much!!!!
I thought of a database integration because I am not even able to do the normal html css for structure and layout of the files using asp.net web technology. The gridviews, listviews etc works only upon connection string to database! The href tag didn't work and it is impossible to manually link the files and folders that way for a better design! I used GetFiles, GetDirectories method for a button click but only got a bunch of string path as display. In order to make it clickable, I need an additional technology support and that's where sql came into picture. It is supposed to be deployed in a company server! I do not know much about server technologies. For every image source tag for a book cover, it should open to a pdf file!
November 15, 2019 at 2:02 pm
Database or not, listen to me, listen to Jeff, listen to both of us, you should be able to access a file system and retrieve these objects for display without the need of SQL to make it happen. I fully endorse Jeff's experience and point of view (he knows this), so a database could work well for you. However, I'd put a database in because you need one as he outlines (or as I outlined), not because having a database will fix the code. That's a horrible reason to do it. If anything, I'd walk away from the database for a minute and focus on why you can't get the code to work. Databases also involve quite a bit more code, on top of the asp.net stuff. Adding to your overhead isn't going to make things easier.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
November 27, 2019 at 5:36 am
How about storing only the string values of filepaths in a database? When we did that, we had trouble displaying pdf or image in the webpage unless it is also stored in the visual studio project file. How can we store 50GB of data inside visual studio project file to get it to display/read?
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply