Thoughts on ORM

April 9, 2010 at 8:00 am (nHibernate, Object Relational Mapping, SQLServerPedia Syndication) (, , )

I’ve posted before about issues I’m having either with behaviors of nHibernate, or behaviors of teams using nHibernate, but I don’t think I’ve made my thoughts on ORM too clear. Let me do that now. 

 I think some form of ORM is here to stay. There are lots of different ORM tools out there and acceptance of them is absolutely growing. Further, it should grow. Developing software is hard and if you can write code that reduces the overall amount of code you have to write, I’m in favor of it. I’m not convinced that the current crop of tools are quite as good as they ought to be, but most of them seem very flexible which should mean implementation of them can be, overall, beneficial to your project.

That’s all to the good. The problem is, and I don’t know if this is intentional marketing, poor understanding or just a general lack of ability, these tools are being hyped, or are perceived to be hyped, as a way to completely ignore and hide the unfortunate fact that there’s this dirty, tainted, completely un-object-oriented relational data engine persisting our information (or storing our data, if you will). Somehow, the idea that with an ORM tool, you can, and should, completely ignore the very idea of the database is persistant. Don’t believe me? Read through this excellent post by Daniel Auger. This guy is not in the enemy camp when it comes to ORM tools. He’s the very epitome of a booster of ORM. But read that post. Understand what it says. You need to take into account that you have a database, not a persistance layer, that is storing data, not information, into a relational data engine, not an object model. If you don’t, your ORM project will fail.

That’s all I’m after. I’m not advocating for the elimination of ORM tools. I think that’s silly. I see their benefit (conceptually, in my own experience to date, I haven’t seen any actual benefit at all). I’m in favor of them. Let me say that again, just so we’re clear, I am in favor of implementing ORM tools. But, I think if you’re implementing an ORM tool and there’s not a database developer or DBA involved with your project… you’re looking at trouble. Remember what ORM stands for, Object Relational Mapping. Relational is still a piece of the puzzle. Pretending otherwise doesn’t make the problem go away, it exacerbates it.

As an aside for those who are still reading, I wrote this whole thing after being inspired by reading Mr. Auger’s great post. That’s a developer I’d love to work with and learn from.

Permalink 2 Comments

nHibernate, First Look at TSQL

April 5, 2010 at 10:19 am (nHibernate) (, , , , )

I’ve blogged in the past about the nHibernate project that has been going on at my company for the last eighteen months. Prior to today, I have only seen the database generated by nHibernate. But today I finally started seeing some TSQL code. My first impressions… oy!

There are two levels of concern coming out of the gate. First, it appears that some of the programming decisions, completely independent of nHibernate, are going to cause problems. Second, it appears we’re going to be hitting issues with nHibernate.

First, the programming decision. I’ve been using Confio to monitor this server for a few days now (look for upcoming posts on my eval of Confio). Only one day has captured any real activity from the nHibernate team (and yes, I’m basically spying on these guys because they are absolutely insistent that my team stay out of their way). The longest running query was two calls to this (names have been changed to protect my job, there are no innocents):

SELECT BookID
FROM Books WITH (updlock,rowlock)
WHERE BookID = 42

 What they’re doing is locking the row so that no other user can get at it while it’s being edited. Total lock time for the first day was 38 seconds for two calls. I’ve posted about this previously, so I won’t go into it again, but this is potentially a disaster.

On to nHibernate. The next query was pretty benign:

SELECT OrgID
,OfficeID
,StartDate
,EndDate
,OtherID
FROM SpecialList
WHERE OfficeID = 42

Unfortunately this table, as created out of nHibernate, has no primary key, no clustered index or any other type of index, so this is just a table scan. But it’s only on 109 rows… in dev, not production, oh, and most of the 109 rows have a null value for OfficeID, but it’s all good I suppose… until I notice that this query also had 38 seconds of wait time, but it was called 394,652 times… oy (and no, I’m not even one of the chosen, but that’s a wonderful expression for this situation). Looking at the data in cache, this query has been called, since it was created in cache on the 2nd, 598351 times with a total elapsed time on the server of 5429689ms. The average then is 9ms, but the max was 29296 or 29 seconds.

The next query up looked like this:

SELECT col1
,col2
,col3
FROM Table1
INNER JOIN Table2
ON Table1.ChildID = Table2.OtherID
WHERE tabl2.ParentID IN (@p1,@p1,@p2…@p99)

Yep. 99 parameters passed and used against an IN statement. I can’t post the execution plans on this stuff without talking it over with the boss, but suffice to say, it’s two table scans and a merge join to get the data back. Oh, and this was called 1,138 times with a minimum elapsed time of 976ms. Yeah, minimum time to call this proc is 1 second and it’s been called over a thousand times.

It went down hill from there. I’m looking at other queries, one that hits seven tables, but instead of using JOINs uses a bunch of sub-selects in the WHERE clause. It has a minimum run time of 9 seconds. Luckily it’s only been called 64 times.

This is a horror show.

A lot of benefit can be reaped if we’re able to go in and create some indexes on these tables, but that’s going to be an issue anyway because I’m seeing lots of implicit data conversions on the parameters being passed in, as I noted a couple of years ago. At this point in time, I would say, at best, I’m very underwhelmed by what nHibernate is producing. To be perfectly honest, I’m concerned for the health of the project.

Permalink 22 Comments

Buck Woody on Code Writing Code

February 16, 2010 at 10:33 am (nHibernate) (, , )

I realize I’m prejudiced, being one of those evil DBA’s & all, but I can’t help but agree with him. It’s a short post, but worth the read.

Permalink Leave a Comment

nHibernate Database, First Look

February 15, 2010 at 1:39 pm (nHibernate, SQL Server 2008) (, , , )

I’m getting my first look at a full-fledged nHibernate database developed by consultants for our company. I thought I’d share my initial impressions. I’ll be capturing trace events from the database over the next couple of weeks, so I’ll be following up on the behavior of nHibernate within this database as well.

The first thing I saw & thought was, “Foreign key constraints. Thank the gods.” That really is good news. I was frankly concerned that they might go with the “let the code handle it” approach. There are quite a few null columns. I’m also seeing tons & tons of nvarchar(255) which must the default string size. Lots of bit fields too. They also used bigint in a lot of places too. None of this is definitively good or bad, just observations.

There are tables that lack a primary key. That raises a bit of a concern. The primary keys I’ve looked at have all been clustered, which isn’t too problematic since that’s probably the primary access path. There are a few unique constraints on some of the tables too.

Overall though, I don’t see anything that, at first glance, makes me want to run screaming from the room (or pick up a big stick & start looking for developers).  The devil is no doubt in the details. Time to get started looking at the trace events.

Permalink 4 Comments

How do -You- use SQL Server

December 1, 2009 at 10:57 am (nHibernate, SCOM, SQL Server 2005, SQL Server 2008, SQLServerPedia Syndication, Tools, TSQL, Visual Studio) (, )

I’ve been tagged by a misplaced yankee, uh, New Englander, whatever. The question is, how do I/we use SQL Server where I work. That’s a tough one. It would make a much shorter, and easier, blog post to describe the things we don’t use it for. However, keeping with the spirit of these tags, I’ll try to lay out it.

For those that don’t know, I work for a rather large insurance company. This means that we have lots and lots of databases, but not much data. We also are cheap. That means we’ll run an app into the ground rather than spend the money & time to replace it. We have apps still running from the 70’s and 80’s propped up by ancient men with pocket protectors, spit, bailing wire and happy thoughts. This also means that we have apps running on SQL Server 7, 2000, 2005 and 2008. Give me a couple of weeks and I’m sure I can get an R2 app deployed. There is also a few Oracle databases, our warehouse and Peoplesoft in particular. We even have a DB2 and, I think, one Sybase database somewhere.

I don’t want to go into lots of details about the type of data we store, lest I get in trouble, but basically, think insurance and you’ll get a pretty good idea of a lot of it. Add in the fact that my company prides itself on engineering to avoid risk and you’ll know that we gather quite a bit of information about the things that we insure. There are lots and lots of very small databases. Our largest db’s are just breaking 100gb, but must are in the 20-60gb range. We have a ton of OLTP systems gathering all the different data. These have been developed in just about every possible way. We even have a couple of systems using nHibernate under development. We move, mostly, pretty standard structured data. We have a few processes that are using XML, mostly from third party sources, to import data, so we’ve learned how to shred that stuff into the database. Spatial data, insurance remember, is the big new thing on the block. We’re seeing lots more implementations taking advantage of this. We don’t see much in the way of unstructured data, but some of the reports from the engineers falls into this realm. We also get quite a few photo’s from them that want us to store. We’re working on using FileStream to keep those in sync with the db rather than storing them within the database itself.

Currently, and I hate this, the overwhelming majority of our OLTP data is collected in SQL Server. All our datamarts used for reporting are in SQL Server. BUT, in the middle sits our Oracle warehouse. So we have to convert our data from SQL Server into Oracle and then back into SQL Server. It’s fun. Swapping data types over & over, shrinking column names only to re-expand them into something a human being can read… It’s lots of fun.

We use SSIS for almost all our ETL processes, although we have a few DTS packages still chugging away on some of the 2000 servers. We’re running a bit of replication, but nothing major. We have several fail-over clusters in production. We’re running virtual machines in lots of places. We’re expanding our Reporting Services implementation pretty rapidly. After attending Buck Woody’s sessions at PASS this year we’re getting Central Management Servers and Policy Based Management implemented.

Most of my time is spent working with development teams. We do most our deployment work using Visual Studio. I do database design, stored procedure writing and tuning, data access methods… you name it and I’m involved with it. The level of our involvement varies from team to team, but by & large, we get involved early in most development projects and are able to influence how databases are developed.

For monitoring we’re using Microsoft System Center Operations Manager (or whatever it’s called this week). We’ve modified it somewhat, adding special rules & monitors to keep an eye on the system better. We also use Idera’s SQL Diagnostic Manager to help keep a more detailed eye on the systems. I already mentioned that we use Visual Studio for most of our development & deployments. We also use several Red Gate products, Compare, Data Compare, Prompt, pretty much every day.

That’s pretty much it. We keep busy and keep the systems up, running & happy most of the time.

Because I think they’ll have interesting answers, I’m going to pass this on to Jeremiah Peschka and Tim Ford.

Permalink 4 Comments

I Started a Fight

October 28, 2009 at 2:14 pm (nHibernate) (, , )

I had no idea, but evidently I started a bit of a donny-brook. Cool!

Permalink 2 Comments

Object Database Editorial

June 17, 2009 at 7:57 am (nHibernate, Object Relational Mapping, SQL Server 2005, SQL Server 2008)

I never used to read editorials. Not in emails, magazines, newspapers, whatever. Now, I make it a point of always reading them. You can learn as much from an editorial as you can from the technical articles within, sometimes more. Tony Davis has just posted a guest-editorial over at SQL Server Central. Tony is normally the editor at Simple-Talk, where he also writes interesting editorials. This one is not to be missed. It makes a very clear, and concise case for why object databases have a fundamental flaw for most business needs (not all, not always, but a pretty hefty majority). It’s worth a read.

Permalink 1 Comment

A Fix for nHibernate’s Parameter Problem

March 11, 2009 at 6:26 am (nHibernate, Visual Studio) (, , , )

I had previously outlined a problem with nHibernate code that, in my opinion, was going to cause a lot of problems on production systems especially with memory and the procedure cache. However, it does appear that there is a possible solution. That doesn’t eliminate my concerns over nHibernate, but it sure does help.

Permalink 1 Comment

Sign Me Up!

November 25, 2008 at 3:02 pm (nHibernate, Object Relational Mapping, PASS, SCOM, SQL Server 2005, SQL Server 2008, Tools, TSQL, Visual Studio) (, )

I am joining the battle. It’s the Battle Against Lawless Database Design (BALD-D or baldy). Because, after all, enough is enough.

I encourage you too to join the battle. Cross your arms, join the battle cry! Enough is enough!

Permalink 2 Comments

VSTSDBE RC0 Post 2

November 3, 2008 at 4:01 pm (nHibernate, Tools) (, , )

There is no joy in Mudville.

The install of RC0 went smoothly. I was able to create a new server project and deploy it. I was able to reverse engineer AdventureWorks into a new project and deploy that. When I went to create a new compound project, combining the output from the two… deployment failed. I got an arcane error about something in the Microsoft.Data.Schema.SchemaModel.ModelSerializationException erroring out. Useful. So, at this point, the functionality I had in my demo for the PASS Summit isn’t working. Yikes. I’ve got two weeks… no pressure…

I posted a note about it over at the MSDN discussion site. If you have a solution, swing by and drop it off.

Permalink Leave a Comment

Next page »