Wednesday, June 6, 2007

Optimized paging in queries over two or more tables using Sql Server 2005 functions

The default paging made by the ASP.Net controls that support pagination is based on the following steps:
1) Receive the DataSource with the result of the query
2) Based on the page size and the page selected by the user, it selects the records of the "selected page" to show, discarding the rest of the data. This pagination is highly expensive in terms of resources (greater traffic and data processing), when the volume of records on which the query is made is of a considerable size.
For example, a user has 200 Orders in the database and a form with gridview that shows 10 records for each page. If he browse 10 grid pages, the amount of records transferred to gridview so that this pagine will be of 2000 (200 records by each selected page) whereas the shown amount indeed is of 100 records. That is that 1900 records are transferred only to be discarded, by all means that this waste exponentially increases with the amount of records that the query gives back. The example of custom paging which we can easily find in the Web, makes use of function ROW_NUMBER() of SQL Server 2005, and only is useful when the query is made on an single table. At the time of making queries on related tables function ROW_NUMBER() does not apply, since to a registry “father” (as the head of an order) would assign so many RowNumbers as “children” (order details) records have. Therefore, looking for the new functions of SQL Server 2005 to solve this problem, I found the DENSE_RANK() function that allows to obtain only one RowNumber by each registry “father” in a query over multiple tables. Thus, I developed an stored procedure for the AdventureWorks database that gives back a certain page of result of a query over Orders and its detail in optimized form, this can be useful when need populate paged hierarchical data like Orders & Order Details and want make to do a single call to retrieve all data of a single page.

Stored procedure PagedOrders

CREATE PROCEDURE [dbo].[PagedOrders]
@pagesize int,
@pageindex int


SELECT DENSE_RANK() OVER (ORDER BY Sales.SalesOrderHeader.SalesOrderID DESC) AS Row,
FROM Sales.SalesOrderDetail
ON Sales.SalesOrderDetail.SalesOrderID = Sales.SalesOrderHeader.SalesOrderID
ON Sales.SalesOrderDetail.ProductID = Production.Product.ProductID

AS Result

WHERE Row between ((@pageindex - 1) * @pagesize + 1) and (@pageindex * @pagesize)

Monday, June 4, 2007

Localization and UICulture="auto" attribute

While develop an application that requires location in all its pages I follow these 3 steps:
1) Develop the pages and generate the resources files (in VS2005 Tools Option --> Local Generate Resource)
2) Create a page where the user selects the language of the application, which is stored in a Session variable or Profile field or Database field.
3) Apply the language selected to all requests in the Application_PreAcquireRequestState.

culture = HttpContext.Current.Session["culture"].ToString();
System.Threading.Thread.CurrentThread.CurrentUICulture = new System.Globalization.CultureInfo(culture);
System.Threading.Thread.CurrentThread.CurrentCulture = System.Globalization.CultureInfo.CreateSpecificCulture(culture);

When test the application I found that the changes of language were not reflected in the different pages. This is because the UICulture="auto" attribute of the @Page directive in each page, added automatically in step 1, does that the initialization of the localization succeeds at page level overriding the language selected by the user that is applied in the Application_PreAcquireRequestState. The solve this problem follow the next steps:
· Apply the default culture to all pages in web.config (using globalization tag)
· Delete UICulture="auto" attibute tags added when generating the resources files.

To have a better notion of the execution order of events in a request you can read this article or explore this graphic.

· Globalization:Das Blonde Blog
· Profiles : MSDN2

Changing the Default Profile Provider

I confess that it had not research so much over Memberships, Roles and Profiles provider until arrived the hour to use them in a project. While installing the SQL Server database to give support to providers with the Aspnet_regsql.exe tool, it thought how the fields corresponding to each property would keep from a profile, “wishing” that it was not an only field that contains names and values for all properties. When finalizing the installation I verified that my desires did not become reality and I find a table with 3 fields, one that describes the properties (PropertyNames) and in the other two the values are stored (PropertyValuesString and PropertyValuesBinary)

This way to store the profiles reduces the flexibility when needs make reports, changes in the fields or store profile fields in different tables. The essence of providers is offer a point of variation in certain parts of an application, protecting the application of the implementation of the selected provider (that is the code of the application is not sensible when selects any provider). Native Providers of framework 2.0 does not try to include an exhaustive functionality, but rather they solve the common topics that it can be need in an application and serve as reference for the development of new providers. Luckyly they exist providers for SQL Server that solves the mentioned problems.
In Sandbox are available 2 Sql Server providers:
· SqlTableProfileProvider: it allows map each property of profile with a field of an unique table.
· SqlStoredProcedureProfileProvider: it allows map each property of profile with a parameter of a Stored Procedure and within this, we can map each parameter to any table. The configuration is quite simple, only requires to add the class of the profile provider in directory APP_CODE and modify the web.config so that utilize this.