Southern BITS of Technical Goodness

Our business has grown quickly, from $1M in annual revenue a few years ago to almost a hundred times that now. In that time, I’ve moved from web based Java development to .NET rich client development.

The company I work for has used client server applications that use Access front end with a SQL Server back end. Access is written in VBA using forms over data in a procedural programming model.

I’ve argued the value of OOP and best practices, but my success has been gated by one thing: SPEED TO DEVELOPMENT. Why?

IT (geeks) provide what value?

I used to believe we generated profit. I used to measure my value in added revenue, but soon I realized that upper management sees IT as a cost – waste. Their goal was to minimize cost and maximize profit (or in a LEAN way of thinking, reduce waste).

IT is WASTE – we are an expense not revenue.

Don’t believe me? See your paycheck for proof. I hate this idea, but that’s the way that upper management often sees us (geeks).

So consider the two perspectives that geeks and business have. We see this:

But business owners see this:

 

As Dennis Hopper said in True Romance If that’s true, am I lying?

So what are you (GEEK) going to do about it?

We’re right. We know we’re right. We can’t be wrong. Right?!

So why in the hell are they saying big gray blobs and BIG dollar signs when we want them to see…

How to do that:

1. Reference prominent companies (& geeks within) that use best practices. Business values experience and most importantly they value SUCCESS & FAILURE. If you can show that following good practices is what successful companies do, and that unsuccessful companies have failed for not following best practices.
2. Does business want it RIGHT or right now? Software projects fail. They have failed time and time again. Getting software implementations right takes time. Moving too quickly only results in failure and rework. You can only move as fast as possible but no faster (see Einstein: Make things as simple as possible but no simpler).
3. Ask questions: Is this a long term investment or short term? If business is seeking a POC (proof of concept) or the financial/business climate is such that they need to prove something out or “just get it done” then perhaps fast is best. But if the project is a long term investment, then they should do it right. Pay upfront for good architecture, project methodologies and infrastructural support.
4. Be Reasonable. Understand business. You want business to understand you, but you need to understand business. Why should they care about your best practices when you don’t give a damn about their profitability or time to market. Remember IT is an enabler – IT is waste, help them reduce waste.
5. Best practices lead to flexibility & more responsive, cost effective solutions: Face it, best practices are going to cost more to develop. Forms over data are fast. MVP / MVC / MVVM with repository patterns and asynchronous enterprise service buses mocked out and developed using TDD just take longer. So why would business want that? If you don’t have an answer, don’t expect business to buy in.
• Design patterns increase flexibility which allow IT to respond faster to business.
• Test Driven Development decrease mistakes and bugs and leads to automated regression testing (decreased testing costs)
• Electronic Service Buses formalize application boundaries which allows business to swap out new technologies, vendors and applications as they become available thus decreasing cost and increasing IT responsiveness.

Summary:

If you can’t sell what you believe in, then you need to work on the pitch. Being a geek doesn’t omit us from having to persuade people to accept our beliefs.

IT is a sort of religion. It’s mysterious, poorly understood (even by us), and we constantly are trying to understand a thing that is far greater than ourselves – something we’ll never fully comprehend.

In the end, all a geek can do is convey what we believe in using the language of business.

alan.huffman Architecture, Business, Business Growth, Development Infrastructure, Projects Best Practices, development, leadership, management

Summary:

Over the past 1.5 years my team has been using LINQ SQL Designer. We have written a significant amount of code, but our team is doubling in size and we can no longer afford the SVN conflicts from concurrent changes to .DBML files (SQL Designer).

 

Differences:

 

Why:

DLINQ (Linq to SQL) was created to showcase LINQ (Language Integrated Query) but was never meant as a ORM solution. Entity Framework is Microsoft’s chosen ORM solution, but since it was released after LINQ To SQL, EF is not used as much. That’s the reason we did not use it.

    Obviously different teams must have come up with the LINQ SQL Designer versus SQLMetal. I can only assume that the differences outlined above are due to a lack of communication b/w these teams.

Result of these differences:

In switching from LINQ TO SQL to SQL Metal, we had to do the following:

1. Build DBMLGod (about 1 week) (though http://www.plinqo.com/ might have sufficed)
2. Refactored nearly every file in our code base (another 1 week)
3. Testing (TBD)

See related post DBML Hell.

alan.huffman C#, Development Infrastructure, LINQ .NET, Coding Practices, LINQ, Microsoft, ORM

(when migrating from SQL Designer)

History:

For the past 1.5 years our team has developed HIT (Healthcare IT) LOB (line of business) software using DLINQ SQL Designer. The experience has been pleasant from a tooling perspective except for the reasons that are frequently blogged about, namely DBDesigner results in SVN conflicts when 2 people make changes to the model.

We have a few large DB’s – one is about 9 years old and has hundreds of tables (600+) and even more views plus hundreds if not thousands of SPROC’s (many of which are complex)

We have 3 such DB’s. (each w/ their own pain)

Consider a common use:

More than 1 developer is making changes to a shared relational database (adding/changing tables, sprocs or functions).  Changes are made by Developer A & Developer B and then both try to check in. SVN detects a conflict – given that the code is generated, resolving the conflict is *not* easy to resolve.

Primary Issue:

When a checking in DLINQ Designer DLinq DBML generated files a conflict occurs.

Solution? Move from using the DLINQ Designer to using SQLMetal. (We could use Entity Framework, but that would require significant changes. So we want to stick with DLINQ (LINQ to SQL)).

SQLMetal does not generate what SQL Designer does!

Obviously, if you are on a green field project, this is not as big of an issue. If you are on an existing (brown field) project that has used SQL Designer, this hurts (a lot).

A) SQLMetal does not handle complex stored procedures (in same way)

What is a complex stored procedure?

1. Uses control flow logic (If/Else) that results in different result sets.
2. Uses temporary tables (#TmpTable )

#1 results in a IMultipleResults being returned by SQLMetal even though SQL Designer results in a ISingleResult. So migrating from SQL Designer to SQLMetal results in compilation errors.

#2 SQLMetal does not generate these sort of sprocs (at all) though SQL Designer does (ugh!)

#2 is particularly difficult to cope with, assuming you need a #Temp Table. SQLMetal just isn’t going to work.

SET FMTONLY OFF;  — explicitly in the SPROC

http://msdn.microsoft.com/en-us/library/ms173839.aspx

– what does this do? Results in the **ACTUAL** execution of the sproc and not just returning the meta data about what the SPROC result will be.

– in other words, when you run SQLMetal, your sprocs (that have FMTONLY OFF) will be executed. There could be side effects (assuming your SPROC does something & doesn’t just return information)

– This was *NOT* an option for us

B) SQLMetal does not support manual tweaking of DBML as SQL Designer does.

1. Can not add associations between DTO (Data Transfer Objects) that do *not* exist in DB. [ SQL Designer allows this ]
2. Can not map a SPROC to a DTO/Entity (easily done in DBML Designer by simply dragging the SPROC to an existing entity (table)

C) Not easy to change the inheritance hierarchy of the generated DataContext?

If you need to change the class that the datacontext inherits from the change must be made each time SQLMetal is run.

e.g.

MyDataContext  : System.Data.Linq.DataContext // generated by SQLMetal each time

// but we want

MyDataCongext : BaseDataContext

// where

BaseDataContext : System.Data.Linq.DataContext

D) SQLMetal DBML or Code generation is VERY SLOW for large DB (15 min !)

Can you imagine adding a table and then waiting 15 minutes before you can start coding using that new table b/c SQLMetal takes that long to generate the DBML or DataContext.cs?

Me neither b/c this is totally unacceptable

E) SQLMetal generates the entire database model! (ugh)

The only filtering that can be done using SQLMetal is at the SPROC / View / Function level. What happens if you only want a specific schema, a limited number of tables or certain functions? SOL.

F) SQLMetal generates a DataContext file so large that Visual Studio crashes (esp. with Resharper)

Essentially this is an extension of the (E) issue, but it is a very different issue. Where E may result in too many things being generated, the fact that Visual Studio crashes may make exploring the DataContext file impossible. Our DataContext.cs file was over 13MB. The DBML was 4MB.

Solutions:

A) Use a different ORM + CodeSmith

This was not a consideration for us. We love LINQ. We drink the MSFT cool aid, and we weren’t about to move to NHibernate or LLBGEN (though I had a history w/ Hibernate). We have too much invested in LINQ. ( I suspect many are in this camp ).

B) Hand code the DBML

Not an option for us, as there we had a very green OOP / ORM team. We needed something convenient that could work with several very large DB’s with a very diverse set of objects (functions, SPROCs, Tables, Views).

C) Move to Entity FrameWork

This doesn’t provide much shelter from the issues outlined above, not to mention that we have a large amount of existing LINQ code. (we’re vested / locked in)

D) Mix the advantages of SQLMetal & SQL Designer

This is the choice we took. But it required the building of a utility tool that provided a mixture of functionality of SQLMetal & SQL Designer. We were in DBML Hell, and thus only DBMLGod could set us free! (tongue in cheek, but I named the tool just that DBMLGod).

DBMLGod ( SQL Designer + SQLMetal + Hand Coded DBML )

The idea was to take the strengths of SQL Designer and that of SQLMetal and merge them into a hybrid that has all the positive qualities but none of the negative. Plus we need the ability to HAND code very complicated SPROC’s that even SQL Designer doesn’t generate correctly. This succeeded, but it does require a us to develop in a certain way.

SQL Designer

Strengths:

1. Handles Complex Sprocs better than SQLMetal (often times)
2. Allows SPROCs return types to be mapped to entities
3. Can add associations that do not exist in the DB / ER
4. Quick additions of new DB objects (See issue #D above)

Primary weakness: SVN Conflicts

Hand generated DBML

Strengths:

1. Full control over what is generated & how

Primary weakness: Must hand code / Must understand the DSL.

SQLMetal

Strengths:

1. Reduces conflicts (on par with Hand generated DBML)
2. Generates required classes

Weaknesses: See above (A-F)

Ideally we should be able to accumulate strengths without suffering weaknesses.

Implementation of DBMLGod

(Hopefully to be a open source project) – requires my companies sign off.)

In a green field project we might be able to avoid weaknesses of SQLMetal, but given real world scenarios of large existing databases with complex SPROC’s this isn’t possible (for us).

Requirements:

1. Support complex SPROCs (use SQL Designer / hand coded DBML) [ Issue A ]
2. Add associations not defined in DB / ER [ Issue B ]
3. Map SPROC return types to Entities [ [ Issue B ]
4. Change generated SQLMetal DataContext code (support inheritance from custom DataContext Object) [ Issue C ]
5. Quick addition of new DB objects (no waiting 15 minutes for regen of DBML / DataContext file) [ Issue D ]
6. Decrease the size of the generated DBML / DataContext [ Issue E ]
7. Generated DataContext must not be unnecessarily large (make things as simple (small) as necessary but no smaller) [ Issue F ]

How do we do that?

And so the idea is to take the SLOW SQLMetal Generation of the DBML (for large DB) and the faster FILTER / MERGE / SQLMetal Code Gen process and make it fast.

DBMLGod does just that. It gets us out of hell by filtering the Generated DBML, merging those into Hand/Designed DBML files (preferring elements in the latter), generating the code and then replacing code elements in the generated DataContext to get the desired result.

Step 1) Gets value of SLQ Metal DBML Gen but allows us to filter resolving issues E & F

Step 2) Leverages value of Hand/SQL Designer to resolve issue A & B

Step 3) Generate the Code

Step 4) Allows us to resolve isssue C & D

How well does this work?

Addition of DB objects has performance on par with SQL Designer (fast)

SVN conflicts are minimized.

Generation of DBML by SQLMetal (SLOW) can be left to END OF DAY process!

Unresolved issues

SQLMetal capitalizes properties on entity clasess and SPROCs. Default behavior of SQL Designer is to *NOT* do this.

Thus you have 2 choices:

1) Change the SQL Designer generated Sprocs/Entities to conform with SQLMetal generation

2) Use SQL Designer & correct/deal with compilation errors as they occur

Can you believe that MSFT uses different strategies depending on the tool? (UGH, SIGH, HORROR)

But thanks for LINQ! Love it!

Related Post: Unfortunate differences between SQL Metal and SQL Designer

alan.huffman Architecture, C#, Development Infrastructure, LINQ .NET, Coding Practices, LINQ, Microsoft, ORM