Fastest way to retrieve data from database

Here are 2 suggestions that will help. The first suggestion is what will improve your performance significantly. The second suggestion will help also, though probably not make your app faster in your case.

Suggestion 1

You call the method GetDataFromDB(string query) very often. This is bad because you create a new SqlConnection and SqlCommand each time. This takes time and resources. Also, if there is any network delay, that is multiplied by the number of calls you are making. So it's just a bad idea.

I suggest that you call that method once and have it populate a collection like a Dictionary<int, string> so that you can quickly look up your Username value from the user id key.

Like this:

// In the DataField class, have this code.// This method will query the database for all usernames and user ids and// return a Dictionary<int, string> where the key is the Id and the value is the // username. Make this a global variable within the DataField class.Dictionary<int, string> usernameDict = GetDataFromDB("select id, username from Users");// Then in the GetValue(int userId) method, do this:public string GetValue(int userId){    // Add some error handling and whatnot.     // And a better name for this method is GetUsername(int userId)    return this.usernameDict[userId];}

Suggestion 2

Here is another way that you can improve things, though slightly in this case—use the StringBuilder class. There are significant performance gains (here is an overview: http://support.microsoft.com/kb/306822).

SringBuilder sb = new StringBuilder();sb.Append("<table><tr><th>Username</th>");foreach (DataField f in fields){    sb.Append("<th>" + f.Name + "</th>");}// Then, when you need the stringstring html = sb.ToString();

Let me know if you need some more clarification, but what you are asking for is very do-able. We can work this out!

If you make these 2 simple changes, you will have great performance. I guarantee it.

The database design you choose is named Entity-Attribute-Value, a design that is well known for its performance problems. SQL Server team has release a whitepaper for guidance around EAV design, see Best Practices for Semantic Data Modeling for Performance and Scalability.

Alas, you already have the design in place and what can you do about it now? The important thing is to reduce the miriad calls to the dB to one single call, and execute one single set oriented statement to retrieve the data. The name of the game is Table Valued Parameters:

declare @users as UsersType;insert into @users (UserId) values (7), (42), (89);select ut.Id,   ut.Username,   df.Name as DataFieldName,   dfv.Valuefrom Users utjoin @users up on ut.Id = up.UserIdjoin DataFieldValues dfv on ut.Id = dfv.UserIdjoin DataFields df on dfv.DataFieldId = df.Idorder by ut.Id;

For a full example, see this SqlFiddle.

While, strictly speaking, it is possible to retrieve a result on the shape you desire (data field names transposed as column names) using the PIVOT operator, I would very strongly advise against doing so. PIVOT on its own is a performance quagmire, but when you add the dynamic nature of the desired result set is basically impossible to pull it off. The traditional result set consisting of one-row-per attribute is trivial to parse into a table, because the required order by user Id guarantees a clean break between sets of correlated attributes.

This is slow because under the hood you are making 20 x 10 = 200 queries to the database. Correct way would be to load everything in one turn.

You should post some details about the way you load data. If you are using Entity Framework, you should use something called Eager Loading using Include command.

// Load all blogs and related postsvar blogs1 = context.Blogs                      .Include(b => b.Posts)                      .ToList();

Some samples can be found here: http://msdn.microsoft.com/en-us/data/jj574232.aspx

EDIT:

It seems that you are not using the tools .NET Framework gives you. These days you don't have to do your own database access for simple scenarious like yours. Also, you should avoid concatenating string HTML like you do.

I would suggest you to redesign your application using existing ASP.NET controls and Entity Framework.

Here is a sample with step by step instructions for you:http://www.codeproject.com/Articles/363040/An-Introduction-to-Entity-Framework-for-Absolute-B