Fastest way to retrieve data from database

Question

I am working on a ASP.NET project with C# and Sql Server 2008.

I have three tables:

Each user has a specific value for each data field, and this value is stored in the DataFieldsValues.

Now I want to display a report that looks like this:

I have created the objects User, and DataField. In the DataField object, there is the Method string GetValue(User user), in which I get the value of a field for a certain user.

Then I have the list of Users List<User> users and the list of DataFields List<DataField> fields and I do the following:

string html = string.Empty; html += "<table>"; html += "<tr><th>Username</th>"; foreach (DataField f in fields) {    html += "<th>" + f.Name + "</th>"; } html += "</tr>"  foreach (User u in users) {    html += "<tr><td>" + u.Username + "</td>"    foreach (DataField f in fields)    {       html += "<td>" + f.GetValue(u) + "</td>";    }    html += "</tr>" } Response.Write(html); 

This works fine, but it is extremely slow, and I am talking about 20 users and 10 data fields. Is there any better way in terms of performance to achieve this?

EDIT: For each parameter inside the classes, I retrieve the value using the following method:

public static string GetDataFromDB(string query) {     string return_value = string.Empty;     SqlConnection sql_conn;     sql_conn = new SqlConnection(ConfigurationManager.ConnectionStrings["XXXX"].ToString());     sql_conn.Open();     SqlCommand com = new SqlCommand(query, sql_conn);     //if (com.ExecuteScalar() != null)     try     {         return_value = com.ExecuteScalar().ToString();     }     catch (Exception x)     {     }     sql_conn.Close();     return return_value; }  

For instance:

public User(int _Id) { this.Id = _Id this.Username = DBAccess.GetDataFromDB("select Username from Users where Id=" + this.Id)  //... } 

Answer 1

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 string string 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.

Answer 2

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.Value from Users ut join @users up on ut.Id = up.UserId join DataFieldValues dfv on ut.Id = dfv.UserId join DataFields df on dfv.DataFieldId = df.Id order 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.