Database-Level Optimizations
>> Use Stored Procedures
The quickest way to improve your code's performance is to replace your inline SQL statements with stored procedures. Stored procedures are routines that are stored in the database. They are precompiled by the database engine and offer substantial performance improvements over the process of going back and forth between your calling application (typically a PHP page) and your database engine.
In addition, stored procedures are much easier to maintain. Your logic resides within one piece of code; any changes are made in that one place, and all the code that uses it gets the fixes immediately. It is also easier to be certain that your business logic conforms to your functional requirements across the board if all your code refers to a single point. If your procedures are generic enough, you can reuse them in several projects and cut your development time.
Stored procedures also reduce network traffic, as compared with executing SQL code from within PHP, ASP (Active Server Pages), JSP (Java Server Pages) or other Web development languages. Finally, if scalability is an issue, it is much easier to scale your application code across several application servers if most of the database access logic is stored and executed inside the database.
>> Store Multimedia Files in the File Systems
Multimedia files -- whether still images, sound files or a movie -- often are dealt with as binary objects. More specifically, these are known as blobs (binary large objects). There are two ways to handle blobs: Store them in the database, or store them in the file system and their paths in the database. Storing blobs in the file system requires a little more work, but you get much better performance than if they are stored in the database.
The performance of your database engine can degrade quickly when you store a lot of binary objects. And deleting those files can result in large quantities of dead space in the database files. It is more difficult to multitask operations when everything is going through the database engine. In contrast, storing them in the file system makes creating download links from a Web page easy. On downloading, the Web server can serve the file while the database engine is used in other tasks. As an extra advantage, your administrator can back up, catalog and administer multimedia disk files with ease.
>> Use Indexing
Indexing is one sure way to speed up performance. It also is one of the most overlooked essentials in a database design. Typically, rows in a database are stored in the order in which they are created. Retrieving some random value from a database record requires a sequential scan of the database rows. An index creates a separate set of rows ordered according to the chosen index and containing pointers to the original rows. Database lookups on indexed databases are performed much faster than lookups on nonindexed tables. However, indexing consumes more disk space. In addition, updates to the table take longer, since all applicable indexes have to be updated as well.
>> Use Integer Key Fields
You might be tempted to create your table without an integer key field. For example, in a table of personnel records, you might create your table with one character field called last_name and one called first_name, plus address and contact information, and use the names in joins, lookups and so on. You should avoid such practice. Instead, use a numeric key field -- let's call it person_id. If your data does not have such a field, you should create an autoincrement field that does not hold real data but serves as a key field.
Numeric fields offer many advantages. Numbers are much less likely to be used incorrectly. If a person's name changes -- because of marriage, for example -- you do not need to change all references to it in your code. And numeric joins are more efficient than character-based joins. It is good practice to create a numeric primary key field whenever you are creating a new table.
Optimizing Code
Several strategies can be used within your code to optimize its performance when dealing with a database.
The following optimization should be usable in any Web development language.
>> Avoid Using Sessions
Several Web development environments support sessions. Sessions usually are implemented using cookies, and they are extremely popular among ASP and PHP programmers. Because the Web is a stateless environment, it does not afford programmers the luxury of knowing what the user may have done before landing on a particular page. Using sessions, the programmer can keep track of the user's navigation. As a side effect, many programmers tend to store everything in session variables. While it is tempting to store references to the database, such as connections or record sets in a session variable, this is bad practice. Storing connections in sessions prevents them from being pooled. That practice also eats memory and CPU power.
Unless the sessions are cleaned up explicitly, they exist until they time out. The time-out varies, but in most instances it is upward of 20 minutes. During this time, memory and CPU are being hogged for no reason. While opening a connection in the page and then closing it may seem to be a waste of resources, it is actually a much better way to preserve resources. The rule of thumb is to create the connection as late as possible and release it as soon as possible. The same rule applies to record sets.
RSS
