Using Output Caching to Improve Application Response Time

Output Caching is a technique used by software developers to improve application performance. If a result set takes a long time to produce, then it is mandatory to have it cached in some temporary storage and reuse the cached data for a period of time.

This consideration applies when using stored procedures or web services outputting a custom result set.

One method is to have the output stored in the server memory. This works great when the data has already been processed. For example, it makes a lot of sense to capture a fragment of a web page in the server memory cache. An application extracts the fragment from cache and sends it to the web browser when needed.

In a data centric application the final shape of data presented to the users is not known. Users may need to sort and filter data. This will require caching a custom result set in a native format. The server memory cache is extremely fast but will not tolerate large datasets. Besides, developers will not be able to execute standard SELECT statements against an in-memory result set when responding to filtering and sorting requests from the user. The natural solution is an output cache table.

An output cache table is a database tables designed to temporarily store a custom result set produced by a stored procedure,  web service, or any other resource-intensive data source.

An output cache table must have columns that match every column in a custom result set.

Additional columns representing parameters of a stored procedure or web service producing a custom result set will improve the reusability of the data stored in the output cache table.

If the cached data cannot be shared with other users, then an additional column representing the user identity must be added to the structure of the output cache table.

The temporary nature of cached data requires a column capturing its expiration date and time. An application using the output cache table must be able to detect the “stale” data and repopulate the cache with fresh content.

Code On Time web applications work really well with  database tables in general and will effortlessly handle an output cache table. SQL Business Rules will help populating an output cache table at just the right time.

Custom search bars and search dialogs allow easy input of parameters that must be passed to the stored procedure or web service. The input parameters may also be passed through master-detail relationship filters.

The built-in filtering performs a seamless segregation of output cache table content based on the end-user identity.

Using Calculated Field as User-Friendly Foreign Key Identity

Let’s examine two tables in the Northwind database, Employees and Orders. Each order is linked to an employee via the EmployeeID foreign key relationship.

A Code On Time web application will perform automatic denormalization and use the LastName field as alias for EmployeeID. This field is shown below, labeled “Employee Last Name”.

The end user may want to see the full name of the employee instead.

Let’s create a calculated field that combines First Name and Last Name of the employee, and use this field as an alias for EmployeeID in both Employees and Orders controllers.

Creating the Calculated Field in Employees Controller

Activate the Project Designer. In the Explorer, switch to Controllers tab. Let’s take a look at the command of Employees controller. Double-click on Employees / Commands / command1 node.

Take a look at the text of the command.

select
    "Employees"."EmployeeID" "EmployeeID"
    ,"Employees"."LastName" "LastName"
    ,"Employees"."FirstName" "FirstName"
    ,"Employees"."Title" "Title"
    ,"Employees"."TitleOfCourtesy" "TitleOfCourtesy"
    ,"Employees"."BirthDate" "BirthDate"
    ,"Employees"."HireDate" "HireDate"
    ,"Employees"."Address" "Address"
    ,"Employees"."City" "City"
    ,"Employees"."Region" "Region"
    ,"Employees"."PostalCode" "PostalCode"
    ,"Employees"."Country" "Country"
    ,"Employees"."HomePhone" "HomePhone"
    ,"Employees"."Extension" "Extension"
    ,"Employees"."Photo" "Photo"
    ,"Employees"."Notes" "Notes"
    ,"Employees"."ReportsTo" "ReportsTo"
    ,"ReportsTo"."LastName" "ReportsToLastName"
    ,"Employees"."PhotoPath" "PhotoPath"
from "dbo"."Employees" "Employees"
    left join "dbo"."Employees" "ReportsTo" on "Employees"."ReportsTo" = "ReportsTo"."EmployeeID"

You can see the alias “Employees” in front of the LastName and FirstName columns. You will need to use this alias when referring to columns of the Employees table in the SQL Formula of the calculated field.

