February 15, 2008 at 10:02 am
Currently in my company all the asp pages developed by the web developers had embedded SQL statement. I want them to change to use stored procedure because it is easier if the database changes and also they do not need to put in connection in the asp page so the whole world knows the sign on id (they do not have window authentication.)
But the web developers said the performance of executing the SQL statement vs executing a stored procedure is the same. Is there anything else I can say so I can make a case that stored procedure is better than SQL statement in ASP ?
February 15, 2008 at 10:33 am
Well, their perception is that it is the same. They could be doing a great job at parameterization and getting the SQL optimizer to reuse the query plans similar to when it executes SPs but this may be something that you want to look into in more depth. Another avenue for you would be from a code managability perspective. What if you have to make a schema change? Does that require analysis on how much of the ASP code will change? It is much simpler to have all of the data access code managed in the database so you can make schema changes as needed to support scalability, performance, and new development without having major (or unknown) impact on the application. If the ASP pages were calling stored procedures, the physical table structure would be hidden from the application and you would be free to make changes without affecting the application code. What if you had a table that you decided to make a view, or vice versa? You could easily do this and then change a few SPs and your changes would be completely transparent to the application code.
February 15, 2008 at 10:47 am
Another thing you have to think about is the security. When you want to have ASP pages to execute SQL code directly (Raw SQL) instead of calling stored procs, You have give atleast select permission to the Tables they are selecting from. If they are doing DML statements as well, then you have to give permissions to the objects directly.
If they change that to Stored Procs, the user that is used on ASP page does not need any rights directly on the Tables and views. The user needs only execute permission for the stored Proc.
That will make things a bit more secure even if the User that is being used by the ASP page gets compromised.
-Roy
February 15, 2008 at 10:57 am
If we're talking about classic ASP - there's a serious "man in the middle" problem with SQL code being executed like that (on top of the "ASP is not encrypted, so user and password are stored in plain text" issue). There's some amount of improvement with some encryption in ASP.NET on the channel, so not so much, but the issue is still there (somewhat).
Like John already mentioned - that kind of behavior is often frowned upon from a design stance because they're putting everything in the same "layer". It makes it harder to refactor anything on the DB side if the code has to be changed whenever the DBA (or whoever is playing that role at the time) need to change the data layout.
The secondary idea is - like a lot of folks like to beat me over the head with - developers are not usually expected to be SQL experts (I find that remarkably stupid, but that seems to be the trend). So by them writing dynamic statements, you often end up with less than optimal SQL calls, often pissing off the server something fierce. This kind of leads into point #3
Point #3 - it also comes down to job responsibilities. When the SQL server starts having a bad day at 3AM, and the piece of SQL code causing the issue is buried in their ASP code, who fixes it? The DBA had to do all of the legwork on the server to find out what is working badly, and now needs to change the Developer's code, or wake the culprit up and they have to fix it.
Never mind the data validation, the better security (by preventing the user from having direct edit ability against the tables), and the better centralization of the SQL code in the place it's relevant.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply