Joey Adams Dot Net

I cannot tell you how many times I see developers make multiple queries to related database tables per page. This creates a TON of overhead which is horrible for performance of your applications. Usually it is selecting all members from the members table, then selecting the correct group for that member from the ‘gid’ column of the members table which relates to the ‘id’ column on the groups table etc.

Listen, SQL optimization and manipulation is it’s own role. Don’t think just because you know how to C/R/U/D that you know sql. There are so many things to learn that can help your application and speed it up ten fold.

I am not going to give a lecture on proper SQL, simply because I am not qualified. I have studied for the MYSQL developer exam, but have yet to take it.

I will, however, suggest a few things to look up if you are unfamiliar with them. Aliasing is a pretty powerful method for organizing your queries. You can alias a table and use the alias as the identifier later on in the query to shorten your overall length and keep it much more manageable.

When selecting records from a database, STOP using the ‘*’ character. You should be selecting only the columns you need. If you need them all, then it is up to you, but I always define the columns I need, even in that case. This keeps structural lookups from happening.

Be aware of special database functions while inserting/retrieving/updating records. You can modify the data on the database so that it is properly formatted for insertion or retrieving. You can also count records, etc. as your databases indexing system is undoubtly faster than selecting everysingle row and counting the resultset.

The most important query improvement I can suggest to developers are joins. Joins allow you to select columns out of one table, and add them to the results from another table. This solves the problem above for multiple queries for member group selection. For example, look at the following query.

“SELECT * FROM members JOIN groups ON members.gid = groups.id WHERE members.uname = ‘joey’;”

This may not be 100% correct as I don’t have access to a db for testing, but you get the picture.

So for this installment, read some advanced SQL tutorials and remember that the database is a very powerful aspect of your application and any sort of interaction with it should be optimized.