In the Real WorldOptimizing Multitable Queries

In the Real World Optimizing Multitable Queries

Chapter 9's "In the Real World Optimizing Query Design" section discusses the art and science of query design to optimize the presentation of information and query performance. The single-table query design recommendations apply equally to multitable queries.

This chapter is one of the longest in the book because of the importance of multitable SELECT and SQL aggregate queries in production database applications. Joins are fundamental to relational databases. You're likely to find that more than 75% of the queries you create require at least one join, and a substantial percentage need two or more joins.

Subdatasheets

Access 2003's subdatasheet feature is useful for browse-mode editing of related tables, but not much else. Browse-mode is a term for editing table data in a datasheet. As mentioned earlier in the chapter, it's easy for inexperienced users and data-entry operators to make errors when editing datasheets, so minimize or eliminate updatable datasheets and subdatasheets in production Access applications. Instead, use multitable forms the subject of Chapter 15, "Designing Custom Multitable Forms" that display a single record of the base table in the main form and show related records in a subform.

Note

Browse-mode editing operations in multiuser and client/server environments are the bane of database administrators (DBAs) because they require multiple database connections and create a substantial amount of network traffic.


For more discussion of the browse-mode method and other alternatives, see "In the Real World Alternatives to Action Queries," p. 514.


Aggregate Queries

Aggregate queries generate summary data that's critical for decision-support analysis. Aggregate queries offer quick and easy totaling of orders, sales, and other financial data for one or more time periods. Aggregation methods create large-scale data warehouses and smaller data marts, which are used in online analytical processing (OLAP) applications. Experience with SQL aggregation techniques is a necessity for understanding OLAP methodology.

Tip

Apply "reasonableness" tests against every summary query you design. Testing becomes increasingly important as the significance of data to others grows. It's very embarrassing to find that you provided data for 1998 when your manager needed 1999 information. Become familiar with trends in the summary data generated by your queries, and compare new query result sets with previous values. If the comparison shows unexpected changes (good or bad), run a simple summary query for one or two periods to verify your data. If the summary data still fails the reasonableness test, you must review the underlying detail data (called drilling down). Familiarity with the detail data you summarize is job insurance when your manager says the "numbers don't look right to me."


Crosstab Queries

Most executives prefer the crosstab formats for time series and other comparative financial analyses. Access's Crosstab Query Wizard does a respectable job of generating simple crosstab queries for you. Designing crosstab queries that are more complex than the Wizard can handle requires that you first gain experience writing conventional summary queries.

One of the primary issues with Jet crosstab queries is that the Jet SQL reserved words, PIVOT and TRANSFORM, both of which you need to generate crosstab queries, aren't available in SQL Server's T-SQL or any other client/server SQL dialect. Thus you can't automatically upsize Access 2003 Jet applications that include crosstab queries to ADP with the Upsize Wizard. (You can't automatically upsize UNION queries, either.) ADP substitute SQL Server views for select queries; you create views by writing T-SQL SELECT queries for views, functions, or stored procedures. ADP are the subject of Chapter 20.

If your data source is a client/server RDBMS, you can use linked server tables with the Crosstab Query Wizard. Alternatively, you can generate the summary query on the server with an Access passthrough query, and then use the Jet query result set as the data source for the crosstab query. Passthrough queries, which are more network-efficient than linked tables, are one of the subjects of Chapter 19.

graphics/power_tools.gif

An alternative and more interesting approach is generating crosstab queries with SQL Server stored procedures. The Visual Basic 6.0 CrosstabUpsizer application, which you install by running \Seua11\Crosstab\Setup.exe on the accompanying CD-ROM, translates most Jet crosstab queries to a combination of SQL Server views, tables, and stored procedures. Figure 11.79 shows the result set of a view, table, and stored procedure generated from a slightly modified version of the qry1997QuarterlyProductOrdersCT query you created in this chapter.

Figure 11.79. The Visual Basic 6.0 CrosstabUpsizer program (Crosstab.exe) detects Jet crosstab queries in a database and generates SQL Server views, tables, and stored procedures that emulate the Jet queries.

graphics/11fig79.jpg

graphics/globe.gif

The wizard-like CrosstabUpsizer offers more options than Access 2003's Crosstab Query Wizard you can round values to the nearest dollar, add row and column totals for crossfooting, and determine how often to update the crosstab data. Avoiding the aggregate calculation process by storing crosstab data in a table that's updated periodically minimizes resource consumption and speeds execution greatly, especially when the query is based on source tables with thousands of rows. The "Upsize Jet Crosstab Queries" article from Visual Studio Magazine (http://www.fawcette.com/archives/premier/mgznarch/vbpj/2001/10oct01/sqlpro0110/rj0110/rj0110-1.asp) explains the project's design principles. You can download the project and its source code from a link on page 1 of the article.



Special Edition Using Microsoft Office Access 2003
Special Edition Using Microsoft Office Access 2003
ISBN: 0789729520
EAN: 2147483647
Year: 2005
Pages: 417

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net