473,220 Members | 2,164 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,220 software developers and data experts.

flat file vs sql database

I'm hoping I might be able to pick someone's brain out there.

Our company is currently running an inhouse application in which data
is being stored in SQL. Included in this data are thousands (over
100,000) of .jpg images.

I am starting to run out of disk space and a developer suggested that
we take the images out of SQL and put them in a file. In doing they
indicated we could recover the SQL database faster if needed, we could
also manage the backup of the SQL server easier.

Does this make sense?

Nov 23 '05 #1
8 2548
The short answer is "yes" it does make sense. Beyond that, I'm not sure what
your specific question is...


"Inspector" <Jn*****@gmail.com> wrote in message
news:11**********************@g49g2000cwa.googlegr oups.com...
I'm hoping I might be able to pick someone's brain out there.

Our company is currently running an inhouse application in which data
is being stored in SQL. Included in this data are thousands (over
100,000) of .jpg images.

I am starting to run out of disk space and a developer suggested that
we take the images out of SQL and put them in a file. In doing they
indicated we could recover the SQL database faster if needed, we could
also manage the backup of the SQL server easier.

Does this make sense?

Nov 23 '05 #2
thanks for your reply. If I can ask another question, what are the
benefits of going with a the .jpg images in a file server rather than
sql.

Nov 23 '05 #3
Currently we are running a db of 300 gigs. In 2006 this could double.
Would this have any impact on the disk solution? Thanks for your help.

Nov 23 '05 #4
> thanks for your reply. If I can ask another question, what are the
benefits of going with a the .jpg images in a file server rather than
sql.


If they are in the database, then aspnet needs to retrieve that image
from the db-server and send it to the browser. If the images are on the
filesystem, then IIS can serve them, without bothering either aspnet or
the db. Also the client can have them cached. So it saves a lot of
processing.

You loose on security: anyone can retrieve those images, if they know
the filename (but that might not be a problem for you).

Hans Kesting
Nov 23 '05 #5
The problem I am facing is that the most important information (I'll
call it content) in the db accounts for 5% of the space. So in
considering back-up and recovery it makes sense to separate the content
from the images.

I guess my question would be, in the long run is the money better spent
moving the images into a new db or moving it to a file? That is,
assuming the programming for both is the same.

FYI - the reason this may sound so scrambled is that I am the COO not
the CIO. We have lost our development team and the area we are in does
not have a lot to choose from. I am concerned I may be dealing with a
new development team that can talk the talk, but when it comes to
walking the walk their knowledge isn't there.

Thank you for you time.

Nov 23 '05 #6
Okay, I'll give some general guidelines beyond the valid points made
elsewhere in this thread by Hans Kesting.

Regarding your question "is the money better spent... images in db vs in
files"...
I'll have to preface my response by saying that we are very much engaged in
*art* when it comes to answering the question, "how do we implement featrue
xyz?" So nobody here can tell you definitively what you should do. What we
can do is try to tell you what is better given certain facts, and only those
facts. If we get additional facts about your scenario then we might offer
completely different advice and rationalle.

That being said, it's generally easier to work with images when they are
stored in files and not in a database. It is a standard practice to store
information *about* the images in the database, but the actual images go
each into their own file on the server. The images/files are generally
organized into their own sensible folder structure. The information "about
the images" stored in the datbase could be extensive - and one piece of such
information is the name of the particular file (complete with path)
containing the image. This is also a cleaner implementation because the
database can do it's specialized job of sorting and searching and organizing
information (about your images); while the disk system does its specialized
job of serving up files (the actual images). The database will also perform
much faster if it's not dealing with images.

Regarding expense (money better spent)...
I suspect you are thinking strictly about hardware costs (i.e., new databse
server to handle expected 2006 volumes)... new database server or not (or
perhaps increased capacity of existing server). If you do not in fact need
to store the images in the database and you have plenty of available disk
space, then you might better spend the money elsewhere because the new
database server would be unnecessary (point favors going to files). Of
course if you decide to move all the images out of the database, then there
is real money to be spent in modifying your existing application(s) that
currently retrieve from the database. That could be far more than the
expense of a new database server (point favors keeping images in database).
Of course if you put "too many" images in the database in 2006 and it gets
"too big" then it would perform unacceptably; the whole system might also
become less reliable (i.e., it breaks more frequently) - in which case you
might eventually be "forced" to move all of the images out of the database
(point favors moving images out of database). Then there is the issue of
ongoing maintenance. It's generally easier and quicker to back up a database
without all those images in it (point favors moving images out of database).

It would generally be easier, IMO as an applications programmer and database
administrator, to deal with images stored in files and not in the database.
Of course if you choose that route, then you immediately incur some expense
in modifying your application(s) to retrieve images from disk (and not from
db). That short-term expense may be far more than the short-term cost of a
new database server. So, you could chose to go with the new database server
in order to save a buck now... but when the volumes make the system
unweildly in 2006 and beyond, you may be forced to move the images out
anyway... in which case you would have the most expensive long-term scenario
on your hands (paying for both a new database server AND development costs
required to rework your applications).

I hope this is helpful.

Jeff


"Inspector" <Jn*****@gmail.com> wrote in message
news:11**********************@o13g2000cwo.googlegr oups.com...
The problem I am facing is that the most important information (I'll
call it content) in the db accounts for 5% of the space. So in
considering back-up and recovery it makes sense to separate the content
from the images.

I guess my question would be, in the long run is the money better spent
moving the images into a new db or moving it to a file? That is,
assuming the programming for both is the same.

FYI - the reason this may sound so scrambled is that I am the COO not
the CIO. We have lost our development team and the area we are in does
not have a lot to choose from. I am concerned I may be dealing with a
new development team that can talk the talk, but when it comes to
walking the walk their knowledge isn't there.

Thank you for you time.

Nov 23 '05 #7
Yes. Thank you very much.

Nov 23 '05 #8
Thanks Stan. You all have provided some great information that I can
take back to my developer and IT management team. I appreciate it.

Nov 25 '05 #9

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

7
by: mir nazim | last post by:
hi. i want to know if there is any flat-file relational database system available for python. i require it in a project. it should have following capabilities: 1. should be small and compact on...
13
by: raykyoto | last post by:
Hi all, I'm sure this is a popular question that comes up every few months here. Indeed, I've looked at some of the past postings, but I would like to ask things differently. Basically, I'm...
1
by: Tim Fierro | last post by:
Hello, I have had many years using flat file databases (File Express from way back) but am now at a company where a relational database is needed and would carry us into the future. Since I...
4
by: Ben | last post by:
So, at my place of employment, we use a national standard to transmit data between certain applications. This standard consists of a fixed width, flat file 4500-some-odd chars wide that contain...
14
by: vunet.us | last post by:
Hi, I would like to use flat file data storage instead of database. Since I am new to it, I am wondering: What text file extension is a safe one to store my data online and how cost- and...
9
by: FFMG | last post by:
In my site I have a config table, (MySQL), with about 30 entries; the data is loaded on every single page load. This is not the only call to the db, (we do a total of about 8 calls to the db). As...
9
by: =?Utf-8?B?TmF2YW5lZXRoLksuTg==?= | last post by:
Hello I am developing a system for user tracking. In this I am tracking all the visitors that came to my website. i developed this using a HTTPModule and storing the data in a SQL Server...
15
by: lxyone | last post by:
Using a flat file containing table names, fields, values whats the best way of creating html pages? I want control over the html pages ie 1. layout 2. what data to show 3. what controls to...
1
by: lxyone | last post by:
Using a flat file containing table names, fields, values whats the best way of creating forms / html pages? I want control over the html pages ie 1. layout 2. what data to show 3. what...
3
by: Flugeldorph | last post by:
I am new to Visual Basic, my preferred languages is C or C++. I am trying to load a local database with data that I am reading from a delimited flat file. I have created the database tables and...
1
isladogs
by: isladogs | last post by:
The next online meeting of the Access Europe User Group will be on Wednesday 6 Dec 2023 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, Mike...
0
by: VivesProcSPL | last post by:
Obviously, one of the original purposes of SQL is to make data query processing easy. The language uses many English-like terms and syntax in an effort to make it easy to learn, particularly for...
0
by: abbasky | last post by:
### Vandf component communication method one: data sharing ​ Vandf components can achieve data exchange through data sharing, state sharing, events, and other methods. Vandf's data exchange method...
2
by: jimatqsi | last post by:
The boss wants the word "CONFIDENTIAL" overlaying certain reports. He wants it large, slanted across the page, on every page, very light gray, outlined letters, not block letters. I thought Word Art...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
0
by: fareedcanada | last post by:
Hello I am trying to split number on their count. suppose i have 121314151617 (12cnt) then number should be split like 12,13,14,15,16,17 and if 11314151617 (11cnt) then should be split like...
0
by: stefan129 | last post by:
Hey forum members, I'm exploring options for SSL certificates for multiple domains. Has anyone had experience with multi-domain SSL certificates? Any recommendations on reliable providers or specific...
1
by: davi5007 | last post by:
Hi, Basically, I am trying to automate a field named TraceabilityNo into a web page from an access form. I've got the serial held in the variable strSearchString. How can I get this into the...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.