List<T> fill collection performance

Question

Hi, all I am having performance problem when truing to fill typed list. Below is my simplified code:

         public static List<Product> GetProducts()
         {
         List<Product> products = new List<Product>();            
         using (DbQuery query = new DbQuery()) //this is database access class
         {

            query.CommandText = "SELECT ProdID, ProdName,Price FROM SomeTable " ;+

            using (IDataReader rdr = query.ExecuteReader())
            {
               while (rdr.Read())
               {
                  Product prd = new Product();
                  prd.ProdID = DbQuery.ReadInt ( rdr, "ProdID", -1 );
                  prd.ProdName = DbQuery.ReadString(rdr, "ProdName", "");
                  prd.Price = DbQuery.ReadDouble(rdr, "Price", 0);
                  products.Add(prd);
               }
            }
         }
         }

I also have simple struct Product (ProdID, ProdName,Price).

my problem is that it takes 4 seconds to execute GetProducts(). The query returns about 600 records and it takes miliseconds to return result, so I am perrty sure that filling up products collection takes all this time. Am I doing something inefficient here? Please, help. Thanks, Gerda

Answer 1

Have you tried to put an index on the fields you're filtering on? This will definitely speed up your queries versus not having indices.

Answer 2

List insertion is very fast. Something else must be going on here. Try timing it with and without products.Add(prd) commented out. I suspect you'll find they're almost identical.

Here's how I'd solve this: comment out one line at a time until you see a big jump in performance-- then you'll have found your culprit.

Another thing to watch out for: database performance is not static. For example, a query which takes 4 seconds to execute the first time might only take 100 msecs to execute the second time, because all the data is cached into RAM so no physical I/O needs to be done the second time. And the very first query to a database will be slow as SQL spins up lots of things. My suggestion: always start with a warm cache, meaning run the code once and then run it again, and only consider the second timing real. Even better, run the code 10 times, then time it 10 times, and throw out the first 10 and average the second.

Answer 3

I would be surprised if that is your bottleneck. I would try making a little test app that has a mock database connection.

You can usually do a little bit better if you can pre-size the list. If there is a reasonable minimum size for the query, or if you have some other way of knowing (or reasonably guessing) a good initial size you might get a tiny boost.

Answer 4

Just from the sounds of it, this has to be a database performance or latency issue (if connecting remotely.)

Answer 5

Hi there.

As Justin recommends, I personally would start commenting out sections of code until I found what was causing the bottleneck. For example, I might start by making the routine only run the database-centric code:

public static List<Product> GetProducts()
        {
            List<Product> products = new List<Product>();

            using (DbQuery query = new DbQuery())
            {
                query.CommandText = "SELECT ProdID, ProdName,Price FROM SomeTable ";

                using (IDataReader rdr = query.ExecuteReader())
                {
                    while (rdr.Read())
                    {
                        //Product prd = new Product();
                        //prd.ProdID = DbQuery.ReadInt(rdr, "ProdID", -1);
                        //prd.ProdName = DbQuery.ReadString(rdr, "ProdName", "");
                        //prd.Price = DbQuery.ReadDouble(rdr, "Price", 0);
                        //products.Add(prd);
                    }
                }
            }

            return products;
        }

By commenting out the section which creates the Product objects and adds them to the list, I can see how fast the database related code runs. If that runs quicker then 4 seconds then I know that it's something to do with the Product object creation and/or the calls to DBQuery which are taking a long time to complete.

It's trial and error, but since this routine if very simple, it should not take long to do this type of analysis.

Cheers. Jas.