stored procedure speed vs simple SQL string 2

[TheInsider]

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

 

[jjgraf]

Stored Procedures are faster the Then sending a SQL Command String which it will have to compile, but the flipside you discussed is you need 3 ADO controls which are resource hogs. It would make more sense to remove an ADO control from your web page.

Also you not talking a very complex SQL query where it would take 15 to 30 seconds just to compile. you only talking about a simple command the speed increase there is very little.

 

[BobLoblaws]

In this case, a disadvantage to using a SQL string is that if you use the same code in many places, it could be time consuming if you have to change the it in the future. I do not think that there would be any difference in speed, due to the limited number of records and simplicity of the SQL statement. I would suggest simply using a SQL string instead of a stored procedure.

 

[TheInsider]

Thanks jjgraf and BobLoblaws. That was the conclusion that I had come to, but I wanted to get some opinions. Is it worth using the stored procedure in the case of the insert where it requires 12 input parameters but is really nothing more than an INSERT statement?

 

[jjgraf]

Agian Not really your taking very small performance increase.

Also since this site is going to have allot of traffic it mite make since for you to develop it in a 3 tier layout.

 

[TheInsider]

Thanks. I was trying to avoid the 3-tiered architecture due to my inexperience with COM. I can create simple ActiveX dlls in Visual Basic, but I don’t have any experience in designing them efficiently. I am hoping to release this website soon so I don’t have time to experiment, but I agree with you. When I get some free time, I’ll look into learning/creating ActiveX components with Visual C++ for future projects. For the moment I was hoping that I could use stored procedures as a poor man’s component.

 

[jjgraf]

If your looking at data manipulation tools look at playing with Visual FoxPro 7.0 which ships with MSDE Sql server 2000 royality free. VFP is faster than SQL server on manupulating/Querying data. It is allso the only tool MS ships that is able to do all 3 tiers in an application.

 

[TheInsider]

Thanks. I have Visual Studio 6 Ent., I'll look at FoxPro.