Balancing the workload between the websever & database server

How to write content rich - optimized web applications quicky which does not over load the webserver?

Its a common habit of newbies to use SQL queries on the database just to fetch the records. Then use the functions provided by the Server side scripting language like ASP / PHP to process that result set and print the output. The next step would be to filter out records using the WHERE in the SQL.

As you gain experience as a web programmer, you learn to do wonders by writing effective SQLs.

Imagine you have a timestamp field on the DB. Don't you think its a lot of work & over head on the server to fetch the time stamp and process the timestamp using string function in a loop ?

Rather will it not be easy to use the following SQL:

SELECT DATE_FORMAT('format_string', ) FROM tablename

Also, you can optimize the performace of a web application by writing SQL JOINS. Use the DB server do the job and reduce the work over load on the web server & the server side scripting language. Check out the following SQL:


SELECT users.uname AS USERNAME, groups.name AS GROUPNAME, COUNT( * ) AS LOGINS, DATE_FORMAT( userlogintrack.ltime, '%b %D, %Y' ) AS Date
FROM userlogintrack
LEFT JOIN users ON users.uid = userlogintrack.uid
LEFT JOIN group_membership ON group_membership.uid = users.uid
LEFT JOIN groups ON groups.gid = group_membership.gid
GROUP BY userlogintrack.uid, TO_DAYS( userlogintrack.ltime )
ORDER BY Date DESC , users.uname
LIMIT 0 , 1000


The above SQL process 3 different tables and gives the result set to the server side scripting language (PHP) which can just print the results without any additional processing at the web server. If you were to do this with PHP alone, then you have to process 3 different result sets with increased overload on the busy webserver.

That being said, don't start writing complex SQLs and sacrifice the power of a server side scriptiong language. A good, efficient programmer has to strike the correct balance between the SQL & the functions of the server side scripting language to distribute the workload.