Make sure not to modify and/or save the command – the code generator
will stop automatically updating the command if you do so.

Let’s create the new field. Right-click on Employees / Fields node and select New Field option.

Give this field the following settings:

Property	Value
Name	EmployeeFullName
Allow Null Values	True
The value of this field is computed at run-time by SQL expression.	"Employees"."LastName" + ', ' + "Employees"."FirstName"
Label	Employee Full Name
Values of this field cannot be edited	True
Allow Query-by-Example	True
Allow Sorting	True

Press OK to save the new field.

Now let’s change the data fields in the grid view. In the Project Explorer, double-click on Employees / Views / grid1 / LastName data field node.

Make the following change:

Property	New Value
Field Name	EmployeeFullName

Press OK to save the data field. In the Project Explorer, right-click on Employees / Views / grid1 / FirstName data field node, and choose Delete option. Select OK to confirm the delete operation.

Creating the Calculated Field in Orders Controller

Before creating the “EmployeeFullName” field in Orders controller, we’ll need to find the alias of the Employees tables in the command text.

Double-click on Orders / Commands / command1 node.

Take a look at the text of the command.

select
    "Orders"."OrderID" "OrderID"
    ,"Orders"."CustomerID" "CustomerID"
    ,"Customer"."CompanyName" "CustomerCompanyName"
    ,"Orders"."EmployeeID" "EmployeeID"
    ,"Employee"."LastName" "EmployeeLastName"
    ,"Orders"."OrderDate" "OrderDate"
    ,"Orders"."RequiredDate" "RequiredDate"
    ,"Orders"."ShippedDate" "ShippedDate"
    ,"Orders"."ShipVia" "ShipVia"
    ,"ShipVia"."CompanyName" "ShipViaCompanyName"
    ,"Orders"."Freight" "Freight"
    ,"Orders"."ShipName" "ShipName"
    ,"Orders"."ShipAddress" "ShipAddress"
    ,"Orders"."ShipCity" "ShipCity"
    ,"Orders"."ShipRegion" "ShipRegion"
    ,"Orders"."ShipPostalCode" "ShipPostalCode"
    ,"Orders"."ShipCountry" "ShipCountry"
from "dbo"."Orders" "Orders"
    left join "dbo"."Customers" "Customer" on "Orders"."CustomerID" = "Customer"."CustomerID"
    left join "dbo"."Employees" "Employee" on "Orders"."EmployeeID" = "Employee"."EmployeeID"
    left join "dbo"."Shippers" "ShipVia" on "Orders"."ShipVia" = "ShipVia"."ShipperID"

You can see that this data controller refers to Employees using “Employee”, which is different than the Employees data controller reference of “Employees”. The SQL Formula of the calculated field will need to use the alias of the command text in Orders data controller.

Make sure not to save any changes to the command. Right-click on Orders / Fields node, and choose New Field.

Use the following settings for the new field:

Property	Value
Name	EmployeeFullName
Allow null values	True
The value of this field is computed at run-time by SQL Expression	"Employee"."LastName" + ', ' + "Employee"."FirstName"
Label	Employee Full Name
Values of this field cannot be edited	True
Allow Query-by-Example	True
Allow Sorting	True

Press OK to save the field.

Let’s change the alias of the EmployeeID data fields in each view so that Employee Full Name is displayed. In the Explorer, double-click on Orders / Fields / EmployeeID field node.

At the top, switch to Data Fields tab. For all three data fields, make the following change:

Property	New Value
Alias	EmployeeFullName

On the tool bar, press Browse to generate the web application. When it opens in your default web browser, navigate to Orders page. The EmployeeID field now shows the full name of each employee, instead of just the last name.

If you edit a record and activate the lookup for Employee Full Name, you will see that the grid view will show the Employee Full Name in the first column.

Selecting a record from the grid will insert the full name into the lookup field.

Navigate to the Employees page of the web application. If you activate inline edit mode on the grid view, you will not be able to edit the Employee Full Name field.

