homepage Welcome to WebmasterWorld Guest from 54.237.98.229
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member

Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderator: open

Databases Forum

    
SQL versus flat files
Why is SQL so popular?
encyclo

WebmasterWorld Senior Member encyclo us a WebmasterWorld Top Contributor of All Time 10+ Year Member



 
Msg#: 7 posted 5:05 pm on Sep 5, 2005 (gmt 0)

Nowadays every open source CMS or other script, down to the most basic of ideas such as simple password-protection scripts, demand the use of a (usually) MySQL database to function. Simple scripts using Berkeley DB files or even plain text files are seen as marginal, too limited or too awkward for use. I have several sites which run a simple CMS based on plain text files, and I can't see a reason to switch.

Is MySQL overkill for many uses, or does it offer any significant advantages over flat files other than the fact that you can't reliably use flat files when load-balancing between different servers? Flat files are usually faster, aren't they? What do MySQL/PostgreSQL/MS-SQL offer that makes them so popular for web development?

 

arran

5+ Year Member



 
Msg#: 7 posted 5:48 pm on Sep 5, 2005 (gmt 0)

Very strange - i was thinking about this yesterday!

SQL (the query language) is the main reason i abandoned my plan to switch from database to flat-files. Although not perfect, it's extremely easy to quickly throw together all the queries you need for a site. The thought of writing a hacky php function every time you think of a new way to display/sort information doesn't appeal.

Other reasons that come to mind are:

- Transactions
- Scalability (Indexing in particular)
- Security

In general, i do agree that many sites could live without databases and in terms of performance would definitely benefit from switching to flat-files.

arran.

txbakers

WebmasterWorld Senior Member txbakers us a WebmasterWorld Top Contributor of All Time 10+ Year Member



 
Msg#: 7 posted 6:44 pm on Sep 5, 2005 (gmt 0)

Flat files are usually faster

Flat files are NOT faster. they can only be read from top to bottom, and usually they have to be read all the way through.

A good database engine will take a query (Select field from table where field = '#*$!x') and first determine the best way to go into the database to solve the query, then leave the processing when the query is solved.

Another main reason to use databases is the ability to minimize the repetitions of data. Think about having multiple spreadsheets for a project. If you have an ID number assigned to a particular item, you only need to reference the ID number between different sets of data, rather than replicating all of the data for each sheet.

Database tables give you the ability to do this easily with "joins".

Still, if all you need to do is validate passwords, a flat file might be easier to use. But beyond that, you're much better off using a database.

Lord Majestic

WebmasterWorld Senior Member 10+ Year Member



 
Msg#: 7 posted 6:53 pm on Sep 5, 2005 (gmt 0)

Good databases have key advantage over flat files -- concurrency. When you just read stuff from file its easy, but try to syncronise multiple updates or writes into flat file from scripts that run in different process spaces.

AffiliateDreamer

WebmasterWorld Senior Member 10+ Year Member



 
Msg#: 7 posted 2:38 am on Sep 7, 2005 (gmt 0)

