Typed Datasets vs Normal Datasets

I know there are gazillions of posts explaining about the difference between these 2 data interaction approaches in .NET, but I still would like to contribute a bit. I spent couple of days analyzing the performance of typed vs normal datasets using ASP.NET and SQL Server 2005. Since typed datasets gives so much ease of development and of course, compile time type safety, it is pretty obvious that there is a performance price. I wanted to observe the one to one comparison of these 2 candidates.

Following is the environment in which I did this analysis –:

Database – AdventureWorks in SQL Server 2005

Dev environment – Visual Studio 2008, ASP.NET Web Application

Profiler – Redgate ANTS profiler.

I created a data access layer for each type of dataset, so for typed dataset it consisted of the xml schema and other designer files generated and for the normal dataset, I created classes which essentially did the same thing as some of the classes generated by typed datasets, but with less code. (By less I means the designer generated code has lots of things which are unnecessary for simple operations but nevertheless they are good programming practices, I omitted them). I created a simple, complex and very complex queries in the database so that I see their performance at different levels. Also I analyzed the results for small number of rows, medium and large number of rows in the each of the query result. To explain the kind of queries I wrote, simple query was just a normal select statement, complex query had couple of joins and very complex query had couple of inserts and then a select with couple of joins.

Following are the results of the experiment –:

Small data set (about 100 rows in final result set)

Type of query

Normal Dataset

(sec.)

Typed Dataset

(sec.)

Simple Query/Procedure

0.012/0.0005

0.0144/0.0008

Complex Query/procedure

0.0197/0.004

1.923/0.007

Very Complex Procedure

0.041

0.072

Medium data set (about 7000 rows in final result set)

Type of query

Normal Dataset

(sec.)

Typed Dataset

(sec.)

Simple Query/Procedure

0.194/0.164

4.19/0.44

Complex Query/procedure

0.48/0.54

4.96/0.76

Very Complex Procedure

0.64

0.79

Large Data set (> 500000 rows in final result set)

Type of query

Normal Dataset

(sec.)

Typed Dataset

(sec.)

Simple Query/procedure

1.675/1.44

4.62/4.27

Complex Query/procedure

8.46/7.61

18.19/17.98

Very Complex Query/procedure

7.921

19.935

From the data collected, it is not surprising that typed datasets take a performance hit compared to normal datasets. As you can observe, for simple queries, typed datasets create quite a bit of overhead. The major reason is, in this case, the table which was used for this simple query was pretty big (about 30 columns) hence the typed data table created was a large object.

Although typed datasets cannot outperform normal datasets in terms of execution time, they were introduced for a reason right! That reason is to reduce the development time and generate good quality code. Following table shows the development time needed to build the same functionality with these 2 approaches.

Development Time (same for all size of datasets)

These values show the coding time required to create the actual functions which retrieve values from the database and return to the calling client.

Type of query

Normal Dataset

Typed Dataset

Simple Query/Procedure

2.8/2.5 min

2/1.5 min

Complex Query/procedure (not parameters for the procedure)

3.5/2.5 min

2.4/1.5 min

Very Complex Procedure (About 30 parameters)

14.5 min

4 min

 

To sum up this discussion, following are the pros and cons of Typed Datasets. Since Typed Datasets are just wrapper classes over normal datasets, their pros and cons are pretty much the same.

Pros:

· Visual Studio intellisense available to access the individual table fields. Compared to normal dataset code, typed dataset code is shorter making it more readable, maintainable and more intuitive.

· Compile time type checking is achieved for individual fields for tables. Since the columns of a typed dataset are strongly typed, there is no explicit casting required while retrieving the values from the dataset unlike in normal dataset.

· The dataset provides a replica of the underlying database including the relationships between the tables and their indices. This gives the developer the ability to check constraints within the code itself.

Cons:

· In order to achieve the type safety, typed datasets need to have the underlying information of the table schema. Creating a typed dataset or a typed DataTable is an expensive operation. For simple operations, you might end up having lots of unnecessary code in the designer file of the typed dataset. For example, to perform a simple read of say 3 columns from a table with 30 columns, the designer would still create a DataTable consisting of 30 properties and 30 type safe columns. One can certainly delete unwanted columns but that becomes an extra task as schema changes frequently.

Although one can store this datatable in a Session or some other place where he could just do DataTable.Clear() and return it after it has been created once. I implemented this method and saw about 40 – 50% improvement. Please visit this link to read more about it.

· Every time the database schema changes, one has to refresh the .xsd file to generate the class again.

· Using the typed adapter could be an overhead for simple operations. The typed adapter consists of table mappings, transaction support which might not be what you want for a simple operation like select.

Happy Programming!

Abhang Rane


7 comments :

Reader's Comments

  1. Good job
    Thanks

    ReplyDelete
  2. There are many things in .net for comparison. Try explaining them, carry on.

    ReplyDelete
  3. Sure. Just that the limited time stopped me to explore more factors. Do check this link for caching datasets to improve performance by some good margin. http://www.codeproject.com/KB/grid/typeddsproxy.aspx

    ReplyDelete
  4. I was looking for a article that clearly mentions the advantages and drawbacks of using these two types of datasets.The moment I found this article, I thought this is the one, I am looking for.A great article indeed explaining all the aspects.I will forward this one to all my friends.I will rate it number one among all other articles, I found on this particular category.

    ReplyDelete
  5. Im glad that you found this so useful :).

    ReplyDelete
  6. It is nice article. Do you have any idea if there is any difference using type datasets in framework 1.1 vs framework 3.5?

    ReplyDelete