efficient paging via ObjectDataSource paging

Friday, August 31, 2007

When using paging with data-bound controls, performance issues can arise when accessing large amounts of data.  By default, all rows needed for the data-bound control will be acquired even if paging is only exposing a small subset of these rows per page index.  Utilizing an ObjectDataSource (ODS) and enabling paging functionality within the ODS allows a specified amount of rows to be accessed from the database per page index.  Setting the ODS to use paging requires a few extra properties to be utilized within the ODS: EnablingPaging, MaximumRowsParameterName, StartRowIndexParameterName, and SelectCountMethod to be specific.

In this example, I am utilizing the following ODS:

<asp:ObjectDataSource ID="ObjectDataSource1" runat="server" EnablePaging="True" SelectCountMethod="GetDepartmentCount" SelectMethod="GetDepartments" TypeName="TempBLL" MaximumRowsParameterName="maximumRows" StartRowIndexParameterName="startRowIndex">

The ODS points to the following class:

using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;

[System.ComponentModel.DataObject]
public class TempBLL
{
    [System.ComponentModel.DataObjectMethod(System.ComponentModel.DataObjectMethodType.Select,
    true)]
    public DataTable GetDepartments(int startRowIndex, int maximumRows)
    {
        using (SqlConnection connection = new SqlConnection("Data Source=localhost;Initial
        Catalog=AdventureWorks;Trusted_Connection=Yes"))
        {
            SqlCommand command = connection.CreateCommand();
            command.CommandType = CommandType.StoredProcedure;
            command.CommandText = "HumanResources.uspGetDepartments";
            command.Parameters.Add(new SqlParameter("@FirstRow", SqlDbType.Int));
            command.Parameters["@FirstRow"].Value = startRowIndex + 1;
            command.Parameters.Add(new SqlParameter("@MaxRows", SqlDbType.Int));
            command.Parameters["@MaxRows"].Value = maximumRows;
            DataSet result = new DataSet();
            SqlDataAdapter adapter = new SqlDataAdapter(command);
            adapter.Fill(result);

            HttpContext.Current.Items["DepartmentRowCount"] = (int)result.Tables[1].Rows[0][0];
            return result.Tables[0];
        }
    }

    public int GetDepartmentCount()
    {
        return (int)HttpContext.Current.Items["DepartmentRowCount"];
    }
}

When using paging within the ODS, the method specified in the SelectMethod property must, at the least, have a parameter to indicate the starting row index and a parameter to indicate how many rows should be returned per page.  The names of these parameters are set in the StartRowIndexParameterName and MaximumRowsParameterName properties of the ODS respectively.

The purpose of the customized SelectMethod is to provide a mechanism by which only a pre-determined number of rows will be returned from the database per page index.  The ODS will take care of passing in the appropriate arguments for the two custom parameters.  The “maximumRows” parameter is determined from the data-bound control’s PageSize property and the “startRowIndex” parameter automatically increases based on the currently selected page index.  For example, if you only want 5 rows to show up per page on a GridView, simply set the GridView’s PageSize property to 5 and the ODS will automatically send the value 5 into the SelectMethod’s parameter that is named in the MaximumRowsParameterName property.  This value can then be used to limit the amount of rows being grabbed from the database (as I did with the uspGetDepartments stored procedure).

The SelectCountMethod property points to the second method within TempBLL.cs.  This method lets the data-bound control know the total amount of rows so it can display the correct number of page links.  This is necessary because the paging enabled ODS will only be returning a subset of the total rows.  I am utilizing the HttpContext in order to prevent two round-trips to the database since the SelectCountMethod is called after the SelectMethod.  I originally got the HttpContext idea from Fritz Onion.

I am using SQL Server 2005 in this example, so I am able to use the ROW_NUMBER() function in my T-SQL to grab the appropriate group of rows per page index:

CREATE PROCEDURE [HumanResources].[uspGetDepartments]
      @FirstRow int,
      @MaxRows int
AS
BEGIN
      WITH OrderedDepartments AS
      (
            SELECT d.DepartmentID, d.[Name], ROW_NUMBER() OVER (ORDER BY d.[Name]) AS
            'RowNumber'
            FROM HumanResources.Department d
      )
      SELECT od.DepartmentID, od.[Name]
      FROM OrderedDepartments od
      WHERE RowNumber BETWEEN @FirstRow AND (@FirstRow + @MaxRows) - 1 

      SELECT COUNT(*) FROM HumanResources.Department
END

With this stored procedure, I am able to get the proper subset of rows and the total row count.  The multiple result sets are placed within distinct tables when the stored procedure is called in the SelectMethod.  This allows the SelectMethod to grab the subset of rows needed for the current page index and retrieve the total row count in one database call.


11:23:08 PM   | c# | sql | asp.net | ado.net |
 
transfering sql server logins from 7.0/2000 to 2005

Tuesday, November 21, 2006

Ran into an interesting issue when I attempted to restore a SQL 2000 database to a SQL 2005 database.  The user account restored into SQL Server, but it did not tie in with a corresponding login.  Creating a login would not help because the login's SID did not match the original login's SID.  I found a KB article that helped sort it out.  In a nut shell, there are a couple of stored procedures that can be run on the base 7.0/2000 server which generates a script that can be used on the upgraded server to create a login with the original SID and password.  Run the following TSQL on the source server to generate the two stored procedures:


USE master
GO
IF OBJECT_ID ('sp_hexadecimal') IS NOT NULL
  DROP PROCEDURE sp_hexadecimal
GO
CREATE PROCEDURE sp_hexadecimal
    @binvalue varbinary(256),
    @hexvalue varchar(256) OUTPUT
AS
DECLARE @charvalue varchar(256)
DECLARE @i int
DECLARE @length int
DECLARE @hexstring char(16)
SELECT @charvalue = '0x'
SELECT @i = 1
SELECT @length = DATALENGTH (@binvalue)
SELECT @hexstring = '0123456789ABCDEF'
WHILE (@i <= @length)
BEGIN
  DECLARE @tempint int
  DECLARE @firstint int
  DECLARE @secondint int
  SELECT @tempint = CONVERT(int, SUBSTRING(@binvalue,@i,1))
  SELECT @firstint = FLOOR(@tempint/16)
  SELECT @secondint = @tempint - (@firstint*16)
  SELECT @charvalue = @charvalue +
    SUBSTRING(@hexstring, @firstint+1, 1) +
    SUBSTRING(@hexstring, @secondint+1, 1)
  SELECT @i = @i + 1
END
SELECT @hexvalue = @charvalue
GO

IF OBJECT_ID ('sp_help_revlogin') IS NOT NULL
  DROP PROCEDURE sp_help_revlogin
GO
CREATE PROCEDURE sp_help_revlogin @login_name sysname = NULL AS
DECLARE @name    sysname
DECLARE @xstatus int
DECLARE @binpwd  varbinary (256)
DECLARE @txtpwd  sysname
DECLARE @tmpstr  varchar (256)
DECLARE @SID_varbinary varbinary(85)
DECLARE @SID_string varchar(256)

IF (@login_name IS NULL)
  DECLARE login_curs CURSOR FOR
    SELECT sid, name, xstatus, password FROM master..sysxlogins
    WHERE srvid IS NULL AND name <> 'sa'
ELSE
  DECLARE login_curs CURSOR FOR
    SELECT sid, name, xstatus, password FROM master..sysxlogins
    WHERE srvid IS NULL AND name = @login_name
OPEN login_curs
FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @xstatus, @binpwd
IF (@@fetch_status = -1)
BEGIN
  PRINT 'No login(s) found.'
  CLOSE login_curs
  DEALLOCATE login_curs
  RETURN -1
END
SET @tmpstr = '/* sp_help_revlogin script '
PRINT @tmpstr
SET @tmpstr = '** Generated '
  + CONVERT (varchar, GETDATE()) + ' on ' + @@SERVERNAME + ' */'
PRINT @tmpstr
PRINT ''
PRINT 'DECLARE @pwd sysname'
WHILE (@@fetch_status <> -1)
BEGIN
  IF (@@fetch_status <> -2)
  BEGIN
    PRINT ''
    SET @tmpstr = '-- Login: ' + @name
    PRINT @tmpstr
    IF (@xstatus & 4) = 4
    BEGIN -- NT authenticated account/group
      IF (@xstatus & 1) = 1
      BEGIN -- NT login is denied access
        SET @tmpstr = 'EXEC master..sp_denylogin ''' + @name + ''''
        PRINT @tmpstr
      END
      ELSE BEGIN -- NT login has access
        SET @tmpstr = 'EXEC master..sp_grantlogin ''' + @name + ''''
        PRINT @tmpstr
      END
    END
    ELSE BEGIN -- SQL Server authentication
      IF (@binpwd IS NOT NULL)
      BEGIN -- Non-null password
        EXEC sp_hexadecimal @binpwd, @txtpwd OUT
        IF (@xstatus & 2048) = 2048
          SET @tmpstr = 'SET @pwd = CONVERT (varchar(256), ' + @txtpwd + ')'
        ELSE
          SET @tmpstr = 'SET @pwd = CONVERT (varbinary(256), ' + @txtpwd + ')'
        PRINT @tmpstr
EXEC sp_hexadecimal @SID_varbinary,@SID_string OUT
        SET @tmpstr = 'EXEC master..sp_addlogin ''' + @name
          + ''', @pwd, @sid = ' + @SID_string + ', @encryptopt = '
      END
      ELSE BEGIN
        -- Null password
EXEC sp_hexadecimal @SID_varbinary,@SID_string OUT
        SET @tmpstr = 'EXEC master..sp_addlogin ''' + @name
          + ''', NULL, @sid = ' + @SID_string + ', @encryptopt = '
      END
      IF (@xstatus & 2048) = 2048
        -- login upgraded from 6.5
        SET @tmpstr = @tmpstr + '''skip_encryption_old'''
      ELSE
        SET @tmpstr = @tmpstr + '''skip_encryption'''
      PRINT @tmpstr
    END
  END
  FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @xstatus, @binpwd
  END
CLOSE login_curs
DEALLOCATE login_curs
RETURN 0
GO

The two stored procedures will be added in the source server's master database.  Next, run the sp_help_revlogin stored procedure on the source server.  The stored procedure will generate a script that needs to be run on the upgraded server to complete the login migration.
After utilizing this method of login migration, I was able to get my database user to associate to a newly created login account with the same SID and password.  This method works for login transfer between:
  • SQL Server 7.0 --> SQL Server 7.0
  • SQL Server 7.0 --> SQL Server 2000
  • SQL Server 7.0 --> SQL Server 2005
  • SQL Server 2000 --> SQL Server 2000
  • SQL Server 2000 --> SQL Server 2005
For more info, check out http://support.microsoft.com/kb/246133.

2:25:13 PM   | sql |
 
excluding data with the sql except statement

Friday, August 11, 2006

I stumbled across an interesting SQL command the other day--the EXCEPT statement.  The EXCEPT statement returns distinct values from query A where query A records do not match query B records.  To explain how it works, lets work with two tables:

--Customers and their email addresses
CREATE TABLE [Customers]
(
   [CustomerID] [int] NOT NULL,
   [FirstName] [nvarchar](50),
   [LastName] [nvarchar](50),
   [Email] [varchar](100)
)
--Customer attributes assignment table
CREATE TABLE [CustomerAttributes]
(
   [CustomerID] [int] NOT NULL,
   [Attribute] [int] NOT NULL
)


The Customers table is responsible to hold customer data and the CustomerAttributes table assigns multiple attributes to the customers.  Lets say that attribute 5 is "No Mail" indicating that this customer does not wish to receive email.  In order to make sure customers do not receive email if they have the "No Email" attribute, we would need to create something like this:

SELECT DISTINCT
   c.CustomerID,
   c.FirstName,
   c.LastName,
   c.Email
FROM
   Customers AS c
LEFT OUTER JOIN
   (SELECT CustomerID FROM CustomerAttributes WHERE Attribute = 5) AS a
ON
   c.CustomerID = a.CustomerID
WHERE
   (a.CustomerID IS NULL)

Using the EXCEPT statement, we can create the following alternative SQL:

SELECT
   CustomerID,
   FirstName,
   LastName,
   Email
FROM
   Customers
EXCEPT

SELECT
   c.CustomerID,
   c.FirstName,
   c.LastName,
   c.Email
FROM
   Customers AS c
INNER JOIN
   CustomerAttributes AS a
ON
   c.CustomerID = a.CustomerID
WHERE a.Attribute = 5

There are a couple of constraints involved in using the EXCEPT statement.  There must be an equal number of fields returned in both queries and the data types of these fields must match.

The EXCEPT statement can be used to compare more than one set of queries.  In this situation, query A and query B are compared and a result set is created, then this result set is compared with query C and so on.  In the end, you will have a result set with distinct values  filtered of all records that match any of the EXCEPT queries provided.


11:26:39 PM   | sql |



Copyright SharpeningC.com 2007