ColdFusion Day 29:

================

 

Using ColdFusion Studio's SQL Builder   

 

Using ColdFusion's Studio, you can build a SQL query without leaving the integrated development environment (IDE) for the ColdFusion Development System. One of the tools available to you is the SQL Builder, which enables you to view database schema and construct and test SQL operations for inclusion in your templates.

 

Setting Up The Connection To The Database

 

ColdFusion Studio uses the Database Connection Manager in the ColdFusion Server to connect to Remote Data Sources (RDS). Connection to these sources requires authentication. When you initially try to connect to your ColdFusion data sources, you might encounter an error message.

 

This error message means that the password supplied is incorrect or the password has not yet been set. Most likely, you have not yet supplied a password. The following procedure will enable you to set the password to access the data sources in your ColdFusion server.

 

1.    Click the remote files tab on the lower left side of the Studio screen.

2.    Click the rds://hostname entry for the remote host or the remote server you want to connect to.

3.    The configure RDS screen is displayed. Enter the password to access the ColdFusion data source.

4.    Click OK to set the password. (You should now be able to use the SQL Builder).

 

Using The SQL Builder Tool

 

The SQL Builder tool enables you to create a CFQUERY, complete with the SQL query, through a graphical interface. To create a query, follow this procedure:

 

1.    Select SQL builder from the tools menu.

2.    Click the desired hostname and find the desired data source. Open it and select the desired table.

3.    Click the new query option. (This opens the query builder window).

4.    The query builder screen appears. Select and drag fields from open tables to empty cells in the column named column.

5.    Add criteria (for instance, LIKE ' %HOMESITE%' ) under the criteria column of different fields. Click the exclamation mark ( ! ) button in the toolbar to run the query.

6.    You can then click the copy CFQUERY to clipboard.

7.    Return to your main Studio Editing screen by closing the window and paste your new CFQUERY code.

 

Now you can create your own CFQUERY code with more complicated SQL statements by using ColdFusion Studio's graphical SQL builder. You never have to leave your favorite ColdFusion IDE!

 

OPTIMIZING SQL and Database Performance

 

When your ColdFusion application starts to slow down, especially during queries, then it is time to analyze and improve the applications performance. You can optimize performance of your queries by using two methods:

 

    *    Database optimization

    *    SQL optimization

 

Database optimization means doing something to your database to improve performance. It may also mean restructuring the database and the design and layout of the tables. One of the first things that you should check is the index. What should be indexed? Obviously, the Primary Keys in your tables, foreign keys, and columns that are used for table joins. Other candidates for indexing are columns that are referenced by ORDER BY or GROUP BY clauses. Columns frequently used in WHERE clauses should also be considered. The best way to accomplish this is to run a test with or without certain indexes, with your ColdFusion debug turned on, you can see how long it takes to process queries.

 

WARNING

 

Indexes can slow the performance of your queries. Avoid creating indexes for columns with few unique values. You should experiment with different indexes to see which ones improve performance.

 

SQL optimization means optimizing your SQL queries by making sure that elements in your queries are organized properly. Most importantly, you have to analyze the elements in your FROM and WHERE clauses. Here are some basic guidelines for optimizing your SQL queries:

 

    *    In your FROM clause, list the larger tables last.

    *    In your WHERE clause, list the most restrictive condition first. The most restrictive condition yields the least number of rows of data.

    *    Avoid using the HAVING clause.

    *    Avoid using the OR operator and use IN instead.

    *    Avoid using the LIKE operator and wildcards frequently.

    *    Convert recurring SQL statements to stored procedures for faster execution.

 

Remember these guidelines are not cut and dry. You will need to experiment, run tests and time the executions to make sure that your optimizing procedure works