However, if you edit a record using form view, the original First Name and Last Name fields are still there.

Result Set Production With Stored Procedures

Modern database servers provide support for stored procedures. A stored procedure is a script written in a server-specific dialect of SQL. Such scripts are stored in the database and executed by a database server process upon request.

The main benefits are the close proximity to the data and reusability of the procedure. Applications invoke stored procedures by name with parameters that affect the execution result.

A popular reason to implement a stored procedure is the need to produce a custom result set that cannot be achieved with a SELECT statement. Sometimes the database table data needs to be split into multiple streams, merged, pivoted, and conditionally processed over multiple iterations.  A custom result set is returned to the client application in the stored procedure output.

Frequently, it takes a longer time to produce a custom result set with a stored procedure when compared to a straightforward SELECT statement. This creates a unique challenge in presenting the output of stored procedures in the user interface of applications. End users may be willing to wait for initial results, but will quickly grow irritated if every single interaction with the application takes a long time to complete.

Output caching must be employed when working with the stored procedures designed to produce custom result sets.

Removing a Controller

You know how to add a table to your Code On Time project. Let’s discuss the steps required to remove a table from your web application.

First, drop the table from the database. For the purpose of the discussion, we’ll drop the previously created Contacts table.

When you have deleted the table, start Code On Time generator. Click on the project name, and select Refresh option. The generator will automatically detect that the table no longer exists, and will suggest to delete the Contacts data controller. Press Refresh. Choose Yes to confirm the operation.

On the Summary page, select Generate to rebuild the web application.

You will need to remove any data views or pages
that were linked to Contacts data controller manually.

If you navigate to the Shippers page, select a shipper, and switch the child tab to Contact List, an exception will be thrown and an error message will be displayed at the top of the page. This is because the data controller no longer exists.

Switch back to the web application generator. Click on the project name and activate the Project Designer. In the Explorer, right-click on Shippers / container2 / dv100 data view node and choose Delete. Press OK to confirm deletion.

There is also a dedicated Contact List page that previously displayed all Contact records. Visiting the page will raise the same exception. This page is not linked to any other data controllers, so let’s delete the whole page from the project.

In the Project Explorer, right-click on Shippers / Contact List page node and select Delete. Press OK to confirm the operation.

On the tool bar, press Browse to generate the application. When it opens in your default web browser, navigate to Shippers page. You will see that the Contact List tab option is no longer present on the page. Also, the Contact List page is no longer displayed on the menu.

Feature: Automatic Denormalization

Code On Time application generator performs automatic denormalization when constructing application data controllers from tables of a normalized database.

Consider the following subset of tables from the Northwind sample.

Product information is stored in three tables. Normalized table Products replaces category and supplier information with ID of data rows stored in Categories and Suppliers tables.

If you run the sample application then the Products page will be presented in a denormalized user-friendly fashion.

You can control the inclusion of fields in the data controllers with the help of denormalization field map.

Creating a New Controller Based on a Table

Let’s add a new table of contacts to the Northwind database. This table will store contacts associated with each record in the Shippers table.

Use the following SQL script to create the table “Contacts” in the Northwind database.

CREATE TABLE [dbo].[Contacts](
    [ContactID] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY,
    [ShipperID] [int] NOT NULL,
    [FirstName] [nvarchar](50) NOT NULL,
    [LastName] [nvarchar](50) NOT NULL,
    [HomePhone] [nvarchar](50) NULL,
    [MobilePhone] [nvarchar](50) NULL,
    [Email] [nvarchar](100) NULL
)
GO

ALTER TABLE [dbo].[Contacts] ADD  CONSTRAINT [FK_Contacts_Shippers] FOREIGN KEY([ShipperID])
REFERENCES [dbo].[Shippers] ([ShipperID])
GO

