SQLServerCentral / SQL Server 7,2000 / Strategies / n-hibernate and dynamic sql - DBA vs Developers / Latest Posts

RE: n-hibernate and dynamic sql - DBA vs Developers

Nadrek — Wed, 30 Jun 2010 11:44:17 GMT

First: ensure that the developers, and the tools they use, are generating SQL that uses explicit parameters/bind variables/.CreateParameter, and are not concatenating variables into a string, regardless of whether they're calling dynamic SQL or stored procedures. This helps the query plan cache as well as provides the most important layer of SQL Injection protection.Second: Can you mentor at least one member of the dev team on SQL? Where I work, many of our developers also write very good, solid SQL, both inline and stored procedures, and help out other developers.Third: As was said, much of the inline/dynamic SQL vs. stored procedure argument is, in the main, political in segregated environments. Who gets the easiest access to change it, DBA's (stored procedures), or developers (inline). If the DBA's are a major bottleneck, and/or are seen to (or actually) generate more pain than value added, then there are strong political arguments for inline SQL. If there is a surplus of DBA procedure writers who write good, solid code quickly, and a shortage of developers, then there are strong political arguments for stored procedures.Personally, I believe in using both; "SELECT col1, col2 FROM x.y.tab1 WHERE col3 = ? AND col4 = ?" and its myriad cousins gain no real maintainability from being in stored procedures; on the contrary, that's a lot of overhead for such simple statements. A longer, more complex statement, or a set of statements, do gain maintainability from being in a stored procedure as a single defined block of work.Again, all this except explicit parameters in the SQL is environment-specific.

RE: n-hibernate and dynamic sql - DBA vs Developers

CGSJohnson — Tue, 29 Jun 2010 11:52:46 GMT

You can use SPs with nHibernate.

RE: n-hibernate and dynamic sql - DBA vs Developers

David.Poole — Tue, 29 Jun 2010 11:34:04 GMT

[quote][b]Tony++ (6/29/2010)[/b][hr] If the parm data is len(10) on a first call, len(11) on a 2nd, and len(2) on a 3rd, it creates different parameter sizes & ends up being 3 different cached statements. [/quote]The prepareSQL command uses sp_PrepExec and uses fixed parameter sizes. The problem is that this is an undocumented system stored proc which rules it out for production code.I've also seen it produce unkillable spids which is definitely not good.There is a fix for nHibernate which uses a fixed width for string parameters and the sp_ExecuteSQL call so this sorts both the proc cache and undocumented stored proc issue.Having faced a schema generated from an nHibernate model I'd rather not see another one. An app might be rewritten many times before a DB is rewritten plus the DB is likely to support multiple apps so building a DB from an app strikes me as tail wagging the dog.I think it is a good point that both DBAs and devs need to learn Hiberate/nHibernate like the backs of their hands. It is certainly possible to write a DB killer using nHibernate! Blasted lazy reads:sick:

RE: n-hibernate and dynamic sql - DBA vs Developers

Tony++ — Tue, 29 Jun 2010 07:39:00 GMT

We use Subsonic (ORM similar to hibernate, for .net) with C#, and I submit that it's not an either proc or ORM proposition. I do think that either side is just as much at risk of bad design/code, and both ways have to be managed & reviewed.As noted earlier, the "dynamic" SQL ends up as parameterized SQL, so this takes out recompiles & risk of injection.We've gone the route of totally custom procs for everything, and it ends up being a mess where nobody can remember where a proc is used, if used at all. Also this introduces a higher chance of bugs.We've gone the route with NetTiers ORM that generates procs for every possible data access it can see, and end up with a lot of procs that never get used, and as time goes by it just gets cluttered.We've ended up in-between, where most of the "go get me a row" simple queries are done with Subsonic. Complex queries, join queries, etc are done in procs where the logic can be laid out, commented -- and easily tweaked without a .net recompile & code push). Everything gets reviewed, Subsonic or proc, before leaving development & getting into QA, so we have a chance to stop badly-done queries on either side.One other item to note, an ORM tries being efficient to itself first, and HOW the database implements & reuses statements is a secondary concern. If the parm data is len(10) on a first call, len(11) on a 2nd, and len(2) on a 3rd, it creates different parameter sizes & ends up being 3 different cached statements. The statement gets reused only when the data is the same length. This only is an issue with character-based datatypes, so numeric parms are immune from this. I've seen my proc cache get full of these multiple statements that are really the same statement.

RE: n-hibernate and dynamic sql - DBA vs Developers

kattan78 — Mon, 28 Jun 2010 19:40:10 GMT

i am from the other side (the dev team). We are going through the same thing. I try to mediate between the dev team and the DBA. It gets harder when DBA's are not involved from the start .. during development when the data model is being designed. Altering the data model at a later stage can result of lot of bugs. Once thing that makes DBA's work harder is the resistance from dev team. The main reason for the resistance is the unrealistic suggestions by DBA. It took me a while to explain to the DBA the big picture behind hibernate (very similar to nHibernate) and its capabilities. after that he started making meaningful suggestions. "Get rid of hibernate" is not a meaningful suggestion as well as using stored procedures. we only use stored procedures for extreme cases and we document it very well (in case we need to port to a different DB).1) get involved early2) Put a process in place where DBA reviews every named query written by dev team. developers don't write good select or update statements.3) familiarize yourself with hibernate and Hsql (hibernate sql, which is very similar to sql). there is lot of granularity in hsql but most developers will write code in the easiest way possible and rely on hibernate to sort things out. eg. in the scenario a developer want to process some records that were created on a certain day. say processing as simple as sending email notification then flag the records to be processes. the developer might execute hsql to return the records as an array of objects, update the object flag - record.setProcessed(true) then save the object one by one. Obviously, updating the records in that was not not optimal. every object/record updated will result in a long sql statement that updates every single attribute of the object/record. You need to advise the developer to write a named query to update the records in one hsql statement using the same criteria (date created) that was used to retrieve the objects. 4) Print out hsql reference manual. maybe just couple of pages summary of the main features. Use it as a reminder of the hibernate features. use it as a base for your arguments with developers. It is a good starting point to start suggesting solutions5) stay away as much as possible of solutions that are specific to a db vendor. stored procedures is one example.6) validate schema generated by hibernate as early as possible. developers should be able to regenerate schema on the fly. at our company, the DBA keeps track of the changes. there are lots of free tool to compare 2 DBs. that way he can locate the new changes.7) fine tuning the schema. I will leave that to you to sort out with dev team. In some cases the developer can give hibernate some hints about how to create the schema. Nevertheless, hibernate might not generate the optimal indexes. you need create some fine tuning.8) hibernate is highly configurable. [url=http://docs.jboss.org/hibernate/core/3.3/reference/en/html/session-configuration.html]http://docs.jboss.org/hibernate/core/3.3/reference/en/html/session-configuration.html[/url] familiarize yourself with caching and other config properties.9) research alternative hibernate connector to the database. Our DBA suggested we use jtds for java instead of the native connector provided by microsoft. in the connection string used for jtds we were able to specify something like "prepareSQL=0;" not that i understand what it does.the main problem with hibernate is that 1) programmers don't know how to fine tune a database let alone using hibernate to tune it and 2) DBA don't know what can be tuned using hibernate. hence suggesting unrealistic options that developers can't implement

RE: n-hibernate and dynamic sql - DBA vs Developers

David.Poole — Fri, 14 Dec 2007 05:59:09 GMT

Let us suppose you have tables with say 25 million bank account details and child benefit information.You allow direct read access on these tables so said government half wit can copy the entire thing onto 2 CDs drop it in the post (allegedly) and breach the UK data protection act.Stored procs are not necessarily faster than dynamic SQL but they do have the advantage of representing a defined block of functionality. As people have said, this can be locked down at the database level.There must be ORM packages out there that allow mapping to stored procedures. If you look at replication it generates insert, update and delete stored procedures that follow a standard pattern. Surely there must be scope for an ORM package to either produce the scripts to generate these or at least plug into them.The problem with developers writing code is not really technical but political.If I, as a DBA, am going to get my backside kicked when the DB doesn't perform well, then I am going to be very careful about who I allow to write the stored procedures.when working in a big development environment I need to be sure that any lead developer on the project is db competent in their own right and also they are prepared to review their teams code with a critical eye.

RE: n-hibernate and dynamic sql - DBA vs Developers

meichner — Thu, 01 Nov 2007 05:39:50 GMT

I could not have said it better myself.

RE: n-hibernate and dynamic sql - DBA vs Developers

aloj — Wed, 31 Oct 2007 20:02:51 GMT

LINQ uses dynamic queries behind the scenes. You can make use of Stored Procedures with LINQ .. but you lose all the smarts. You write the queries instead of letting Visual Studio do it :hehe:YES stored procedures are the way to go but there are situations that Stored Procedures are not a good solution and are slower solution than dynamic queries. An example is a search query with 15 (or 4 or 8 ..) parameters when parameters can be NULL. So, the point is not to force absolute rules about permitting Stored Procedures ONLY but allowing for exceptions when they make sense. And, yes .. caching can be faster than accessing a database.

RE: n-hibernate and dynamic sql - DBA vs Developers

scoinva — Wed, 31 Oct 2007 07:26:22 GMT

Just tell them to use LINQ.

RE: n-hibernate and dynamic sql - DBA vs Developers

meichner — Tue, 30 Oct 2007 20:58:20 GMT

I wear two hats in my company. I am the lead programmer and the DBA. There is a big debate on this issue in forums like ASP.NET. As you might expect the programmers feel that with caching as well as other programming tricks that database access can actually be faster (or just as fast) using dynamic sql instead of stored procs. Although the majority of my job description is that of a programmer, I prefer to use stored procedures for several reasons.1. The guys who wrote Oracle or SQL Server know more about performance then the average programmer.2. Although I don't like T-SQL (I use Sql Server) as much as I do C#, I can encapsulate more complicated data access logic in my stored procedures then I can using dynamic sql.3. I can add an extra level of security to the stored procedure.4. The last reason is a reason that most people overlook. I have some users that want to access the database via products like Excel. If my selection logic is in a stored procedure they will not only access the tables the way I want them too, but data access code resides in one place. Therefore changes will be localized. If the selections are in dynamic sql the logic will need to be in multiple places. I hope this helps.

RE: n-hibernate and dynamic sql - DBA vs Developers

Erin-295134 — Fri, 12 Oct 2007 17:39:25 GMT

I have been going through this exact conversation with web dev. Thank you all for bringing light to this topic. :)

RE: n-hibernate and dynamic sql - DBA vs Developers

rudy - Doctor "X" — Tue, 21 Aug 2007 13:48:00 GMT

There is also some great onformation on:

http://www.sql-server-performance.com/

To be more specific:

http://www.sql-server-performance.com/articles/dev/sql_best_practices_p2.aspx

RE: n-hibernate and dynamic sql - DBA vs Developers

Solomon Rutzky — Wed, 15 Aug 2007 12:42:00 GMT

(sorry this is so long but "the devil is in the details" )

Hello. I am a DBA facing pretty much the same issue and would appreciate some DBA feedback. As has been mentioned already (I guess in another forum on the same topic) the vast majority of information on this topic is all from the developer perspective; there is very little information from DBA's that have experience with ORMs (Object-Relational Mappers). The situation I am faced with has a few differences and nuances so I will try to describe in detail.

To start with, the company I work for uses Java as the application language and SQL Server as the database. The issue of ORM has reared its ugly little head as we are starting a new project. The developers have already advocated using Hibernate as it is becoming (or is already) an industry standard for Java. In our discussions we talked about performance and security as the main concerns, as well as maintainability of DB code and trouble-shooting.

The issues of maintainability and trouble-shooting regard the fact that we are unable to easily test the queries that are being submitted in order to test the performance. Yes, we can use SQL Server Profiler to capture queries and their exact CPU, Reads, Writes, and Duration, but that is not a good long-term solution to have running against production on a constant basis. Also, if we want to find where a table or field is used we cannot search the text of all procedures. Finding where a table or column is used might not be a major issue as it might be possible to search the applications DB access mapping file to see the references.

The main concerns really are security and performance. These issues have been discussed already in this and one or two other forums but I don’t think the level of detail needed was ever given to the topics as certain assumptions were made about how Stored Procedures are used and about how Hibernate submits queries. I will start with just the benefits of the Stored Proc method and then point out some variables that might alter what the typical response regarding them is.

Security can be split into two subcategories: data access and query modification (e.g. SQL Injection). If we are using Stored Procedures, then we can control data access directly to the tables tightly. We can turn off all read/write access to the tables and give only Execute permission on Stored Procedures that access the data. This way, the account that the application logs in as, even if compromised, cannot issue direct ad-hoc queries; it can only Execute Stored Procs and hence only interact with the data via the predefined API of the Stored Procedures. This does not mean that someone cannot do any damage if they do compromise an application account, but the risk is minimized a bit. Regarding query modification, given how Stored Procedures pass in and use parameters, SQL Injection is not a problem.

In terms of performance it has been widely noted that Stored Procedures have the benefit over dynamic SQL in terms of being able to re-use the Execution Plan. It might also be that the time it takes to parse and evaluate as valid SQL might be less in the case of Stored Procedures but I do not recall seeing this noted as a benefit for some reason.

One other benefit of a Stored Procedure API over dynamic / embedded SQL is that of controlling the data access in terms of protecting the integrity of the data and making sure the data model is properly adhered to.

So what about Hibernate and other ORM packages? To be clear, they do not all do the same thing; some are fully dynamic and others use static mappings from the DB and some are just code generators that build simple data access Stored Procedures that then get mapped. Hibernate can do fully dynamic or map to existing Stored Procedures. The developers I am working with advocate the fully dynamic option as it supposedly cuts down on a lot of development time. The question is: is this worth it in the long run given the security and performance concerns and even the minor concerns? To be fair, what has not been said so far in this discussion is that when Hibernate (and a few other ORM packages) say "dynamic", they are not meaning a concatenated text string that is submitted to the DB. A concatenated piece of text IS subject to the concerns of security (e.g. SQL Injection) as well as performance as it cannot re-use an execution plan. But Hibernate (and some others) use prepared statements that are parameterized. This is akin to using sp_executesql and passing in parameters. Using a prepared statement has two benefits over concatenated text: it uses parameters like Stored Procedures so it is not susceptible to SQL Injection and it can actually re-use its Execution Plan.

So does this information alone change anyone's mind about using such an approach? A few people and even articles on SQLServerCentral.com have pointed out that sometimes Execution Plans can hurt performance. But how often is this the case? And are Stored Procedures able to cache their Execution Plans for longer than prepared statements? Meaning, does a prepared statement have to run repeatedly in order to use the same Execution Plan or are those Plans cleared from the cache sooner than those of Stored Procedures so you wouldn’t see the benefit on a statement called once an hour, maybe? Also, according to Books Online, using a prepared statement will "probably" re-use a previous Execution Plan and is not guaranteed to do so. Does this tip the scale towards Stored Procedures?

If Hibernate's use of a prepared statement is good enough to be on par with Stored Procedures regarding the efficiency of using Execution Plans and is not susceptible to SQL Injection, it seems that the only real issue left is that of direct read / write access to the tables. Is this a major concern or acceptable to give up? In my opinion it should certainly be avoided but now I am not sure what I gain by restricting that access.

I advocated using a code generation tool (e.g. Code Smith) that builds the simple INSERT, UPDATE, DELETE, and SELECT Stored Procedures for all tables in the database. This can of course be customized using templates. In my thinking this would give us the benefit of being able to restrict direct read / write access to the database since the application would only interact via Stored Procedures. This in turn gives us the definite benefit of re-using Execution Plans. Of course, there is still the issue of sometimes Execution Plans do hurt if the first run of the procedure pulls back a vastly different size result set than other executions of that procedure. However, in the case of the generated Stored Procedures, they are all simple SELECTs so the result sets will rarely be vastly different in size. However, if all of the Stored Procedures are simple statements, then there is no true database API that will ensure proper use of the Data Model and ensure data integrity; the Stored Procedures in this case are merely a layer to gain the benefits of direct-access security and guaranteed Execution Plan re-use. And in terms of the direct-access security, what is the gain over allowing the application to submit its own ad-hoc queries? A compromised account can do just as much damage with INSERT, UPDATE, and DELETE procs for every table just as much as being able to issue their own SQL. Or is this not true? Does allowing read / write access as opposed to ONLY Execute permission on Stored Procedures open up areas of risk indirectly, such as possibly getting to system databases and/or system tables?

So, to me this is a slightly more complicated issue than previously presented, especially in light of Hibernate's (and other's) ability to use prepared statements and not submitting concatenated text which everyone was right to object to. Is the approach of using Hibernate to create dynamic, parameterized queries worth the amount of hours that the developers will save in creating the application? Would we be sacrificing too much by giving read / write access to the tables? If so, are the generated simple Stored Procedures a good compromise? I am not a huge fan of the business logic being in the DB as that is not what the DB does best, as much as I do appreciate the ability to control how the data model is used and trying to ensure data integrity. So a benefit of only simple Stored Procedures is that no longer are people who are not SQL experts writing Procedures that have inefficient JOINs, etc. The only Procedures left to write and debug for performance are reports. So this would save me some time as well. Of course, as long as we are not sacrificing query performance once we have millions of rows and not sacrificing security.

Again, any insight would be greatly appreciated. As per usual, the decision to use Hibernate to build its own SQL dynamically (no Stored Procedures) had already been partially made well before anyone on the database team was involved on the project so there is not too much time left to offer evidence against this if it is indeed a foolish decision.

Thanks in advance.

P.S. Here is the REAL kicker: the developers also wanted to use Hibernate to generate the Data Model directly from their object model. Supposedly Hibernate can generate the entire data structure for you once you build the objects, as opposed to getting a set of objects from the Data Model that was created by a lowly human.

RE: n-hibernate and dynamic sql - DBA vs Developers

Knight — Fri, 22 Jun 2007 04:52:00 GMT

Many thanks to all those who have posted.

RE: n-hibernate and dynamic sql - DBA vs Developers

aloj — Thu, 21 Jun 2007 20:18:00 GMT

Misunderstood “Security” was a reason why developers were made to write queries like :WHERE @CustomerID IS NULL OR tblx.CustomerID = @CustomerID. Once tables grow to significant size the performance starts to be unacceptable and the whole application starts to be in trouble. And, of course you do not catch this in development when tables are almost empty (one should have test data that is close approximation of the real stuff but it’s another story.)

All of it really pertains to Read Queries. I think that creating views or giving Read permissions on tables works well with dynamic queries.

RE: n-hibernate and dynamic sql - DBA vs Developers

AndrewMurphy — Thu, 21 Jun 2007 03:40:00 GMT

"There is no rule that says: SP is always better than a dynamic query."

At the moment you're only looking at the performance angle...throw in the security issues generated by D-SQL and the reason to swing to SP's will be even more pronounced.

RE: n-hibernate and dynamic sql - DBA vs Developers

aloj — Wed, 20 Jun 2007 21:58:00 GMT

It's not so simple though. Would you rather see a dynamic select query that joins 2 tables and has 2 conditions in a WHERE clause (because other parameters are nulls) or would you rather see a SP with 16 joins and 16 conditions, like that:

AND (@CustomerID IS NULL OR tblX.CustomerID = @CustomerID )

I have seen the second and I am sure the first (dynamic query would be faster). SQL Server will cache execution plan for a dynamic query too.

Also, I have seen SPs that cached execution plan but that plan was causing other executions of the same SP (with different parameters) to be very slow. I had to use WITH RECOMPILE to make SQL server not to store the execution plan.

Some code generators create SPs and some create dynamic queries. Which way is better will depend on the amount of data being processed, on application characteristics etc...

I would think that you would need to learn more about the application to be able to judge possible impact it would have on the DB that you take care of. There is no rule that says: SP is always better than a dynamic query.

RE: n-hibernate and dynamic sql - DBA vs Developers

Loner — Wed, 20 Jun 2007 15:12:00 GMT

A stored procedure is a group of t-sql statements compiled into execution plan. When you use the stored procedure, the same execution plan will be used and that's why it increases the performance.

For dynamic SQL, it creates its own execution plan when it runs, that is why it is slower.

In my old company, the developers used regular SQL Statement in the C# program and the DBA tried to advised them to use stored procedure because it would run faster. Guess what!!! No one listens!!!!!

Actually some of the logic in the C# program can be put in a stored procedure, however the C# programmers are not sql programmers, all they knew how to write SELECT statement.

Two different worlds!!!

RE: n-hibernate and dynamic sql - DBA vs Developers

Sugesh Kumar — Wed, 20 Jun 2007 03:40:00 GMT

Knight, Make the developers and Management understand that SPs are faster than D-SQLs. Say them the pros and cons for each i am pretty sure that they will go for SPs after your advice. As SPs are always faster than D-SQLs.

n-hibernate and dynamic sql - DBA vs Developers

Knight — Wed, 20 Jun 2007 03:27:00 GMT

I'm currently working with a development team that are trying to implement n-hibernate as a design tool to speed up their development. My main problem with this application is that it uses dynamic sql and the developers are resisting using stored procs.

My db server environment is high volume and I'm greatly concerned that if they push through this approach it will not work in the live environment and create alot more admin for me in terms of security and will make changes to the SQL alot slower than if we were using stored procs.

Does anyone have experience of this sort of issue and how best to resolve this issue with developers?

My role is new to the company so resistance is an issue as they have not worked with a DBA before.

Cheers