December 18, 2009 at 11:54 am
So, way back when I was coding ASP.NET, I was in the habit of embedding SQL code (for example, "select * from sometable where somecolumn = somedefinition"), putting it into a SQL string, and calling an execution from ASP.NET using that string. (More often than not, it would end up being the data source definition for some programming object -- a DataGrid, for example.)
A while later, it was suggested to me that I would be better off putting my (SQL) code into a stored procedure and calling it from my ASP.NET code.
It seems to be that there's a gray area as to when to use one or the other. So I got to thinking: when is it better to embed your SQL statements into your (non-SQL Server) programming code, and when is it better to call a stored procedure?
I have to admit that I'm still not entirely clear on the answer.
Discuss.
+--------------------------------------------------------------------------------------+
Check out my blog at https://pianorayk.wordpress.com/
December 18, 2009 at 12:37 pm
The answer depends on a lot of things.
First, moving the code to stored procedures helps with security, because it can be used to help prevent SQL injection attacks. Those are much more difficult to prevent in inline code. (Search "SQL injection", it's a relatively complex, and incredibly important, subject.)
Second, you can get performance improvements out of parameterized queries, whether those are built in stored procedures or in the front end. They're often easier to build as stored procedures, but that's less true now than it was 10 years ago.
Third, stored procedures can act as an API for the database. This means necessary changes to the database can all be made in the database, without having to rebuild the data access lay or front end (web page), so long as the inputs and outputs are kept consistent.
Those are some of the pros on procs.
The main disadvantage to them is that they aren't OOP, and they put code and business rules into the database, when many devs and architects believe strongly that those things belong in higher layers.
There are long, complex, sometimes vicious arguments on this subject. On a site like this, mostly populated by DBAs, you'll probably get a lot of votes in favor of stored procedures. Including my own vote in favor of those. But do realize there's a bias there, and look for opinions elsewhere as well.
If you just want my opinion on the thing, I say go with procs, and the people who think that making their database significantly less secure by opening the door to SQL injection, are doing themselves and others a disservice. I've never yet seen an implementation of SQL-outside-procs that did as good a security job as procs can provide. Not even close.
Also, less factual, more just "it seems to be this way", people who specialize in coding outside the database (.NET, etc.) tend to write SQL code that doesn't perform or scale as well as people who specialize in coding within the database. Not always true, but it sure seems to be an overwhelming trend.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
December 18, 2009 at 12:40 pm
Ray K (12/18/2009)
So, way back when I was coding ASP.NET, I was in the habit of embedding SQL code (for example, "select * from sometable where somecolumn = somedefinition"), putting it into a SQL string, and calling an execution from ASP.NET using that string. (More often than not, it would end up being the data source definition for some programming object -- a DataGrid, for example.)A while later, it was suggested to me that I would be better off putting my (SQL) code into a stored procedure and calling it from my ASP.NET code.
It seems to be that there's a gray area as to when to use one or the other. So I got to thinking: when is it better to embed your SQL statements into your (non-SQL Server) programming code, and when is it better to call a stored procedure?
I have to admit that I'm still not entirely clear on the answer.
Discuss.
I think there are some serious advantages to using stored procs over raw ad hoc SQL queries. Stored procedures allow your queries to become more efficient, as their execution plans are stored and can be reused. Stored procs also encapsulate the code, so making sure that logic, syntax, and business policies are consistent throughout your code becomes much easier. All query code can be modified in one location should any database schema change. Permissions can be granted directly to stored procs as well.
It sounds like you would probably benefit most by using stored procs because your development time may be cut down in cases where repeated queries are used.
_________________________________
seth delconte
http://sqlkeys.com
December 18, 2009 at 12:52 pm
Touching on something that Gus mentioned.
When the procs are moved into the database, there is better control on performance of the proc by the DBA. When the code is "inline" then you have to query the asp pages to find the line of code that is similar to the one causing performance problems. When moved to procs, the DBA can tune the code prior to implementation, and then again should performance sag. When it comes to trying to fix a performance issue, it is easier if (IMO) with less hands in the mix trying to fix the code. I can look at it, adjust it, get approval for deployment while not having to find a developer to research the code - give me the query, alter the query and send it back, and then have them deploy it.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
December 18, 2009 at 1:31 pm
Good thoughts. I have to admit that the issue of injection attacks didn't even occur to me.
I remember an instance where I'd prefer the embedded code direction (and I'm pushing my memory here, so bear with me). I had to run a query that involved multiple (at least three) parameters. As I recall, having to set up code to pass multiple parameters to a SP was a pain. (For all I know, it may have changed since then.) The SQL code itself was a relatively simple SELECT query, nothing hugely complex.
I found it easier to create a SQL string using those parameters (similar to a dynamic query, except that this was on the .NET OOP side) than it was to pass the parameters to the SP.
Of course, back then, I was better versed in ASP.NET than I was with SQL Server.
Anyway, might this be a scenario where it'd be better to embed SQL code? (I'm not necessarily arguing in favor of embedding, I'm just asking . . . )
+--------------------------------------------------------------------------------------+
Check out my blog at https://pianorayk.wordpress.com/
December 18, 2009 at 1:44 pm
Knowing what I know (and don't as well), I'd prefer to keep away from creating dynamic sql in the application. I would prefer to use stored procedures to encapsulate the dynamic code. There are things that we can do on the database side to minimize SQL Injection attacks, for one. Also, at the database level, we can do things to tune the queries that may not be done at the application level. And, another plus of using stored procedures, we don't have to give applications direct access to the tables in the database. Provides another level of security.
December 18, 2009 at 3:12 pm
Wow, major can of worms opened here. As GSquared mentioned, on this site we are mostly DBA's so we lean toward stored procedures. Paul Neilsen has an excellent blog post, Why Use Stored Procedures, and I have a 3 part series on my blog about Maintaining Security and Performance Using Stored Procedures, here's part I.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
December 21, 2009 at 7:27 am
Ray K (12/18/2009)
Good thoughts. I have to admit that the issue of injection attacks didn't even occur to me.I remember an instance where I'd prefer the embedded code direction (and I'm pushing my memory here, so bear with me). I had to run a query that involved multiple (at least three) parameters. As I recall, having to set up code to pass multiple parameters to a SP was a pain. (For all I know, it may have changed since then.) The SQL code itself was a relatively simple SELECT query, nothing hugely complex.
I found it easier to create a SQL string using those parameters (similar to a dynamic query, except that this was on the .NET OOP side) than it was to pass the parameters to the SP.
Of course, back then, I was better versed in ASP.NET than I was with SQL Server.
Anyway, might this be a scenario where it'd be better to embed SQL code? (I'm not necessarily arguing in favor of embedding, I'm just asking . . . )
I'm not as familiar with ASP.NET as I probably should be, so this may or may not be relevant/correct.
In VBA, you can create an SQL connection object, and a query object that inherits the connection from it. Then you can define it as a stored procedure, add parameter definitions and values to it, including directions (input/output) for the parameters. When I first started using this instead of calling SQL strings directly on the connection, it was less convenient and took some getting used to. After a while, I got comfortable with it, and even built a dynamic version that I could call with a string of parameter values and it would build the connection for me, all without dynamic SQL (and no chance of injection).
So, if it can be done in VBA, I have to assume it can be done in C# or VB.NET or whatever.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
December 21, 2009 at 7:46 am
Here's my reason for recommending stored procedures.
If the procedure is performing poorly, someone (the DBA, the developer, an outside consultant, etc) can take the proc, tune it, ensure that it returns the same it did before, test and implement the changes, without having to touch the front end app at all. Now, that may not be such an issue for a web app where you just update the aspx pages on the server, but think of a client app that is used by hundreds of people. Compare the work to compile a new release, get everyone updated and using the new version vs making the change in the database.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 21, 2009 at 7:49 am
Seth Delconte (12/18/2009)
I think there are some serious advantages to using stored procs over raw ad hoc SQL queries. Stored procedures allow your queries to become more efficient, as their execution plans are stored and can be reused.
That was true pre-SQL 7. In the current versions of SQL the execution plans of ad-hoc and stored proc queries are both cached and the rules about how long they're cached for and what triggers a recompile are pretty much the same. The only real difference is what's considered a 'matching' query for the purposes of plan lookup.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 21, 2009 at 7:55 am
GSquared (12/21/2009)
I'm not as familiar with ASP.NET as I probably should be, so this may or may not be relevant/correct.In VBA, you can create an SQL connection object, and a query object that inherits the connection from it. Then you can define it as a stored procedure, add parameter definitions and values to it, including directions (input/output) for the parameters. When I first started using this instead of calling SQL strings directly on the connection, it was less convenient and took some getting used to. After a while, I got comfortable with it, and even built a dynamic version that I could call with a string of parameter values and it would build the connection for me, all without dynamic SQL (and no chance of injection).
So, if it can be done in VBA, I have to assume it can be done in C# or VB.NET or whatever.
Actually, it does sound relevant. Since most of my .NET code was data-centric, I created a connection object immediately when the page was opened.
Which way I went (between embedded code and stored procedures) usually depended on the scale. If it was just a small amount of SQL code and/or a small amount of data from SQL Server, I would embed the code. If it was complex SQL code or a large amount of data, I would call a stored procedure.
Of course, like I said, I was better-versed on the .NET/OOP/client end back then. Now that I have more experience with SQL Server, I'd probably go the SP route.
This actually brings up another thought. Hypothetical situation: let's say my .NET OOP code and my SQL Server reside on different servers. I have a large amont of data (let's say, 5,000 records) that needs to be processed. Both my SQL Server and my .NET are capable of processing this data the way I want. However, I'm concerned about performance.
Would it be better to process this in SQL or ASP.NET?
As DBAs, I'm assuming most of you would say SQL, but I'd be curious to hear your thoughts.
(Gail: I was in mid-type when you posted your last posts, so I didn't get to see them before posting this!)
+--------------------------------------------------------------------------------------+
Check out my blog at https://pianorayk.wordpress.com/
December 21, 2009 at 8:05 am
Ray K (12/21/2009)
This actually brings up another thought. Hypothetical situation: let's say my .NET OOP code and my SQL Server reside on different servers. I have a large amont of data (let's say, 5,000 records) that needs to be processed. Both my SQL Server and my .NET are capable of processing this data the way I want. However, I'm concerned about performance.Would it be better to process this in SQL or ASP.NET?
As DBAs, I'm assuming most of you would say SQL, but I'd be curious to hear your thoughts.
(Gail: I was in mid-type when you posted your last posts, so I didn't get to see them before posting this!)
This is a classic, it depends. How is it being processed? If you need to get aggregates and just return them to the application then I'd say SQL Server, but if you have to do some kind of RBAR, I'd say .NET. Typically the recommendation is to only return the data the application needs and no more.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
December 21, 2009 at 8:06 am
What type of processing are we talking about? If it is updating data in the database, keep it on the server. If it is processing for presentation, put it on the presentation layer.
December 21, 2009 at 8:13 am
Ray K (12/21/2009)
Hypothetical situation: let's say my .NET OOP code and my SQL Server reside on different servers. I have a large amont of data (let's say, 5,000 records) that needs to be processed. Both my SQL Server and my .NET are capable of processing this data the way I want. However, I'm concerned about performance.Would it be better to process this in SQL or ASP.NET?
SQL, for two reasons.
1) SQL is designed for data processing. It's very good at it. ASP is a front-end language. While it can do the data processing, it's not it's core job.
2) If all of those 5000 aren't needed for display or if after processing those rows have to be saved back to the DB then doing it in SQL saves the expense of sending loads of data all over the network
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 21, 2009 at 8:44 am
Yeah, I purposely made my scenario generic, and I realize that there's a lot of "it depends" involved. (Lynn: to answer your question, it could be either -- almost all of it is for presentation, and a lot of it is expected to be updated. The scenario I was thinking of -- a project I used to work on at my previous job -- was way too complex to put into a short and simple "what if" scenario. However, I suspect I might come across a similar situation here in my new job, so I figured that it'd be a good idea to learn as much as I can before that situation rears it's head.)
Back then (and before I knew any better), I pretty much downloaded all my data to my .NET using a simple, embedded SQL query and did all my processing (using RBAR -- again, I didn't know any better) on the .NET server for presentation. Occassionally, updates would be required, some of it manual using my presentation, and some of it automatic on the SQL back-end. For manual updates, I would do the processing in my .NET code and put it into a SQL statement ("update [some table] set [some column] = [some value]," etc.), then send it back to my SQL server for processing.
Of course, now my knowledge of SQL is light-years ahead of what it was just a couple of years ago -- your classic "if-I-knew-then-what-I-know-now" type of thing. Now that I have more SQL experience, I think about my old project and say to myself, "what was I thinking?!?"
I'm actually learning a lot from this thread. Thanks! Keep your thoughts and opinions coming!
+--------------------------------------------------------------------------------------+
Check out my blog at https://pianorayk.wordpress.com/
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply