Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

n-hibernate and dynamic sql - DBA vs Developers Expand / Collapse
Author
Message
Posted Tuesday, October 30, 2007 8:58 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, February 6, 2012 12:43 PM
Points: 215, Visits: 640
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.


Post #416861
Posted Wednesday, October 31, 2007 7:26 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, July 7, 2014 7:54 AM
Points: 16, Visits: 230
Just tell them to use LINQ.


Kindest Regards,

scoinva

Post #417051
Posted Wednesday, October 31, 2007 8:02 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, September 5, 2011 10:27 AM
Points: 36, Visits: 242
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

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.



Post #417362
Posted Thursday, November 1, 2007 5:39 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, February 6, 2012 12:43 PM
Points: 215, Visits: 640
I could not have said it better myself.
Post #417472
Posted Friday, December 14, 2007 5:59 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 3:23 PM
Points: 2,907, Visits: 1,830
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.


LinkedIn Profile
Newbie on www.simple-talk.com
Post #433279
Posted Monday, June 28, 2010 7:40 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, June 28, 2010 7:40 PM
Points: 1, Visits: 2
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



Post #944349
Posted Tuesday, June 29, 2010 7:39 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 7:21 AM
Points: 1,522, Visits: 1,609
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.
Post #944650
Posted Tuesday, June 29, 2010 11:34 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 3:23 PM
Points: 2,907, Visits: 1,830
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


LinkedIn Profile
Newbie on www.simple-talk.com
Post #944876
Posted Tuesday, June 29, 2010 11:52 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 7:59 AM
Points: 1,590, Visits: 1,358
You can use SPs with nHibernate.
Post #944891
Posted Wednesday, June 30, 2010 11:44 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 11:52 AM
Points: 876, Visits: 2,421
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.
Post #945689
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse