stored procedure speed vs embedded SQL strings 2

[TheInsider]

(I also posted this in the SQL Server forum)
Hello,
Real simple question here. I am working on an ASP page and would like some clarification on when I will benefit from using stored procedures and when I will not.

I have a table that has 2 columns -- column 1 is a TinyInt primary key and column 2 is a VarChar(30). The table has about 60 records.

If I simply want to populate a combobox with the data from this table; will I really get any benefit by creating a stored procedure to call the "SELECT * FROM MyTable" statement? I don't even need to sort the records, as I have entered them into the table in the order that I want them already.

By creating a stored procedure I would have to use 3 ADO objects -- a Connection, a Command object, and a Recordset to receive the records from the Command object.

By using an embedded SQL string in my ASP page I only need 2 ADO objects -- a Connection and a Recordset.

This webpage will have a high volume of users at any given time. Is their any advantage of using the stored procedure here over the SQL string in ASP?

One other case: if I am inserting a record into a table that has 12 columns, the data coming from an ASP page; would this be an appropriate place to use a stored procedure to do the actual inserting with "INSERT INTO...", as opposed to doing it in the ASP page?

This is another case of 2 objects to multiple. If I use a Command object to simply insert from a SQL string, I only need a Connection object and a Command object. If I use a stored procedure, I would need both of those objects + a Parameter object which I would call the CreateParameter function on 12 times!

I have many books on ASP and SQL Server, but none clarify this.
Thanks

 

[link9]

In the first case, for something that simple, using just a recordset will work better for you. Your observation of the number of objects floating around in memory is correct, and since there doesn't seem to be any major processing going on to make the SQL string, you don't need the extra command object.

As far as the INSERT question, here's my rule of thumb:

VBScript is not very good at string manipulation. It's generally considered to be it's weakest point, so if your INSERT statement is going to take alot of logic to build up, then a SPROC would be the way to go. If not, then use a simple SQL command executed directly on your connection object to do your work:

con.execute "INSERT...."

There really isn't a solid point at which you decide to use a SPROC, but I will normally just look at the amount of work being done, and weigh the alternatives just as you have up there. Some databases that you work with have a very complicated structure where an INSERT might need to be propagated out to many different tables and such... in this case, if you can't handle those with a trigger, then a SPROC is the way to go, but for simpler designs where building the statement is simple, and the one statement will take care of it, then just use con.execute.

hope that helps
Paul Prewett

 

[TheInsider]

Thanks for the advice.

 

[jonscott8]

PMFJI, but the "# of objects" argument is moot. A command object is not absolutely required to call a SP, nor are the parameter objects necessary for simple input parameters.

oConn.Open MyConnection
Set oRS = oConn.Execute("EXEC MySQLServerSP&quot
or
Set oRS = oConn.Execute("EXEC MyInsertSP 'Param1','Param2'&quot

As for your question, there really is no rule of when to use which. If you are more comfortable coding SPs, then use that route. Some argue that since SPs are precompiled you see a performance return when you get into larger, complex queries. However, there is only one possible execution plan for a INSERT VALUES query, thus SQLS has to perform no optimization.

IMO, if 1) the code will be called (or has the potential of being called) from multiple areas in your application or 2) the processing consists of more than a simple query, I'd implement it as a SP. Jon Hawkins

 

[TheInsider]

Thanks Jon.

One other question: Many sources recommend using ActiveX components for scalability and performance over straight ASP. This I understand. Compiled is faster than interpreted -- so a function is better placed into a dll.

I need good performance because I expect to get a lot of users on my website. However, I don't have very complex logic. My website needs simple ADO SELECTs and INSERTs. Will it really be beneficial to place these into a dll? A given function could consist of a single INSERT statement. My logic is: The custom component would just add one more object into memory.

So... if I had a SELECT statement, I would need a Connection object, a Recordset object... 2 objects so far, + 1 object for my component. Where, if I had only placed the code into an ASP page, I would only be creating 2 objects in memory.

