Usually, when ever we need to show some data from Database to screen , we set DataGrid / GridView control’s datasource property to DataSet / DataTable object. If you were not using DataTable / DataSet as media to bind data to Grid, my sincere apologies for including you in (we). Most of us are following this practise and after my recent experimentation(more details below), I would rather keep away myself from DataTable / DataSet. Why?

I was working on my home grown project (Technical Stock Analyzer for Indian market) and have come across requirement to display all the NSE listed companies. The project architecture looks something like this

At first, when user requests Stock / Symbols listing I returned DataSet from Data Access Logic Component and forwarded it to the end user. The source I used to achieve looks something like this

    Public Shared Function GetQuotes() As DataSet
        Dim db As Database = Nothing
        Dim oComm As Data.Common.DbCommand = Nothing
        Dim ds As DataSet = Nothing

        Try

            db = DatabaseFactory.CreateDatabase("MyLocalDatabase")

            oComm = db.GetStoredProcCommand("[dbo].[usp_SearchIndexes]")
            ds = db.ExecuteDataSet(oComm)

        Catch ex As Exception
            Throw
        End Try

        Return ds
    End Function

and on the Presentation Layer, DataSet is bound to a DataGrid / GridView control with code similar to

   Private Sub BindData()
        Dim ds As Data.DataSet

        ds = WebServiceHelper.GetQuotesDataSource(forceRefresh)

        GridView1.DataSource = ds
        GridView1.DataBind()
    End Sub

Everything works like charm and as expected. Previously, I never bothered to analyze XML Data being returned in the form of DataSet. But this time, I looked at XML being generated by the WebService. Oh man, it looks like crap with many unrelated tags & attributes. Not only, it added crappy XML tags but also returns XML specific to .NET, disrupting the whole purpose of WebService, (can be consumed by other application).

Complete XML generated by DataSet approach, can be download here DataTable.xml

So, I wrote another WebMethod that returns a Collection of Class Objects holding Stock / Symbol details. The Quote Class, is built for this purpose and looks something like this

This time rather than creating a DataSet, I used DataReader, to loop through entire result set row by row. A Generic List of Quote is created and for each Symbol details a new Quote object is created and added to the list.

Public Shared Function GetQuotes() As List(Of Quote)
        Dim oQuotes As New List(Of Quote)
        Dim oQuote As Quote = Nothing

        Dim db As Database = Nothing
        Dim oComm As Data.Common.DbCommand = Nothing
        Dim dr As IDataReader = Nothing

        Try

            db = DatabaseFactory.CreateDatabase(Arjarapu.Commons.Environment.Name)

            oComm = db.GetStoredProcCommand("[dbo].[usp_SearchIndexes]")
            dr = db.ExecuteReader(oComm)

            While dr.Read
                oQuote = New Quote()
                oQuote.QuoteId = Convert.ToInt64(dr.GetValue(dr.GetOrdinal("IndexId")))
                oQuote.Symbol = dr.GetString(dr.GetOrdinal("Symbol"))
                oQuote.CompanyName = dr.GetString(dr.GetOrdinal("CompanyName"))
                oQuote.Series = dr.GetString(dr.GetOrdinal("SecuritySeries"))
                oQuote.ListingDate = dr.GetDateTime(dr.GetOrdinal("ListingDate"))
                oQuote.PaidUpValue = dr.GetInt32(dr.GetOrdinal("PaidUpValue"))
                oQuote.MarketLot = dr.GetInt32(dr.GetOrdinal("MarketLot"))
                oQuote.ISINCd = dr.GetString(dr.GetOrdinal("SecurityISINCd"))
                oQuote.FaceValue = dr.GetInt32(dr.GetOrdinal("SecurityFacValue"))

                oQuotes.Add(oQuote)
            End While

        Catch ex As Exception
            Throw
        Finally
            If Not dr Is Nothing Then
                dr.Dispose()
            End If
        End Try

        Return oQuotes
    End Function

Though, it looks like extra effort on Developer to code this, its definetly worth doing so, as DataSet is heavy weight object, and more of database replica than just Container of Data. ExecuteDataSet of Enterprise Library, internally creates a DataReader, Creates a DataTable objects with schema of returned data column types. Then for each record a new DataRow would be created and appended to the DataTable.

Eventhough, I do exactly same in above code, time consumed to do so is relatively low & uses less resources when compared to use of DataSet.

Complete XML generated by List Of Quote approach, can be download here Generics.List.Of.xml

Here are short statistics figures:

Seconds took to Load – File Size of Generated XML using DataSet
3.40 – 352,384 bytes

Seconds took to Load – File Size of Generated XML using List Of(Quote)
3.24- 250,754 bytes

Clearly, the XML file generated from WebMethod returning List Of(Quote) is not only 101,630 bytes (29%) smaller but also it is renedered on screen 0.16 secs faster than the XML file generated from WebMethod returning DataSet. Instead of concluding stuff based on single test results, I thought of testing these facts in detail. I repeated a test (time required to generated DataSet & time required to generate List) for 20 times and tabulated the facts as below.

For Resultset Size: 723

Average Ticks Difference 45,062.55 = 0.0045 Seconds

The above figures show only ticks took to generate the DataSet / List Of(Quote) collection and excludes the resultant XML file size and time to look to load the XML. Though I didn’t notice much difference, from my initial tests there is significant difference in size of generated XML. Inorder to analyze the performance of both DataSet & List Of(Quote) I did a Cross JOIN on my previous result giving me total resultant set as 723 X 723 = 522,729 rows.

For Resultset Size: 522,729

Average Ticks Difference 78,619,128.9 = 7.86 Seconds

Again, the above figures show only ticks took to generate the DataSet / List Of(Quote) collection and excludes the resultant XML file size and time to look to load the XML. In my latter experiment , resultset is 723 time of previous experiments resultset, you could clearly see a significant amount of time difference, 7.86 Seconds, which is 1426 (2 X 723) times of previous experiments time difference. These two results, show that – as the resultant set size increases, the average gain in time, by switching to List Of(T) from DataSet, increases in non-linearly(Parabolic, Y = 2X, in our example).

Finally, I wanted to perform test on how much time it takes for browser to load the XML & Size of XML returned from Webservice. Since, data is cached in memory, these figures doesn’t include time to fetch data from database. The following facts show that there is more or less same amount of % gain in Size of generated XML nomatter how many rows were returned. Except for two rows, stats show that % gain time to render XML on the screen is also remained same. In a nutshell, use collection list of Class objects rather than DataTable to cut short data transfer by 29% and for faster release of Data Resources while returning large data.

Note: All the above statistics are gathered on my personal laptop with following config – Dell 600m, 1GB RAM, Centrino Processor, SQL Server 2005, VS.NET 2005, Mozilla with FasterFox extension.

– Shyam K. Arjarapu