Most performant way to populate list classes from SQL server

Question

Need some strategy help here... I am providing an example in an attempt to clearly communicate my question.
 
From a performance standpoint only (emphasis added)... I am trying to determine the best way to populate the "ThreeLists" class below (which is made up of 3 lists itself).

This method makes 3 calls to stored procedures... a datareader is used to populate the lists.

What would be the most performant method to populate "ThreeLists" ?   Is there a better (meaning faster) way then that illustrated below ?  



    public class ThreeLists
    {
        public List<List1> firstone { get; set; }
        public List<List2> secondone { get; set; }
        public List<List3> thirdone { get; set; }
    }

    public class List1
    {
        public string stufffrom1 { get; set; }
    }

    public class List2
    {
        public string stufffrom2 { get; set; }
    }

    public class List3
    {
        public string stufffrom3 { get; set; }
    }


static void Main()
        {
            TheMethods tm = new TheMethods();

            ThreeLists threelists = new ThreeLists();
            threelists = tm.GetTheLists("stringhere");
            
        }


 public class TheMethods
    {
        public ThreeLists GetTheLists(string criteria)
        {
            ThreeLists three = new ThreeLists();
            
            three.firstone = GetList1("criteria");
            three.secondone = GetList2("criteria");
            three.thirdone = GetList3("criteria");

            return three;

        }

        public List<List1> GetList1(string criteria)
        {
            List<List1> l1 = new List<List1>();

            // call to a stored proc
            // use data reader to populate list one row at a time
            l1.Add(new List1 { stufffrom1 = "stuff1" });

            return l1;
        }

        public List<List2> GetList2(string criteria)
        {
            List<List2> l2 = new List<List2>();

            // call to a stored proc
            // use data reader to populate list one row at a time
            l2.Add(new List2 { stufffrom2 = "stuff2" });
            return l2;
        }

        public List<List3> GetList3(string criteria)
        {
            List<List3> l3 = new List<List3>();

            // call to a stored proc
            // use data reader to populate list one row at a time
            l3.Add(new List3 { stufffrom3 = "stuff3" });

            return l3;
        }



    }

Answer 1

Make one single call to one stored procedure that in turns calls the other three stored procedures and returns three result sets, e.g:

CREATE PROCEDURE spYour AS

EXEC sp1
EXEC sp2
EXEC sp3

You will then get three populated DataTables back if you use an SqlDataAdapter to fill a DataSet:

            DataSet ds  = new DataSet();
            using (SqlConnection c = new SqlConnection("conn string...")) {
              SqlCommand command = new SqlCommand("spFirst");
              command.CommandType = CommandType.StoredProcedure;
              command.Connection = c;

              c.Open();
              using (SqlDataAdapter a = new SqlDataAdapter(command)) {
                a.Fill(ds);
              }
            }

            DataTable sp1 = ds.Tables[0];
            DataTable sp2 = ds.Tables[1];
            DataTable sp3 = ds.Tables[2];

And then you can create the CLR objects and populate them as usual:

            ThreeLists threeLists = new ThreeLists();
            threeLists.firstone = new List<List1>();
            foreach (DataRow dr in sp2.Rows) {
              List1 l1 = new List1();
              //set properties....
              threeLists.firstone.Add(l1);
            }

          //and so on...

The key here is to make as few roundtrips to the database as possible.

Hope that helps.

Please remember to close your threads by marking helpful posts as answer and then start a new thread if you have a new question.

Answer 2

Thanks for the response !

I have heard that data readers are supposed to provide the fastest data access and that other strategies such as using Entity framework provide the best method for maintaining code (at the expense of speed due to the extra overhead).   Is there any overhead associated with using the Data Tables and Adapters ?  Also, do I need to consider different strategies for different sized lists that are being returned ?   

Answer 3

You should not call three stored procedures from one.

This is a bad idea.

.

I would recommend you use entity framework.

If you read a datatable in and then use it you will have code and overhead from boxing/unboxing as you take the value from each property and cast it.

EF obviates this by filling strongly typed classes.

It's way easier to understand and maintain.

.

You should only read the data you need at a time if you don't know for sure the data in a table will be a low number of records.

Assuming this is being presented to a user on a screen you should aim to show a maximum of a few hundred records at a time.  Like 200 to 300.

A very common mistake is to just throw all the data out a table onto the screen.

Then the developer wonders why the 100,000 records are taking a while to show up.

Let the user pick a subset somehow - nobody really wants to wade through thousands of records.

---

Hope that helps.
Recent Technet articles: Property List Editing ;   Dynamic XAML

Answer 4

>>I have heard that data readers are supposed to provide the fastest data access and that other strategies such as using Entity framework provide the best method for maintaining code (at the expense of speed due to the extra overhead).

That's correct.

A data reader will be theoretically faster as it provides a forward-only stream reading of the rows returned from the query. As I said, the most important factor is to reduce the number of roundtrips to the database -calling one stored procedure instead of three different ones is certainly not a bad idea in any way - but you might as well use a SqlDataReader to read the data. Something like this:

      using (SqlConnection connection = new SqlConnection("connection string...")) {
        SqlCommand command = new SqlCommand("spFirst", connection);
        command.CommandType = CommandType.StoredProcedure;

        connection.Open();
        using (SqlDataReader reader = command.ExecuteReader()) {
          while (reader.Read()) {
            //populate from first result set...
          }

          if (reader.NextResult()) {
            while (reader.Read()) {
              //populate from second and third result set...
            }
          }
        }
      }

  

>>Also, do I need to consider different strategies for different sized lists that are being returned ? 

No. There is no faster way to read the data than using a reader. And as I said, the most important factor when it comes to the performance is to reduce the calls that you make to the database.

Please remember to close your threads by marking all helpful posts as answer and then start a new thread if you have a new question.

Answer 5

Calling one stored procedure which calls 3 can be slower than calling three stored procedures separately.

All you're saving is network latency between the machine wants the data and the database.

You can of course ask for all three sets of data asynchronously and each can be returned as it's read.

But you should really ask about such things in  the sql server forum which is full of people who currently work as DBA.

It's a while since I did.

Optimising queries is a small part of my day to day work nowadays.

---

Hope that helps.
Recent Technet articles: Property List Editing ;   Dynamic XAML

Answer 6

In order to provide a little more context here... I am currently returning data using the method I described above via a WCF service.    

The customer is passed as an argument and all the data is returned in the call.  As you can guess, some customers have very little data, and some have a boatload.

When I analyzed the different segments in the call byt time taken in order to find out what was taking the most time... even though the stored procs could take a good bit of time, it appeared that the time it took to populate the class was taking the most time (for "big" customners).   I don't know if that information  changes the suggested approach.  

Answer 7

Choose entity framework.

EF is layered on ADO.net.  Guess what super efficient method it uses to read data?

.

If you have performance issues the first thing to consider is the quantity of data you return.

That is often the only thing you need to optimise.

Do your users really use the entire boatload of data?

Often they're much more interested in todays numbers or this weeks rather than the last 20 years.

(Or some such.)

Can you return fewer columns?

Can you benefit from using LINQ rather than a stored procedure?

For simple to medium complexity data retrieval LINQ is usually pretty much as fast as a stored procedure but offers very flexible criteria.

.

If you have services returning quantities of data then you're often better off with three calls asking for subsets of the data you want rather than one big one.

Another aspect is these will be asynchronous calls.

You can potentially start doing stuff with one list whilst the other two are still on the way.

.

You shouldn't mark an answer if you don't have one.

---

Hope that helps.
Recent Technet articles: Property List Editing ;   Dynamic XAML