Stored Procs blues

I client just told me I had to use stored procs.  Well their development team looked at my code and said so.  And this comes just after I had worked out a slick little query factory.  So I'm not too happy about the double work.

I am also disappointed that this breaks some of my database-neutral design.  By cramming more of the DAL into the specific database, I make future migration to something else much more difficult than if I just had to tweak the use of a few TSQL-specific functions.

This site will be fairly low traffic, so the perf gains don't excite me much.

I had things fairly well protected against SQL injection.

Anyone have any reasons why I should be excited about this?  Anything I haven't thought of?  The best I can say is that I'll get more practice w/ stored procs.

Blargh!!!! [C]

EDIT: The app is really only doing simple CRUD work.

Stored procs can help performance (stored execution plans) and make it easier to manage permissions (no-one gets direct permissions on the table)

Maurits wrote:

Stored procs can help performance (stored execution plans) and make it easier to manage permissions (no-one gets direct permissions on the table)

Do the stored execution plans still apply when you are doing queries such as

SELECT someValue FROM myTable WHERE myValue LIKE '%someParameter%'

when someParameter is passed in dynamically?  (Sorry if this is a complete noob question)

Larsenal wrote:

I am also disappointed that this breaks some of my database-neutral design.  By cramming more of the DAL into the specific database, I make future migration to something else much more difficult than if I just had to tweak the use of a few TSQL-specific functions.

I've come around to wanting to put CRUD in a layer outside my business object library, the trick is to make the library 'database-neutral' and require only sprocs on the chosen db platform to fill out the system.

It's a departure from a more 'pure' OOD, imho, but you should be able to reclaim the database-independence you want.

Having the DAL loosely coupled has benefits of its own, besides any performance and security improvements; schema changes are less likely to affect the business object's I/O.


Sorry about the double work, hope you're paid hourly.

I believe the execution plan is saved with such stored procedures but that kind of query will almost certainly involve a full table scan. Miss out the first wildcard (IE a "starts with" query rather than an "includes" query) and SQL Server will use the relevant index to speed up the processing if such an index exists.

SimonJ

Re: execution plan:

Actually that's an EXCELLENT question.  Critical, even.

The short answer is - sometimes.

Read Ian Jose's post on condition-specific stored procedures for a more helpful answer.

Larsenal wrote:

I client just told me I had to use stored procs.  Well their development team looked at my code and said so.  And this comes just after I had worked out a slick little query factory.  So I'm not too happy about the double work.

I am also disappointed that this breaks some of my database-neutral design.  By cramming more of the DAL into the specific database, I make future migration to something else much more difficult than if I just had to tweak the use of a few TSQL-specific functions.

This site will be fairly low traffic, so the perf gains don't excite me much.

I had things fairly well protected against SQL injection.

Anyone have any reasons why I should be excited about this?  Anything I haven't thought of?  The best I can say is that I'll get more practice w/ stored procs.

Blargh!!!!

EDIT: The app is really only doing simple CRUD work.

I think a better way is to make a middle tier object generator that can output the stored procedures (using a slick little query factory perhaps) and then have your middle tier object that is generated use those queries. Then you get the best of both worlds.  Having queries in the SQLServer side allow for more fine grained security than giving your app full access to do what it needs. 

Adhoc queries are normally not the best solution unless the query itself is heavily dynamic where writing a sql server query would be painful, but it sounds like that isn't the case for you.  

Larsenal wrote:

Anyone have any reasons why I should be excited about this?  Anything I haven't thought of?  The best I can say is that I'll get more practice w/ stored procs.

That and you are also getting paid. There are plenty of tools out there that can generate these sprocs for you (codesmith).

Did the client not see or understand the design before you started coding?

harumscarum wrote:

Larsenal wrote: Anyone have any reasons why I should be excited about this?  Anything I haven't thought of?  The best I can say is that I'll get more practice w/ stored procs.

Did the client not see or understand the design before you started coding?

The client is a financial institution.  While the app I'm writing is not mission critical, anything hosted on their intranet servers  has to be reviewed by their in-house devs to make sure "general good coding practices are followed."

They haven't asked about my app design at all.  My guess is that they saw some SQL in my  middle tier and freaked out.

Larsenal wrote:

I make future migration to something else much more difficult than if I just had to tweak the use of a few TSQL-specific functions.

Don't take this the wrong way, and I can totally see where you are coming from - but in a lot of cases nobody cares whether it will be harder to port in 2/3/5 years time.  If a company decides on a database platform they don't expect to be porting - so why waste time with portability.

Like I said, I agree with you, but we're both in the wrong commercially.

Larsenal wrote:

EDIT: The app is really only doing simple CRUD work.

Cool then you can use CodeSmith to generate the sprocs with minimal effort on your part.

Rossj wrote:

Larsenal wrote:I make future migration to something else much more difficult than if I just had to tweak the use of a few TSQL-specific functions.

Don't take this the wrong way, and I can totally see where you are coming from - but in a lot of cases nobody cares whether it will be harder to port in 2/3/5 years time.  If a company decides on a database platform they don't expect to be porting - so why waste time with portability.

Like I said, I agree with you, but we're both in the wrong commercially.

Larsenal wrote: EDIT: The app is really only doing simple CRUD work.

Cool then you can use CodeSmith to generate the sprocs with minimal effort on your part.

I hear ya, Rossj.  The portability is not a huge issue for this project.  It's disappointing that I'm "wasting" time not because I am making it more portable, but it is actually now less portable with very little gained elsewhere.

Maybe my real complaint is that I'm not a sprocs guru yet.  I'm only now feeling like I'm pumping out some sweet .NET code (after a few years of ramped-up usage).

Larsenal wrote:

I hear ya, Rossj.  The portability is not a huge issue for this project.  It's disappointing that I'm "wasting" time not because I am making it more portable, but it is actually now less portable with very little gained elsewhere.

Maybe my real complaint is that I'm not a sprocs guru yet.  I'm only now feeling like I'm pumping out some sweet .NET code (after a few years of ramped-up usage).

I say ditch T-SQL altogether and use stored procs.  SQL Server 2005 supports writing your stored procedures in "sweet .NET code" so you can leverage your .NET skills and meet the customer's requirements:

http://msdn2.microsoft.com/en-us/library/6s0s2at1.aspx

Microsoft says this new ability to run managed code WITHIN SQL SERVER doesn't mean T-SQL is going away.  (Yet.)  (*cough*)  In fact, they still recommend T-SQL for performance-sensitive stuff (if you are processing a billion transactions a day, I guess).

You might be able to sell your customer on the desirability of upgrading to SQL Server 2005 because of the new ability to create robust, managed-code stored procs using the .NET language of your choice (rather than T-SQL).  You'd have all of .NET at your disposal -- regular expressions, crypto -- within an sproc!!!

I don't know if I'd go so far as to proclaim this is the new "strategic direction" (i.e. what all the cool kids are doing now) or that T-SQL is so last year... even though both of those might be true

Karim wrote:

Larsenal wrote:I hear ya, Rossj.  The portability is not a huge issue for this project.  It's disappointing that I'm "wasting" time not because I am making it more portable, but it is actually now less portable with very little gained elsewhere. Maybe my real complaint is that I'm not a sprocs guru yet.  I'm only now feeling like I'm pumping out some sweet .NET code (after a few years of ramped-up usage).

I say ditch T-SQL altogether and use stored procs.  SQL Server 2005 supports writing your stored procedures in "sweet .NET code" so you can leverage your .NET skills and meet the customer's requirements:

http://msdn2.microsoft.com/en-us/library/6s0s2at1.aspx

Sweet idea.  I'd love to give it a shot.

<poo:poo>However, this is very unlikely since the client is a large financial institution.  They've probably hired some chimp to sit in his desk in Milwakee to make decisions about when they upgrade SQL Server.  No way they'll upgrade their entire hosting system just for me.  I wish they would though!!!</poo:poo>

Karim wrote:

I say ditch T-SQL altogether and use stored procs.

Ah... so instead of merely locking in to SQL Server, you lock in to .NET too...

Maurits wrote:

Karim wrote: I say ditch T-SQL altogether and use stored procs.

Ah... so instead of merely locking in to SQL Server, you lock in to .NET too...

haha.

Well you could port it to db2 "stinger".

Larsenal wrote:

<poo:poo>However, this is very unlikely since the client is a large financial institution.  They've probably hired some chimp to sit in his desk in Milwakee to make decisions about when they upgrade SQL Server.  No way they'll upgrade their entire hosting system just for me.  I wish they would though!!!</poo:poo>

<glasses type="rosy">

Well, they may be on "software assurance" and thus planning to upgrade anyway.

There are other good reasons to upgrade to SQL Server 2005, such as HTTP endpoints -- web services directly from SQL without using IIS!!!  Even if you are stuck using T-SQL, you can take advantage of new stuff like support for exception handling in T-SQL...

</glasses>

Maurits wrote:

Karim wrote: I say ditch T-SQL altogether and use stored procs.

Ah... so instead of merely locking in to SQL Server, you lock in to .NET too...

It's a fair cop.     But it eliminates the need to become a T-SQL slingin' sproc wiz...

It's funny how the Microsoft ice cream tastes so much better when served in a Microsoft cone...

Karim wrote:

Maurits wrote: Karim wrote: I say ditch T-SQL altogether and use stored procs. Ah... so instead of merely locking in to SQL Server, you lock in to .NET too...

It's a fair cop.     But it eliminates the need to become a T-SQL slingin' sproc wiz...

It's funny how the Microsoft ice cream tastes so much better when served in a Microsoft cone...

That is what OR mappers and code generators are for.  That is the problem with MS ice cream. It only tastes good when its on a MS cone.