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
|Generic.List Of<T>||Data Table||% Change|
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|
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|
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