Data Sources

Labels
AJAX(112) App Studio(9) Apple(1) Application Builder(245) Application Factory(207) ASP.NET(95) ASP.NET 3.5(45) ASP.NET Code Generator(72) ASP.NET Membership(28) Azure(18) Barcode(2) Barcodes(3) BLOB(18) Business Rules(1) Business Rules/Logic(140) BYOD(13) Caching(2) Calendar(5) Charts(29) Cloud(14) Cloud On Time(2) Cloud On Time for Windows 7(2) Code Generator(54) Collaboration(11) command line(1) Conflict Detection(1) Content Management System(12) COT Tools for Excel(26) CRUD(1) Custom Actions(1) Data Aquarium Framework(122) Data Sheet(9) Data Sources(22) Database Lookups(50) Deployment(22) Designer(178) Device(1) DotNetNuke(12) EASE(20) Email(6) Features(101) Firebird(1) Form Builder(14) Globalization and Localization(6) How To(1) Hypermedia(2) Inline Editing(1) Installation(5) JavaScript(20) Kiosk(1) Low Code(3) Mac(1) Many-To-Many(4) Maps(6) Master/Detail(36) Microservices(4) Mobile(63) Mode Builder(3) Model Builder(3) MySQL(10) Native Apps(5) News(18) OAuth(9) OAuth Scopes(1) OAuth2(13) Offline(20) Offline Apps(4) Offline Sync(5) Oracle(11) PKCE(2) Postgre SQL(1) PostgreSQL(2) PWA(2) QR codes(2) Rapid Application Development(5) Reading Pane(2) Release Notes(184) Reports(48) REST(29) RESTful(29) RESTful Workshop(15) RFID tags(1) SaaS(7) Security(81) SharePoint(12) SPA(6) SQL Anywhere(3) SQL Server(26) SSO(1) Stored Procedure(4) Teamwork(15) Tips and Tricks(87) Tools for Excel(3) Touch UI(93) Transactions(5) Tutorials(183) Universal Windows Platform(3) User Interface(338) Video Tutorial(37) Web 2.0(100) Web App Generator(101) Web Application Generator(607) Web Form Builder(40) Web.Config(9) Workflow(28)
Archive
Blog
Data Sources
Tuesday, May 1, 2012PrintSubscribe
Creating a Table for Output Caching

Consider the stored procedure CustOrderHist from the Northwind sample.

CREATE PROCEDURE [dbo].[CustOrderHist] @CustomerID nchar(5)
AS
SELECT ProductName, Total=SUM(Quantity)
FROM Products P, [Order Details] OD, Orders O, Customers C
WHERE C.CustomerID = @CustomerID
AND C.CustomerID = O.CustomerID AND O.OrderID = OD.OrderID AND OD.ProductID = P.ProductID
GROUP BY ProductName

Enter the following query in SQL Management Studio to test the procedure.

EXEC [dbo].[CustOrderHist] @CustomerID = 'ANTON'

The stored procedure will produce a custom result set. The result set contains the total number of products purchased by the customer with ID of ANTON.

A custom result set produced by the stored procedure 'CustOrderHist' when executed with parameter 'ANTON'

Let’s design a table to cache the output of the stored procedure.

The columns of the output cache table will have to match the columns in the custom result set. It will help if all columns in the table allow storing a null value.

We will also include a column to capture the value of the parameter. If you have more than one parameter, then create a column for each of them. This will allow distinguishing cached result sets produced with different parameters.

You may need to incorporate a column capturing the user identity to segregate cached data produced by application users. You don’t have to implement the column if it is acceptable that data cached by any user is shared with other application users.

One more column will be required to register a cache expiration date. We will assign an expiration date to the cached data rows. This will simplify removing the “stale” data from the output cache table.

Let’s name the output cache table after the the stored procedure.

Output Cache Table captures parameters, user identity, and expiration date of each cached data row

The following script will create the output cache table with the name CustOrderHist_Cache.

CREATE TABLE [dbo].[CustOrderHist_Cache](
    [ProductName] [nvarchar](40) NULL,
    [Total] [int] NULL,
    [CustomerID] [nchar](5) NULL,
    [CacheID] [uniqueidentifier] NOT NULL PRIMARY KEY,
    [CacheUserName] [nvarchar](40) NULL,
    [CacheExpires] [datetime] NULL
) 

This is a brief description of the table columns.

Column Description Source
ProductName The name of the product purchased by customer. Custom Result Set
Total The total number of products purchased by customer. Custom Result Set
CustomerID The value of the CustomerID parameter. Stored Procedure Parameter
CacheID Generic primary key of the output cache table. Caching Algorithm
CacheUserName The name of the application user that has caused the caching of data rows. Caching Algorithm
CacheExpires The date when cached data rows need to be deleted. Caching Algorithm

The highlighted columns represent the custom result set produced by the stored procedure and its parameters.  If your own stored procedure or web service returns more columns in the output or requires more parameters, then incorporate them in the design of the table.

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