say you go the flat file route....(maybe i'll start a new thread)

I'm curious as to how many files in a single directory have you guys stored in the past?

I mean, can you store 1 million files in a folder on a windows server? unix server?

Will file access times differ between unix and windows2003?

Lord Majestic

WebmasterWorld Senior Member 10+ Year Member



 
Msg#: 7 posted 2:46 am on Sep 7, 2005 (gmt 0)

I mean, can you store 1 million files in a folder on a windows server? unix server?

Possible, but this is a very bad idea to have so many files -- I know, I have got 130,000+ files in one directory :o

physics

WebmasterWorld Senior Member 10+ Year Member



 
Msg#: 7 posted 3:04 am on Sep 7, 2005 (gmt 0)

Storing a lot of flat files in one directory is a very bad idea. There is a serious slowdown for the directory listing (though some unix file systems are designed so that this isn't as much of a problem, but not the common ext3). If you must use tons of flat files you should split them up into different directories so that each directory doesn't have too many files in it... But really your time will be better spent getting MySQL running.

mrMister

WebmasterWorld Senior Member 5+ Year Member



 
Msg#: 7 posted 1:32 pm on Sep 8, 2005 (gmt 0)

I use flat files to cache database output for data that doesn't change very often.

I pull the data out of the database and store it in a flat file. It is quicker, especially if you have a lot of processing to do (if you're using joins for example) whenever you retreive the data.

Hester

WebmasterWorld Senior Member 10+ Year Member



 
Msg#: 7 posted 9:03 am on Sep 15, 2005 (gmt 0)

Hmm, this forum is actually built on flat files!

aspdaddy

WebmasterWorld Senior Member 10+ Year Member



 
Msg#: 7 posted 11:56 am on Sep 15, 2005 (gmt 0)

It has nothing to do with speed or performance. Simple flat files will always out-do a database because manipulating them is closer to the machine language.

The main reason for databases is independance from hardware, user intefaces and data integrity.

trillianjedi

WebmasterWorld Senior Member trillianjedi us a WebmasterWorld Top Contributor of All Time 10+ Year Member



 
Msg#: 7 posted 11:59 am on Sep 15, 2005 (gmt 0)

I use flat files to cache database output for data that doesn't change very often.

I also use that type of hybrid design and have found it gives the best performance/data integrity balance.

Most CMS's are designed to do everything. That functionality always comes with a compromise.

Absolutely any CMS can be customised to work better for you than it does straight out of the box. Reducing unnecessary queries by removing them, or caching them, is one of the simplest things you can do that makes a significant performance difference.

I have several sites which run a simple CMS based on plain text files, and I can't see a reason to switch.

Great - a good sign of slick software and no unnecessary bloat.

I think it's a horses for courses thing. If you have a largely static site in terms of the data type you're displaying (i.e. what would be the DB structure in a DB based site doesn't ever need to change) then flatfile is great. And fast.

If you need to budget in the future for either complex load-balancing, or a structure of data that will need to grow and change, then a DB is probably the favourite choice - for reasons of management rather than speed.

Is MySQL overkill for many uses.....?

Yes. But given the low cost these days of an incredibly fast server, fast hard disk and plenty of RAM, does it really matter all that much?

Why is SQL so popular?

Convenience, simplicity of design, portability and easy management of content data.

TJ

physics

WebmasterWorld Senior Member 10+ Year Member



 
Msg#: 7 posted 6:19 pm on Sep 15, 2005 (gmt 0)


It has nothing to do with speed or performance. Simple flat files will always out-do a database because manipulating them is closer to the machine language.

This is first of all assuming that the code that you write is 'more efficient' than the code that the developers of the database software have written. Plus you have to take the time to write all of those functions and debug them. Also, there are other things to consider such as the fact that if you have tons of data stored on a hard disk in all different files then every time you want to access a file a file handle has to be opened at some random point on the disk, etc. With a database it's stored in a 'central' location and the handle is open... There are also all sorts of creature comforts like the MySQL slow query log and things like mytop so you can keep track of what queries might be slowing your system down. I recently wrote an application that initially used flat files, then switched to MySQL, the speedup was immediately evident. Finally, yes many sites are based on flat files but they would probably be better off with an 'advanced' database system.

Hester

WebmasterWorld Senior Member 10+ Year Member



 
Msg#: 7 posted 3:39 pm on Sep 16, 2005 (gmt 0)

Only one file to backup too.

webprofessor

5+ Year Member



 
Msg#: 7 posted 2:10 am on Sep 19, 2005 (gmt 0)

>> Simple flat files will always out-do a database because manipulating them is closer to the machine language.

What a silly thing to say. Do you know how php handles flat files? Do you know how asp handles flat files? Do you know how C++ handles flat files ( which realy the notion of which doesn't even exist ) All do it diffferently and with different advantages and disadvantages.

Do you mean all flat files?
Are you using locks and semaphores?
Are you doing row processing?
Are you searching the flat file?
If your searching how many lines are in the flatfile?
Are there even lines?
And here's the doozy.. do you even know how to write assembly and what advantages it brings by being closer to the metal?

ALWAYS is a term you shouldn't use. Further more for anything data sets of signifigent size or doing anything signifigent with usually you are wrong. It takes quite a skilled programmer to use flat files to manage large amounts of data effeciently.

** anyways back to the point of the thread. **

The use of SQL allows for modular design of your application. If you think this is a one time app and you'll rarely if ever need to change it or its data, then a flat file may be the choice for you.

I always use SQL when there is the possibility of having the scale the application larger or the complexity of the data may increase. The reason being is that for me it is easier to change a sql based application than rewrite all my functions for parseing files.

If your worried about performance, getting a better server may be a solution for you. Processing power is cheap these days. For me it is cheaper than the amount of time it takes me to write code.

FIN

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / Databases
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved