Is it better to use stored procedure?

[hmcheung]

I've to query some tables on SQL Server using ASP. I've to join these tables in the query. Will the response time be faster if I make a stored procedure for the query and let the ASP to execute the stored proc? Thanks! Please visit my WebCam!!

http://i.am/andycheung

 

[Antzz]

Yes, a stored procedure is always better. The main reason is that SQL Server has to re-compile the SQL statement everytime you pass it on to ASP page for execution.

On the other hand a SP(Stored procedure) is already compiled.

Also you can have multiple SQL statements inside a SP thus reducing database intensive tasks to single time execution on the server instead of a to-and-fro communication between the ASP pages and the database.

 

[Esoteric]

Hey,

Can you explain to me how that works? I am interested in this and technially what makes it faster?

Say I have a query within my ASP script or I call a stored procedure within my ASP script how can one be faster than the other? If they are both queries don't they both have to be executed and the results compiled regaurdless?

I am working on a project right now that could benefit from stored procedures if it is acctually faster then I will use them otherwise I prefer to just have it all within ASP for easy editing.

 

[Antzz]

Ok this is how it works :
1) You have a SQL statement which is like :
&quot;SELECT * FROM <TABLE> WHERE <CONDITION>
Suppose you have this sql statement in an asp page and you are sending it to SQL Server to be executed.
This SQL statement is first compiled by SQL Server for any syntax errors, etc before actually executing it. Now surely compilation takes some time no matter however negligible this might seem to the human eye.

On the other hand if you have the same SQL statement in a SP, the SP is already compiled and the SQL Server has only to execute it without bothering to check for other errors when you invoke the SP from your ASP page.

2) Suppose you are doing some processing in your ASP page where you have a function or sub routine which has multiple SQL statements something like :
SELECT * FROM <TABLE> WHERE <CONDITION>
{other processing}
UPDATE <TABLE>
SET <FIELD> = <DATA>,
<FIELD> = <DATA>
WHERE
<CONDITION>
.
.
{other processing}
.
.
.
SELECT * FROM <TABLE> WHERE <CONDITION>

Now for all these above statements in the function, each time you execute them from an ASP page, there is to and fro communication to your database server. Now if the database server is not on the same server as the Web Server, then there will be network traffic. Even if your Web and database server are on your own machine, there will still be communication overhead between these two.

You can avoid this by writing a SP which has the same functionality as the sub routine in ASP. I gurantee, it will be much faster and you will see a noticeable gain in performance.

I hope I was able to get the point across.

 

[Esoteric]

I understand your point, and can see the advantage to SP. So now for a simple question. Can you pass variables to an SP from the ASP page?

For instance Database search, the variable for the search criteria which may be something like this:

sql = &quot;SELECT Table.* From Table WHERE Table.Var1 LIKE '%&quot; & var & &quot;%' OR Table.Var2 LIKE '%&quot; & var2 & &quot;%'&quot;

Is this possible?

 

[JuanitaC]

Using parameters from ASP to stored procedures on SQL Server is simple. I like to do it using the Command object in my ASP:

Set rs=Server.CreateObject("ADODB.Recordset")
Set cmd=Server.CreateObject("ADODB.Command")
cmd.ActiveConnection=connectionVariable
cmd.CommandType=adCMDStoredProc
cmd.CommandText="cp_Ad_EntityInfo"
Set paraID = cmd.CreateParameter("id",adVarChar,adParamInput,40,EnID)
cmd.Parameters.Append paraID
Set rs=cmd.Execute()
If Err<>0 then
	Response.Write(Err.description)
	Response.End
end if

There are probably other ways to send your parameters to the stored procedure from ASP, but the command object is the one that I have always used.

Then, the syntax of my stored procedure would be:

CREATE PROCEDURE sp_StoredProcedure
     @ID varchar(40)
AS
     SELECT * FROM Table
     WHERE ID = @ID