enb081 enb081 - 1 month ago 8
ASP.NET (C#) Question

Fastest way to retreive data from database

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

I have three tables:

Users DataFields
DataField Values

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:

enter image description here

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

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.