Is there a gain in efficiency if I put a lengthy SQL select into a stored procedure?

Question

I have the following SQL Server 2012 query:

var sql = @"Select question.QuestionUId
            FROM Objective,
                 ObjectiveDetail,
                   ObjectiveTopic,
                   Problem,
                   Question
            where objective.examId = 1
            and objective.objectiveId = objectiveDetail.objectiveId
            and objectiveDetail.ObjectiveDetailId = ObjectiveTopic.ObjectiveDetailId
            and objectiveTopic.SubTopicId = Problem.SubTopicId
            and problem.ProblemId = question.ProblemId";

            var a = db.Database.SqlQuery<string>(sql).ToList();

Can someone help explain to me if it would be a good idea to put this into a stored procedure and if so then how could I do that and then call it from my C# code. It was suggested to me that if it is in a stored procedure then it would run more efficiently as it would not be recompiled often. Is that the case?

there's a minor gain from the sp, it being stored in a "compiled" state, but usually compilation is a minor overhead, especially for large datasets. e.g. 2 hour runtime, 0.000001 seconds of compiliation. "woo, I can blink one extra time!" — Marc B, Jun 16 '14 at 16:59
Without moving query to SP, ORM tools will pre-compile the query as well. In Java, constructs like PreparedStatement maintain a pre-compiled Query. — Paddy, Jun 16 '14 at 17:04
It's generally a good idea to have an n-tier design. not just due to performance but due to other reasons: By having the select in the database, you can query system tables for dependencies and better understand. In addition you avoid potential risks to SQL injection. The way this is presently formatted not really a problem; but if someone later adds a where clause that contains a user defined value... Just my 2 cents. — xQbert, Jun 16 '14 at 17:07
This is an old debate: [stored procedures vs. ad-hoc queries](http://stackoverflow.com/questions/22907/which-is-better-ad-hoc-queries-or-stored-procedures). And read [this article](http://sqlperformance.com/2013/05/t-sql-queries/another-argument-for-stored-procedures). — Bogdan Sahlean, Jun 16 '14 at 18:28
@xQbert - Your arguments don't directly apply. One can have an n-tier design without stored procedures. Also, SQL injection can still happen through a stored procedures, depending on how it is written. Using parameterized queries is the best way to avoid SQL injection. — SeraM, Jun 16 '14 at 19:36
@phaedra And just how do you have a SQL injection with a SP? — paparazzo, Jun 16 '14 at 20:43
@Blam - if the underlying stored procedure uses dynamic SQL. You could say, well don't do that, but it would be similar to the situation xQbert describes in saying "if someone later adds a where clause that contains a user defined value" to this query it could have SQL injection. — SeraM, Jun 16 '14 at 20:54
@phaedra OK but that is a reach that they convert a parameter passed to a SP to dynamic SQL. — paparazzo, Jun 16 '14 at 20:56

score 0 · Accepted Answer · edited May 23 '17 at 12:27

Yes, there is. For starters, a stored procedure is precompiled and stored within your database. Being precompiled, the database engine can execute it more efficiently, since no on-the-fly compilation necessary. Also, database optimizations can be added to support a precompiled procedure. A stored procedure also allows business logic to be encapsulated within the database.

If you decide to go the stored procedure route, then consider the following:

First of all, you will need to create a stored procedure that encapsulates your existing SQL query.

CREATE PROCEDURE ListQuestionIds
    @ExamId int
AS
BEGIN

    SELECT Question.QuestionUId
    FROM Objective
    INNER JOIN ObjectiveDetail
        ON ( Objective.objectiveId = ObjectiveDetail.objectiveId )
    INNER JOIN ObjectiveTopic
        ON ( ObjectiveDetail.ObjectiveDetailId = ObjectiveTopic.ObjectiveDetailId )
    INNER JOIN Problem
        ON ( ObjectiveTopic.SubTopicId = Problem.SubTopicId )
    INNER JOIN Question
        ON ( Problem.ProblemId = Question.ProblemId )
    WHERE Objective.examId = @ExamId;

END;

Please make sure that the tables called by your procedure (Objective, Problem, etc,) have all of the relevant primary keys and indexes in place to enhance the performance of your query.

Next, you will need to call that stored procedure from within your C# code. One way--but by no means the only way--is to create a connection to your database using the SqlConnection object and then executing your procedure via the SqlCommand object.

I would recommend that you take a look at How to execute a stored procedure within C# program for some on-topic examples. But a simple example of such might look like:

string connectionString = "your_connection_string";

using (var con = new SqlConnection(connectionString))
{
    using (var cmd = new SqlCommand("ListQuestionIds", con)) {

        cmd.CommandType = CommandType.StoredProcedure

        cmd.Parameters.Add(new SqlParameter("@ExamId", examId))

        con.Open();

        using (SqlDataReader rdr = cmd.ExecuteReader())
        {
            while (rdr.Read())
            {
                // Loop through the returned SqlDataReader object (aka. rdr) and
                // then evaluate & process the returned question id value(s) here
            }
        }
    }
}

Please note that this sample code does not (intentionally) include any error handling. I leave that up to you to integrate into your application.

Finally, just as an FYI... many of the more modern ORMs (e.g., Entity Framework, NHibernate, etc.) allow you to execute stored procedure-like queries from your C# code without requiring an explicit stored procedure. If you are already using an ORM in your application, then you may want to forgo the stored procedure altogether. Whatever you decide to do, a little research on your end will help you make an informed decision.

I hope this helps you get started. Good luck.

Is there a gain in efficiency if I put a lengthy SQL select into a stored procedure?

1 Answers1