How To Increase Performance of Loading Large Data from Database (Part-3)

Level: Advanced

Knowledge Required:
Part-3:

• Typed DataSet
• TableAdapter
• DataGridView

Description:
So far we have discussed the 2 better ways of loading large data,

Implement Paging in SQL Server 2005
Implement Paging in Application using SQLDataAdapter

Now the last one is to use to Virtual Mode of DataGridView control. Here we will NOT use the Data Binding since I have already discussed in Part-1 that Data Binding also slows down the process.

When DataGridView control is in Virtual Mode (VirtualMode=True) then CellValueNeeded Event is triggered for Unbound Columns. In the same event we receive the Row and Column Index, in which DataGridView is needing the Value. Therefore we just go in the same Row and Column of our loaded DataTable, get the Value and give to the DataGridView control.

So what we will do is,

1. Create a DataGridView Control NOT bound to anything
2. Add the Columns (manually) equal to number of Columns in our DataTable
3. Set the property of DataGridView Control i.e. VirtualMode = True
4. Load the Data in our DataTable which is also NOT bound to anything
5. Add the same number Rows in the DataGridView
6. And finally in the CellValueNeeded event you can use the following code

Private Sub grdLargeData_CellValueNeeded(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellValueEventArgs) Handles grdLargeData.CellValueNeeded
    Dim rowLargeData As LargeDataDataSet.LargeDataRow
    rowLargeData = Me.dsLargeData.LargeData(e.RowIndex)
    e.Value = rowLargeData(e.ColumnIndex)
End Sub

This approach is a bit faster as compare to the Data Bound version. But has limitations i.e. Ordering and Filtering cannot be performed.

Source Code:
LargeDataWithVirtualDGV.rar
Database:
TemporaryDB.rar
Note: Execute the ScriptToInsertBulkData.sql file (ziped inside the TemporaryDB.rar) to add the Fake Data

How To Increase Performance of Loading Large Data from Database (Part-2)

Level: Advanced

Knowledge Required:
Part-2:

• Typed DataSet
• TableAdapter
• DataAdapter
• Partial Class
• Data Binding

Description:
In my previous post, I showed you how to Implement Paging in SQL Server 2005 (Database Level). In this post I will discuss How To Implement Paging Using DataAdapter (Application Level). This is a simple technique,

SQLDataAdapter gives us an option to load the Data from Particular Row and to Limit the Loading of Data by providing the Maximum Number of Records to be Loaded. The Fill Method of SQLDataAdapter has 5 Declarations, one of them is: SQLDataAdapter.Fill(StartRecord, MaxNumberOfRecordsToLoad, ParamArray DataTables()) We will use the above Fill Method to start loading Data from Particular Record Number and Limit the SQLDataAdapter to load only 20 or 30 Records, as per our Page Size.

To implement it professionaly, we will create another Fill Method in our TableAdapter Class (using Partial Class) as,

Partial Class LargeDataTableAdapter
    Public Function FillByDataAdapterPaging(ByRef LargeDataDataTable As LargeDataSet.LargeDataDataTable, ByVal PageNumber As Integer, ByVal PageSize As Integer) As Integer
        Dim StartRow As Integer
        StartRow = ((PageNumber - 1) * PageSize)
        Me.Adapter.SelectCommand = Me.CommandCollection(0)
        If (Me.ClearBeforeFill = True) Then
            LargeDataDataTable.Clear()
        End If
        Dim returnValue As Integer = Me.Adapter.Fill(StartRow, PageSize, LargeDataDataTable)
        Return returnValue
    End Function
End Class

Source Code:
LargeDataWithDataAdapterPaging.rar
Database:
TemporaryDB.rar
Note: Execute the ScriptToInsertBulkData.sql file (ziped inside the TemporaryDB.rar) to add the Fake Data

How To Increase Performance of Loading Large Data from Database (Part-1)

Issue: While loading Large amount of Data from Database:

• Connection Timeout occurs
• Application gets Hang/Stuck
• Application takes too much time to load data

Level: Advanced

Knowledge Required:
Part-1:
SQL Server 2005
Stored Procedures

Description:
First of all this is NOT a good practice to load ALL THE DATA from a Table into memory. A Human being cannot process/analyse all this huge data in a glance, instead we usually interested in a short information. For example we have a Table that contains all the Contact Numbers living in the City along with there Addresses and Names. Now we usually want to extract out one particular number or one particular name, NOT all the names and numbers.

But still we (developers) are forced to create such applications which give the user to access all the data by SCROLLING up or down.

So in this Article I will show you how to increase this performance by the following ways:

1. Implement Paging in SQL Server 2005 (Database Level)
2. Implement Paging using DataAdapter (Application Level)
3. Use the Virtual Mode of DataGridView Control

In this part I will discuss the 1st one.
1) Paging in SQL Server 2005
Paging means we divide our huge data into number of small chunks. We will display 1 page at a time and will provide next previous buttons so user can navigate forward and backword. I think this is the fastest way to load data. We will send the Page Number and Number of Records Per Page to Stored Procedure, which will return only that part of data. For example: I have created a Table tbl_LargeData in which there are 3 Fields:

• Row_ID [primary key, int, Identity Column]
• SomeData [varchar(255)]
• InsertDateTime [DateTime, Default = GetDate()]

In this table I have put some fake data with Total 1,000,000 Rows.

Now this is a bit Large Data (NOT that much Large). Now to test, I created a project in VB and simply loaded the Data (in a DataTable) by executing the Query

SELECT * FROM tbl_LargeData

I have 1 GB RAM and AMD Athlon 64 Processor 3500+.

Unbound DataTable: It took 28 Seconds to fill the DataTable.
Bound DataTable: I bind that DataTable to BindingSource, it took 51 seconds, almost double.

BindingSource also increases time to Fill a DataTable, becuase BindingSource itself keeps another Cache of Data for Sorting and Filtering Purpose.

The Loading of Data can significantly increase Time, if:

• System has low RAM and Processing Speed
• Other Applications are also running on Client PC
• Database Server is NOT on same machine, it is somewhere on the LAN
• Client PC is connected to server using low Band Width

Therefore to Implement Paging I have created 2 Stored Procedures in Database:

1. GetLargeDataPageInfo
2. GetLargeDataWithPaging

The GetLargeDataPageInfo stored procedure has a Parameter @PageSize int. User has to provide the Page Size (i.e. Number of Records Per Page) then this stored procedure will return

Total Records	Total Pages
1000000	50000

GetLargeDataWithPaging stored procedure is the main stored procedure which Returns the Particular Page of Data. The script is:

CREATE PROCEDURE [dbo].[GetLargeDataWithPaging]
    @PageNumber int,
    @PageSize int
AS
BEGIN
    SET NOCOUNT ON;

    -- For Paging we have used the ROW_NUMBER() function
    -- which operates on Ordering of Column

    DECLARE @RowStart int;
    DECLARE @RowEnd int;

    -- Calculate the first row's Index
    -- and Last Row's Index
    SET @RowStart = ((@PageNumber - 1) * @PageSize) + 1;
    SET @RowEnd = @RowStart + @PageSize - 1;
 
    SELECT Row_ID, SomeData, InsertDateTime
    FROM (
        SELECT    ROW_NUMBER()
                OVER (
                    ORDER BY Row_ID
                    ) AS Row_Num,
                *
        FROM    tbl_LargeData
        ) AS DerivedTable
    WHERE Row_Num Between @RowStart AND @RowEnd;
END

Source Code:
LargeDataWithSQLPaging.rar
Database:
TemporaryDB.rar
Note: Execute the ScriptToInsertBulkData.sql file (ziped inside the TemporaryDB.rar) to add the Fake Data

Update (12-May-2008): Point #2 changed to "Implement Paging using DataAdapter" which was "Implement Paging using DataReader"