A Simple Solution

A Simple Solution…

Typically, web search engines have two types of web pages—one type for entering criteria and another type for displaying results. The criteria page can sometimes be very simple—a single text box feeding a single table in the database. Sometimes, you may have a web page with a number of objects (such as text boxes, list boxes, and checkboxes) that correspond to different fields in different tables. Potentially, all recordset (table) fields could be exposed as criteria fields. A user can use a single field or multiple fields to specify the criteria. I call such pages and corresponding queries quick and full (or advanced), respectively.

The quick criteria page is naturally implemented in SQL Server as a stored procedure with one parameter. In the case of full criteria, the fact that users can specify values for an unknown number of fields, unsurprisingly, leads developers to implement it as a dynamically assembled ad hoc query joining a large number of normalized tables. To illustrate this in the Asset5 database, I have created a query against Inventory and other associated tables:

      SELECT Inventoryid, Make,              Model,             EqType,      Location,          FirstName,             LastName,    AcquisitionType,   Address,             City,        Provinceld,        Country,             Phone,       Fax,               Email,            UserName,     OrgUnit      from dbo.Inventory Inventory        inner join dbo.Contact Contact        on Inventory.OwnerId = Contact.ContactId          inner join dbo.Location Location          on Inventory.LocationId = Location.LocationId            inner join dbo.AcquisitionType AcquisitionType            on AcquisitionType.AcquisitionTypeId = Inventory.AcquisitionTypeId              inner join dbo.Equipment Equipment              on Equipment.EqId = Inventory.EqId                inner join dbo.EqType EqType                on Equipment.EqTypeId = EqType.EqTypeId                  inner join dbo.OrgUnit OrgUnit                  on Contact.OrgUnitId = OrgUnit.OrgUnitId      where Make = 'Compaq'        And EqType = 'Storage Array'      order by Country, ProvinceId, City, Location 

In the real world, such a query may be designed to run against more tables—I have seen solutions with 20 or more joined tables. Such a design might look elegant during development if it is tested by only a couple of users using only a small number of records per table. Unfortunately, in production, an application will work against tables with thousands (or millions) of records and will have to serve dozens (or hundreds) of concurrent users.

Note 

One such system that I've encountered in the past had difficulty supporting even ten users. The simplest queries took five to ten seconds. Regular queries often timed out after 60 seconds. Users were so frustrated that they would issue a query without criteria, and then copy the complete result set to Excel or Access to query it on their local machines.