Start Code On Time web application generator, click on the project name, and select the Refresh option. Toggle the checkbox next to the new dbo.Contacts table, and press Refresh. Choose Yes to confirm the project refresh.

Select Generate to regenerate the web application. It will open in your default web browser.

If you have not made any page modifications to your web application in the Project Designer , then Contacts will be added seamlessly to the application. The Shippers page will be moved onto the first level of the menu, with Contacts page underneath. If you navigate to Shippers page, there will be a Contacts child data view present underneath the Shippers master data view.

However, if you have modified the application pages, then the generator will not make any changes to the design of application pages. The Contacts page will not change the site map, and will be placed under a “New Pages” menu option after Membership.  There will be no Contacts child data view on the Shippers page.

In this situation, you will need to manually add the child data view to the Shippers page. If you need a global list of all shipper contacts easily accessible to the end user, then you will need to rearrange the site map on your own. Both tasks are explained next.

Adding Data View to the “Shippers” Page

Switch to the web application generator, click on the project name, and access the Project Designer. In the Explorer, right-click on Region / Shippers / container2 node, and select New Data View.

Use the following settings:

Property	Value
Controller	Contacts
View	grid1
Activator	Tab
Text	Contact List

Press OK to save the data view. The Project Explorer will look similar to the picture below.

On the tool bar, press Browse to generate the web application.

When it opens in your web browser, navigate to Shippers page. Select a shipper, and you will see that the Contacts data view has been added to the page as a child.

Moving the “Contacts” Page in the Site Map

Let’s move the Shippers and Contacts pages in a more prominent position in the site map. Switch back to the Project Designer. In the Explorer, double-click on New Pages / Contacts page node.

Change the following settings:

Property	New Value
Index	1065
Title	Contact List
Path	Shippers | Contact List

Press OK to save the page. Using the Project Explorer, double-click on Region / Shippers.

Change the following settings:

Property	New Value
Index	1064
Path	Shippers

In the Explorer, right-click on New Pages page node, and select Delete.

Press OK to confirm the delete operation. The Explorer tree will look like the picture below.

Press Browse to generate the project.

On the menu bar of the web application, the Shippers and Contact List pages will now be displayed between Employees and Categories options.

Automatic Denormalization

Code On Time application generator performs automatic denormalization when constructing application data controllers from tables of a normalized database.

Database architects put a significant effort in creating a normalized database structure. Normalization ensures efficient data storage and maintenance. It is also much easier to extent a normalized database with new tables and columns.

Consider the following subset of tables from the Northwind sample.

Product information is stored in three tables. Raw product data replaces category and supplier information with ID of records stored in Categories and Suppliers tables.

This is not how the application users see the world. Business users want to see the actual category and supplier when looking at a product record instead of a numeric key value.

Application generator composes the following command text stored in the data controller definition.

<dataController name="Products" . . . .>
  <commands>
    <command id="command1" type="Text">
      <text><![CDATA[
select
    "Products"."ProductID" "ProductID"
    ,"Products"."ProductName" "ProductName"
    ,"Products"."SupplierID" "SupplierID"
    ,"Supplier"."CompanyName" "SupplierCompanyName"
    ,"Products"."CategoryID" "CategoryID"
    ,"Category"."CategoryName" "CategoryCategoryName"
    ,"Products"."QuantityPerUnit" "QuantityPerUnit"
    ,"Products"."UnitPrice" "UnitPrice"
    ,"Products"."UnitsInStock" "UnitsInStock"
    ,"Products"."UnitsOnOrder" "UnitsOnOrder"
    ,"Products"."ReorderLevel" "ReorderLevel"
    ,"Products"."Discontinued" "Discontinued"
from "dbo"."Products" "Products"
    left join "dbo"."Suppliers" "Supplier" on 
      "Products"."SupplierID" = "Supplier"."SupplierID"
    left join "dbo"."Categories" "Category" on 
      "Products"."CategoryID" = "Category"."CategoryID"
]]></text>
    </command>
    . . . . . 

