1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

Database Vs Files

Discussion in 'Web Development' started by shabbir, Apr 29, 2005.

?

What according to you is faster?

  1. Database

    16 vote(s)
    88.9%
  2. Files

    2 vote(s)
    11.1%
  1. shabbir

    shabbir Administrator Staff Member

    Joined:
    Jul 12, 2004
    Messages:
    15,292
    Likes Received:
    365
    Trophy Points:
    83
    There has been lots of web developer arguing about the fact as which one is faster Database or Files. I always replied in most cases as database is faster than Flat file and After getting that reply they always argued as why do you think that database is faster. I explained them with lots of words but now thought of just bench marking them so that its clear as what is faster.

    Before going into the codes I would like to explain about the attachments
    shabbir.txt - A Flat file that has 22400 names stored with one line containing one record.
    benchmark.sql - SQL Script to create a table with the same data as the flat file contains. Remember that it also has same order as the flat file

    Now here is the PHP code to be put in a file
    PHP:
          <?php
          
          
    echo("<h3>Test without condition!</h3>");
          echo 
    "Reading File ... <BR />";
          
          
    $start explode(" ",microtime());
          
    $beginfile $start[1]+$start[0];
          
          
    $h fopen("shabbir.txt","r");
          if (!
    $h) return;
              
    $msg="";
          
          while (!
    feof($h))
              
    fgets($h);
          
          
    $start explode(" ",microtime());
          
    $endfile $start[1]+$start[0];
          
          echo 
    "Reading Database ... <BR />";
          
          
    $start explode(" ",microtime());
          
    $begindb $start[1]+$start[0];
          
          
    $db=mysql_connect("localhost","root","");
          
    mysql_select_db("benchmark");
          
    $r mysql_query("Select f1 from t1");
          
          while(
    $f mysql_fetch_array($r));
          
          
    $start explode(" ",microtime());
          
    $enddb $start[1]+$start[0];
          
          echo 
    "Result of benchmark";
          
          
    $resultfile $endfile $beginfile;
          
    $resultdb $enddb $begindb;
          
          echo 
    "<BR />Flat File time span to do the operation ==> $resultfile .";
          echo 
    "<BR />Database time span to do the operation ==> $resultdb .<br/>";
          
          if(
    $resultdb>$resultfile)
          {
              
    $diff=$resultdb $resultfile;
              
    printf("File operation was faster by %f",$diff);
          }
          else
          {
              
    $diff=$resultfile-$resultdb;
              
    printf("Database operation was faster by %f",$diff);
          }
          
          
    mysql_close($db);
          
    fclose($h);
          
          
    //NEW TEST
          
    echo("<h3>Test with condition where names start with 'A'!</h3>");
          echo 
    "Reading File ... <BR />";
          
    $start explode(" ",microtime());
          
    $beginfile $start[1]+$start[0];
          
          
    $h fopen("shabbir.txt","r");
          
          while (!
    feof($h))
          {
              
    $line=fgets($h);
              if(
    eregi("^a",$line));
          }
          
          
    $start explode(" ",microtime());
          
    $endfile $start[1]+$start[0];
          
          echo 
    "Reading Database ... <BR />";
          
          
    $start explode(" ",microtime());
          
    $begindb $start[1]+$start[0];
          
          
    $db=mysql_connect("localhost","root","");
          
    mysql_select_db("benchmark");
          
    $r mysql_query("Select f1 from t1 where f1 like 'a%'");
          
          while(
    $f mysql_fetch_array($r));
          
          
    $start explode(" ",microtime());
          
    $enddb $start[1]+$start[0];
          
          echo 
    "Result of benchmark";
          
          
    $resultfile $endfile $beginfile;
          
    $resultdb $enddb $begindb;
          
          echo 
    "<BR />Flat File time span to do the operation ==> $resultfile .";
          echo 
    "<BR />Database time span to do the operation ==> $resultdb .<br/>";
          
          if(
    $resultdb>$resultfile)
          {
              
    $diff=$resultdb $resultfile;
              
    printf("File operation was faster by %f",$diff);
          }
          else
          {
              
    $diff=$resultfile-$resultdb;
              
    printf("Database operation was faster by %f",$diff);
          }
          
          
    //NEW TEST WRITING
          
    echo("<h3>Test writing to Database & File</h3>");
          echo 
    "Writing to File ... <BR />";
          
    $start explode(" ",microtime());
          
    $beginfile $start[1]+$start[0];
          
          
    $newfile=fopen("shabbir.txt",'a');
          
    fputs($newfile,"Name");
          
    fclose($newfile);
          
          
    $start explode(" ",microtime());
          
    $endfile $start[1]+$start[0];
          
          
    $start explode(" ",microtime());
          
    $begindb $start[1]+$start[0];
          
          echo 
    "Writing to Database ... <BR />";
          
    $db=mysql_connect("localhost","root","");
          
    mysql_select_db("benchmark");
          
    $r mysql_query("insert into t1(f1) values('Name')");
          
          
    $start explode(" ",microtime());
          
    $enddb $start[1]+$start[0];
          
          echo 
    "Result of benchmark";
          
          
    $resultfile $endfile $beginfile;
          
    $resultdb $enddb $begindb;
          
          echo 
    "<BR />Flat File time span to do the operation ==> $resultfile .";
          echo 
    "<BR />Database time span to do the operation ==> $resultdb .<br/>";
          
          if(
    $resultdb>$resultfile)
          {
              
    $diff=$resultdb $resultfile;
              
    printf("File operation was faster by %f",$diff);
          }
          else
          {
              
    $diff=$resultfile-$resultdb;
              
    printf("Database operation was faster by %f",$diff);
          }
          
    ?>
          
    Results for my machine *

    Test without condition!

    Reading File ...
    Reading Database ...
    Result of benchmark
    Flat File time span to do the operation ==> 0.087211132049561 .
    Database time span to do the operation ==> 0.13485312461853 .
    File operation was faster by 0.047642

    Test with condition where names start with 'A'!


    Reading File ...
    Reading Database ...
    Result of benchmark
    Flat File time span to do the operation ==> 0.11205410957336 .
    Database time span to do the operation ==> 0.018458127975464 .
    Database operation was faster by 0.093596

    Test writing to Database & File


    Writing to File ...
    Writing to Database ...
    Result of benchmark
    Flat File time span to do the operation ==> 0.00011086463928223 .
    Database time span to do the operation ==> 0.00024604797363281 .
    File operation was faster by 0.000135

    * can vary the time from machine to machine and from run to run depending on webserver loads and other network factors

    Inference

    As we can see from the above results
    File operations were faster when there is no condition applied to the results and so the first impression we get is Files are faster than database but if you take the scenario into real time site then hardly there is any time you fetch all the records from the database and so for consideration we can ignore this.

    Now applying a very simple condition where names start with 'A' and we see that the database operation is much faster than and practically similar operations are done more frequently on database where its database which is faster.

    All the sites tend to follow WORM - write once read many and so the write operation in files is faster but again this operation is not that considerable speed.
     

    Attached Files:

    Last edited: Apr 30, 2005
  2. coderzone

    coderzone Super Moderator

    Joined:
    Jul 25, 2004
    Messages:
    734
    Likes Received:
    37
    Trophy Points:
    0
    No doubt db.
     
  3. rapwaydown

    rapwaydown New Member

    Joined:
    Nov 28, 2007
    Messages:
    63
    Likes Received:
    0
    Trophy Points:
    0
    i still dont get the file and the database thing
     
  4. ReekenX

    ReekenX New Member

    Joined:
    Jan 19, 2007
    Messages:
    14
    Likes Received:
    0
    Trophy Points:
    0
    Occupation:
    Developer
    Home Page:
    Database is more secure to use than files, I think.
     
    Last edited: Jan 22, 2008
  5. venkatesanj@hcl.in

    venkatesanj@hcl.in New Member

    Joined:
    Oct 19, 2007
    Messages:
    24
    Likes Received:
    1
    Trophy Points:
    0
    1. Database provides a structured way of arranging datas instead of storing in irregular way

    2. Logical and physical grouping and faster retrieval are some of the major points to be noted in db.

    3. Querying and easy manipulation of stored datas can be achieved.

    Regards,
    Venkatesan Prabu. J
     
  6. Magena

    Magena Banned

    Joined:
    Jul 12, 2008
    Messages:
    20
    Likes Received:
    1
    Trophy Points:
    0
    Hi

    I also think DB is more secure than files.

    thanks
     
  7. Blagoj

    Blagoj New Member

    Joined:
    Oct 17, 2008
    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    0
    Databases are better organized and utilize complex queries while flat file is just a file.
    Databases are also faster.
     
  8. JGRobinson

    JGRobinson New Member

    Joined:
    Nov 15, 2008
    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    0
    Location:
    UK
    Home Page:
    It basically depends on what you are doing, and how busy the hardware is - horses for courses. But usually we are complex animals, and databases rock for that...
     
  9. hkp819

    hkp819 New Member

    Joined:
    Dec 4, 2008
    Messages:
    59
    Likes Received:
    1
    Trophy Points:
    0
    I also prefer database storage.

    The rest of my application data is already in there. A database can have
    better security, because it has its own permission and authentication
    system, while file-based sessions often end up owned by "nobody", which
    other users on a shared system can access.

    I'm not so concerned about "faster", since databse storage is "fast
    enough".

    I think the only reason /not/ to use database storage is because the
    database isn't be used for anything else. In that case, I would consider
    file storage for simplicity.
     
    Last edited by a moderator: Dec 6, 2008
  10. xpi0t0s

    xpi0t0s Mentor

    Joined:
    Aug 6, 2004
    Messages:
    3,012
    Likes Received:
    203
    Trophy Points:
    0
    Occupation:
    Senior Support Engineer
    Location:
    England
    Faster at what? A database will almost certainly be faster than writing your own code to do everything done, because it will already be debugged and optimised.

    But if you're trying to say that accessing the same data through a database is faster than reading the data directly from a file with stuff like fopen() fread() then you need your head examining. A database will need to lookup the index, work out which block it's in, setup read consistency, check for exclusive locks that would prevent you reading the data, and multitask that with all the other stuff that goes on. There's no way on this planet that database access will be quicker than simple file access.

    You wouldn't choose a database just because you need to store some data, you would choose it for the features it provides and for the fact that you don't have to write all that code yourself. If you don't need a database then just bung everything in flat files and take the performance benefit.

    > A database can have
    better security, because it has its own permission and authentication
    system, while file-based sessions often end up owned by "nobody"

    Wrong. Who owns the database files? Security of the filesystem is not a reason to pick database over flat file storage; make sure the files are owned by the relevant people and standard OS security will do the job. On the other hand, if the database files are owned by "nobody" then you get exactly the same problem: anyone can do anything with those files. The fact that you don't know how to setup security on data files is not an argument for a database.

    > Test with condition where names start with 'A'!
    > Flat File time span to do the operation ==> 0.11205410957336 .
    > Database time span to do the operation ==> 0.018458127975464 .
    > Database operation was faster by 0.093596

    You have to do same thing of course for the comparison to be meaningful. In the "names beginning with A" test where the database was faster, the non-database was hampered by the fact that it was doing a regexp comparison of "^a" on each record; that's going to be a hell of a lot slower than doing "if str[0]=='a'" which is basically all that a "like 'A%'" has to do, or maybe "like 'A%'" will do a strncmpi() or similar). The database part of it used "$r = mysql_query("Select f1 from t1 where f1 like 'a%'");" - like and eregi are completely different operations and I would suggest to be fair you should make the database do a regexp lookup instead and see how well it fares.

    Also I think the test for writing to a database and file could be improved substantially. Most of the time is going to be taken in opening and closing the file in the file test, and connecting and disconnecting in the database test. Instead of writing a single record, write 1,000,000 and divide the resulting time by 1,000,000 to get a time per record instead; this will be a far more meaningful comparison. Also you could open the file and connect to the database FIRST, then start the timing, write the data, stop the timing, then close the file and disconnect, then the timings will only relate to the record write and not to all the other gubbins.

    File access WILL be faster, no question, because except for in-memory databases, a database will have to get the data from a file anyway, plus all the other RDBMSy stuff it has to do. Of course, it's perfectly possible to write highly optimised code against the database and really bad code against the files, and the results will be skewed in favour of the database. That doesn't prove the database is faster, it only proves that crap code is slower than good code.

    Also you'll need to run the benchmark test several times to eliminate caching effects. The first run will be skewed by the fact that everything has to be loaded from disk and subsequent runs will be more reliable.
     
    Last edited: Dec 6, 2008
    shabbir likes this.
  11. shabbir

    shabbir Administrator Staff Member

    Joined:
    Jul 12, 2004
    Messages:
    15,292
    Likes Received:
    365
    Trophy Points:
    83
    Even at the first Run with some condition they are faster.
     
  12. smithshn

    smithshn New Member

    Joined:
    May 3, 2010
    Messages:
    7
    Likes Received:
    0
    Trophy Points:
    0
    Database is a collection of data and its format is fixed.
    Database can not access as fast as file the reason is first
    user has require to connect with it.
    As security purpose database is best option.

    While File is in different format.
    File access will be faster than the database.
    The reason is that file is store in same memory.
     
  13. jhon786

    jhon786 New Member

    Joined:
    Oct 12, 2011
    Messages:
    46
    Likes Received:
    0
    Trophy Points:
    0
    Databases are better organized and utilize complex queries while flat file is just a file.
    Databases are also faster.File Management system is old system and time consuming also. And i also voted to Database.
     
  14. pein87

    pein87 New Member

    Joined:
    Aug 6, 2010
    Messages:
    173
    Likes Received:
    47
    Trophy Points:
    0
    Occupation:
    Web Dev
    Location:
    Limbo
    I think most of you need to retake your college programming classes. Flat file will always be faster then a database. Your comparing the output of a compiled language to that of an interpreted one. Not only does the file need to be read by the parser, it has to be checked for errors, turned to byte code and then to opcode for that system and then be executed. In this case a database is normally written in C or C++ and is compiled down already to machine code. It only needs to be loaded into memory and executed. If you wrote this in C it would more then double the flat file results. For php to show such results is fairly impressive but your implementation could be better to speed up the process in php. If it where coded better it would out perform it in all aspects simply because the number of operations it performs would be less regardless of the language it was written in. Your argument is flawed because you do not specify a specific language and arrogantly say that it is faster then flat file. In python, a language actually meant to do such things, it would be faster then the database. If you wrote the code better it would be faster in all areas in php. You have to take into account your using unneeded functions and process. Also php's stl is not the best written either.
     

Share This Page