June 28, 2010 at 7:40 pm
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 early
2) 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 solutions
5) 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. http://docs.jboss.org/hibernate/core/3.3/reference/en/html/session-configuration.html 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
June 29, 2010 at 7:39 am
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.
June 29, 2010 at 11:34 am
Tony++ (6/29/2010)
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 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:
June 29, 2010 at 11:52 am
You can use SPs with nHibernate.
June 30, 2010 at 11:44 am
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.
Viewing 5 posts - 16 through 19 (of 19 total)
You must be logged in to reply to this topic. Login to reply