Unexpected results with Compiled Queries and LINQ to SQL

SOLUTION:

Ok fired up SQL profiler and see my problem, the non compiled query seems to not evaluate the query until you actualy use the object. But the Compiled version is hitting the database straight away. And as I was not using this data the non compiled version seemed fast as it never hit the database to get the data.. So added a .ToList() on my non compiled query and am now getting the results I expected:

Non Compiled: 143ms -Compiled: 78ms


Sorry to all, any input is always appreciated...

I am a bit confused at the moment. Decided to test out compiled queries VS non compiled against northwind and the products table. And got some results I was not expecting.

I created my NorthwindDataContext and created a partial Product class where I added some helper methods:

partial class Product
        {

            #region Non-Compiled

            public static IEnumerable<Product> GetAllProducts()
            {

                   NorthwindDataContext db = new NorthwindDataContext();
               
                        IEnumerable<Product> query = from p in db.Products
                                                                       select p;
                        return query;

                                 
            }

            #endregion

            //----------------------------------------------------------------------------------//

            #region Compiled

            protected static Func<NorthwindDataContext, IQueryable<Product>>
                GetAllProductsCompiledQuery = System.Data.Linq.CompiledQuery.Compile((NorthwindDataContext db) =>
                    from p in db.Products
                    select p);

            public static IEnumerable<Product> GetAllProductsCompiled()
            {
                    NorthwindDataContext db = new NorthwindDataContext();
               
                         IEnumerable<Product> query = Product.GetAllProductsCompiledQuery(db);

                          return query;  

                                   
            }

            #endregion

        }

One method allows me to get a list of all products not using a compiled query and the other uses my compiled query to get the list of products.

My test harness looks like so:

            // Run Non-Cached query test...

            sw.Start();
            for (int i = 0; i < 1000; i++)
            {
                IEnumerable<Product> products = Product.GetAllProducts();
            }
            sw.Stop();

            Response.Write("Non Compiled: " + sw.ElapsedMilliseconds.ToString() + "ms -");
           
            //----------------------------------------------------------------------------//
           
            sw.Reset();

            // Run cached query test

            sw.Start();
            for (int i = 0; i < 1000; i++)
            {
                IEnumerable<Product> productsCompiled = Product.GetAllProductsCompiled();
            }
            sw.Stop();
            Response.Write("Compiled: " + sw.ElapsedMilliseconds.ToString() + "ms");

The result times as follows (I run it a few times and posted random results...)

No Compiled: 91ms -Compiled: 333ms
Non Compiled: 85ms -Compiled: 328ms
Non Compiled: 86ms -Compiled: 334ms

From what I can see here cached queres are running slower. To make matters worse I changed my helper functions to accept a DataContext so that I could create one at the start of the app and reuse this throughout. This gave me these results(again over a few times):

Non Compiled: 25ms -Compiled: 916ms
Non Compiled: 24ms -Compiled: 904ms
Non Compiled: 24ms -Compiled: 920ms


So reusing the one DataContext gives me a visible improvement on my NonCompiled version but Slows the Compiled version down.... WTTTTTFFFFFF

Can someone shed some light on this for me.

Cheers

Stefan