In the Real WorldOptimizing Query Design

In the Real World Optimizing Query Design

The objective of select query design is to convert raw data to useful information. The design of decision-support queries in production database applications is a combination of art and science.

The Art of Query Design

An artful query design returns the result set in the format that's most meaningful to the recipient. For example, a query that displays orders sorted by customer and uses the customer code for identification might be understandable by a salesperson, but not management. Salespeople are likely to know the codes for their particular customers. Few sales or marketing managers, however, are capable of memorizing hundreds or thousands of codes. This is especially true if the codes are numeric, rather than based on the first few letters of customers' names, as is the case for the Northwind Customers table.

Access's table lookup feature propagates to queries; when you specify CustomerID as a query column for the Orders table, a table lookup against Customers automatically substitutes CompanyName for CustomerID in the query result set. On the other hand, database purists object to displaying tables with embedded lookup queries that disguise the actual design of the table.

Another aspect of the art of query design is appropriate left-to-right and top-to-bottom ordering of query columns. If your summary query is a time series such as qryOrdersSummary that you created in the "Trying the Simple Query Wizard" section at the beginning of the chapter the Order Date column is the most important, so it appears in the leftmost column. If you're writing Access applications for others, make sure to interview prospective users to determine their column presentation priorities. Use drag and drop to optimize the relative position of columns in Datasheet view.

Most recent data probably is what users want, so applying a descending sort on the Order Date column aids information usability. You can quickly apply a descending sort on a date column in Datasheet view by right-clicking the field-name header, and choosing Sort Descending from the context menu.

Apply intuition and inductive reasoning when designing decision-support queries. Access makes it easy to alter the presentation of your queries in Datasheet view. As in music, painting, dance, and the other performing and pictorial arts, practice and experimentation are the keys to query artistry. This is especially true when you design queries that act as the data source for PivotTables and PivotCharts.

The Scientific Side of Query Design

The scientific part of query design is optimizing query performance. All production database applications deliver query result sets over some type of network, usually a Local Area Network (LAN) but often a Wide Area Network (WAN), such as the Internet. The performance of queries executed over LANs and, especially WANs is dependent on a multitude of factors, the most important of which is the network connection, followed by the amount of network traffic. Although broadband Internet access is becoming more widespread, most Internet users still connect with dial-up modems. Even if you're writing queries for execution on a single PC, plan ahead for networking your application.

graphics/power_tools.gif

The tables of the Northwind.mdb sample database contain far fewer records than you find in typical production databases, and thus aren't representative of the databases behind real-world applications. The nine-person Northwind Traders sales force produced only 830 orders over a span of almost two years, indicating a serious lack of sales productivity. If you have 15MB of disk space to spare, install the Oakmont.mdb database from the \Seua11\Oakmont folder of the accompanying CD-ROM and run test queries against its tables. To better emulate network performance, install Oakmont.mdb on a file-sharing server and link the tables to a new .mdb file on your client PC. The Students table has about 30,000 rows, which is more typical of a production customers table. The Enrollments table has 50,000 records.

With networked data, smaller definitely is better. Limit the data returned by your query to only that required by your application's immediate need. It's especially necessary to restrict the amount of data you send to modem-connected mobile users, whether they dial into your LAN or get their data over the Internet. You minimize the amount of data sent "over the wire" to database users' PCs in two ways setting precise criteria and limiting the number of columns.

Setting precise criteria minimizes the number of rows sent to the client PC. For example, restrict initial queries against large tables such as those containing orders or invoices to provide only the current month's or week's orders. Create separate "last month," "this quarter," and "last quarter" queries for users who need historical data. Jet's query-expression service lets you write queries that automatically roll over when the month or quarter changes.

There's seldom a reason to include all fields (by using the field list's * choice) in a query. Include in the initial query only those fields necessary to provide the basics. For example, you might want to include the ShipName column in a query on the Orders table to identify the customer, but don't include the ShipAddress, ShipCity, ShipRegion, ShipPostalCode, and ShipCountry columns in management reports. Only salespeople and shipping departments need detailed destination data. Salespeople only need shipping information for their particular accounts, so you can use EmployeeID as a criterion to limit the number of records that have large text fields.

Don't include OLE Object (usually images) or Memo fields in initial queries unless they're absolutely essential. Access doesn't automatically retrieve these data types unless the user double-clicks an OLE Object cell or moves the cursor to a Memo field, but data in OLE Object and Memo fields often is very large. A modem-connected user who accidentally double-clicks a 1MB high-resolution image won't be happy when his computer is tied up for several minutes downloading unwanted data. If some users require either of these field data types, create a special query for them.

The science of query design requires detailed analysis and deductive reasoning. Keep these basic query design rules in mind as you progress through the remaining chapters of the "Transforming Data with Queries and PivotTables" part of this book.



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