Posts
967
Comments
825
Trackbacks
1
This is a semi-complex query?

Rob posted something about how to implement complex queries using Massive:

“Here’s a semi-complex query that is all too common in the business case:

var results - DynamicModel.Open(connectionString).Query(@"
SELECT Orders.OrderNumber, Categories.Name FROM Products
INNER JOIN Categories ON Categories.CategoryID = Products.CategoryID
INNER JOIN OrderItems ON OrderItems.ProductID = Products.ID
INNER JOIN Orders ON Orders.OrderID = OrderItems.OrderID
WHERE Orders.OrderDate > @0
", DateTime.Now.AddYears(-1));

This is freehanded - but if you tried to write this in LINQ and weren’t very good at it you’d end up with a mess, and mangled SQL. You could, literally, email your DBA, ask for a Query, and pop it into your code and be done here. “

Uh, on a scale of 1 to 10, an inner join is at best a 1.5.   And if you couldn’t write it using LINQ, you probably shouldn’t be using LINQ.

Since I’m being all catty and obnoxious about it:

<rant>

Have you ever worked with the sort of developer who thinks that, because he figures out how to write a SQL query that properly utilizes Group By and Order By, he’s now mastered all that there is to know about T-SQL?

This is the sort of thing that has traditionally led to the ‘divide’ between developers and DBAs when it comes to data access.  ‘Traditional’ DBAs (in some sense as it relates to SQL Server at least, anyway) have reacted to this by insisting on using stored procedures or other ‘harmful’ measures that just gets in the way of successful iterative friction-less development.

As a certified DBA and certified developer (as well as generally being certifiable), this example really isn’t helpful at all.

</rant>

That crap out of the way, this actually isn’t a reflection on Massive one way or another.  I’ve looked at it, in the same sense that I’ve “looked at” PetaPoco and Dapper, briefly.  I see a lot of strings, but given that I am known to actually champion inline SQL (properly parameterized) over stored procedure calls in managed code, I can’t really complain about that.  The ability to put in a truly complex query and get out a dynamic resultset seems pretty decent from what I can tell.

<digression>

To get around a common (and reasonably valid) complaint about inline SQL is that it can produce runtime errors due to fat-fingering mistakes, etc.  The easiest way around this is to make your queries available to some integration tests that will parse them against the database of your choice and make it part of your build process.

</digression>

Since the whole “are stored procedures evil?” debate is old and boring, I will shortcut you to the correct answer.  Are stored procedures evil?  No, not when used properly.  When are they used properly?  Hardly ever.  Some typical cases (though in every case, “Thought Is Required ™”) are:

  • if you need to do set-based operations over large amounts of data (since that is what things like T-SQL shine at)
  • if you need to return multiple result sets (though you can do this in other ways, a stored procedure is often easier)
  • if you need to pass a complex object of some sort into a query (such as a table value into SQL or an array into Oracle)
  • or anytime you need to process through a lot of intermediate steps before getting to your final result (which often happens in ETL situations)

Otherwise, you should prefer to use an ORM, or micro-ORM as I gather things like Massive are now called.

YMMV.

posted on Tuesday, August 09, 2011 5:28 PM Print
Comments
No comments posted yet.

Post Comment

Title *
Name *
Email
Url
Comment *  
Please add 3 and 4 and type the answer here: