Andres Aguiar's Weblog

Just My Code

June 2006 - Posts

Stored Procs vs Dynamic SQL

In DeKlarit's booth at TechEd I had to talk more than I wanted about the famous Stored Procs vs Dynamic SQL debate.

People still thinks Stored Procedures are faster, even if there is much evidence that shows otherwise. Fortunately, when they go and ask the LinQ for SQL/Entities team they get the same answer than they get from me. They are not.

To add more evidence, a couple of weeks ago I was in Redmond in a meeting with the ADO.NET team, and a Tech Lead from that team wrote the code below and said 'this is the fastest way to execute this SQL sentence with the .NET framework today'.

SqlCommand cmd = sqlConnection.CreateCommand();
cmd.CommandText = @"
       SELECT Sales PersonId, FirstName, HireDate
       FROM SalesPerson as sp
       INNER JOIN Employee AS e ON sp.SalesPersonID = e.EmployeeID
       INNER JOIN Contact AS c ON e.EmployeeID = c.ContactID
       WHERE e.HireDate < @date";
cmd.Parameters.AddWithValue("@date", date");

DbDataReader r = cmd.ExecuteReader();

(if it looks similar to the query shown here is just a coincidence ;) )

Why is this _faster_ than a stored procedure? Because the SQL Server engine could select a better execution plan depending on the value of the @date parameter.

Even in that room there was people that were surprised.

 


 

   

Posted: Jun 22 2006, 02:52 PM by aaguiar | with 12 comment(s)
Filed under: ,
ADO.NET Entity Framework - It's not just O/R mapping

Pablo Castro re-announced the Entity Framework. The white papers are up again.

A lot of people have been referring to the ADO.NET EF as an O/R mapper. And it is. But it's much more than that.

The EF lets you design your conceptual model based on updateable client-side views. You can use those views without Objects (I mean objects in the 'domain model' sense, of course you will use some CLR object to access it).

Additionally, the same conceptual model will eventually be used in other MS products, like Reporting Services, Replication, Analysis Services, and probably third party products. This means that you will want to create an Entity Framework model for your conceptual model because you will get a lot of value from it, not just a O/R mapping layer. You can even think of creating it and not using O/R mapping at all.

I think this is very good news for people building applications for the Microsoft platform.

 

Posted: Jun 20 2006, 04:34 PM by aaguiar | with 2 comment(s)
Filed under: , ,
VS Tools for DB Professional - Does it make sense to generate test data?

The new VS for DB Professionals supports generating test data for your data model.

For each column, you can specify the Generator you want to use, and you have a bunch of predefined ones (numbers in a range, strings that comply with a regular expression, values from a list, etc, etc, etc), with a lot of sophisticated features like using different distribution algorithms for the data, or read an existing table to find patterns there to then generate the test data (like 'the CustomerName has between 10 and 15 characters with an average of 12').

Now, as they are inserting data in the database tables directly, they are bypassing your business logic layer. Unless you have all the validations and triggers in the database, the data you have in your tables won't be consistent, and in that case is difficult to use it as a set of data that I can use for my tests.

Additionally, there are scenarios where creating test data is very difficult. If you have a table with the product Inventory, and each time you create an Invoice you check if there is enough inventory, and after that you decrease it, you need to make sure that in your test data, the Invoices you create don't make the Inventory go negative, because in that case your constraints will fail. In real world applications this kind of scenarios happens all the time. Unless we have a model where we can declare that, which is not trivial, I don't see a way to solve this issue.

So, I think the data generator feature it's a little naive, and I'm not sure if it can applied it in the real world. I hope I'm wrong because it looks cool ;).

 

DAT101 - Microsoft's Data Platform Vision

It was a good session. I totally buy MS's Data Vision.

The most interesting stuff for me is the Entity Framework.

At last there will be a conceptual data model that all MS products will share (Reporting, Replication, Analysis Services, ADO.NET). This is a big and important improvement in the way we used data.

The 'Data Dude' tool looks cool. If they manage to integrate it with the Entity Framework, it would rock.

 

DLinQ = LinQ for SQL

OK, it actually happened. We'll have two mapping technologies in .NET v.next.

LinQ for SQL, previously known as DLinQ is the 'simple' mapping technology.

LinQ for Entities, will be on top of the new ADO.NET Entity Framework, and will be the 'complex' (we could say 'real') mapping technology.

Now, does this make sense? How will someone decide to use one or the other?

I think Microsoft did this for internal politically correctness (they did not want to not to ship any of the frameworks) but I can't see why this is good for the .NET Framework as a whole.

 

Teched - I hate when the are no bags for exhibitors

Is not that I need the bag (and this year's one does not look very good), but we always raffle all the stuff we bring back from TechEd, and the bag is a pretty requested item.

Exhibitors pay a lot of money to be here, and I really don't think it's that expensive to have bags for the exhibitors staff. In most of the TechEds we got bags.

I'll make sure I eat enough Haagen Dazs to compensate for the money they saved with my bag ;).

 

Posted: Jun 12 2006, 01:54 AM by aaguiar | with no comments
Filed under: ,
Chloe saved the Keynote

I went to TechEd Keynote today and I almost got asleep. Then Chloe appeared and it was the only excuse I had to not to leave before it finished.

Luckily, at the end of the keynote the showed the most interesting stuff, like a 30 seconds demo of the new Data tools in VSTS, and the Office demo.

I have the Office Beta installed and I think the killer application is Powerpoint 2007. I've been showing the 'Convert to SmartArt'  feature to everyone. Most of the keynote demo was around Powerpoint, so I guess the Office team has the same feelings about it. The integration with Sharepoint looks cool too.

 

SqueezeBox and Pandora

Some time ago I blogged about Pandora. Pandora it's free now, and they've partnered with SlimDevices and you can hear your radio stations using the Squeezebox!

This is super cool.

 

Posted: Jun 08 2006, 01:03 AM by aaguiar | with no comments
Filed under:
Boston, here we go

DeKlarit will have a booth in TechEd (5th year in a row), and I'll be there most of the time, so please come and say hi if you are around. We'll be giving away a Squeezebox!

If I'm not there is because I found a TV and Ronaldinho is playing ;)

I will be probably attending to the 'ADO.NET Meetup' , so you can find me there too.

See you!

 

Posted: Jun 08 2006, 12:52 AM by aaguiar | with no comments
Filed under:
ADO.NET v.next in TechEd, and DLinq?

If you are interested in ADO.NET Entity Framework, make sure you attend to the following sessions:

DAT101  Microsoft's Data Platform Vision, Dave Campbell   (I'm not actually not sure if he'll talk about it in this one, but I guess so).

DAT304  Next Generation Data Access in .NET Applications with ADO.NET vNext, Pablo Castro

There's also a Hands On Lab:
 
DAT013  ADO.NET Orcas: LINQ and the Entity Framework
 
By the way, there are no DLinQ breakout sessions in TechEd. The only 'session' on DLinQ is the DLinQ BOF.
 
 
Posted: Jun 08 2006, 12:32 AM by aaguiar | with no comments
Filed under: ,
More Posts