Dynamic SQL or Stored Procedure
-
My guidance to our developers is to use stored procs for all data access - unless it's dynamic sql. For those we take a look at the situation, first to make sure dynamic is really required, if so to see if any of the work around techniques are worth considering, and then finally to see if we think it should go in a proc or on the client (often a web page).
I use the SQL debugger occasionally, works ok for me. I debug only on dev server though - holding locks can be an issue.
Andy
-
From the gent with the ORs to David.Poole (or anyonw who may wish to comment on this one):
I'll definitely try your suggestions for making the queries faster - but you still havn't addressed the main issue here - how to make a static query with optional search-conditions? This was the main PRO for using dynamic SQL in the first place..
- Avi
-
I've found the ORs to be an adequate solution for most things that I need to allow users to "search". In most apps I have worked with, most of the tables that allow a more or less free-form search are relatively small. Performing a table scan on a table that occupies 6 data pages isn't a big deal in my apps.
There is also the case of very large tables. Scanning 10 million pages is very big deal. For these kinds of tables, I find that both dynamic SQL and ORs can be problematic. In these cases, I tend to want users to define EXACTLY what it is they need to know based upon the tables. Specifically written queries are a necessity for performance and indexing strategies in these cases.
Chris Cubley, MCSD, MCDBA
quote:
From the gent with the ORs to David.Poole (or anyonw who may wish to comment on this one):I'll definitely try your suggestions for making the queries faster - but you still havn't addressed the main issue here - how to make a static query with optional search-conditions? This was the main PRO for using dynamic SQL in the first place..
- Avi
-
As far as optional arguments/parameters is concerned, if there are a large number of them then I switch to using ORs simply because writing a SQL stored procedure that produces a union query for every possibility is simply not practical.
I tend to specify non null default values for arguments in stored procedures.
Let us suppose that I have an optional numeric parameter that is used in a search.
CREATE PROC usp_GetSearchResults @lStatus Int=-1 AS
SET NOCOUNT ON
SELECT A.Field1....A.Fieldn
FROM dbo.tbl_ AS A
WHERE A.status= @lStatus
UNION
SELECT A.Field1....A.Fieldn
FROM dbo.tbl_ AS A
WHERE @lStatus=-1
GO
An alternative is
CREATE PROC usp_GetSearchResults @lStatus Int=-1 AS
SET NOCOUNT ON
IF @lStatus=-1
SELECT A.Field1....A.Fieldn
FROM dbo.tbl_ AS A
ELSE
SELECT A.Field1....A.Fieldn
FROM dbo.tbl_ AS A
WHERE A.status= @lStatus
GO
I tend to use the IF...ELSE more when I am trying to retrieve information from text fields particularly when I am using a LIKE clause as per a contacts database.
-
quote:
It's much easier to just write the code in the development IDE (where you are in the first place). The other complaint is that often it is not worth the time and trouble of getting permission from the DBA or overcoming other buracratic policies just to write a simple query. Also, I think that many of the developers feel that because they don't really know T-SQL very well or at all and really would not care to learn it since dynamic SQL has worked just fine so far...Ryan,
Good to hear that you adhere to good development practice, even if the rest of your team doesn't. As a DBA who's also an ex-developer, I find that it's usually the developers who write the sloppiest, most unmaintainable, bug ridden code who are the same ones who use dynamic SQL all of the time, don't believe in using source control, and can't be bothered with asking the DBA to write a piece of TSQL for them. Usually I find that it's people who are trying to cover up their coding inadequacies who bumble their way through trying to do something which they are woefully inadequate at doing ; it's more often the brighter, more disciplined developers who are willing to put their hands up, ask and learn if they're not sure how to do something, who do things right.
Guess it all comes down to bad developers having to save time because they can't be bothered to learn how to code properly. This means they have to 'just get it done' as quickly as possible and churn out code to cover up their lack of ability, instead of doing it right first time.
Problem is, management think they're great because they produce the most code. But the unseen downside is that the company they work for pays for it ten times over when it comes to maintaining the product, because it's SO much more costly to fix bad code than to take a little longer to write it well in the first place.
Unfortunately, the IT sector is full of blaggers like that, you just get to know what the warning signs are over time...
Edited by - jonreade on 04/23/2003 04:16:39 AM
Jon -
Excellent article! I debate often with myself over the use of Dynamic SQL and Static SQL. We use both in our stored procedures and applications. Right now I'd say about 40% of our stored procedures are static or mostly static (we have about 700+). This is up from 3 years ago when I started here and only 20% or less used all static SQL.
Our security is such that we don't have to change it at all when desinging Dynamic SQL and most of the people who have a say in the matter don't want to reduce database permissions to tighten security. I've indicated many times the added security benefit but to no avail. So, since security isn't likely to change I use dynamic or static freely as seems easiest to maintain as well as simple to develop and read. Lately I've leaned more toward the Dynamic SQL than I had a year ago. Our testing includes testing all text parameters with a single quote. If this breaks the query then we've forgotten to trap for it and if you don't trap for that single quote then that Dynamic query can all an injection attack. We simply replace all single quotes with two and then the query can't break and no injection attack is possible.
Robert W. Marda
SQL Programmer
bigdough.com
The world’s leading capital markets contact database and software platform.
Robert W. Marda
Billing and OSS Specialist - SQL Programmer
MCL Systems -
Very good article.
I was needing one to show it to my team lead, manager, etc. We are currently in phase of converting SPs (2400+) to dynamic queries (not all but most of them). The problem was that nearly all of the business was in SPs so there were problems like locks for very long periods or even deadlocks. Can u give some suggestions on what type of SPs should be left as it is and what type should be converted to Dynamic Queries?
Regards,
Kashif.
Edited by - kashif on 04/24/2003 04:12:19 AM
-
You would be better off finding what it is that causes the locks and modifying the stored procedures accordingly.
In addition to the points raised by Chris Cubley I should like to add the following about dynamic SQL are
- Exactly the same query could be written many times by the developers. Duplication of effort!
- There are different ways of retrieving exactly the same data, each with different performance overheads.
- Dynamic SQL in an app means that come rewrite time you have to write absolutely everything.
We have just had to rewrite a huge application that used stored procedures exclusively, no dynamic SQL calls at all.
Because the stored procedures provided a well documented interface to the database the rewrite was simply a case of redesigning the user interface and using a new method of calling the existing stored procedures.
The key to it is having that well documented interface. If necessary buy a copy of ROBOHELP and make a help file that your developers all have access to.
I would agree with Jon Reade's point that it is the sloppy programmers who tend to want to use dynamic SQL. I would also add that poor project planning leads to the "just get it done quick" mentality that fosters dynamic SQL in apps approach.
Proper Planning Prevents Piss Poor Performance!
-
I agree with all the points in the article and really believe developers should do more to learn how to use both stored procs and SQL Server to their advantage. I am not sure I understand why developers bother to use T-SQL in their development lives if they can't make the effort to learn it properly and at least understand the basics of how queries and SQL Server work.
Being from a web development background I have written both dynamic SQL for Access databases and stored procs for SQL Server. A good DBA is a willing DBA, who will help a developer make the most of using stored procs and SQL Server, but the responsibility is on the developer to approach the DBA in the first place and ask for their advice and assistance. They will become better developers for taking some time to expand their knowledge.
Having had to integrate dynamic SQL into ASP web pages, it gets messy. All those tangled lines of HTML, VBScript, JavaScript and SQL create a lot of code. By taking the SQL out of the pages, the code size is reduced, overhead is lowered, the potential for accidentally deleting a line of SQL is removed and all the SQL is safely stored in one place on the SQL Server, where it can be reused without major duplication of code.
And as for developers who can't be bothered to use Query Analyzer, they can be missing out on color-coded SQL keywords and syntax checking which saves them development time in the long run. I don't think developers understand the security issues of dynamic SQL either, as they don't really know much about SQL Server. I took the time out to learn some basics and thankfully have found DBAs to be more than helpful to me, and as a result I am now doing some SQL Server training and it all makes me a much better developer. Hence why I am here
-
quote:
Often we find ourselves with search screens that allow the user to enter one or more search criteria. Since we can't predict what parameters they'll pass we build the SQL statement in the stored procedure and dynamically build the WHERE clause based on what they pass in.We have this situation and we use a fixed stored procedure instead. Every optional parameter is assigned a default value before it is used in the query. For example, if I have parameters (all optional) for an order query that include Customer ID, Order date (from and to), and Salesman ID, I could have a stored procedure similar to the following:
Create Procedure OrderQuery
@Customer varchar(10) = null,
@DateFrom datetime = null,
@DateTo datetime = null,
@Salesman varchar(6) = null
As
If @Customer Is Null Or Len(@Customer) = 0
Set @Customer = '%'
If @Salesman Is Null Or Len(@Salesman) = 0
Set @Salesman = '%'
If @DateFrom Is Null
Set @DateFrom = '1900-01-01'
If @DateTo Is Null
Set @DateTo = '9999-12-31'
Else
Set @DateTo = DateAdd(day, 1, @DateTo)
Select o.*
From Order o
Where o.Customer Like @Customer
And o.Salesman Like @Salesman
And o.OrderDate >= @DateFrom
And o.OrderDate < @DateTo
With appropriate indexes we find the stored procedure performance is very good. The execution plans generated are consistently faster than dynamic SQL, at least for our database structure.
Any comments?
-
Dynamic queries and stored procedures have the same potential for blocking and deadlocks. To resolve these problems you must analyze what is happening as each query in the various stored procedures is executed and as each dynamic query is executed. Converting all your SP's to dynamic queries won't necessarily solve your blocking and deadlock problems.
One option we have used to eliminate most blocking is to use WITH (NOLOCK) after each table name in a query. This allows for dirty reads of data and means that no SELECT query has to wait for an UPDATE or DELETE query thus eliminating a lot of blocking.
Robert W. Marda
SQL Programmer
bigdough.com
The world’s leading capital markets contact database and software platform.
Robert W. Marda
Billing and OSS Specialist - SQL Programmer
MCL Systems -
I experimented with query hints on tables because I had a large batch job that is only run when no-one else is on the box.
The original query showed up a huge number of page locks so I used TABLOCK with the hope that the query would run faster. There was no significant difference.
I did a number of tests doing 10,000 updates and measuring the average execution time and standard deviation.
The average time decreased marginally with TABLOCK but taking into account the standard deviation of the execution time this time decrease wasn't significant.
Eventually I concluded that locking strategies are solely for data protection, not for performance (with the obvious exception of users perceiving the app to be running slower because their function is waiting for a resource to unlock).
-
We are currently not using stored procedures at all, because there seem to be no way to effectively use a tool like visual source safe to protect them. I need a version control for the SQL statements of our application, too.
Also we have a highly normalized database design and very different search criteria. Our dynamic SQL "engine" is using an additional "abstracion" from SQL so the developer can define search criteria without knowing SQL. Our engine then builds up the JOINs for the concret SQL. This results in about 4 to 6 possible constellations of joins (performance optimized, only joining tables that are used) with multiple different WHERE statements. I think a stored procedure would need to be very complex to do something like this - and as far as I know, I can't debug stored procedures in a single step mode like I can in VB.
We are thinking about dynamic building of stored procedures (with reusage of them) so that we can tune the application generated SQLs without recompilation, but there we have the version control problem again. Also this will add another step to deployment (and testing): installation of stored procedures.
When we find a solution for these problems we will move to stored procedures as fast as possible.
-
I use Source Safe to store the scripts for all my objects. As the scripts are simply text files this causes no problems.
In terms of debugging SQL I tend to break down stored procedures into small sections and test the smaller sections prior to reassembling them as the one big proc.
In my development environment I am dealing with a small subset of data so this doesn't take too long.
I have to say that I don't like the Source Safe user interface and I always keep backup copies of the active instance of my objects because I have had Source Safe wipe out my work on several occcassions.
I use it because it is the only tool of this nature that I have got and it came free with Visual Studio. My management seem to think that Microsoft is the only solution.
-
We use VSS. Ok solution, wish they'd do an update though, that circa 1998 interface and all. I agree it will store scripts as text files, the piece that is missing is decent VSS integration with EM/QA. In practice Im not sure how much that matters. Should always be a script for changes to production, DBA just has to be disciplined enough to check in the new version. Bill Wunder has some code posted here on the site that will script everything out and check into VSS. Lately I've been experimenting with something similar, only storing in local tables. Beyond version control, I often have 3 or 4 different variations of a proc that match to specific versions, need a way to figure out which procs are live, deprecated, can be deleted. Maybe you could do that with labels in VSS, just seemed clunky.
Debugging works fine for me, not a reason not to use procs in my opinion.
Andy
Viewing 15 posts - 16 through 30 (of 63 total)
You must be logged in to reply to this topic. Login to reply