Code 10x More Efficiently Using Data Access Objects: Part 1 - Convenience and Performance (Page 5 of 6 )
Perhaps the nicest thing about using the database DAO is that it increases coding productivity through convenience and at the same time it allows your scripts to perform well. Now, let's look at how would we use the MySQL DAO in a script where we need to retrieve multiple data chunks from the database…
<?php require("core/mysqladapter.phpclass"); $objDB = new MySQLAdapter("localhost;contact_db;user;password");
// get the total number of records in the table $total_contacts = $objDB->getValue("SELECT COUNT(*) FROM Contacts");
// get the record for the contact with id 15 $record = $objDB->getRecord("SELECT * FROM Contacts WHERE id=15");
// get the records whose first name is "John" $records = $objDB->getRecords("SELECT * FROM Contacts WHERE first_name='John'"); ?>
Here we have just retrieved three chunks of data from the database, stored them in variables and they are ready to be processed in a template that will produce a nice output. This is what I call increased coding productivity through convenience. And that is not all. The above code will actually perform very well due to a built in feature of the DAO, which lets it reuse the open database connection. Here is how it works. When the data access object is instantiated, the connection string is stored as an internal class variable. The connection to the database is not actually established until the first method is called. After the first method call, the connection is reused for all of the successive calls to the database, substantially decreasing the communication overhead and consequently increasing performance. A frequent question might be: Is my script execution time going to increase when using the MySQL DAO? We have benchmarked the MySQL DAO and found the difference in performance compared to traditional database access insignificant if any.
Another great feature of the DAO is the ability to call the methods on a different database without the need to instantiate another data access object. You can simply call a method and supply an optional second parameter which is a connection string. In this case, the connection stored in the object is not reused, but rather a new, temporary connection is established to the database specified by the supplied connection string. Here is an example of how this would work…
The $row1 is returned from db1 while $row2 is returned from db2. The connection to db1 is kept open and is ready to be reused in the remainder of the script, while the connection to db2 is closed immediately after the $query2 is executed. The connection to db2 is closed automatically at the end of the script execution, or optionally it can be closed explicitly by calling the "close()" method.