Last post Aug 27, 2008 12:10 AM by Tulsim83
Aug 22, 2008 12:04 AM|Tulsim83|LINK
How do you know what to use for accessing the db?
Aug 22, 2008 12:33 AM|santa_1975|LINK
It simply depends on the requirement.
Stored proc has its own advantages like security, performance, executing multiple sql statements, maintaining transactions, etc.
What exactly is your requirement? Based on that we can arrive at a conclusion.
Aug 22, 2008 12:34 AM|HeartattacK|LINK
Depends on your situation. Stored procs are more efficient and they can be used to restrict access (you can give a user permission to a sproc, but not to a table). Sql statements are more flexible as you can change them without meddling with the database.
If you don't know how many fields to retrieve at runtime, they will suite you better. You have to decide which side of the tradeoff is better for you.
Aug 22, 2008 12:55 AM|vinz|LINK
How do you know what to use for accessing the db?
I would suggest you to read the following atricles below for a better idea
Determine when to use stored procedures vs. SQL in the code
Aug 22, 2008 04:38 AM|Mr^B|LINK
Valid reasons for using stored procedures.
1. Security - assign permissions to execute stored procedures rather than Select/Delete permissions on the underlying tables
2. Security - parameter driven stored procedures help prevent SQL injection attacks
3. Data Abstraction - can change underlying data structure and stored procedure, no change needed to the code
Invalid reasons for using stored procedures.
1. Speed - this just simply is NOT true and hasn't been true since SQL started caching the execution plans of in-line SQL about 10 years ago in SQL 7 - stored procedures are not quicker than in-line sql.
2. Encapsulating business logic - business logic should be nowhere near the database. stored proc processing should do that which SQL is good at (ie. set based operations) anything else should be somewhere else
Aug 22, 2008 06:18 AM|HeartattacK|LINK
I'd differ with the last two:
1. Sprocs mean that only the sproc name and parameters are passed to the database. If there are 200 lines of SQL code needed, even then, using a sproc, you pass only the sproc name and parameters. In case of inline SQL, you'd pass all 200 lines. While they'd
execute the same inside the database, the network traffic would cause the sproc to "execute" faster from the perspective of the application.
2. Sprocs can server as the last resort to ensure invalid data doesn't go in. [Well, last resort before the table specific constraints]. It's not "business logic" per se, but we can ensure data is stored and retrieved in a way we want by using sprocs.
As for the benefits, I can't agree with no. 2 either. When using SQL Commands by coding in SQL statements, we can and should ALWAYS use parameterized statements. This is not a case where sprocs are better. Of course, sprocs force you to use parameters whereas
inline sql can be used without parameters (although parameters should be used).
Aug 22, 2008 06:45 AM|TATWORTH|LINK
What exactly is your requirement?
You were asked what are your requirements?
Do you need to use dynamic TSQL? Dynamic TSQL is sometimes requires where a user can choose from 1 to n selection criteria. If you do need to use dynamic TSQL, you should use a parameterised query.
Please be specific as what version of SQL Server you are using?
What version of Visual Studio you are using?
Where are you going to deploy your site?
Aug 22, 2008 07:21 AM|connect2sandeep|LINK
Use stored procedures, when complex CRUD operations are required consisting of multple sql statements. Simple SQL can be used for insert, updated and deletion of records in a single table.
First you can start out with a simple SQL statement and as you move along mulitple SQL statements can be combined into single stored procedure, if it groups together logically.
Aug 22, 2008 07:26 AM|hs_jha|LINK
In my view there are many advantages of using stored procedures over sql text queries like
1.Better performance due to precompiled code and cached in memory.
2.Better secuity when many programmers are working in an organization and you don't want to reveal the structure of database details to them.
3.Better opportunity of organised data access code with stored procedure.
But contrary to the view of most of the new programmers stored procedure do have some serious disadvantages.
1.If you plan to port your application to other databse like ms access or oracle etc , you will have to rewrite the whole sql logic coz stored procedure like mechanism is not same in all dbms So , in that case if you write sql queries tranfer process would
be smooth coz sql ext queries are more like the same in all database management systems.
2.In case of advance search forms also writing stored procedures is ineffective where there are multiple search criteria's and frequent addition of new search options are happening to a portal.
But at the end its all upon the choice of the programmer !
Aug 22, 2008 04:40 PM|TATWORTH|LINK
I have just received an email from Microsoft about the latest edition of URL Scan - the following quote is worthy of note:
"UrlScan 3.0 is by no means a Web security cure all. Hilmo described it as a "stopgap measure" that can be used to protect the server. Security ultimately needs to be enforced in the Web application itself.
"Really the application running on the server is the only piece of code that actually knows what the SQL query is intended to do," Hilmo explained. "So the fix for the root cause is for application developers to go in and do the validation and make sure
that the SQL data that they are sending to the SQL Server is what they intend."
Aug 22, 2008 04:59 PM|shados|LINK
Better performance due to precompiled code and cached in memory
As was already mentionned, that is not true. Actually, if SPs were precompiled, they'd be -slower-, because they would not be able to take into account the current table statistics (which is even more true in SQL Server 2008, as usage of statistics is more
powerful). The query or stored procedures need to analyse the context before being compiled to a query plan, and thats cached for a certain amount of time, depending on various things. Both stored procedures and dynamic, parameterized queries will be compiled
and cached (for a time). The difference of performance vary mostly in where dynamic sql will send a bigger string to the server, while a stored procedure, since its less "dynamic", potentially will return data you don't care about (to reuse the SP), so its
a toss up.
Also, it is definately possible (and not that hard) to write stored procedures with multiple search criterias (without any dynamic T-SQL), with large amounts of case-when and other such tricks... Its fast too, its just annoying as hell.
Aug 22, 2008 05:11 PM|TATWORTH|LINK
>because they would not be able to take into account the current table statistics (
Depending on how fast the data changes, you run a script that resets the stored procedure so that its execution plan is re-evaluated when it is executed next. You this get the initial performance hit just one once a day/week but every hit thereafter is back
to peak efficiency.
Aug 22, 2008 06:52 PM|shados|LINK
My point is you don't need to do this, because stored procedures are not precompiled. They are compiled on the fly, exactly like parameterized queries (and well, any query, except non-parameterized queries cannot be reused much at all), and frequently recompiled
as the database statistics change. You have nothing to do, its done automatically, but its just a way to explain whats happening and why they are NOT precompiled (and haven't been since SQL Server 7.0, if I remember well).
Besides, its not much of a performance hit... evaluating the query plan is almost instant. It really only is noticeable with queries made from string concatenation in heavy duty systems (that gets an insane of hits per minute), then it adds up, and hurt
performance (thus why we tell people to use either SPs or parameterized queries for performance, and not only for protection against sql injection).
You would need to run such a script IF they were precompiled, else performance would suffer incredibly... but SQL Server doesn't work that way :)
Aug 27, 2008 12:10 AM|Tulsim83|LINK
Thanks everyone for replying. These posts were very helpful. I'm not sure what requirements are yet, but wanted to get more info. on the the pros/cons of each.