Lastly, since I am using SQL Server, and since stored procedures are "compiled", couldn't I use them as a poor man's component? Wouldn't stored procedures give me a fair deal of speed, where more complicated logic is required... or are components the be all and end all in speed?
Thanks.

 

[codestorm]

My main argument for using SPs where possible is just that all your SQL queries are in one place then - easier to make across-the-board changes (as well as the other point mentioned above, of course, that they're good for doing multiple SQL statements).

Why not use the dll for the database access itself, but not for storing individual queries. <insert witticism here>
codestorm

 

[TheInsider]

I agree that stored procedures are easier to maintain than scattered SQL strings, but as I gathered from my cross-post of this question in the SQL Server forum (thread183-168742), as well as the advice from link9 and jonScott8, the ADO objects waste resources when stored procedures are used for simple tasks. I am still unclear as to the benefit that I would get from placing my data access methods into a dll, when I am only returning a recordset or inserting data with a sinle-line INSERT statement.

In the case of my most complicated situation: say I have to call a couple of SELECT statements and return a value based on that, wouldn't a stored procedure have the fastest access to the database?

I guess I don't understand just how much faster a component is to straight ASP or stored procedures. If I am not doing complex logic, for example sorting algorithms, when would I benefit from using a component? Or, rather: when will I not benefit from using components?

 

[link9]

I would venture to say that using a component for very simple tasks (such as your single line INSERT) is definitely overkill... the performance gain (if any??) wouldn't be enough to warrant that type of solution.

The more you let your database do, the faster your application is going to be. That's a fact.

If you're expecting very high traffic on the site, then I would suggest your &quot;poor man's component&quot; solution and go with the SPROC, instituting jonScott8's con.execute() solution (which I wasn't even aware of -- learn something new every day ;-)) to cut down on the number of objects in memory at any given time.

For all the reasons that have been posted here, and in the other thread in the SQL Server forum, that sounds like your best solution to this problem.


paul

 

[link9]

Oh, and as for what you gain from components... you've really answered that yourself.

Compiled vs. Interpreted --> for coding stuff

and then

Programming Logic vs. Data Manipulation --> for data stuff

If your &quot;coding stuff&quot; is very complex in its operations (sorting algorithms, string manipulation, etc...), then it's best to write a .dll. When do you write one? Who knows... when your application seems to be running slower than you like. There really isn't any other measure.

For your &quot;data stuff&quot;, just try to keep 'data manipulation' and 'programming logic' separated as much as possible.

Programming logic is what programming languages are for, and data manipulation is what databases are for. Both are very good at their jobs, and it's best to let them do their thing.

---Data---
For example, if you're running aggregates on a large data set to display to a user, let the database handle that, rather than keeping a running total in memory and then dividing by the number of records in a recordset. (It's a gimme, I know, but I'm just trying to illustrate).

---Programming---
There's really two good reasons for components, each can be mutually exclusive:

Performance & Ease of coding

If you have a large set of conditional statements and many different actions to take based on the outcome, program your way through that one, and then write SPROCS to take care of smaller tasks, rather than writing one big SPROC that makes its own decisions. If the logic is very complex, abstract the whole thing into a component... your application will thank you.

Likewise if there is an enormous set of operations that are inter-related in their functions, then put it all into a class and compile that puppy. That will make your coding a breeze, and you won't have to sit around and wait for your ASP code to interpret before any execution takes place.

hope that made some sense

paul

 

[TheInsider]

Thank you all for helping me! Link9, if I could give you another tipmaster star I would.

 

[jonscott8]

I'd recommend you head over to the MSDN and read a few articles on N-tier development. Here are a couple to get you started:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsqlsg/html/msdn_designeff.asp

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnovba01/html/EnterpriseApps.asp

Also, say for instance, you need functionality that isnt surfaced by a scripting language(VBS,JS,Perl,etc..). C++ is capable of the functionality you desire, but you never paid attention when you took that C++ class. What do you do? You go to your buddy (who's a C++ wiz) and have him develop a COM component in C++ that surfaces the functionality you need. Once that's been properly deployed to your webserver, ALL your apps have access to the functionality. Jon Hawkins