Sponsored By Aspose - File Format APIs for .NET

Aspose are the market leader of .NET APIs for file business formats – natively work with DOCX, XLSX, PPT, PDF, MSG, MPP, images formats and many more!

Stored Procedures: Going…going…gone?

For years now, I’ve been a fan and advocate of stored
procedures. I’ve used them in almost every system I’ve worked on. Recently I
even used them almost exclusively for a large system with a MySQL Cluster
back-end (which was an interest project in many respects). At some point, like
many others, I woke up and found out that it wasn’t cool to use stored
procedures anymore. So I became a closet-sproc-lover.

The last couple weeks, I’ve started doing some preliminary
work on two small projects. In both cases, I’ve found myself completely abandoning
stored procedures.

 
One of the systems is a reporting application, and like it
or not, I’m stuck doing very ugly dynamic SQL (like dynamic table/column names
and such). For such a case, C# and VB.NET are infinitely better. I mean string
formatting, concatenation and manipulation is just easier and far cleaner
(hence maintainable). Security is equally problematic in both cases. As for
performance, well, it’s a highly indexed and de-normalized system….I don’t have
enough data to test yet, but I’m positive I wouldn’t be able to measure any difference.

 
The other system though is a more typical web-app, and I
surprised myself when I started to write SELECT statements in-code. I was
annoyed because I had multiple stored procedures to pull one or more rows from
a Users table….say:

 
User_GetFromCredentials(@userName, @password)
User_GetFromId(@userId)
User_GetAllForCompany(@companyId)

 
And whenever I added data to my User class/Table definition
I’d need to hunt down all the places where I had the same select (but different
wheres) and update them. This was a problem I had faced before, and more or
less gotten around by doing some whacky thing with an XML data type, but it was
really frustrating. So for fun, I ended up trying something like:

 
private string _userSelect = “SELECT
UserId, UserName, FirstName, LastName, Email FROM Users”:

 
And I haven’t really looked back.

 
I found an old post by Jeff Atwood that actually sold me on
this entire inline-SQL (here for anyone interested). He’s right on all three points,
but most importantly that using a stored procedure only for performance IS a
micro-optimization. I’ve never EVER registered a significant performance gain
by moving to a stored procedure (you get significant performance gains by
properly indexing and modeling and not using datasets.)

Who knows how it’ll go. We’ll see. And who knows, maybe my
next step is O/R Mapper land.

This entry was posted in Uncategorized. Bookmark the permalink. Follow any comments here with the RSS feed for this post.

18 Responses to Stored Procedures: Going…going…gone?

  1. Keslavi says:

    whoops, in above post i meant:

    where Password=coalesce(@password,Password)

  2. Keslavi says:

    regarding the where statement:
    WHERE (@password is null or Password = @password)

    wouldn’t you retain the indexing via:
    Where Password=(@password,Password)

    I tend to like the Oracle Package concept, and rather than do several SPs I’ll make the parameters optional and perform the CRUD operations via if statements, and even return a summary or detail cursor depending on the existence of a specific ID.

    This structure also works well for me in parameterized queries, which can be saved in a resource file or text files with an extension that IIS won’t retrieve. Then you get the best of both worlds.

  3. A. Simon Mielniczuk says:

    Came across this most interesting posting and discussion. Seems that the ‘best’ solution depends on the nature of the specific query, the availability or sanction of certain development tools, and corporate policies around access to and accountability for assets represented in the database.

    Thanks to Keith for starting the discussion and to all the thoughtful commentators.

    ASM – ITScooperative

  4. Yeah, I’ve never met a DBA that would allow an application to access a DB without the majority of the SQL being in stored procedures or views. They simply have no control over the database. I’ve actually had several client’s whose DBA mandated no inline SQL. It wasn’t about optimization or performance, it’s about responsibilities; the DBA can’t take responsibility for the database if it’s code is outside of her control.

  5. karl says:

    I ran the query last night before posting to make sure :) I think index-selection is a lot harder than we think.

  6. Jostein says:

    If you say so, I’m going to take your word for it.

    I just find it odd that SQL-Server wouldn’t apply the same auto-parameterization (using different execution plans) to SPs as it would do to parameterized queries.

    Sounds like something MS might consider fixing.

  7. karl says:

    Jostein:
    I know the point of my own post was not to micro-optimize, but I have extensive experience with your approach, and it’s an absolute performance killer. Basically, SQL Server can’t use indexes for that kind of query. It’s the perfect example of where sprocs actually performance [much] worse than dynamic/inline sql given enough data.

    With an index on UserName, the first query will do an index clustered seek, while the second one will do a scan:

    SELECT * FROM Fuel_Users
    WHERE UserName = @userName

    SELECT * FROM Fuel_Users
    WHERE (@password is null or Password = @password)
    AND (@username is null or UserName = @userName)

    With 1000 rows there’s no real difference, but with 100 000 rows, the 1st one will take millseconds, while the 2nd one will take seconds.

  8. Jostein says:

    As for having 10 SPs for the same purpose.

    If all they do is filter differently, I usually just leave the input-parameters optional with default to NULL and make a WHERE-chain like this

    WHERE (@param IS NULL OR column = @param) AND (@param2 …

    Works like charms. The DB-engine will most certainly optimize whatever loss that should cause in performance. Not that I’ve done measurements, but still.

    Sometimes inline-SQL feels like the only thing right, but I’m not ditching my SPs yet.

  9. karl says:

    Baffled: To my knowledge, it started around 2003 when a couple influencers started to sing to O/R mapper gospel. Since then, there’s been a definite (although slow) shift away from stored procedures (I know the discussion pre-dates 2003, but that’s seems to be about when the topic got traction in the .NET community).

    Eric: Maybe once I start working on the more complicated reports and/or I get more data it’ll be an issue. But using sprocs for performance gains now really IS a micro-optimization. These are internal projects and we have no SQL Server DBAs…even if there were, it’d be hard to justify a less maintainable approach so that someone has easy access to the source (I’m not sure I believe anything in that statement 😉 )

    Erik & Henning (and all you NHibernate fans):
    I agree I’m late…but I wouldn’t call it very late. I’m sure the adoption of o/r mappers is far behind the adoption of ..say…datasets – but that’s no excuse for my own tardiness. I confess that I’ve stayed away from NHibernate for a couple of bad reasons. I’ve felt that my projects have either been too small or too big for NHibernate (for large projects, I just didn’t want to add another significant unknown). Also, the last time I looked at NHibernate was a very long time ago, and it wasn’t nearly as mature as it seems to be now. There IS a learning curve, and it’s a matter of finding the right projects to apply it to.

    sprocer: great question about security. First, I plan on using parameterized sql whenever possible, which is as bullet-proof as stored procedures. For the crazy dynamic SQL of the reporting app, the account will only have SELECT access, I’ll still used parameterized SQL whenever possible, and user input will be rather limited to dropdowns and such. It’s not like I plan on having a textbox where users can enter their SQL and blindly execute it. There’s a misconception that just because you use stored procedures, you’re immune from injection or other attacks. There’s also a misconception that if you don’t use stored procedures, you’re wide open to attacks. The truth is that in either case how and what you code will define if you’re open to attacks.

    Haacked: I agree. Baby steps before I turn over to the dark side (NHibernate).

    Loknar: As always, you need to use the right tool for the right job. DBAs and developers need to learn about O/R mappers so they can use them where appropriate. I don’t want to sound like a dick, but from what I’ve seen, the anti-SP crowd knows a lot about SPs and the alternatives, while the pro-SP crowd only knows about SPs and refuse to even look at the alternatives. I’d love to see some hard-core DBAs really understand and know NHibernate and then give a detailed opinion.

    Eber’s right of course:
    “use the best tool for the job, don’t let knowledge get in the way of creativity”

  10. Loknar says:

    Sure, if your sp’s are bad, inline sql is better. Why does your DB have so many sp’s only for user information? Should be 2 sps: 1 to check password and another to get all possible information. No point saving bandwidth when we’re talking about 20 bytes of data.

    If you think .net is a good place to share sql statements with your development team, I hope your team is small (eg: 1 person).

    I’m the DBA in a .net reporting system and we need to create new reports every month. Imagine that with sql all over the DLLs. I use views for UI and SP for reports.

    Yes, im tired of this anti-sp talk with some old freaks in top management, thypically never touched a PC ever since we stopped using punch cards.

  11. Marco Turrini says:

    SP generally gives better performance over ad-hoc query (“SELECT * FROM Table WHERE Key = x”), because Sql Server can reuse execution plans; but you can get (almost) the same by using paramterized queries (“SELECT * FROM Table WHERE Key = @key”). Though it may seem unnatural, SP could give better abstraction from the db server being used: if you embed your SELECT statements in your code (C#, VB.NET…) you can find yourself rewriting them because of the (little) differencies in the Sql dialects and you may need to keep different versions of the same source code for different db servers; by using SP to access data, your source code can be left untouched.
    Oddly, in the applications I’m writing these days (well… months) I’m using SP for the reporting and parameterized queries for CRUD operations; but my reports take several thousands records from half a dozen tables. In the SP I write for my reports (one SP for each report), I explicitly SELECT only the columns I will show in the report; this means that if I add a new column in a table definition, I don’t mind updating all the SP (unless I want this new column to appear in a particulr report).

  12. Henning Kilset says:

    You seriously need to take a look at NHibernate (recommended), Castle/Activerecord, LLBLGEN or the like. I promise you, you will NEVER look back. SQL and SP is sooo oldskool :) (btw, Nhibernate, at least, supports stored procedures for your objects, too).

  13. Haacked says:

    I’d warn against “inline” SQL and instead focus on dynamic SQL using a dynamic SQL engine like Subsonic and NHibernate. If you do stick with Inline SQL, make sure to use parameterized prepared queries and don’t concatenate or string.format your queries together.

    Paramaterized prepared queries offer all the safety and performance of stored procs with more flexibility. This whole notion that Stored Procs outperform modern dynamic SQL execution is a bit outdated. Preparade queries are compiled by SQL server as well and perform quite well. Parameterized queries avoid the SQL Injection attacks just like stored procs.

  14. I still think stored procedures have a big place in development (specially in reporting, but not always though), there is just to much anti-stored procedures crap all over the place

    but just follow whatever makes you more productive and gives you better code

    on the end, use the best tool for the job, don’t let knowledge get in the way of creativity

    …and when using stored procedures, don’t forget that views exist…

  15. Erick Dovale says:

    If you haven’t try any ORM yet you are running late.. very late..

  16. sprocer says:

    What about injection attacks?

  17. Eric Wise says:

    I’m kind of shocked that in a reporting application the optimization argument doesn’t hold water with you. :)

    So your client has no DBA’s? If they do, how do they optimize sql they can’t see?

  18. Baffled says:

    When did it become un-cool to write stored procedures?!?