Dynamic SQL or Stored Procedure
-
To the debugging Shew: the sp debugger in QA is great, I've been lamenting it's absence since 6.5. Watches, param setting, breaks, autorollback - no more print.
As for dynamic sql: I don't think you can say it's bad really. I use sp's religously for transactions, cursor ops, and any complicated DML, or complex business rule implementation. BUT being also a 'web developer' dyn-sql happens everyday - it's very useful. I can see wanting to control the development effort for large groups but for the smaller teams it can be managed.
Losing out on cached query plans is bad but a bit of a fact of life for ad-hoc querying applications, protecting against sql injection is easy and a must, done best in your database access layer. Having views sitting on top of user-viewable data is definitely the right model.
-
I'm also a DBA who comes from a development background. At my company, we've been using Visual Studio .NET database projects for our new systems. There are some bugs, but I *finally* have all the developers using it to source-safe their stored procedures (whoo-hoo!). I also put my foot down about using stored procs--no more straight SQL going to the server. The security benefits are very important to me. There may come a day when a stored procedure won't work for us, but at least our developers are getting into good habits. If there's something they can't figure out, it's my job to help them.
-
Here's how we code our stored procs now that used to be created as dynamic SQL based on parameters supplied by the users. We haven't seen a measurable impact on response time using this method, and it's easy to implement. Defaults are set as the user doesn't have to always enter a value, and null values are handled if they are passed in.
CREATE PROCEDURE rpt_Example
@FLD1 varchar(20) = '0',
@FLD2 int = 0
AS
SELECT
FLD1,
FLD2
FROM
TABLE1
WHERE
1 = CASE
WHEN @FLD1 = '0' THEN 1
WHEN @FLD1 is null THEN 1
WHEN @FLD1 = FLD1 THEN 1
ELSE 0 END
AND 1 = CASE
WHEN @FLD2 = '0' THEN 1
WHEN @FLD2 is null THEN 1
WHEN @FLD2 = FLD2 THEN 1
ELSE 0 END
-
We also use the case statement for dynamic queries. The performance seems very good and is easy to maintain. If you add an option to your search criteria you just add a when clause or another case statement. It sure has cut down on the size of my stored procs.
Dave Lech
Programmer's Law #1 - The Software is NEVER Finished. -
You can integrate VSS, SQL Server, and Visual Studio with only one minor hitch. The named user that SQL Server is running as is what accesses VSS. You get source control and a full history, but you lose who changed the Stored proc. We managed this by making the developers properly document all mods.
There is a Knowledge Base article on setting this up.
-
Thanx to all of the responses.
Now can anyone please comments on the scalability of SPs and Dynamic queies i.e. if there are 200+ users accessing the system what should be done to get maximum performance.
-
SP's will always scale better. 200 users, or 200 concurrent users - there's a huge difference! I'll put up some code one of these days to show better, but you can do a simple test yourself. Write some VB code (or whatever) that just opens a recordset using a select with a couple parameters, run it 20k times, time it. Then replace that with a call to a stored proc, run the same number of times, you'll definitely find it to be faster.
Andy
-
Good article - and some good comments. Nice to have a succinct summary of why you should use sp's or sometimes not. Yes there are times when dynamic can expedite or improve the application.
And I agree with Andy that sp's scale much more than dynamic. And security is a hell of a lot better.
And I've heard all the stories of why a developer will not use sp's or will not document. The best one is where a developer told me he did not need to document the code because he would be the one making any changes.
What's the business problem you're trying to solve? -
Thanx to all of u. I am just compiling a small report based on this discussion. This helped me a a lot.
And yes Andy, as soon as I completed my report I will be testing the scenario u suggested (may even try multi threaded application to simulate concurrent calls)
Regards,
Kashif.
-
Sorry, but I disagree completely with this author.
I have written a web-based system that builds all pages from metadata. Every table in every system is searchable and editable through the same TWO pages. Lets look at the benefits of my approach:
Currently, I have about 150 different tables through the different systems.
How many update/insert/delete stored procedures would I need for all those tables? A lot.
Every column on every table is searchable/sortable includinging multiple filters being able to be applied. I have ONE codebase for all that code. It includes role and user based table and column level permissions definable as metadata.
"Advantage 1" of stored procedures - speed
The author admits this is not much of an advantage, and so far I have found no performance issues in my design.
"Advantage 2" of stored procedures - injection attacks.
Yep, definitely a concern, but I've coded for that and since every system uses the same codebase, I never have to code for it again.
"Advantage 3" security - not giving permissions to base tables
This just doesn't compute for me. You'd have to make all your views indexed, then you'd have to have separate code to look at the TABLE when doing inserts and the views when doing selects. If you are doing your inserts and deletes in a stored procedure anyway, I see no "advantage" here and if you never give an individual user permission to the database in the first place, there shouldn't be a concern anyway.
I have another page in my system which generates a crosstab type report where the user can change the selection for each axes to look at information in different ways. The queries generated by my ASP.NET pages are huge, but the performance is amazing. NO WAY are you doing that with stored procedures, at least not in a timely manner from just metadata. To make a new crosstab report in a new system takes me about 5 minutes. To make a new SYSTEM with say 5 tables in it and have edittable and searchable ROLE base permissions pages takes about 30 minutes.
I think the whole Dynamic Sql is bad movement is very mistaken - perhaps is it driven by the minority if DBA's dealing with GB sized tables or by the certification "mandatory" book smart group. Frankly, I don't know.
To them, I say - trying writing and supporting a system a month with one developer/DBA all in stored procedures. Then a year in, go add some features, perhaps new sorting or filtering capabilities. Then see which way you wished you had your system written.
Jim Craddock
Non-Certified 8 Year user/DBA of SQL Server
-
- Dynamic SQL in an app means that come rewrite time you have to write absolutely everything.
You have it exactly in reverse.
I'm a lot less likely to rewrite the app then I am to change the table structures/add new tables. Part of the reason is that it isn't a chore for me. Changing/adding tables or columns causes NO rewrite for me. Can't say that with stored procedures.
Even given enough developers, I wouldn't go for the labor intensive stored procedure approach to application development.
If you are writing stored procedures for every table in your system, you really need to look into if you are in the right field.
-
I dont think that my position on this is based on being certified OR being book smart - I deal with this stuff every day and in fact I write a lot of code, enough to know what works and what hurts. True enough that you can mitigate the security threats to a large extent. Maybe your best developers do that. In practice few bother. Just as testing and code reviews are seen to 'take more time', security is rarely a primary consideration. If you owned an enterprise, which approach would make you sleep better at night, your DBA locking things down or relying on your developers to think security and get it right every time?
Changing tables doesn't always require changing procs, depends on why, right? If you need a value from the app, you'll usually have to change the calling code anyway.
At the end of the day you're writing almost the same amount of SQL, the question is where to store it. I've seen developers do hybrid approaches that stored all the sql in a table so they could change it without recompiling. Not my favorite for many reasons, but definitely configurable. So if you throw out security considerations, throw out performance (which varies depending on what you're doing), you're down to how long does it take you to maintain the code in a proc vs inline.
We can agree to disagree! To me, it just means I need to work harder at documenting why I think I'm right. In my view the entire industry spends too much time discussing it, we should come to a point where best practices are stated, the reasons supporting it are crystal clear, and we move on to bigger issues.
-
No, this is what I'm talking about:
"Changing tables doesn't always require changing procs, depends on why, right? If you need a value from the app, you'll usually have to change the calling code anyway."
How are you going to insert into a table or update it or select from it if you change the columns when you have hardcoded statements in stored procedures? Dynamic sql based on metadata requires no changes...NONE...suck in the new metadata and off you go.
Stored procedure based solutions are essentially hard coding. Nothing has ever been said good about that. Throwing all that code out in stored procedures is just job security and in the day when such practices will get your job outsourced to India faster than you can say pinkslip, data driven solutions are a much better approach.
Also:
"At the end of the day you're writing almost the same amount of SQL, the question is where to store it. I've seen developers do hybrid approaches that stored all the sql in a table so they could change it without recompiling. Not my favorite for many reasons, but definitely configurable. So if you throw out security considerations, throw out performance (which varies depending on what you're doing), you're down to how long does it take you to maintain the code in a proc vs inline."
NO WAY am I writing as much sql. My pages write the sql automatically. I never write the sql. The page builds the update statements, the select statements and the delete statements. My code figures out how to write the sql statement and writes it. That is the nature of dynamic sql. Why would I store off select statements and update statements? Think bigger...not just a single page for a single table, but a single page for ALL tables. Code that page and then see what I mean.
I'm not saying there aren't tradeoffs, but given the time saved, they are worth the tradeoff. Most noticeably, customizing a given data entry page is limited to defining different sections of the page and how many columns across the page is in each section.
-
Jim,
I'm just a cave man. Your system of displaying and updateing 150 tables from within two pages confuses and bewilders me.
It sounds like you are implementing some sort of table editor. In this very specific case you may be correct.
The systems that I write tend to be centered around specific actions that someone can take in a certain context. My systems tend to have
- an interface that helps them understand the action they are taking and how to take it
- business logic that checks the action that they take and processes it
- data access with definable units of database work to do
-
May I add this has been a guideline for quite some time now :
The curse and blessings of dynamic SQL
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 15 posts - 31 through 45 (of 63 total)
You must be logged in to reply to this topic. Login to reply