August 30, 2011 at 7:28 am
1) dynamic sql is an INCREDIBLY POWERFUL AND EFFICIENT solution to a number of classes of problems. I strive mightily to always use the right tool for the task and dynamic sql is that more often than most will acknowledge.
2) I have a long-time consulting client (11+ years now) that has built an amazingly capable and flexible data indexing/search subsystem that uses stored metadata to build out the indexing/search code - mostly at runtime. They process and access terabytes of total data volumes across many databases in a system that can be configured by business analysts stepping through the new-client setup processes they have documented.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
August 30, 2011 at 7:40 am
TheSQLGuru (8/30/2011)
1) dynamic sql is an INCREDIBLY POWERFUL AND EFFICIENT solution to a number of classes of problems. I strive mightily to always use the right tool for the task and dynamic sql is that more often than most will acknowledge.2) I have a long-time consulting client (11+ years now) that has built an amazingly capable and flexible data indexing/search subsystem that uses stored metadata to build out the indexing/search code - mostly at runtime. They process and access terabytes of total data volumes across many databases in a system that can be configured by business analysts stepping through the new-client setup processes they have documented.
Agreed. Used correctly dynamic sql can be "incredibly powerful and efficient". I don't think anybody suggested otherwise. However, in the example presented it is not used correctly. The example in this thread is a good example of how NOT to use dynamic sql.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 30, 2011 at 11:25 am
Francis McFaul (8/29/2011)
I think that most of their Developers are application developers who know some Transact-SQL. I haven't heard of any dedicated SQL Server database developers, only SS DBA's.
I'm a "dedicated SQL Server database developer" ("dedicated" both because I am committed to my job and because my work consists primarily of SQL Server database programming!). In my experience, "application developers who know some Transact-SQL" often produce sub-optimal T-SQL. It's not their fault. They usually don't have either the background or the time to comprehend SQL's declarative, set-based paradigm, so they write T-SQL that mirrors the procedural, iterative paradigms of their "native" programming languages. When that code produces the expected result, they check the items off their "to-do" lists and move on. Usually, the weaknesses of their code become palpable only when transaction volume on the database server exceeds some tipping point and performance suffers, and sometimes, not even then because companies often respond to "the SQL Server CPU load/memory usage/IO throughput is pegged at 100%" by purchasing bigger/better/faster hardware. With plenty of new server overhead, the sub-optimal T-SQL runs fine until the load on the server exceeds the next tipping point and the cycle continues.
I wholeheartedly agree with last two sentences of this SSC editorial:
http://www.sqlservercentral.com/articles/Editorial/75503/
"However, if only one aspect of an application’s architecture is assigned to a specialist, I believe it should be the database programming. The type of thinking required for maximizing performance is not the same for database programming as that needed by the other tools."
Jason Wolfkill
August 30, 2011 at 12:13 pm
wolfkillj (8/30/2011)
Francis McFaul (8/29/2011)
I think that most of their Developers are application developers who know some Transact-SQL. I haven't heard of any dedicated SQL Server database developers, only SS DBA's.
I'm a "dedicated SQL Server database developer" ("dedicated" both because I am committed to my job and because my work consists primarily of SQL Server database programming!). In my experience, "application developers who know some Transact-SQL" often produce sub-optimal T-SQL. It's not their fault. They usually don't have either the background or the time to comprehend SQL's declarative, set-based paradigm, so they write T-SQL that mirrors the procedural, iterative paradigms of their "native" programming languages. When that code produces the expected result, they check the items off their "to-do" lists and move on. Usually, the weaknesses of their code become palpable only when transaction volume on the database server exceeds some tipping point and performance suffers, and sometimes, not even then because companies often respond to "the SQL Server CPU load/memory usage/IO throughput is pegged at 100%" by purchasing bigger/better/faster hardware. With plenty of new server overhead, the sub-optimal T-SQL runs fine until the load on the server exceeds the next tipping point and the cycle continues.
I wholeheartedly agree with last two sentences of this SSC editorial:
http://www.sqlservercentral.com/articles/Editorial/75503/
"However, if only one aspect of an application’s architecture is assigned to a specialist, I believe it should be the database programming. The type of thinking required for maximizing performance is not the same for database programming as that needed by the other tools."
I have known plenty of "dedicated sql specialists" who couldn't write decent sql to save their lives and developers who could write some of the most elegant and efficient sql. I have also known some developers that can't even spell sql. It doesn't matter what somebody's job title or their current position is. It all comes down to the ability of the individual. Just because somebody is a "specialist" it does not provide any actual indication of their ability.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 30, 2011 at 3:13 pm
Sean Lange (8/30/2011)
wolfkillj (8/30/2011)
Francis McFaul (8/29/2011)
I think that most of their Developers are application developers who know some Transact-SQL. I haven't heard of any dedicated SQL Server database developers, only SS DBA's.
I have known plenty of "dedicated sql specialists" who couldn't write decent sql to save their lives and developers who could write some of the most elegant and efficient sql. I have also known some developers that can't even spell sql.
So have I. That's why I tried not to make categorical statements about any group of people. I didn't say ALL application developers write bad SQL or that application developers are inherently incapable of writing good SQL. I did try to say that application developers often (NOT "always") write bad SQL because their experience and skill sets tend to be centered around other programming languages that serve entirely different purposes. I wasn't criticizing application developers who do the best they can with SQL because I know that SQL is not their primary skill set, and I admire application developers who also write solid SQL. While some master carpenters probably are capable of sturdy, attractive masonry, I wouldn't expect them to build beautiful, sturdy masonry or judge them harshly if their masonry skills seem limited. After all, they're master carpenters, not masons. I apologize if I didn't communicate this clearly.
It doesn't matter what somebody's job title or their current position is. It all comes down to the ability of the individual. Just because somebody is a "specialist" it does not provide any actual indication of their ability.
I also didn't intend to suggest that there's some mystical power in labels or job titles. Merely calling oneself a "SQL specialist" or sticking someone in a "SQL specialist" job doesn't magically confer the ability to write good SQL. As some folks around here say, "You can throw your boots in the oven, but that don't make 'em biscuits." There certainly are some people posing as SQL specialists who couldn't SELECT their way out of wet paper sacks, and there are plenty of people who go by all kinds of other titles who write excellent SQL.
I stand by my point, though, which is that database programming and application development differ in many significant ways, and it makes sense to give each job to a person with the skills and experience most suited to the task. Unfortunately, in my experience, many software development companies would never think of hiring a database programmer with the idea that he can just knock out the UI and application layers, too, but will readily assume that the .NET developers writing the UI and application layers can handle the database programming regardless of whether they've ever written a line of SQL at all. Some application developers have pretty decent SQL chops and do a good job with it, but others muddle through the database work to produce some SQL that "works" in that it returns the expected result but performs poorly, sometimes shockingly so, than SQL written by someone who has developed an in-depth understanding of RDBMSs and SQL through years of experience and study. While not all SQL programmers are created equal, past results do not guarantee future performance, your mileage may vary, etc., etc., in general, companies should expect better outcomes when they intentionally assign database programming to people with strong skills and extensive experience in SQL (the "SQL specialists") rather than leaving that job to people whose primary skills and experience lie in other areas.
Jason Wolfkill
August 30, 2011 at 3:23 pm
Sean Lange (8/30/2011)
I have known plenty of "dedicated sql specialists" who couldn't write decent sql to save their lives and developers who could write some of the most elegant and efficient sql. I have also known some developers that can't even spell sql. It doesn't matter what somebody's job title or their current position is. It all comes down to the ability of the individual. Just because somebody is a "specialist" it does not provide any actual indication of their ability.
Interestingly enough - to me a "SQL specialist" is an oxymoron, since you'd have to master multiple major specialties, making you (come on, say it with me) a generalist. Now - if you were to tell me you were a specialist in writing efficient queries or designing scalable data structures, NOW you're a specialist. Otherwise, you're likely to be just as much of a generalist as the others.
I've often been tagged as a "generalist" because I happen to navigate in an area that crosses the boundaries between data and application (so yes - I used to be described as a developer, but I know a fair amount about writing data and persistence layers that don't bring the house down when you lean on them, actually have relational integrity, etc...). I'd have to say, just because every skill you have acquired doesn't start or end with "SQL" doesn't necessarily make you any more or less of a specialist (although there might not be a dandy premade title to state what you are a specialist in).
(edit putting this back to not lose Sean's post).
----------------------------------------------------------------------------------
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?
August 30, 2011 at 3:25 pm
As some folks around here say, "You can throw your boots in the oven, but that don't make 'em biscuits."
That is funny!!! I think we are 100% on the same page and that quote says it exactly.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 31, 2011 at 7:16 am
Sean Lange (8/30/2011)
TheSQLGuru (8/30/2011)
1) dynamic sql is an INCREDIBLY POWERFUL AND EFFICIENT solution to a number of classes of problems. I strive mightily to always use the right tool for the task and dynamic sql is that more often than most will acknowledge.2) I have a long-time consulting client (11+ years now) that has built an amazingly capable and flexible data indexing/search subsystem that uses stored metadata to build out the indexing/search code - mostly at runtime. They process and access terabytes of total data volumes across many databases in a system that can be configured by business analysts stepping through the new-client setup processes they have documented.
Agreed. Used correctly dynamic sql can be "incredibly powerful and efficient". I don't think anybody suggested otherwise. However, in the example presented it is not used correctly. The example in this thread is a good example of how NOT to use dynamic sql.
I honestly have no idea if the 216-line code the OP pasted into this thread is good or bad code. The only time I wade through that much code (especially exceptionally complex code such as that) to try to understand it is when I am being paid to do so. :hehe:
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
August 31, 2011 at 8:21 am
I actually designed an app that worked in a similar way. The application would read the sql out of a (developer maintained) table and present the results in the front-end. It went as far as supporting parent-child relationships is a tree/list view fashion. Incredibly flexible and powerful. The motivation for this was enabling dynamic configuring of the app without having to recompile it.
The app was developed with the perspective that the business would change it's mind constantly & with little warning. Ultimately, this could all have been avoided if the business could have provided concise requirements (or any at all) & committed to reasonable deadlines.
Truly the reflection of a dysfunctional company.
August 31, 2011 at 9:16 am
Whenever a team of ppl using a foreign (brainwise) power tool and compounded over time, voi'la a big mess -> time to call the experts.
Neverthelss, power tools juggling can be useful, fun and possibly insane!
For a few projects, I catalog/metacod sql codes (sprocs, udfs, predicates, templates,settings) in a db-driven env for:
a) conditional compilation/deployment/re-deployment schema/code/etc.
b) conditional execution where criterias base on security role and or supplied from app server.
c) when all codes and securities reside on app server(s), where db servers is merely a storage - therefore dynamic sql is the only viable option. Rant: Msft went overboard with this approach on their sharepoint/titant/crm/dynamic. Ain't easy nor fun trying to reverse-engineer their sql code (almost all logics are in managed codes).
d) pipeline processing - where clr functions and sql codes configed to exec in threads - similar to function pointers invoke.
Procrastinating pays now, design an Accounting engine (what wheels?) is a writeoff!
August 31, 2011 at 12:46 pm
Back to the question of the OP:
I have used this technique (or part of it), and as a solution to precisely the problems mentioned by the first couple of answerers (to improve maintainability and understandability.) I have also seen attempts to do this on the scale you seem to be talking about, but anecdotally the attempt was abandoned.
Specifically, I have written stored procedures that took SQL snippets and parameters stored in tables, and used those to construct dynamic SQL. A key difference is that the dynamic SQL created database objects like stored procedures and triggers, and it was the procedures that were executed at run-time, not the code generator. The key benefit of this technique is that it creates code which exactly follows a particular pattern. This means you only have to invest the time to understand that pattern once, and it allows you to save on testing, since you can test that really complicate pattern once, and fix the bugs for everything. (it also enabled me to use automated testing against the domain, but YMMV)
There are some features of their code that I do not like:
The table aliases declared in one field, and referenced in the sql stored in another field, so you have data that must be consistent, but no way to verify the consistency;
The code they generate and execute will thrash the execution cache;
The data(sql snippets) they store does not reference or take advantage of the structure of the target tables, so the person putting the code snippets into the table has to know way too much about the schema, and might as well be writing the resulting SQL code directly, also any change to the schema could break code with no ability to find out what broke;
Finally, the stored proc generating and executing the sql relies on actual data values, etc. that are not parameterized. Again, it appears that the person writing the dynamic code generator might as well have written the dynamic code directly.
In contrast to what appears to be the general tilt of the discussion so far, dynamic SQL is _not_ inherently poor performing, is not automatically a security hole, and can make for code that is easier to understand than it would be otherwise. I think it is quite difficult to meet all those goals, and I would never think that it should be used for everything, it's a tool that has it's place. In the case of your current employer, it looks like they might be able to benefit from converting some of their multi-bit-screwdrivers to hammers, however, I would not assume that they don't have a good reason for doing it sometimes, unless I understood why they thought it was a good idea first.
edited for clarity.
Dan Guzman - Not the MVP (7/22/2010)
All questions have to be prefaced by Server version and 'according to MS Docs' or 'my own personal opinion based on how much detail I felt like digging into at the time.'
August 31, 2011 at 1:36 pm
Francis,
I've worked with a POS (Point Of Sale) system that uses text stored in tables for use in Dynamic SQL. It's mostly for appending to the WHERE clause (filtering) in creating reports. I've found nothing wrong with this sort of Dynamic SQL. As Kevin mentioned a couple of pages ago, Dynamic SQL can be a very powerful tool. One thing it avoids is parameter sniffing. You almost always get a good query plan out of it regardless of the parameters. It's also the only way I've every figured out to do a pivot with a variable number of columns.
It can, however, be over done. I briefly scanned the code in the original post and I think this type of Dynamic SQL was way over done. I would hate to have to support it. The designers were probably looking for a level of abstraction where they could just manipulate the data in tables to get the desired results and not have to change any stored procedure code. Kind of like a home grown ORM tool.
This type of design I'm sure permeates the whole system and is very difficult to change without re-writing the whole thing at once. Good luck!!
Todd Fifield
Viewing 12 posts - 16 through 26 (of 26 total)
You must be logged in to reply to this topic. Login to reply