13.9 Debugging SQL Code

Your Flash Remoting application may depend on calls made to a database, as the searchProducts.fla example does. If you have a database server such as Microsoft SQL Server, you will have all the tools at your disposal to properly test and debug SQL code. This means that you should have a workable copy of the database and database-programming environment available to you. It is unwise to debug database queries using a live server, so all debugging and optimization should be done locally or on a staging server before deploying your application.

13.9.1 Database Errors

Database errors can be difficult to track down from within Flash, so it is a good idea to try to track them down at the source: from within the database environment. Frequently, the error can be found in the syntax at the source, eliminating the guesswork when you execute the database code from Flash.

If the query runs successfully in the database environment, the next logical place to look for errors is in your server-side service. A successfully worded query can fail if you use a datatype that is not consistent. Frequent errors include forgetting the single quotes around character data or putting single quotes around numeric data improperly.

Another frequent error to watch out for is the improper use of NULL versus using an empty field. Often, a query result can be returned incorrectly if you are doing a search like this:

SELECT * FROM Customers WHERE Region = ''

You might be looking for Customers that don't have a Region column provided, but running this query on the Northwind database returns 0 records. If you look for NULL values instead, the query returns a handful of records:


NULL values and empty values are two entirely different things to your database, and they must be treated differently when you build your queries. This can have some insidious consequences in your data results, especially when you are creating reports and doing aggregate queries.

13.9.2 Query Optimization

Debugging involves not only making the program run without errors but also optimizing the code to run as fast as possible. One of the bottlenecks in Flash Remoting can be your SQL code, if it is not properly optimized.

The SQL Server environment contains tools such as the Query Analyzer to help you optimize queries. It can help determine where to place indexes in your tables, by measuring the demands of a given SQL query and analyzing where the bottlenecks occur. Typically, when a SQL query takes a long time to run, that means the indexes are not set up properly.

There are a few key areas to look for in your queries. Columns that are joined in queries should be indexed, as in this query:

SELECT p.ProductID, p.ProductName, s.SupplierName
FROM Products p
INNER JOIN Suppliers s
ON p.SupplierID = s.SupplierID

In this case, an index on SupplierID in the Suppliers table will dramatically increase the speed of the query.

Columns referenced in ORDER BY clauses should be indexed. This is because data that is ordered frequently can be ordered more quickly if that column is indexed, as an index is an ordered list.

Columns that are searched frequently should be indexed, as in this query:

SELECT * FROM Products WHERE CategoryID = 7

On a small table, the savings might not be substantial, but as tables become increasingly large, query execution times become an important consideration. Imagine if you had to look up a phone number in a phone book, starting from page 1 and reading every name until you found the one you were looking for. This is the same principle as searching a database column that has no index on it. With the index, the database is able to narrow the search down to a few disk accesses, rather than scan the entire table.

There are a few things to watch out for in your queries. Queries that use LIKE with wildcards are slower than queries that use exact matches. In other words, this query is slower than a query that matches the field exactly:

SELECT * FROM Products WHERE ProductName LIKE '%sauce%'

Furthermore, queries that have a wildcard in the first character in the filter will not make use of an index on the column. The preceding query does not use an index. The following query can use an index on the ProductName field to speed up the search:

SELECT * FROM Products WHERE ProductName LIKE 'sauce%'

Database environments that don't contain tools like the Query Analyzer can be difficult to work with. MySQL contains an optimizer, but it is used from a command line. Regardless, you should make use of the tool and learn about query optimization. Flash Remoting calls can eat up precious seconds of Internet time, and a query that takes too long to execute is going to make restless users go elsewhere.

    Part III: Advanced Flash Remoting