This is the partial output of the query executed in SQL Management Studio.

If you run the sample application then the Products page will be presented in a denormalized user-friendly fashion.

The actual query executed by application is not the same as text stored in the data controller definition. In fact, the application framework uses the command text as a developer-friendly dictionary to locate at runtime the expressions behind the field names, the base table, and “join” constructs.

This is the actual query text that matches the screen shot.

with page_cte__ as (
    select
        row_number() over (order by "Products"."ProductID") as row_number__
        ,"Products"."ProductName" "ProductName"
        ,"Products"."SupplierID" "SupplierID"
        ,"Products"."CategoryID" "CategoryID"
        ,"Products"."QuantityPerUnit" "QuantityPerUnit"
        ,"Products"."UnitPrice" "UnitPrice"
        ,"Products"."UnitsInStock" "UnitsInStock"
        ,"Products"."UnitsOnOrder" "UnitsOnOrder"
        ,"Products"."ReorderLevel" "ReorderLevel"
        ,"Products"."Discontinued" "Discontinued"
        ,"Products"."ProductID" "ProductID"
        ,"Supplier"."CompanyName" "SupplierCompanyName"
        ,"Category"."CategoryName" "CategoryCategoryName"
    from
    "dbo"."Products" "Products"
        left join "dbo"."Suppliers" "Supplier" on 
            "Products"."SupplierID" = "Supplier"."SupplierID"
        left join "dbo"."Categories" "Category" on 
            "Products"."CategoryID" = "Category"."CategoryID"

    where
    (
    ("Supplier"."CompanyName"=@p0)
    )
)
select * from page_cte__ 
where 
    row_number__ > @PageRangeFirstRowNumber and 
    row_number__ <= @PageRangeLastRowNumber

Notice the use of parameters that prevent any possibility of SQL injection attack.

The SQL statement also utilizes a common table expression for efficient data retrieval.

You can control the inclusion of fields in the data controllers with the help of denormalization field map.

Feature: Many-to-Many Fields

If you have an existing many-to-many relationship in your database, you can turn it into a virtual many-to-many field.

When you edit the record, you will see a check box list of all territories. Those linked to the employee are checked.

The field will display a comma-separated list of territories linked to the employee when presented in read mode.

Learn how to configure a virtual field based on an existing many-to-many relationship.

Many-to-Many Relationship in the Northwind database

If you have an existing many-to-many relationship in your database, you can turn it into a many-to-many field. In the Northwind database, there is a many-to-many relationship between tables EmployeeTerritories, Employees, and Territories.

An employee can be assigned to several territories, but these territories are not exclusive to an employee. Each employee can be linked to multiple territories, and each territory can be linked to multiple employees.

On the Employees page of a Northwind web application, a separate child data view underneath the master record shows a list of territories associated with the selected employee.

Let’s display these territories as a comma separated list on the master employee record, and allow end users to select territories for each employee using a check box list style of presentation.

Start Code On Time web application generator and activate the Project Designer. In the Explorer, switch to the Controllers tab. Right-click on Employees / Fields node and select New Field.

Give this field the following settings:

Property	Value
Name	Territories
Type	String
Allow null values	True
The value of this field is computed at run-time by SQL expression	True
SQL Formula	NULL
Label	Territories
Items Style	Check Box List
Items Data Controller	Territories
Data Value Field	TerritoryId
Data Text Field	Territory Description
Target Controller	EmployeeTerritories

Press OK to save the field. Next, we’ll need to bind the field to each view.

At the top of the page, switch to the Data Fields tab.

On the action bar, press New | New Data Field. Give this data field the following settings:

Property	Value
View	editForm1
Category	Employees
Columns	5

Press OK to save the data field. In the Explorer, double-click on Employees / Fields / Territories field node.

Create another data field with the following settings:

Property	Value
View	createForm1
Category	Employees
Columns	5

On the tool bar, press Browse to generate the application. It will open in your default web browser. Navigate to the Employees page, and select an employee. You will see a comma separated list of territories that belong to the employee.

If you edit the employee, you will see a check box list of all options.

Using Check Box List for New Many-to-Many Relationship

The “Check Box List” lookup item style can be used to enter multi-value or many-to-many fields.

In the Multiple Value Field tutorial, we created a Shipping Methods field in the Shippers table that stored a comma separated list of values. This may be sufficient for some people, but it would be standard operating procedure to normalize this list into a separate table in order to store the values externally.

Let’s create a separate table by the name of ShipMethods, and a table to store the many-to-many relationship called ShippersShipMethods.

In the Object Explorer, right-click on Databases / Northwind / Database Diagrams and select New Database Diagram. Select Yes to install diagram support in the database.

When the Add Table screen opens, highlight Shippers and press Add.

Press Close to close the screen. Click on the Shippers table. Mouse over the Table View property, and change it to Standard.

Right-click on white space in the diagram and press New Table. Give the table the name of “ShipMethods”. Press OK to create a table with this name.

Give this table the following fields:

Primary Key?	Column Name	Data Type	Allow Nulls
Yes	ShipMethodID	int	No
	ShipMethodName	nvarchar(50)	No

Highlight the ShipMethodID column. In the Properties window, change (Is Identity) to “Yes”.

Create another table with the name of “ShippersShipMethods”.

Give this table the following fields:

Primary Key?	Column Name	Data Type	Allow Nulls
Yes	ShipperID	int	No
Yes	ShipMethodID	int	No

Drag the ShipperID column from ShippersShipMethods to Shippers table. Keep the default Relationship name and press OK twice to save the relationship.

Drag the ShipMethodID column from ShippersShipMethods to ShipMethods table. Press OK twice to save.

Save this diagram to apply the changes. Your diagram should look like the one below.

Start Code On Time web application generator, click on the project name, and select the Refresh option. Toggle the checkboxes next to the tables dbo.ShipMethods, dbo.ShippersShipMethods, and the controller Shippers. Press Refresh, and press Yes to confirm the operation.

When the Refresh is complete, select the Design option on the Summary page.

In the Project Explorer, switch to the Controllers tab. Right-click on Shippers / Fields node and select New Field.

Give this field the following settings:

Property	Value
Name	ShipMethods
Allow Null Values	True
The value of this field is computed at run-time by SQL Expression	True
SQL Formula	NULL
Label	Ship Methods
Items Style	Check Box List
Items Data Controller	ShipMethods
Data Value Field	ShipMethodID
Data Text Field	ShipMethodName
Target Controller	ShippersShipMethods

Press OK to save the field. In the Project Explorer, right-click on Shippers / Views / editForm1 / c1 – Shippers category node and select New Data Field.

Give this data field the following settings:

Property	Value
Field Name	ShipMethods
Columns	3

Press OK to save the data field. Right-click on Shippers / Views / createForm1 / c1 – New Shippers category node and choose New Data Field.

Give this data field the following settings:

Property	Value
Field Name	ShipMethods
Columns	3

Press OK to save the data field. Let’s display a read-only list of fields in the grid view as well. Right-click on Shippers / Views / grid1 view node and select New Data Field.

Give this data field the following settings:

Property	Value
Field Name	ShipMethods
Columns	3
Values of this field cannot be edited	Yes

Press OK to save the data field. On the tool bar, select Browse to generate the web application.

When it opens in your default web browser, navigate to the Ship Methods page. Create three new shipping methods:

Ship Method Name

Land

Air

Sea

Next, navigate to Shippers page. You will see all shippers have “N/A” Ship Methods. Edit a record, and a check box list will appear with the available options.

Select a few ship methods for each shipper. When not in edit mode, the check box list will be replaced with a comma separated list of values. The data field on the grid view will stay in read only mode when a record is being edited.