Should I use DataTable or NOT?

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


            db = DatabaseFactory.CreateDatabase("MyLocalDatabase")

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

        Catch ex As Exception
        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
    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


            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"))

            End While

        Catch ex As Exception
            If Not dr Is Nothing Then
            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

Generic.List Of<T> Data Table % Change
300417 300417 0.00
300417 200278 -33.33
200278 300417 50.00
300417 200278 -33.33
200278 400556 100.00
200278 400556 100.00
300417 400556 33.33
300417 300417 0.00
300417 300417 0.00
300417 400556 33.33
200278 200278 0.00
300417 300417 0.00
200278 300417 50.00
200278 300417 50.00
200278 300417 50.00
200278 300417 50.00
300417 200278 -33.33
300417 200278 -33.33
200278 300417 50.00
200278 300417 50.00

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

Generic.List Of<T> Data Table % Change
167432408 218503298 30.50
149006832 209190371 40.39
145401828 218503298 50.28
143599326 218703576 52.30
137991542 219805105 59.29
137190430 221006773 61.09
138692515 217301630 56.68
138091681 225212611 63.09
138592376 234525538 69.22
134186260 226013723 68.43
132584036 219404549 65.48
136890013 216400379 58.08
133985982 213195931 59.12
133885843 216800935 61.93
134286399 216099962 60.92
134386538 212895514 58.42
136489457 214397599 57.08
136990152 210191761 53.44
134787094 212695236 57.80
135488067 211493568 56.10

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.

Return Type List Of<T> Data Table % Gain in
Row Count XML Size Time to Load XML Size Time to Load XML Size  Time to Load
1000 337,034 4.306 463,740 4.426 27 2.71
2000 677,696 9.183 958,854 9.48 29 3.13
3000 1,025,306 14.902 1,447,465 15.710 29 5.14
4000 1,361,161 20.590 1,924,319 21.711 29 5.16
5000 1,701,893 29.810 2,406,051 31.416 29 5.11
6000 2,041,724 39.487 2,886,882 44.814 29 11.88

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

Leave a Reply

Your email address will not be published. Required fields are marked *