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.

Answer 3

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

Answer 4

As Remus Rusanu said, you can get the data you want in the format you require by using the PIVOT relational operator, as far as performance of PIVOT is concerned, I've found that it will depend on the indexing of your tables and the variability and size of the data set. I would be greatly interested in hearing more from him about his opinion of PIVOTs as we are all here to learn. There is a great discussion on PIVOT vs JOINS here.

If the DataFields table is a static set then you may not need to worry about generating the SQL dynamically and you can build yourself a stored procedure; if it does vary you may need to take the performance hit of dynamic SQL(here is an excellent article on this) or use a different approach.

Unless you have further need for the data try to keep the returned set to the minimum you need for display it's a good way to reduce overhead as everything will need to go over the network unless your db is on the same physical server as the web server.

Make sure that you perform as few separate data calls as possible will reduce that time you spend raising and dropping connections.

You should always double-check of data calls within a loop when the control for the loop is based on a (probably related?) data set as this screams JOIN.

When you are experimenting with your SQL try to become familiar with execution plans these will help you figure out why you have slow running queries check out these resources for more info.

Whatever you approach you decide you need to figure out where the bottlenecks are in your code, something as basic as stepping through the execution can help with this as it will allow you to see for yourself where problems lie, this will also allow you to identify for yourself possible problems with your approach and build good design choice habits.

Marc Gravel has some interesting points to make about c# data reading here the article is a bit old but worth a read.

PIVOTing your data.(Sorry Remus ;-) ) Bases on the data example you have provided, the following code will get what you need with no in-query recursion:

--Test Data
DECLARE @Users AS TABLE ( Id int
                        , Username VARCHAR(50)
                        , Name VARCHAR(50)
                        , Email VARCHAR(50)
                        , [Role] INT --Avoid reserved words for column names.
                        , Active INT --If this is only ever going to be 0 or 1 it should be a bit.
                        );

DECLARE @DataFields AS TABLE ( Id int
                        , Name VARCHAR(50)
                        , [Type] INT --Avoid reserved words for column names.
                        );

DECLARE @DataFieldsValues AS TABLE ( Id int
                        , UserId int
                        , DataFieldId int
                        , Value VARCHAR(50)
                        );

INSERT INTO @users  ( Id
                    , Username
                    , Name
                    , Email
                    , [Role]
                    , Active) 
VALUES (1,'enb081','enb081','enb081@mack.com',2,1),
       (2,'Mack','Mack','mack@mack.com',1,1),
       (3,'Bob','Bobby','bob@mack.com',1,0)


INSERT INTO @DataFields  
                    ( Id
                    , Name
                    , [Type]) 
VALUES (1,'DataField1',3),
       (2,'DataField2',1),
       (3,'DataField3',2),
       (4,'DataField4',0)

INSERT INTO @DataFieldsValues  
                    ( Id
                    , UserId
                    , DataFieldId
                    , Value) 
VALUES (1,1,1,'value11'),
       (2,1,2,'value12'),
       (3,1,3,'value13'),
       (4,1,4,'value14'),
       (5,2,1,'value21'),
       (6,2,2,'value22'),
       (7,2,3,'value23'),
       (8,2,4,'value24')

--Query
SELECT *
FROM
(   SELECT  ut.Username, 
            df.Name as DataFieldName, 
            dfv.Value
    FROM @Users ut
    INNER JOIN @DataFieldsValues dfv 
        ON ut.Id = dfv.UserId
    INNER JOIN @DataFields df 
        ON dfv.DataFieldId = df.Id) src
PIVOT
(   MIN(Value) FOR DataFieldName IN (DataField1, DataField2, DataField3, DataField4)) pvt

--Results
Username    DataField1  DataField2  DataField3  DataField4
enb081      value11     value12     value13     value14
Mack        value21     value22     value23     value24

The most important thing to remember is to try things out for yourself as whatever we suggest might be altered by factors at your site that we aren't aware of.

Answer 5

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.

Answer 6

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.

Answer 7

Use Indexed for the primary key field of the table and in the code behind use string builder.

Answer 8

Worst problem there: tons of round trips to the database. Each time you get a value a request goes over the network and it waits for the result.

If you must have the user list in code first, then make sure that:

1. You retrieve all the info in the user list in a single db call. If you have a set of user IDs, send you can send it with a table valued parameter.
2. If the above didn't include the field values, send the list of user IDs and the list of field IDs in 2 table valued parameters to retrieve it all in one go.

That should make a huge difference. With those 2 specific queries you have taken the network noise out of the way and can focus of improving the indexes if necessary.

Another gain you will get is on the whole concatenating strings. First step is to replace with a StringBuilder. The next step is to write to the output stream directly, so you don't need to hold all that data in memory ... but it is unlikely you will need that; and if you do due to too much data you will have trouble with browsers handling that anyway.

ps. not the OP scenario, but for those needing speed in bulk, you want bulk export instead: http://technet.microsoft.com/en-us/library/ms175937.aspx

Answer 9

FAST... USE

1. stored procedures

2. use reader

   SqlDataReader dbReader = mySqlCommand.ExecuteReader();
   
   //if reader has row values
   if (dbReader.HasRows) // while(xxx) for more rows return
   {
        //READ DATA
   }
   

3. DO PROPER INDEXES if need go for partitions...

4. Use and HINTs for SELECT NOLOCK work for me

Query Hints (Transact-SQL) http://technet.microsoft.com/en-us/library/ms181714.aspx

Locking Hints http://technet.microsoft.com/en-us/library/aa213026(v=sql.80).aspx

Yeah the only time I will use LINQ will be if I call a stored procedure.

Search LINQ to SQL

BUT I AM OLD SCHOOL....

This Entity Framework I get of rid of them since Entity Framework 1.0 is good when you do school project...

But is very expensive as compute instance...

READ ALL IN THE MEMORY DO SOMETHING???? WHY I AM PAYING FOR SQL? USE some JSON file structure then....

Answer 10

Instead of DataReader use DataAdapter and Dataset. Execute all queries one time as show below:

  string SqlQuery ="Select * from Users;Select * From DataFields;Select * From DataFieldsValues;";

This will open sqlconnection one time only fire all these three queries and return three different data table in dataset then use your method for rendering.