Results 1 to 8 of 8
  1. #1
    Join Date
    Sep 2002
    Location
    Kyiv, Ukraine
    Posts
    77

    Lightbulb Unanswered: basic concepts of DB programming

    I'm looking for information about basic concepts/rules of a professional DB programming: is better to use SQL queries or do same stuff from a programm, what's better - a lot of small queries or one big and others considering process time, memory usage, stability of the overall system, etc. Any advice much appreciated, links to articles or something also.

    Thanks In Advance!
    Yours faithfully,
    Yaroslav Zaremba

  2. #2
    Join Date
    Nov 2002
    Location
    Ohio
    Posts
    90
    Hi aZa,

    Most of the online systems I've worked on use multiple small queries to limit the number of rows accessed, network traffic, etc.

    We try to limit the selection criteria on the client side machines or use the "Top N rows" option to ease the burden on the server side machines.

    Large jobs are scheduled at night whenever possible.

    Good Luck,
    Bruce Baasch

  3. #3
    Join Date
    Sep 2002
    Location
    Kyiv, Ukraine
    Posts
    77
    I have a 'real-life' question: creating authentification using PHP and MySQL database I've met with the following dilemma - keep only user's ID in session variable and whether I will need his full name I have to make a simple query getting this info from the DB ... even though user's name is on every page of the site

    OR

    once write also a user's full name to the session variable and always keep it there instead of making additional queries ... even though they are very small and fast ... (SELECT FullName FROM Users WHERE ID=id;)

    What's better? What's faster? What's theoretically right? What practically would be a best decision?
    Yours faithfully,
    Yaroslav Zaremba

  4. #4
    Join Date
    Dec 2002
    Posts
    65

    Depends...

    How are you handling your authentication? Do you have different levels of permissions or is it just logged/not logged in?

    If you have user permission levels are you just storing some permission mask in a session variable or do you check the DB for it on every secure action? Basically, if you are already doing some type of related DB query I'd just go ahead and grab the info within that query, otherwise you can just user your method of a session var.

    -b
    (I'm only available at the email address provided in my profile on weekdays, if you have questions or advice, during off hours use AIM). Also any views I provide here or on my website are mine and not representative of any views of my work, family, friends and sometimes even myself.

    http://www.bcyde.com

  5. #5
    Join Date
    Sep 2002
    Location
    Kyiv, Ukraine
    Posts
    77
    Ok, going deeper into the problem ... as you wish!

    I have no levels of authorization - just 'guest' or 'some user'. On every page automatically shows so-called (by myself) login row which shows input boxes for login/password for 'guest' user or something like "Welcome my dear $username !" if user is logged. That's all. $User_ID-s I will need throughout pages so this is the thing I should surely register in session variables, but what about $username? It's not a problem for me to launch simple query before outputting this 'Welcome ...' string with the needed data but what about overall system's economical resource usage? Is it the right decision?

    What is better: launching simple, fast queries or keeping session variables in the memory?

    Is it a good programming habit to keep the number of queries as less as possible or maybe better use number of small queries instead of complex one? Complex queries are usually more difficult to create because you should think about all data you need on the page and getting it to the one resulted recordset ... But is it worth your time, efforts?
    Yours faithfully,
    Yaroslav Zaremba

  6. #6
    Join Date
    Dec 2002
    Posts
    65
    Well in this case I would say that I would rather go with storing the username in a SESSION variable. Personally, I believe that the small amount of memory holding the username is a better use of resources than constantly opening and closing connections to the DB just to retreive the username . If you're using persistent connections you won't have the overhead of having to connect to the database again each page, but I'd still go with the session variable unless memory usage is going to be a huge thing in your app.

    No matter which way you go, though, I don't believe either will cause a noticeable bottleneck.

    In regards to your other comments, I would say it is better to keep the amount of queries to a minimum instead of many small queries. You should leave all the hard work up to the database since that's what it's there for . In the end though it all depends on how much of the data you're going to use. Just make sure your queries are nice and tidy and packed with only the info you're going to use and you should be alright.

    -b
    (I'm only available at the email address provided in my profile on weekdays, if you have questions or advice, during off hours use AIM). Also any views I provide here or on my website are mine and not representative of any views of my work, family, friends and sometimes even myself.

    http://www.bcyde.com

  7. #7
    Join Date
    Sep 2002
    Location
    Kyiv, Ukraine
    Posts
    77
    Excactly information that I needed!

    So the conclusion to overall discussion: there is no standard rule about dividing calculating work on OS/interpreters/DBs. In one way better to put all work on DB, other - on interpreter and in third case - on OS. Everything depends from the situation and should be solved individually with each problem!

    ... Have I made the right conclusion for myself?
    Yours faithfully,
    Yaroslav Zaremba

  8. #8
    Join Date
    Dec 2002
    Posts
    65
    In my opinion yes. It all depends on what your situation is, what are your primary constraints (bandwidth, memory, disk space, processor utilization, etc.).

    From the looks of it, the type of information you're trying to keep seems to be fairly small and shouldn't affect performance either way you choose to go.

    -b
    (I'm only available at the email address provided in my profile on weekdays, if you have questions or advice, during off hours use AIM). Also any views I provide here or on my website are mine and not representative of any views of my work, family, friends and sometimes even myself.

    http://www.bcyde.com

Posting Permissions

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