sp_execute & Stored Procedures vs Ad-Hoc queries

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

sp_execute & Stored Procedures vs Ad-Hoc queries

Author

Topic

Adelyra
Starting Member

14 Posts

Posted - 2010-11-04 : 10:21:11

Hello!

I'm working on an ASP project where there's a lot of interaction with the database. My background is in PHP and My SQL so I'm kind of new to ASP and MSSQL (SQL Server 2003).

I've been monitoring the Current Activity > Locks/Object and I've been seeing a bunch of SPIDs that say 'sp_execute;1' and 'sp_prepare;1' and, since I have no idea what those mean, I decided to research them.

I can across this post [[url]http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=117803[/url]] and at the bottom (by PESO) I noticed that I may be writing these 'ad-hoc queries' to query the DB instead of stored procedures.

A lot of my sql executions look like this (to save space):

conn.Execute("UPDATE [tablename] SET Column1='value1' ... etc")

What's the best way to define and execute SQL statements in ASP? What do stored procedures look like when executing SQL statements?

Thanks,
Adelyra

Adelyra
Starting Member

14 Posts

Posted - 2010-11-04 : 10:55:35

I was continuing my research while waiting on replies to this thread when I came across this website: [[url]http://databases.aspfaq.com/database/using-stored-procedures.html[/url]]

Just thought I'd share as it's useful and probably answers a few of my questions.

Kristen
Test

22859 Posts

Posted - 2010-11-04 : 15:48:04

You can write a Stored Procedure - which is a single unit of self contained logic. For example


UPDATE MyTable SET Col1 = @MyParameter WHERE MyIDColumn = @MyID

and then parameters for @MyID and @MyParameter can be passed to the Sproc.

If the logic needs to change that can happen in the Sproc. The need arises to raise an error if the record is marked as InActive. Then we decide we need to log an Audit record for each change. And so on. Each of those changes-of-need just requries a change to the Sproc, we don't need to change the application at all.

Also, SQL will create a Query Plan for the Sproc, the first time it is run, and then Cache it. Next time the Sproc is called SQL will reuse the Query plan from Cache - without having to build a new one (this is the first of many over simplifications

, but this is generally how it works). This makes Sprocs more efficient than dynamic SQL built (usually by string concatenation) in the application.

You can call a Stored Procedure using Parmeterised methods (each parameter is assembled and passed to SQL) or by using Dynamic SQL (strSQL = "EXEC MySproc @PARAM1='" & SomeWebForm.SomeFormField & "'"). Dynamic SQL has significant risk of SQL Injection (being hacked by the user). There are ways around it, but you MUST remember to use them and you must use them in EVERY SINGLE dynamic SQL expression you write, without fail!!! - even good coders have an off day

Sometimes Stored Procedures don't suit what you want to do. You may offer your users an Advanced Find page for every part of your system. Each Advanced Find has 100's of optional fields on it. Its amazing, it can find anything!!! but passing all those parameters to an Sproc and using a single "do anything" query in your Sproc will probably be very inefficient.

So an alternative is to use sp_ExecuteSQL.

You an do something like:


strSQL = "SELECT Col1, Col2, .. FROM MyTable WHERE 1=1 "

IF MyForm.CustomerName <> "" THEN strSQL = strSQL & "AND CustomerName = @CustomerName "
IF MyForm.StartDate <> "" THEN strSQL = strSQL & "AND MyDateColumn >= @StartDate "

and then you assemble your call to sp_ExecuteSQL which is like this (note: shown in SQL pseudo code, not the application language, as APP languages differ widely, but this would actually need to be a Procedure Execute from your APP)


EXEC sp_ExectuteSQL strSQL, "@CustomerName varchar(30), @StartDate datetime", @CustomerName = MyForm.CustomerName, @StartDate = MyForm.StartDate

(You would actually pass these "parametrised", so this is over simplified and just "for show").

Possibly useful point here is that the definition for @CustomerName and @StartDate, and their values, can be included in all instances (i,e, regardless of whether they are in the WHERE clause / used, or not)

So ... you will have statements that have a WHERE clause ONLY including things that were filled in on your Mega Advanced Search form. For each different WHERE clause SQL will make a different query plan, and cache it. If the query is seen again it will reuse the cached query plan. The likelihood is that you will have some commonly repeated queries - 80% of queries might JUST fill in the form field for Name

- so this becomes efficient; combinatiosn fo search fields, in the WHERE clause, that are not repeated will cause their query plan to be dropped from the cache.

By comparison if you use dynamic SQL:


strSQL = "SELECT Col1, Col2, .. FROM MyTable WHERE CustomerName = '" & MyForm.CustomerName & "'"

then EVERY query will be different because each one will have a different value for the CustomerName from the form (there may be some repeats, of course, but in the main they will all be for different Customer Names). Each one will cause SQL to create a new query plan, which it will then put in the cache, and (probably) never see that exact same query again ... (this is an oversimplification too, SQL is smarter than that, but the principle applies).

Note that for really REALLY complex queries it can take SQL longer to make the query plan than to actually find the data. Either way, the "cost" of creating a Query Plan is significant, and you should aim to reuse query plans,s o that they are used from Cache, where possible.

So:

Dynamic SQL - SQL Injection risk, in the main every single one needs to have a new Query Plan made.

Stored Procedure - a discrete unit of logic, query plan is created on first use and cached

sp_ExecuteSQL - great combination of the two for "variable logic" queries, or if you really want to have the SQL code mixed-in with your Application code.

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2010-11-04 : 16:39:52

sp_execute and sp_prepare are an indication of the worst possible solution, a server side cursor, so you need to get away from that. That means that the client is going back to the server to do a fetch next for each row sent to the client, and it can generate a huge number of database calls. I saw one application making 30,000+ calls to the database every second using client side cursors.

I prefer to use a stored procedure to build the SQL used by sp_executesql. Then the client side code passes the parameters to the stored procedure and it does the rest. It helps to eliminate the common source of SQL injection in the client side code.

CODO ERGO SUM

Adelyra
Starting Member

14 Posts

Posted - 2010-11-05 : 07:26:27

@Kristen and @Michael Valentine Jones:

Thank you so much for taking the time to reply with such detail. I strongly believe that there's enough information here for me to start meking the right decisions when writing the code for my projects.

That being said, there's a lot of Greek in these replies so it'll take some time for me to go through and really get a handle on the concept of stored procedures and sp_executesql queries.

I'm going to begin researching each of the points made here in this thread and trying to implement them. If I have any questions I hope it's ok that i post them here.

Thanks again,

Adelyra

Kristen
Test

22859 Posts

Posted - 2010-11-05 : 12:29:04

"there's a lot of Greek in these replies"

Feel free to ask questions / for clarification

We all had to start somewhere too ...

Adelyra
Starting Member

14 Posts

Posted - 2010-11-05 : 14:39:08

Well, I've jumped in with both feet and started testing some of the custom stored procedures that I made.

In fact, I was able to execute an update and an insert procedure from my ASP project.

What I'm struggling with now are the select procedures. My project is a task tracker where tasks are created and inserted into the database then shown in a table on the webpage. The user also has the ability to edit and delete these tasks.

I have around 7 tables in my database including the main one that stores the tasks. Having said that, I also have (I just counted them all) 46 SQL queries in my whole project, 36 of which being SELECT statements to populate the tables.

SO, I'm trying to figure out how to organize all these select statements that are similar, yet dynamic, in as few stored procedures as possible. For this I figured, given what you wrote about sp_executeSQL, that I would use that but it's a little more complicated than I thought.

Here's a snippet of code from one of my pages where the user can customize 5 tables and their data (of course leaving out the private info):

'USERS
Dim sqlUsers
sqlUsers = "SELECT * FROM tableUsers ORDER BY ID ASC"
Set rstUsers = conn.Execute(sqlUsers)

'IMPACT
Dim sqlImpact
sqlImpact = "SELECT * FROM tableImpact ORDER BY ID ASC"
Set rstImpact = conn.Execute(sqlImpact)

'ISSUE
Dim sqlIssue
sqlIssue = "SELECT * FROM tableIssueTypes ORDER BY Issue_Type_ID ASC"
Set rstIssue = conn.Execute(sqlIssue)

'SUBJECT
Dim sqlSub
sqlSub = "SELECT * FROM tableSubject ORDER BY ID ASC"
Set rstSub = conn.Execute(sqlSub)

'EMAIL NOTIFY
Dim sqlNotify
sqlNotify = "SELECT * FROM tableCustom WHERE VariableName = 'Notifications'"
Set rstNotify = conn.Execute(sqlNotify)

As you can see these are all Dynamic SQL (creating new query plans everytime this page is called). I also have SELECT statements on other pages that are, if not identical, very similar to this. I assume this is an easy problem to solve by using sp_executeSQL and having the client code passing the 'WHERE parameters' in.

Anyway, I think I'm just thinking out loud at the moment as I'm not certain there were any questions there. If you wouldn't mind providing some feedback at where my head is at based on this post that would be great! Am I going in the right direction?

Thanks again! I really appreciate the time you've spent helping me.

Adelyra

TimSman
Posting Yak Master

127 Posts

Posted - 2010-11-05 : 14:57:11

My opinion is that you're better off having separate stored procedures in the db, and calling those procedures from your code. I like it because it gives me options on what to pull, how to pull it, where to put validation, etc.

What you're doing is perfectly acceptable (some people might even prefer it). I just like removing SQL code from application code as much as I possibly can.

I don't mind having lots of stored procedures, because it gives me flexibility, and a chance to identify more easily where my code is screwing up. Plus, when the application requirements change, you change your code, you may not have to do as much work in the back end, and vice versa.

Just some thoughts from my experience, so take that for what it's worth.

Kristen
Test

22859 Posts

Posted - 2010-11-08 : 03:39:15

a"As you can see these are all Dynamic SQL (creating new query plans everytime this page is called)"

Not quite the case. If you send exactly the same SQL (including to change to upper/lower case, spaces, the lot) then any existing query plan will be reused.

If you send

SELECT * FROM MyTable WHERE MyColumn='JOHN'

twice then the second one will reuse the cached query plan, but it you then send:

SELECT * FROM MyTable WHERE MyColumn='BILL'

SQL will make a new query plan (it might not, SQL is quite smart, but for more complex queries it probably will. For a stored procedure that is not identified as needing a new query plan every time it runs then the Query Plan will just be got straight from cache.)

The benefit of sp_ExecuteSQL is that you query becomes

SELECT * FROM MyTable WHERE MyColumn=@FindName

(and @FindName is passed separately containing either "John" or "Bill") - so the actual SQL is identical every time, and the Cached query plan is used.

Note that you should not use "SELECT *" - you should list the actual column. (If you need to know why pls ask)

"I assume this is an easy problem to solve by using sp_executeSQL and having the client code passing the 'WHERE parameters' in."

Yes. You could have a stored procedure or sp_ExecuteSQL (or dynamic SQL!). For example, a Stored Procedure to search by either Town or Name (or both) would take two parameters and do:


SELECT Col1, Col2, ...
FROM MyTable
WHERE (@NAME IS NULL OR MyNameColumn = @NAME)
      AND (@TOWN IS NULL OR MyTownColumn = @TOWN)

then you call the Sproc with any parameter as NULL for "don't care"

Query plans for such versatile queries can be inefficient

Using sp_ExecuteSQL with a parmeterised WHERE clause might well be more efficient. Your where clause will be one of three possible combinations:

WHERE MyNameColumn = @NAME
or
WHERE MyTownColumn = @TOWN
or
WHERE MyNameColumn = @NAME AND MyTownColumn = @TOWN

and, in particular, your will NOT have the actual value for Town or Name in the WHERE clause. Thus there are three possible WHERE clauses (probably more in your actual solution). Assuming that all combinations are used then SQL will generate three query plans, optimised for each query, and cache them. If the third one is very rarely used then the cached query plan may have become stale and removed from Cache before the query is requested again - so that particular query might be slower as SQL will have to re-make the Query Plan (same for a rarely called Sproc of course)

NOTE: Lets say you have indexes on NAME and TOWN

WHERE MyNameColumn = @NAME will use the Name index, and

WHERE MyTownColumn = @TOWN will use the Town index.

But


WHERE (@NAME IS NULL OR MyNameColumn = @NAME)
      AND (@TOWN IS NULL OR MyTownColumn = @TOWN)

may use neither index (and scan the whole table every time) or the very first execution of the Sproc may have a @TOWN parameter and @NAME = NULL, and SQL will (lets say) decide to use the TOWN index. Now every time you search for a Name it will be slow because SQL is using the TOWN index - that's like having a telephone directory ordered by Number rather than Name! - hence sp_ExecuteSQL may well be a better solution.

Key thing, also, about sp_ExecuteSQL is that it will keep the SQL code in your Application code - rather than having ASP code and separate SProc SQL code. I use Sprocs! but that doesn't suits everyone. You can mix & match of course.

I agree with TimSman on separate Sprocs for each "Task". It continues to amaze me how much our Sprocs evolve over the lfietime of a product, and how much flexibilty having discrete logic units gives us.

We have CRUD sprocs (Create, Retrieve, Update, Delete) which we generate mechanically. These process one record, the Retrieve returns all rows - which can be slightly inefficient for things that only want some columns, but most "maintenance forms" need all columns so we trade precision for time&cost saving.

Create/Update have parameters for all columns, but update only a single row. Ditto Delete that also takes the PK columns as a parameter and deletes that one row.

But all the other more application-specific stuff has pretty much one-sproc-per-task

It also aids with debugging as TimSman said. Add some "logging" code to an Sproc to see what's going wrong. Work on that Sproc (which won't accidentally intyerfere with other, broader, application logic that editing a large ASP file might)

Sachin.Nand

2937 Posts

Posted - 2010-11-08 : 03:57:35

There's a great article by on this by Gail

http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/

PBUH

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2010-11-08 : 09:44:40

quote:
Originally posted by Kristen
...
Key thing, also, about sp_ExecuteSQL is that it will keep the SQL code in your Application code - rather than having ASP code and separate SProc SQL code. I use Sprocs! but that doesn't suits everyone. You can mix & match of course.
...

There is no reason that you can't build the dynamic SQL statement for sp_ExecuteSQL in a stored procedure. That is the way I usually to it, and there are advantages to doing it that way.

It allows you to use EXECUTE AS in the stored procedure without granting elevated privileges to the user’s login.

I think it’s easier to test, because you don’t have to run the application code to test it.

CODO ERGO SUM

Kristen
Test

22859 Posts

Posted - 2010-11-08 : 10:53:28

I'm with you 100% on that MVJ, but I have done a lot of consultancy at companies where they sprinkled SQL in their (say) ASP code, and changing it to use SP_ExecuteSQL was pretty straightforward (and gave them HUGE performance boost), but they were much happier doing string manipulation / concatenation for WHERE clauses in their ASP code than in SQL - given that ASP would have been their primary skill.

And it suits such folk to have the SQL "nearby where it is used". I can tell them I don't agree, but not to the point where they lose interest in anything else that I might have to say that will help them out!

I think string manipulation in SQL (e.g. in Sprocs) is pretty ghastly to read, compared to ASP et al, so I have some sympathy - but Permissions and Compartmentalisation are reason enough for me, too, to do it all-in-SQL

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources