Anonymous Monk has asked for the wisdom of the Perl Monks concerning the following question:

If i were to maintain a database for a website with lists for each user which reference entries in another table, would it be more expensive in access time to keep each list as a seperate table within the database or a textfile which i can open up and scroll down to get each if the entries' ids?

Replies are listed 'Best First'.
Re: faster access times
by arturo (Vicar) on Jun 21, 2001 at 00:58 UTC

    Depends on the database and the info, I would suspect.

    My bias in coding database apps for websites is having the webserver / webservers cache info it fetches / they fetch from the DB as much as possible. This makes the most sense when you're going to have several webservers interacting with a single database (because in that situation, the database is a bottleneck). If you've got something very simple and a decent DBMS, as long as there's no overhead for connecting to the database server process (i.e. if you have what are called 'persistent connections', and you will have to run your scripts under mod_perl for that), you're probably better off, speed-wise, going to the database because it will optimize the lookups for you.

    HTH 

    perl -e 'print "How sweet does a rose smell? "; chomp ($n = <STDIN>); 
+$rose = "smells sweet to degree $n"; *other_name = *rose; print "$oth
+er_name\n"'

    [download]
[reply]
[d/l]
Re: faster access times
by malloc (Pilgrim) on Jun 21, 2001 at 00:57 UTC
    I think the main bottle neck in this situation will be connecting to the database in the first place. As far as normalizing the data into seperate tables vs. storing it in flat files, the cost should be negligible compared to the cost of the db hit. My advice: If you are going to the database anyway, just grab all of the data there. If you use inteligent indexing, it should be no problem.

    Hope this helps,
    -malloc
[reply]
      What you are giving is some very bad, unfounded advice. What the bottleneck will be depends on several factors, including which database is being used, and how large the dataset will be. If you have a billion records fetched over a slow link from dark Africa, your connection time will be dwarved if you grab all the data. If you just have five records and 5000 simultanious connections to your database server running on a Sparc Classic, setting up the connection will be the bottleneck.

      But none of this is known. Any claims on what will be the "best" or "fastest" solution without any further disclaimers is misleading at best, and just shows you not understanding databases.

      My advice: implement several solutions and test. And make sure you test on well choosen data sets. And for futher advice, you are much better off at a database forum than at a Perl forum, as this is a database problem - not a Perl one.

      -- Abigail

[reply]
        1- I find your tone insulting, and it takes away from this community and marks you as someone with problems.

        2- I made some assumptions here based upon the nature of this post:
        i. do you think this person has a billion users on there website when they are posting here for advice?
        ii. do you think this person operates a web site with 5000 simultaneous connections and is posting here for advice?
        iii.do you think that this person is african?

        now, silly me, trying to be helpful, made some educated guesses as to this persons goals. i assumed they wanted to grab ONE users info from a database. I said that if they are establishing a connection, the cost will not be that much greater to store and retrieve all data there compared to the cost of making the connection. Once again, i am assuming that this person is not storing the persons genome. Does this show me "not understanding databases"? If it does, maybe you could point out SOME FACTS WITH BASIS IN REALITY in a non-snide manner, not conjecture about Africa.


        -malloc
[reply]

Back to Seekers of Perl Wisdom