Coding question about ASP vs Procedures

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

SQL Server 2000 Forums

SQL Server Development (2000)

Coding question about ASP vs Procedures

Author

Topic

SamC
White Water Yakist

3467 Posts

Posted - 2002-09-06 : 08:51:55

After writing all my queries in straight ASP/SQL for months, I've managed to move almost all of my SQL that can be moved into stored procedures. The prerformance improvement has been very good, along with some functional improvements.

I'm not so clear on my next move. I have a lot of ASP / SQL that builds custom queries for an Administrative Web-based interface. The interface allows Administrators to search by name, date, email, several other flags, and sort by any of the above.

The logic that takes the administrator selections and builds a SELECT statement builds a variety of different joins, order by AND where clauses. This logic resides in ASP to build an SQL string which is then executed in ADO - no stored procedures.

Although this code is long, stringy, bulkey and not too pretty, it isn't obvious to me that it could be rewritten to any benefit using a stored procedure. I can visualize 3 ways to solve this problem.

1 - pass the ASP generated SQL string to a stored procedure to EXEC the string
2 - directly execute the string in ASP/ADO with dataconn.execute()
3 - build a bohemeath stored procedure that accepts all the selected parameters, build the string in the procedure, then EXEC the string there.

Would like feedback on why when I rewrite this in ASP.NET I should plan on either 1 or 3 above being any better than 2. Or maybe there is a 4th solution??

Thanks,

SamC

royv
Constraint Violating Yak Guru

455 Posts

Posted - 2002-09-06 : 09:08:34

The easiest thing for you to do is item 1. If for some reason you cannot execute item 1, execute item 3. Never choose item 2, and here is why. If you execute the sql via the ado connection object, you will be returning every single record of data back to the client. There is no need for this, especially if you are implementing paging. By choosing item 1 or item 3, you have the option of returning a selected portion of the recordset without communicating all the data across the network.

*************************
Someone done told you wrong!

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-09-06 : 09:17:50

Definitely don't bother using options 1 or 2, you won't improve the situation.

One of the fourth options is: re-evaluate how the custom query page is used, and rewrite it to provide the most commonly used queries as stored procedures, possibly sacrificing some features that rarely or never get used. You don't have to write it as one big stored procedure either. If 80-90% of the queries can be represented by 5 or 6 smaller stored procedures, that's the way to go. You can still have a free-form query feature but it will be a lot simpler, but I think you should avoid this as much as possible. Survey your users on what kind of reports they need and provide as many of them as SPs as possible.

IMHO you should never provide free-form querying/SQL statements through a web interface, even though everyone might demand it. It's a lot of work and can lead to a lot of performance problems, if not outright data corruption. You also seriously open yourself up to SQL injection attacks...in fact, the security problems alone should stop this dead. If someone needs to write their own customized query but can't handle Query Analyzer, A) that's a bad sign and they shouldn't be allowed to do it; B) you can always link the SQL Server tables into an Access database for them to use.

SamC
White Water Yakist

3467 Posts

Posted - 2002-09-06 : 10:41:10

Is this newsgroup great or what?

Let me take a half step towards building the same functionality with some simple example of only one dimension of dynamically building a select statement based on web-input search parameters,

Here's a simple one.

If an administrator wants to find all users with last names of 'Jones'

WHERE Lastname='Jones' (ASP string)

or

WHERE Lastname=@Lastname (if coded in stored proc)

BUT if the spelling is in error, he may select last names of 'Jon%'

WHERE Lastname LIKE 'Jon%'

or

WHERE Lastname LIKE @Lastname

Then, if the administrator leaves the Lastname blank, none of the above apply.

The number of "where" items get's large: Firstname, Lastname, Email, Username, Department, there are numerous 'Dates' and so on.

To maximize the benefit of a stored procedure, it would be great it all combinations of the where could be explicitly enumerated so the query could be pre-compiled and an execution plan kept in memory. But there isn't any practical way to do this. In the end, a dynamic SELECT would be built in the stored proc, and executed with an EXEC.

I don't SEE any NET GAIN in Occam's Razor. The sum total of the code in ASP and Stored Procedures looks to be the same or greater. However, at least the ASP code would be reduced if all the logic were pushed out to a stored procedure.

I can't think of a better way to see the net benefit than to implement it once.

Regarding the comment made by royv, I want the recordset returned to the ASP to format a report. I don't believe there's any traffic saved by building a stored proc? Am I wrong?

Regarding Rob's comment about free-form - I may have not communicated this point well. This isn't free-form as in type a query into a textbox and I'll submit it. NEVER! This is a web page of drop-down boxes, and inputboxes where the administrator can enter a lastname if he wants to search on lastname. The query itself is built by ASP.

I believe this is about as secure as it gets?? At least, I think it's as secure as a stored procedure.

SamC

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-09-06 : 11:04:20

Some very fast and totally unscientific testing

reveals that:

WHERE LastName = 'Jones' is equivalent to WHERE LastName LIKE 'Jones'

as far as execution plans go. That would let you use the same WHERE clause for both exact and similar matches. Here's another shortcut:

CREATE PROCEDURE Search @firstname varchar(100)=Null, @lastname varchar(100)=Null,
@email varchar(255)=Null, @dept varchar(100)=Null, @username varchar(100)=Null AS
SELECT * FROM myTable
WHERE FirstName LIKE IsNull(@firstname, FirstName) AND
LastName LIKE IsNull(@lastname, LastName) AND
Email LIKE IsNull(@email, Email) AND
Department LIKE IsNull(@dept, Department) AND
UserName LIKE IsNull(@username, UserName)

You get the idea. The performance may not be great because one set of parameters may generate a plan that doesn't work with another set of parameters, and you'd have to recompile the SP to improve it. But at least you can pass any number of parameters and the WHERE clause will not need to change to accommodate them.

quote:
I don't SEE any NET GAIN in Occam's Razor. The sum total of the code in ASP and Stored Procedures looks to be the same or greater. However, at least the ASP code would be reduced if all the logic were pushed out to a stored procedure.

Ahhhh, but you DO see a net gain. Reducing the ASP code is always beneficial, so is consolidating data functions on the database itself.

Some of the benefits of stored procedures are not readily tangible. Probably the least tangible is the fact that a stored-procedure-only method is a more disciplined skill than generated SQL statements are. You are forced to think things through. This discipline will serve you far better than any "potential" limitations that you might find in stored procedures (I say potential limitations because there really aren't any, it just may seem so at first) It's not an improvement you will see overnight, but in 6 months or less, if you follow SP-only programming, you'll look back and wonder how and why you ever did it any other way. And whatever little roadblocks you might hit you'll overcome, and you'll learn a lot more for doing so.

joldham
Wiseass Yak Posting Master

300 Posts

Posted - 2002-09-06 : 11:54:22

I have to point out that the benefits of a stored procedure go beyond just reducing asp code. As Rob mentioned in his first reply, putting code in the stored procedure reduces the likelyhood of a SQL injection attack. If you haven't read about this attack, I would suggest you do a search on yahoo or google for this topic. I designed what I though was a secure web app. After reading some on SQL injection, I broke into my own site and was able to determine a whole lot about my database.

Jeremy

SamC
White Water Yakist

3467 Posts

Posted - 2002-09-06 : 12:55:40

Hey Rob,

Thank you for the feedback regarding the reduction of logic on the WHERE.

Wish I'd known that earlier this year..

SamC

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources