Monday, January 21, 2008

DataAdapters and DataSets vs. LinQ to SQL

I recently read an article on Visual Studio Magazine from Roger Jennings where he discussed performance using LinQ to SQL.
One of the key "benefits" of using LinQ to SQL with the Entity Framework is the lazy loading. Lazy loading implies that when you are querying Orders you'll get just the orders and in case you want to go thru the Order lines only then LinQ will execute the query against your DB to get the lines.
There are pros and cons against the "old" schema where you used a DataAdapter with the query to fill a DataSet.

One of the pros I see is that the query to get the orders will probably execute faster since it's only going to one table (or two if you want the customer name), thus you'll get to see the orders header faster. But, what happens when you want to see the order lines? well, you have again, to wait for the db to execute "your" query and send in the results. And what if you want to see the Customer address? again another query to the db which Roger refers as to round trips to the database.

In his article Roger discusses a few tips to tweak those round trips and get the best performance out of LinQ SQL, but there's a table in his article which I'm posting here, where you can see not only the time difference between every method of retrieving data, but also the amount of queries LinQ SQL fires to the database in order to get the data. Also, for comparison purposes, he shows typed DataSets which are the fastest and one with the least amount of queries of all.

(The red rows were painted by Roger himself to show the best approach)

So... what to choose and when? The answer is simple... Get DeKlarit ;)
DeKlarit uses DataAdapters and DataSets so you don't have to write the SQL queries. Also, DeKlarit knows what you want to get in advance, how? because you told him so while defining the Date Providers.
So, as I see it, you have three ways of getting your data:
1) You use LinQ SQL with entity framework for compile time syntax checking and stuff and loose performance
2) Write your own queries to fill datastes taking care of the connection and everything else gaining performance but doing a lot of the hard work yourself, or
3) Get DeKlarit and just focus in the UI which as far as the client goes, that's what makes your app appealing

I thought it was going to be a tech post but it ended up in a marketing kind of way :)

Read Roger's full article here

1 comment:

RRave said...

Dear Sir,

I hope you are doing well. I got this email address from one of your contribution web site. I have launched a web site and it is basically aimed C#,JAVA,VB.NET,ASP.NET,AJAX,Sql Server,Oracle,WPF,WCF and etc resources, programming help, articles, code snippet, video demonstrations and problems solving support. I would like to invite you as an author and a supporter.
Looking forward to hearing from you and hope you will join with us soon.