Avoiding Stored Procedures

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQLServerCentral.com » Editorials » Avoiding Stored Procedures

71 posts, Page 1 of 8

1 2 3 4 5 »»»

Avoiding Stored Procedures

Rate Topic

Display Mode

Topic Options

Author

Message

Steve Jones - SSC Editor

Posted Saturday, August 11, 2012 10:09 PM

SSC-Dedicated

Group: Administrators
Last Login: Today @ 11:20 AM
Points: 31,437, Visits: 13,752

Comments posted to this topic are about the item Avoiding Stored Procedures

Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help

Post #1343851

Jeff Moden

Posted Saturday, August 11, 2012 11:22 PM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 7:36 PM
Points: 32,931, Visits: 26,820

I'm a little bit sour on ORMs right now. Admittedly, I'm sour on them all because of what happened with just one. As with so many people, I agree that ORM's are a God-send to replace writing CRUD. Or at least that's the way I used to feel.

We're U.S. centric and, as a result, made the choice a long time ago to NOT use NVARCHAR unless we absolutely needed to do so and then only for the columns that absolutely need it. Imagine my surprise when going through the TextData column of an SQL Profiler run to see that all of the character based criteria (WHERE clauses) coming from the apps where coming across as NVARCHAR. It turned out that the ORM being used does that as a default and it requires that the developers first be aware of that "little" problem and then write code in their app to overcome it. So much for simplicity. And so much for INDEX SEEKs!!! HELLLLLOOOOO table scans!

I'm still looking for a way out of this mess but, so far, it's looking like we're going to have to modify all of the application code to make it work right. And, yes, there have been major complaints about how "slow" the database is because of it all.

--Jeff Moden

"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Post #1343857

call.copse

Posted Sunday, August 12, 2012 10:09 AM

Ten Centuries

Group: General Forum Members
Last Login: Yesterday @ 2:48 AM
Points: 1,088, Visits: 693

I think Kevin Lawry gets it about right with his article. I think he only mentions re-plumbing to a new db as a side benefit, which is about right in my book as it has never actually occurred for me. (Well, I've re-written some 3rd party code that accessed MS Access databases but that is a different kind of thing).

I think there is a reasonable 60% - 30% - 10% split between 'can be written easily with no loss using EF or similar', 'can be written acceptably, might write a procedure' and 'needs T-SQL' in the data driven applications we create. The big advantage is the keeping more logic in the code, which tends to be easier to maintain, and less prone to error. I would say the speed of development is 60% quicker than using a pure sp approach - due to avoiding switching environments and maintaining more concise and comprehensible code.

I would not say you can keep all business logic out of the database. Constraints and relationships (even triggers occasionally) should always be used IMO and do constitute such. Personally I cannot consider starting anywhere except in the database, for instance using CodeFirst is not something I can really endorse (of course if it works for you!). You do of course need to think pretty carefully about how you use them too - in my experience EF / L2S lend themselves brilliantly to a good caching strategy for frequently used small tables, multiple updates are an issue, surrogate keys are a good idea (so they may not be appropriate for database purists).

All in all, not withstanding the potential pitfalls, ORMs have worked wonders for us.

Post #1343906

FIshNChipPapers

Posted Sunday, August 12, 2012 10:58 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, May 08, 2013 7:10 AM
Points: 119, Visits: 124

It completely shut off when I saw "your stored procedures would need to be re-written in order to migrate to MySQL, Oracle or another database" since I think this rarely happens.

I would agree with you in the case of enterprise database development where organisational standards tend to dictate common platforms. However, for those developing database-agnostic COTS applications, where they have to support multiple database platforms (to be consistent with organisational standards), there are obvious benefits to database-agnostic ORM frameworks rather than database-specific stored procedures, functions etc

Post #1343910

davoscollective

Posted Sunday, August 12, 2012 9:13 PM

SSC-Addicted

Group: General Forum Members
Last Login: 2 days ago @ 8:01 PM
Points: 408, Visits: 688

I write a lot of SSRS reports and I prefer to have everything run as stored procs so I can see and manage all of them in the database. If I need to change a table I can query across stored procs to see where tables are referenced and know exactly what will be impacted.

I also keep complete comments in the stored procs so I know which reports use them. Admittedly this is extra maintenance that not everyone would adhere to.

The other reason I use stored procedures is because you can't combine multiple datasets into the same tablix ,i.e. data from multiple sources, so doing that by using linked servers in the database and presenting SSRS with one dataset is an efficient solution.

If you had a single code base and the ORM queries were all in one place then that would be easy to manage, but most operational databases I've seen have multiple front ends & ETL or reporting systems attached. If your queries are in more than one place, or in places that are hard to quickly assess the impact of underlying table & view changes then that's an argument for using stored procs. I realise ORM's are supposed to make that problem go away, but if you can't use your ORM of choice for all systems that interact with the database then that argument doesn't apply.

The phrase 'Database Agnostic' makes me think jack of all trades, master of none.

Post #1343959

Alex Fekken

Posted Sunday, August 12, 2012 9:45 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, May 20, 2013 7:50 AM
Points: 195, Visits: 444

FIshNChipPapers (8/12/2012)

It completely shut off when I saw "your stored procedures would need to be re-written in order to migrate to MySQL, Oracle or another database" since I think this rarely happens.

I also think the (counter)argument is largely circular: it rarely happens because the strongly encouraged and therefore widespread use of stored procedures (and other vendor specific features) makes it too expensive.

Post #1343963

FIshNChipPapers

Posted Sunday, August 12, 2012 9:57 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, May 08, 2013 7:10 AM
Points: 119, Visits: 124

I agree with the vast majority of both the original article and your comments. I was endeavouring to identify a scenario where the ability to develop code that can be ported between database back ends could be a requirement.

Post #1343968

Alex Fekken

Posted Sunday, August 12, 2012 10:27 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, May 20, 2013 7:50 AM
Points: 195, Visits: 444

FIshNChipPapers (8/12/2012)

Maybe I caused some confusion: I meant that Steve's counter-argument is circular.

I think there are lots of reasons why portability would be desirable or even a requirement. But as soon as stored procedures become a "best practice", portability goes out the window as a result (and not because we didn't want it in the first place).

Post #1343970

davoscollective

Posted Sunday, August 12, 2012 10:59 PM

SSC-Addicted

Group: General Forum Members
Last Login: 2 days ago @ 8:01 PM
Points: 408, Visits: 688

Alex Fekken (8/12/2012)

FIshNChipPapers (8/12/2012)

The issue is less about stored procedures and more about optimised code for that environment, whether it be T-SQL or PL/SQL or something else. I doubt any ORM could generate more efficient code than a seasoned DB dev. There's a trade-off between portability and performance. Note Jeff's comment on the lack of index usage.

Post #1343973

FIshNChipPapers

Posted Sunday, August 12, 2012 11:41 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, May 08, 2013 7:10 AM
Points: 119, Visits: 124

I doubt any ORM could generate more efficient code than a seasoned DB dev. There's a trade-off between portability and performance.

Plus ça change! I worked for both Oracle and Sybase in the mid-80s and early-90s and recall the challenges faced by organisations looking to adopt SAP where they had standardised on the latter. The absence of row-level locking, coupled with the database design which originated on the mainframe, posed significant performance challenges and resulted in SAP's lack of support for Sybase (and by implication SQL Server). For COTS developers in particular, other factors need to be considered in the trade-off: time-to-market; market reach; cost of cross-database platform development, support and maintenance. In some cases the trade-off will favour ORM, in others database-specific code and in others a hybrid approach.

Post #1343980

« Prev Topic | Next Topic »

71 posts, Page 1 of 8

1 2 3 4 5 »»»

Permissions