Blog: Posts from April, 2012

Posts from April, 2012
Monday, April 30, 2012PrintSubscribe
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 procedureweb 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.

Sunday, April 29, 2012PrintSubscribe
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.

Employees and Orders table diagram from the Northwind database

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”.

Default Orders edit form with 'Employee Last Name' field

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.

Employees 'command1' in Code On Time Project Explorer

Take a look at the text of the command.

    "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.

New Field for Employees data controller

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.

'LastName' data field in grid1 view of Employees controller

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.

Delete 'FirstName' data field from 'grid1' view of Employees data controller

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.

Orders 'command1' in Code On Time Project Explorer

Take a look at the text of the command.

    "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.

New Field in Orders data controller

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.

EmployeeID field of Orders data controller

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

Property New Value
Alias EmployeeFullName

Change Alias to 'EmployeeFullName' for all EmployeeID data fields

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.

Employee Full Name displayed for Orders data view

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.

Employee Full Name displayed in EmployeeID lookup

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

Employee Full Name inserted into EmployeeID 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.

Employee Full Name is read-only in grid view

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

Last Name and First Name fields are editable in edit form

Sunday, April 29, 2012PrintSubscribe
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.

Continue to Removing a Controller