Is this a valid benefit of using embedded SQL over stored procedures?

Question

Here's an argument for SPs that I haven't heard. Flamers, be gentle with the down tick,

Since there is overhead associated with each trip to the database server, I would suggest that a POSSIBLE reason for placing your SQL in SPs over embedded code is that you are more insulated to change without taking a performance hit.

For example. Let's say you need to perform Query A that returns a scalar integer.

Then, later, the requirements change and you decide that it the results of the scalar is > x that then, and only then, you need to perform another query. If you performed the first query in a SP, you could easily check the result of the first query and conditionally execute the 2nd SQL in the same SP.

How would you do this efficiently in embedded SQL w/o perform a separate query or an unnecessary query?

Here's an example:

--This SP may return 1 or two queries.

SELECT @CustCount = COUNT(*) FROM CUSTOMER

IF @CustCount > 10 SELECT * FROM PRODUCT

Can this/what is the best way to do this in embedded SQL?

Answer 1

How would you do this efficiently in embedded SQL w/o perform a separate query or an unnecessary query?

Depends on the database you are using. In SQL Server, this is a simple CASE statement.

Answer 2

I would generally never put business logic in SP's, I like them to be in my native language of choice outside the database. The only time I agree SPs are better is when there is a lot of data movement that don't need to come out of the db.

So to aswer your question, I'd rather have two queries in my code than embed that in a SP, in my view I am trading a small performance hit for something a lot more clear.

Answer 3

Perhaps include the WHERE clause in that sproc:

WHERE (all your regular conditions)
AND   myScalar > myThreshold

Answer 4

A very persuasive article

SQL and stored procedures will be there for the duration of your data.

Client languages come and go, and you'll have to re-implement your embedded SQL every time.

Answer 5

Benefits of SPs:

1. Performance (are precompiled)
2. Easy to change (without compiling the application)
3. SQL set based features make very easy doing really difficult data tasks

Drawbacks:

1. Depend heavily on the database engine used
2. Makes deployment of upgrades a little harder (you have to deploy the App + the scripts)

My 2 cents...

About your example, it can be done like this:

select * from products where (select count(*) from customers>10)

Answer 6

In the example you provide, the time saved is sending a single scalar value and a single follow-up query over the wire. This is insignificant in any reasonable scenario. That's not to say there might not be other valid performance reasons to use SPs; just that this isn't such a reason.

Answer 7

Lately I prefer to not use SPs (Except when uber complexity arises where a proc would just be better...or CLR would be better). I have been using the Repository pattern with LINQ to SQL where my query is written in my data layer in a strongly typed LINQ expression. The key here is that the query is strongly typed which means when I refactor I am refactoring properties of a class that is directly generated from the database table (which makes changes from the DB carried all the way forward super easy and accurate). While my SQL is generated for me and sent to the server I still have the option of sticking to DRY principles as the repository pattern allows me to break things down into their smallest component. I do have the issue that I might make a trip to the server and based on the results of query I may find that I need to make another trip to the server. I don't worry about this up front. If I find later that it becomes an issue then I may refactor that code into something more performant. The over all key here is that there is no one magic bullet. I tend to work on greenfield applications which allows this method of development to be most efficient for me.