Fastest way to retrieve data from database

user_2063910 0
Viewed 50341

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)
 //...
}
sql-server-2008 asp.net sql .net c#
10 Answers

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 posts
var 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

user_30700520

How are you accessing the database? Check the generated SQL from those queries with the Profiler, if you are using EF, for example. Don't make connection every time in the foreach loop.

I would not build the html on the server side as well. Just return the object for a page datasource control.

user_10148930

Make sure that you are not making a connection to the database for each loop.

As I can see, the f.GetValue(u) part is a method that returns a string value that was fetched from the database.

Put the data in an object once and for all and do the same thing as f.GetValue(u) is doing here.

user_23108740
Related