Dynamic SQL or Stored Procedure
-
Whether something is good or bad depends on the circumstances. It's slightly more administrative work, but if you would otherwise have to write 25,000 different extraction routines (literally), you deal with any security issues. Not only would I have had to write and those routines, but I would have to have validated them (i.e, create tons of paper work).
It's not that something is bad, but whether its costs and benefits match up to what you are trying to do.
-
I'm working with a situation with Text format field in a table where we step through each record in a table and perform a write to a flat file. To do this in TSQL, you are pretty much forced to construct dynamic SQL as you loop through each record in order to grab the Text Pointer, convert to a string, create and execute the ReadText command and then perform the write to the text file.
Of course this eventually will wind up as a stored procedure with no input/output parameters.
Aside from re-writing this in VB, DTS (did I just take a performance hit?) or some other application, utilizing Dynamic SQL is pretty much the only way to handle this in TSQL as a complete, turnkey solution.
Penny for your thoughts...
-
To me, dynamic code (SQL or otherwise) is the greatest thing since sliced bread. My applications don't have large amounts of data, but they have tremendous amounts of metadata, metadata that is itself not static. Actually, we rejected Informatica as an ETL tool because it didn't have dynamic capability.
-
Are you using an OTLT ("One True Lookup Table") or EAV ("Entity-Attribute-Value") "model" in your databases?
-
I've never heard of OTLT, but I have heard of EAV (and used it!). Basically, the setup is as follows: there is a static metadata repository that holds the table structures of the approximately 25,000 heterogeneously structured tables that comprise the ~250 clinical trials (each clinical trial is its own app). The targets are statically structured and consistently structured over the various trials. My code reads the metadata repository to create giant SQL views (this was in Oracle, I'm sure that there is something similar in SqlServer). Since the view was written in Oracle SQL only, it really minimized the validation requirements.
The source tables were normal rectangular structures. There is no reason to believe that the same process wouldn't work with EAV structured tables.
-
From SQL Server's standpoint, parameterized queries are the same as dynamic SQL. Yes, parameterized queries themselves do away with the possibility of SQL injection attacks, but you still must open security up on the tables or views being referenced. It is this lessened security in conjunction with another developer who doesn't using parameterized queries that opens the data up to a SQL injection attack.
If you must use dynamic SQL, parameterized queries arr the way to go, but as Andy has already said, static queries are the better option if given a choice.
-
You are right, political and social aspects of any development project can have negative effects. Abuse, overuse, or miuse of stored procedures can have a detrimental effect. I know it is easier said than done, but people must try to overcome these aspects and do what is possible to prevent them in order to create a more productive development environment and a better application. There are other programming methodlogies that can lead to potentially poor results when the human factor is added, but just because people may abuse or misuse a technology shouldn't prevent it from being a good choice.
-
Andy - if I understand it correctly, you said a stored proc has the advantage of having its query plan stored and reused without the need for recompile. Implicitely it sounds as though you are saying this is not the case for dynamic sql. I'd like to call your attention to SQL Servers Books Online which states:
"....When any SQL statement is executed in SQL Server 2000, the relational engine first looks through the procedure cache to verify that an existing execution plan for the same SQL statement exists. SQL Server 2000 reuses any existing plan it finds, saving the overhead of recompiling the SQL statement. If no existing execution plan exists, SQL Server 2000 generates a new execution plan for the query."
Please notice the use of the word "any" and the lack of the use of the word "stored proc".
It has been something of a consensus among developers dealing with this subject that as of SQL 7.0 the argument of "proc runs faster than dynamic sql" is no longer an issue.
Further in the same section of SQL Server Books Online it states :
" ... After an execution plan is generated, it stays in the procedure cache. SQL Server 2000 ages old, unused plans out of the cache only when space is needed. Each query plan and execution context has an associated cost factor that indicates how expensive the structure is to compile. "
My work is very OO-centric and sometimes requires the use of dynamic sql. For that reason I've worked on a series of ad-hoc tests which found considerable speed differences between dynamic sql and procs - differences in favor of dynamic sql. Here are my notes on the topic.
http://wagnerblog.com/index.php?p=43
Anyway. Keep up the good work!
Thomas
-
Unless the client application is entirely metadata-driven (which is another topic of discussion in iteself), both stored procedures and dynamic SQL (parameterized queries) can both cause require client code to be modified if the data that is being returned is now different. For example, if when using dynamic SQL, a table has a column added than is needed on the client, then the client must change it's call to SQL Server and the UI must change to handle the new field. If using a stored procedures. then the procedure must change and the UI must change to handle the new field. Six of one, half a dozen of the other.
Now some may say that you should simply use the metadata-driven client and prevent any changes at all. SOunds great on paper, but what if your application is not the only one that accesses this data. Let's say another application also needs to access the same database. Now you must enforce all programmers (of which there are many more than SQL people) to use the same methodology, and in a bigger company, this may be more difficult to enforce or even make aware to everyone involved.
In a perfect world, the data wouldn't change much after deployment (except in scheduled upgrades) because the project would have been planned out well before development began. But as you all know, the situation is often different, especially on in-house custom applications (sometimes that have grown from not al well designed prototypes). So, it is understandable to try and keep the development process as efficient as possible. But, should that come at the price of potentially sacrificing security?
If you use dynamic SQL (parameterized queries) to access SQL Server, you have a potential threat to your data. It doesn't matter if you are the most anal-retentive, diligent developer in the world, the person next to you may not be, and it only takes one non-parameterized query to open a hole up to the database. In some industries, this lessened security is not only unacceptable, but against the law, and a company that inadvertently exposes their clients data to illegitimate saources can be held criminally and civilly responsible.
So I strongly advocate using stored procedures, if for no other reason, security. We could debate every other aspect of which is better until blue in the face, but in the end, dynamic SQL opens a security hole that [well-written] stored procedures do not.
That being, said, there are cases where security isn't a concern (non-personal or non-confidential data) and so using dynamic SQL doesn't expose you because there is nothing to expose. There are times where the only way to do a certain task is via dynamic SQL. There will always be an exception to the rule "always use stored procedures", and times where it is the only option, but I personally would never hire a developer who only wanted to use dynamic SQL and refused to use stored procedures. Personal data is not something to be taken lightly, and as cool, innovative, or efficient as some may tout dynamic SQL to be, security is most important, and dynamic SQL exposes you.
-
In my apps, there is no client. These are server to server apps, ETL. Security is not an issue.
I would never hire any developer who thought there was only one way to develop anything. Mark Twain said it best: to a man with a hammer, everything is a nail. Like I said before, everything depends on what you need to optimize for.
-
I wonder how many magnificent stored procedures could have been written, while you people prefer to talk ... 😉
Simply take a look at this discussion. Is it structured? Is it clearly parametrized? Does anyone know, what to search for, what to expect from it, before you're through?
Oh, I know, children like digging in the mud. You know why? They're looking for surprises! If that's what you dynamic sql people are looking for in your databases, I can't think of any reason not to support you 😉
_/_/_/
_/_/_/ paramind _/_/_/ -
I find this dynamic SQL vs Stored Procedure discussion a bit strange, as I am an Oracle guy (getting familiar with SqlServer), because in Oracle I always put dynamic SQL in stored procedures. I'm sure that you can do that in SqlServer--can you?
Bottom line, dynamic code helps you write code that adapts to people, instead of having people adapt to your code. Whether the extra overhead is worth it, that depends on the app.
-
Here's a different twist. I find myself using more dynamic code recently because the current experience level of the people who could be expected to take over the T-SQL code when I'm inevitably hit by a bus is towards the low end, hence a desire to simplify when it shouldn't make a difference. This is in conflict with my previous place where I only used dynamic code if the situation screamed for it.
So now I write more dynamic code but I'd never take on a metadata approach in my current environment, such a porject would be dead the moment I was out the door.
One more observation on the multiple approach environment. Getting the fundamentals in place in my mind is the real battle here. I remeber working with a couple of skilled, but young, programmers that wanted to embrace the whole middle-tier for all logic approach. Great stuff, except neither they or their boss had ever fully embraced documentation. Now the unit has a lot of sweet applications that would be a nightmare to document. I'm hoping they'll eventually do it as part of learning to refactor code.
Everett Wilson
ewilson10@yahoo.com -
I use a couple of phrases quite often when planning, developing, teaching, etc. One of them is "There are always exceptions to the rule." My other favorite is "It depends". I think these quotes apply quite well here.
-
As a contractor and software developer I usually write dynamic SQL Expressly to avoid loosely coupled applications..
If I leave Stored Procs where "in house" developers can get their hands on them, the SP can be improved causing my application to break. Also the Dynamic SQL ends up with the rest of the source code under source code control. (I can do that with SPs but it's automatic with Dynamic SQL)
Using SQL parameters avoids the SQL injection attack, I've never really run into a speed issue that Dymanic SQL was the bottleneck, although I can imagine a few..
It's also easier to clean up after yourself with Dynamic SQL.. A vintage Database will have a ton of SPs and Views that seem to have no purpose BUT you can't retire them unless you know what they are for. So nobody does.. (Admit it. you don't...)
For my line of work, Dynamic SQL a better choice most of the time
Pat Tormey
New Hampshire USA
Viewing 15 posts - 46 through 60 (of 63 total)
You must be logged in to reply to this topic. Login to reply