SitePoint Sponsor

User Tag List

Page 1 of 2 12 LastLast
Results 1 to 25 of 30
  1. #1
    SitePoint Enthusiast technov5's Avatar
    Join Date
    Dec 2006
    Location
    India
    Posts
    52
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Is retrieving frm database faster or from a file

    hi,

    Is retrieving data from the database faster or getting it from text files on the server. Each entry will have a different file and the file name can be something like the username or something which is known and the database wont have to be searched to get the filename.

    Thank You
    I am back, with new designs.

  2. #2
    SitePοint Troll disgracian's Avatar
    Join Date
    Aug 2006
    Location
    Samsara
    Posts
    451
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    The filesystem will be faster with a small amount of files/records, but a database will scale much better. Having said that, the filesystem will never be perceptible faster (the difference would be measurable in milliseconds).

    If a database is available, you're almost certainly better off going that way.

    Cheers,
    D.

  3. #3
    From space with love silver trophy
    SpacePhoenix's Avatar
    Join Date
    May 2007
    Location
    Poole, UK
    Posts
    5,067
    Mentioned
    103 Post(s)
    Tagged
    0 Thread(s)
    How big will the files be? Do you really want 1000s of files potentially a couple of MB in size, clogging up your database when they could be stored in a file system which surprisingly is designed for the storage of files?
    Community Team Advisor
    Forum Guidelines: Posting FAQ Signatures FAQ Self Promotion FAQ
    Help the Mods: What's Fluff? Report Fluff/Spam to a Moderator

  4. #4
    Programming Since 1978 silver trophybronze trophy felgall's Avatar
    Join Date
    Sep 2005
    Location
    Sydney, NSW, Australia
    Posts
    16,862
    Mentioned
    25 Post(s)
    Tagged
    1 Thread(s)
    A database is just a collection of files with a fast lookup system built in.
    Stephen J Chapman

    javascriptexample.net, Book Reviews, follow me on Twitter
    HTML Help, CSS Help, JavaScript Help, PHP/mySQL Help, blog
    <input name="html5" type="text" required pattern="^$">

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,323
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    oh stephen, that's ridiculous

    a "fast lookup system" indeed
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    Programming Since 1978 silver trophybronze trophy felgall's Avatar
    Join Date
    Sep 2005
    Location
    Sydney, NSW, Australia
    Posts
    16,862
    Mentioned
    25 Post(s)
    Tagged
    1 Thread(s)
    Quote Originally Posted by r937 View Post
    oh stephen, that's ridiculous

    a "fast lookup system" indeed
    I was just keeping it simple in connection with the question. It is basically a simple definition of what a database is though so it can't possibly be ridiculous.

    The whole purpose of a database is to put a layer on top of the files that makes finding the required information from those files in a quick and efficient manner.

    So with any significant amount of data the files with the extra layer that using a database provides will generally allow the data to be accessed more quickly than any search you wrote yourself to do the same lookup.

    If using the extra layer provided by the database didn't make things quicker then people would write their programs to access the data directly bypassing that layer.
    Stephen J Chapman

    javascriptexample.net, Book Reviews, follow me on Twitter
    HTML Help, CSS Help, JavaScript Help, PHP/mySQL Help, blog
    <input name="html5" type="text" required pattern="^$">

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,323
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by felgall View Post
    ... so it can't possibly be ridiculous.
    on the contrary, if there is any possibility at all, no matter how small--and there is!!!--then, yes, it is ridiculous

    Quote Originally Posted by felgall View Post
    The whole purpose of a database is to put a layer on top of the files that makes finding the required information from those files in a quick and efficient manner.
    see, there you go again

    the "whole" purpose? no, not even close -- what about enforcing relational integrity?

    see what i mean? your over-simplification is ridiculous

    if i've told you once, i've told you a million times, don't exaggerate!!!





    Quote Originally Posted by felgall View Post
    If using the extra layer provided by the database didn't make things quicker then people would write their programs to access the data directly bypassing that layer.
    this is ludicrous, as only a handful of extremely skilled engineers know how to read a database file natively
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  8. #8
    SitePοint Troll disgracian's Avatar
    Join Date
    Aug 2006
    Location
    Samsara
    Posts
    451
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    One thing I didn't think of until the reply after mine, was the scenario of storing the files themselves in the filesystem, and paths to those files in the database.

    It's a slightly contentious issue among DBAs (and r937 will probably have a more informed opinion on this than I do, so I'll just present the options and leave value judgements out), but you can go one of two ways:
    1. Store the contents of the files directly in the database as some sort of BLOB field (depends on the DBMS). Most databases have a datatype for arbitrarily large amounts of binary or text data.
    2. Store the files in the filesystem, and store the path to those files in the database instead of the files themselves, along with other metadata about them that the filesystem doesn't provide. The files then can be named anything because the database will handle the lookups. This will prevent, in your situation, potentially having to rename a file if somebody's username changes.

    Cheers,
    D.

  9. #9
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,323
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    in that scenario, d, i would opt to store the files in the filesystem

    the meta-data (file creator, description of contents, keywords, tags, number of hits, et cetera) would of course be supremely suited for database storage and searching

    the only advantage i can think of that storing files as BLOBs gives you is that the backup strategy is rather simple -- just back up the database, no worries about synchronizing the database backup with the filesystem backup

    the disadvantages, of course, are legion
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  10. #10
    SitePοint Troll disgracian's Avatar
    Join Date
    Aug 2006
    Location
    Samsara
    Posts
    451
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Another advantage to the BLOB approach is that access to those files is now regulated by the DBMS, there's no need to duplicate it in the FS and possibly have to recreate it if the database moves to another server (and possibly a different FS).

    Cheers,
    D.

  11. #11
    Programming Since 1978 silver trophybronze trophy felgall's Avatar
    Join Date
    Sep 2005
    Location
    Sydney, NSW, Australia
    Posts
    16,862
    Mentioned
    25 Post(s)
    Tagged
    1 Thread(s)
    Quote Originally Posted by r937 View Post
    what about enforcing relational integrity?
    Flat file databases, heirarchical databases, network databases, and object oriented databases don't even have the concept of relational integrity to enforce. So since referential integrity as a concept isn't even a part of most database model theory there in no way that the implementation of those databases would even attempt to enforce it. Only with a relational database is referential integrity even a concept that the database might choose to enforce and in many cases even relational databases do not properly enforce referential integrity (eg. it is impossible to properly enforce referential integrity where any fields in the database are allowed to exist without having a specific value in the field).

    Quote Originally Posted by r937 View Post
    this is ludicrous, as only a handful of extremely skilled engineers know how to read a database file natively
    Obviously if you are not going to use the database layer of a particular database model then you'd use a different format for the actual data. Each database uses its own internal file format based on what the actual database processing layer is going to need and so in many cases your generalisation is completely wrong since the data in many database models is designed to be able to be read directly as well as via the database calls. Again you are limiitiing yourself to a specifiic implementation of a database rather than considering databases in general.

    A flat file IS a database where you get to build your own access layer on it and so if you built it properly then that database would be the quickest way of accessing your data. So the question of database versus flat file where a flat file can be a database is a rather meaningless question anyway.

    You are obviously taking a very narrow view as to just what a database is that only includes one particular database model since relational integrity is not even considered for most types of databases since those models don't require referential integrity as a part of that model.

    Providing a standard way of accessing the data that will be the quickest way under normal circumstances is the ONLY thing that all the different types of database have in common.
    Stephen J Chapman

    javascriptexample.net, Book Reviews, follow me on Twitter
    HTML Help, CSS Help, JavaScript Help, PHP/mySQL Help, blog
    <input name="html5" type="text" required pattern="^$">

  12. #12
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,323
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    stephen, give it a rest

    i have years of experience with IMS and IDMS (precursors to the relational model)

    you're just back-pedalling, as usual

    (eg. it is impossible to properly enforce referential integrity where any fields in the database are allowed to exist without having a specific value in the field)....

    Again you are limiitiing yourself to a specifiic implementation of a database rather than considering databases in general...

    So the question of database versus flat file where a flat file can be a database is a rather meaningless question anyway.
    i'm calling you out, this is just more of your bafflegab and bullcrap
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  13. #13
    Programming Since 1978 silver trophybronze trophy felgall's Avatar
    Join Date
    Sep 2005
    Location
    Sydney, NSW, Australia
    Posts
    16,862
    Mentioned
    25 Post(s)
    Tagged
    1 Thread(s)
    Quote Originally Posted by r937 View Post
    i have years of experience with IMS and IDMS (precursors to the relational model)
    Those aren't precursors to the relational model.

    IMS is a heirarchical database that has no connection to relational databases whatsoever and no way to build any meaningful relational layer onto it.
    IDMS was a network database the last time I used it (admittedly 20 years ago) it may have had a relational database layer tacked on since then but I'd expect that it would still be able to be used as a network database.

    Neither heirarchical nor network databases enforce referential integrity.

    When I first learnt to use IMS there was a lot made of how selecting the appropriate file structure with the database in order to allow the database to access the data in the most efficient manner. The same sort of information was also presented with regard to IDMS. When I first learnt about relational databases the point was made that the difference between a network and a relational database is that a relational database has an optimiser built in whic optimises the access to the data so as to make it as efficiently as possible based on what is known about the data.

    With every database that I have ever worked with (including those I used before learning IMS and the several relational databases I have worked with since) the main point that has always been emphasised as to why a database is used rather than accessing the data in the files directly is all down to providing a consistent and FAST method of handling all the different sorts of access requests. You can write your own access module to handle a specific query on a flat file that works more efficiently than a database call but any other accesses to that data will then be less efficient than using a database would have been.

    So I'll repeat the correct information that I said in my first post A database is just a collection of files with a fast lookup system built in. That remains a correct summary regardless of what names anyone calls me for stating it that way. Everything else I've said in this thread explains what that sentence means in more detail.
    Last edited by felgall; Jan 8, 2010 at 20:57.
    Stephen J Chapman

    javascriptexample.net, Book Reviews, follow me on Twitter
    HTML Help, CSS Help, JavaScript Help, PHP/mySQL Help, blog
    <input name="html5" type="text" required pattern="^$">

  14. #14
    Follow Me On Twitter: @djg gold trophysilver trophybronze trophy Dan Grossman's Avatar
    Join Date
    Aug 2000
    Location
    Philadephia, PA
    Posts
    20,580
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    A database need not have multiple files (a MySQL InnoDB database has only one data file, in-memory databases have none), precursor does not mean "implements the relational model", and fast lookup is not a universal property or goal of all databases. When you get called out for making things up, and you *know* the people calling you out aren't likely to be ignorant about the subject, maybe you could back down sometimes. It'd save a lot of writing.

  15. #15
    From space with love silver trophy
    SpacePhoenix's Avatar
    Join Date
    May 2007
    Location
    Poole, UK
    Posts
    5,067
    Mentioned
    103 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by disgracian View Post
    One thing I didn't think of until the reply after mine, was the scenario of storing the files themselves in the filesystem, and paths to those files in the database.

    It's a slightly contentious issue among DBAs (and r937 will probably have a more informed opinion on this than I do, so I'll just present the options and leave value judgements out), but you can go one of two ways:
    1. Store the contents of the files directly in the database as some sort of BLOB field (depends on the DBMS). Most databases have a datatype for arbitrarily large amounts of binary or text data.
    2. Store the files in the filesystem, and store the path to those files in the database instead of the files themselves, along with other metadata about them that the filesystem doesn't provide. The files then can be named anything because the database will handle the lookups. This will prevent, in your situation, potentially having to rename a file if somebody's username changes.

    Cheers,
    D.
    I was considering which way to go myself for file storage, i went for storing it in the file system and recording the necessary info (file type, mine, etc) in a db table.

    With regard to storing them in BLOBs, what would you do if you had to move servers and for whatever reason BLOBs were not supported, you'd be screwed. By storing them in the file system you don't have to worry about that. Say you were storing photos and say they were about 5MB each (they would probably be bigger files than that with the number of megapixels really modern cameras have), would you want a few thousand or even a few hundred of them clogging up your database? I wouldn't.

    By keeping the files in the file system they wouldn't necessarily have to be kept on the same server, which would give a bit more flexibility as to data backup. What happens if your database becomes (for whatever reason) corrupted beyond recovery, if you store the files in the database, you loose absolutely everything but if you store them in the file system, you will have still lost your database but you will still have the files.
    Community Team Advisor
    Forum Guidelines: Posting FAQ Signatures FAQ Self Promotion FAQ
    Help the Mods: What's Fluff? Report Fluff/Spam to a Moderator

  16. #16
    Follow Me On Twitter: @djg gold trophysilver trophybronze trophy Dan Grossman's Avatar
    Join Date
    Aug 2000
    Location
    Philadephia, PA
    Posts
    20,580
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    It also opens the possibility of offloading the file storage to a 3rd party like Amazon S3 while running the database locally, or serving the files from a web server optimized for static files rather than the one running the application/code.

  17. #17
    SitePοint Troll disgracian's Avatar
    Join Date
    Aug 2006
    Location
    Samsara
    Posts
    451
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by SpacePhoenix View Post
    With regard to storing them in BLOBs, what would you do if you had to move servers and for whatever reason BLOBs were not supported, you'd be screwed.
    I think most popular/serious DBMSs have this, but the differences between them would be a pain, as well as some of the arbitrary limitations that aren't imposed by a decent filesystem. I might be wrong, but I think Postgres has a 1GB limit.
    would you want a few thousand or even a few hundred of them [image files] clogging up your database? I wouldn't.
    I'm not sure what the difference would be in this case. I don't see how they'd "clog up" a database any worse than they'd clog up a filesystem.
    By keeping the files in the file system they wouldn't necessarily have to be kept on the same server, which would give a bit more flexibility as to data backup.
    Wouldn't a distributed database give you the same option?
    What happens if your database becomes (for whatever reason) corrupted beyond recovery
    That's more of an arbitrary "what if". What if the filesystem becomes corrupt? What if, because of a lack of proper locking, the files become corrupt by two simultaneous writes?

    Cheers,
    D.

  18. #18
    From space with love silver trophy
    SpacePhoenix's Avatar
    Join Date
    May 2007
    Location
    Poole, UK
    Posts
    5,067
    Mentioned
    103 Post(s)
    Tagged
    0 Thread(s)
    Found this, it is an intresting read. Looks like flickr store there images in their file system. I dread to think how huge their database would be if they stored the actual images in it (or how long it would take to execute queries).
    Community Team Advisor
    Forum Guidelines: Posting FAQ Signatures FAQ Self Promotion FAQ
    Help the Mods: What's Fluff? Report Fluff/Spam to a Moderator

  19. #19
    SitePοint Troll disgracian's Avatar
    Join Date
    Aug 2006
    Location
    Samsara
    Posts
    451
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    With suitable indexing I can't see how it would affect query speed in any significant way.

    Cheers,
    D.

  20. #20
    Follow Me On Twitter: @djg gold trophysilver trophybronze trophy Dan Grossman's Avatar
    Join Date
    Aug 2000
    Location
    Philadephia, PA
    Posts
    20,580
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    AFAIK, the DB would only store a pointer to the BLOB data in each row, so the table would still have similar performance characteristics as only storing a filename. Only when the final result set needs to be returned does it need to follow that pointer and get the BLOB data off disk.

  21. #21
    SitePoint Enthusiast
    Join Date
    Dec 2009
    Posts
    39
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by technov5 View Post
    Is retrieving data from the database faster or getting it from text files on the server.
    I would say that in theory file storage is faster as you can optimize it perfectly for your specific task. In practice however, it will be hard to optimize better than your database system, and it will probably save you a lot of implementation time to use a database (which will also scale better).

    Many database systems use a server, which means it involves interprocess communication causing some delay compared to reading from file (again, most likely this will be a theoretical difference). One exception is SQLite, which doesn't involve a server process and has every database stored in a single file.

  22. #22
    SitePοint Troll disgracian's Avatar
    Join Date
    Aug 2006
    Location
    Samsara
    Posts
    451
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Dan Grossman View Post
    AFAIK, the DB would only store a pointer to the BLOB data in each row, so the table would still have similar performance characteristics as only storing a filename. Only when the final result set needs to be returned does it need to follow that pointer and get the BLOB data off disk.
    My point was that indexing would negate this. If the fields specified in the SELECT and WHERE clauses are in the index, then the query won't even use the table at all.

    Cheers,
    D.

  23. #23
    Programming Team silver trophybronze trophy
    Mittineague's Avatar
    Join Date
    Jul 2005
    Location
    West Springfield, Massachusetts
    Posts
    17,227
    Mentioned
    194 Post(s)
    Tagged
    2 Thread(s)
    I was going to post a comment based on my gut feelings that any difference in getting small result sets would be insignificant. But I decided to run an admittedly unprofessional benchmark test just to see. I created an XML file

    menu.xml
    Code XML:
    <?xml version="1.0" encoding="utf-8" ?>
    <menu>
    	<page>
    		<title>First</title>
    		<path>first/index.php</path>
    	</page>
    	<page>
    		<title>Second</title>
    		<path>second/index.php</path>
    	</page>
    	<page>
    		<title>Third</title>
    		<path>third/index.php</path>
    	</page>
    	<page>
    		<title>Fourth</title>
    		<path>fourth/index.php</path>
    	</page>
    	<page>
    		<title>Fifth</title>
    		<path>fifth/index.php</path>
    	</page>
    	<page>
    		<title>Sixth</title>
    		<path>sixth/index.php</path>
    	</page>
    </menu>
    a CSV file

    menu.csv
    Code:
    First|first/index.php
    Second|second/index.php
    Third|third/index.php
    Fourth|fourth/index.php
    Fifth|fifth/index.php
    Sixth|sixth/index.php
    and a database table "menu" with varchar(10) "title" and varchar(20) "path" fields containing the same values as the files.

    Then I threw together this file to test

    menu.php
    Code PHP:
    <?php
    $iter = 100;
     
    set_time_limit(120);
    $fs_xml_start_time = microtime(true);
    for ($i = 0; $i < $iter; $i++)
    {
    	//get xml stuff here
    	$xml = simplexml_load_file('./menu.xml');
    	foreach ($xml->children() as $page){
    		$page_title = $page->title;
    		$page_path = $page->path;
    		if ($i == 0){
    			echo $page_title . " ~ " . $page_path . "<br/>\r\n";
    		}
    	}
    }
    $fs_xml_stop_time = microtime(true);
    $fs_xml_run_time = $fs_xml_stop_time - $fs_xml_start_time;
    echo '<p>filesystem XML: ' . $fs_xml_run_time . '</p>';
     
     
    set_time_limit(120);
    $fs_csv_start_time = microtime(true);
    for ($j = 0; $j < $iter; $j++)
    {
    	//get csv stuff here
    	$csv_f = fopen("./menu.csv","r");
    	while (($data = fgetcsv($csv_f, 110, "|")) !== FALSE) {
    		$page_title = $data[0];
    		$page_path = $data[1];
    		if ($j == 0){
    			echo $page_title . " ~ " . $page_path . "<br/>\r\n";
    		}
    	}
    	fclose($csv_f);
    }
    $fs_csv_stop_time = microtime(true);
    $fs_csv_run_time = $fs_csv_stop_time - $fs_csv_start_time;
    echo '<p>filesystem CSV: ' . $fs_csv_run_time . '</p>';
     
     
    set_time_limit(120);
    $db_tbl_start_time = microtime(true);
    for ($k = 0; $k < $iter; $k++)
    {
    	//get db stuff here
    	$host = 'localhost';
    	$user = '*****'; //user name
    	$pswd = '*****'; //password
    	$db = 'test';
    	$connection = mysql_connect($host,$user,$pswd) or die('Could not connect: ' . mysql_error());
    	mysql_select_db($db) or die('Could not select database');
    	$query = "SELECT title, path FROM menu";
    	$result = mysql_query($query, $connection) or exit(mysql_error());
    	while ($row = mysql_fetch_assoc($result)) {
    		$page_title =$row["title"];
    		$page_path = $row["path"];
    		if ($k == 0){
    			echo $page_title . " ~ " . $page_path . "<br/>\r\n";
    		}
    	}
    	mysql_free_result($result);
    	mysql_close($connection);
    }
    $db_tbl_stop_time = microtime(true);
    $db_tbl_run_time = $db_tbl_stop_time - $db_tbl_start_time;
    echo '<p>Database table: ' . $db_tbl_run_time . '</p>';
     
    ?>

    Several runs (100 iterations each) consistently gave around .2 sec for XML, .08 sec for CSV and .9 sec for MySQL

    So I guess it depends on whether you want it to scale and what other things you might want to do with the data.

    If anyone sees anything I'm missing, please be politely harsh, I'm interested in learning more.

  24. #24
    From space with love silver trophy
    SpacePhoenix's Avatar
    Join Date
    May 2007
    Location
    Poole, UK
    Posts
    5,067
    Mentioned
    103 Post(s)
    Tagged
    0 Thread(s)
    Just looking at three open source apps (MediaWiki, phpBB and SMF) they all store their images (uploaded user avatars) in the file system. Anyone know how vBulletin stores avatars (in the database or file system)?

    Most forums I've used don't allow any file uploads apart from uploading avatars (MediaWiki does but it stores the images in the file system). Most make you provide a link to where the image resides (generally on sites like flickr, image shack or photo bucket).

    The ones who would really know which way is most effiecient are the sites like flickr, image shack or photo bucket given the huge numbers of image files they host between them.

    EDIT: What might be more efficient stored in the database is thumbnails of larger images.
    Community Team Advisor
    Forum Guidelines: Posting FAQ Signatures FAQ Self Promotion FAQ
    Help the Mods: What's Fluff? Report Fluff/Spam to a Moderator

  25. #25
    SitePoint Author silver trophybronze trophy
    wwb_99's Avatar
    Join Date
    May 2003
    Location
    Washington, DC
    Posts
    10,649
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)
    Personally, I'd make the database the ultimate backing store for the blobs. Doesn't mean I wouldn't cache them out to disk if need be, but for backup/restore/dr purposes having all the application data living in one place is the vastly superior option.

    I'd also argue, for most cases, scalability is a bit of a red herring. Any modern setup should be able to handle the traffic for most sites we discuss here, and sustain- and maintainability will pay off lots more than performance improvements.

    Oh, and thirding the "databases are far, far more than glorified filesystem abstraction" sentiment.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •