How to increase perfomance for retrieving complex business object?

Question

Currently we have a complex business object which need around 30 joins on our sql database to retrieve one item. (and this is our main use case). The database is around 2Gb in sql server. We are using entity framework to retrieve data and it takes around 3,5sec to retrieve one item. We haved noticed that using subquery in a parrallel invoke is more performant than using joins when there is a lot of rows in the other table. (so we have something like 10 subqueries). We don't use stored procedure because we would like to keep the Data Access Layer in "plain c#".

The goal would be to retrieve the item under 1sec without changing too much the environnement. We are looking into no sql solutions (RavenDB, Cassandra, Redis with the "document client") and the new feature "in-memory database" of sql server.

What do you recommend ? Do you think that just one stored procedure call with EF would do the job ?

EDIT 1: We have indexes on all columns where we are doing joins

Answer 1

In my opinion, if you need 30 joins to retrieve one item, it is something wrong with the design of your database. Maybe it is correct from the relational point of view but what is sure it is totally impractical from the funcional/performance point of view.

A couple of solutions came to my mind:

• Denormalize your database design. I am pretty sure that you can reduce the number of joins improving your performance a lot with that technique. http://technet.microsoft.com/en-us/library/cc505841.aspx

• Use a NoSQL solution like you mention. Due to the quantity of SQL tables involved this is not going to be an easy change, but maybe you can start introducing NoSQL like a cache for this complex objects. NoSQL Use Case Scenarios or WHEN to use NoSQL

Of course using stored procedures for this case in much better and it will improve the performance but I do not believe is going to make a dramatic change. You should try id and compare. Also revise all your indexes.