locked
Compiled Queries in LINQ to SQL - performance considerations and entity caching RRS feed

  • Question

  • Hello!

     

    This question relates to compiled queries in LINQ to SQL as well as general performance observations / considerations. Also, I pose a few questions regarding patterns related to entity caching.

     

    Performance:

    I am looking at replacing a data tier with LINQ to SQL and would like to get the best performance from LINQ to SQL. Implementing an O/R mapper obviously relieves me from keeping the data tier synchronized with the relational structure, but I am concerned that the query performance of LINQ to SQL may degrade performance.

     

    Currently, my data tier implements the following pattern:

    1. Query central cache for an entity (representing an ID).
      1. Entity was not cached.
        1. Query the data store using a SqlCommand / SqlDataReader.
        2. Hydrate an entity from the SqlDataReader and add the entity to the central cache.
    2. Return entity (or null).

    The performance of this pattern is great. I can query for ~1024 unique entities / second (edit: query the database, not the cache). It's the hydration part of the pattern I'm afraid of.

     

    The above pattern is distributed across many segments in my data tier. Changes to the relational model is a pain as it's currently a manual process to synchronize the entity hydration from the SqlDataReader results.

     

    I was hoping that LINQ to SQL could allow me to focus on other parts of the application by replacing the entities (and hydration process) with LINQ to SQL entities (having that infrastructure hydrate the entities for me automatically).

     

    With the March 2007 CTP installed, I decided to do some performance measurements in order to determine, whether LINQ to SQL (in the current version) would seriously degrade performance. Please note that this is a simple performance test and should only be used as a guide on the performance on the final version. LINQ to SQL (and LINQ in general) is the best thing since sliced bread - I'm hoping my findings may be of some help.

     

    The first performance test uses LINQ to SQL to query for a CmsUser entity. 

    Code Snippet

    Stopwatch watch = new Stopwatch();

    watch.Start();

     

    using (CmsDataContext db = new CmsDataContext())

    {

    for (int i = 0; i < 4096; i++)

    {

    CmsUser u1 = (from c in db.CmsUsers where c.UserID == Guid.NewGuid() select c).FirstOrDefault();

    }

    }

     

    watch.Stop();

     

    The second performance test uses the SqlCommand / SqlDataReader pattern to hydrate a CmsUser entity. 

    Code Snippet

    Stopwatch watch = new Stopwatch();

    watch.Start();

     

    using (SqlConnection connection = new SqlConnection("connection string"))

    {

    for (int i = 0; i < 4096; i++)

    {

    SqlCommand command = new SqlCommand(string.Format("SELECT * FROM CmsUsers WHERE UserID = '{0}'", Guid.NewGuid()), connection);

    command.CommandType = CommandType.Text;

     

    connection.Open();

     

    using (SqlDataReader reader = command.ExecuteReader())

    {

    CmsUser result = new CmsUser();

    while (reader.Read())

    {

    result.UserName = (string) reader["UserName"];

    // Start hydrating the entity from the reader

    // .. property assignments ommitted for clarity

    }

    }

     

    connection.Close();

    }

    }

    watch.Stop();

     

    Please note that the performance tests were run in a loop of 64 iterations (meaning that the code should be fully optimized by the CLR).

    1. The first test completed in ~10 seconds.
    2. The second test completed in ~2 seconds.

    I was a bit surprised to see the results - although the manual entity hydration runs without overhead, I was surprised to see that the LINQ to SQL pattern was more than 5 times slower. I ran the tests several times - same result each time. SQL generated DataContext was spot on.

     

    I'm looking for answers to the performance differences and have come up with some suggestions.

    1. Query Compilation is not implemented (or activated). The Expression Tree is compiled to SQL for each request (even though the signature of the LINQ query is the same for repeated reads). I suspect LINQ to SQL for using SqlDataReaders under the covers for hydrating business entities (indicating that it's the Expression Tree compilation thats causing the lag).
    2. The LINQ to SQL API is compiled in debug mode or haven't been fully optimized yet.

    Please provide your comments on this topic (and the tests). I'm more than happy to change the tests and provide updated results.

     

    Entity Caching:

    I currently use hand crafted entities (as demonstrated in the pattern above) that resides within a central cache. The entities are disconnected from the data store and provides core properties such as byte[] timestamps that are used during the transactional persistence process (i.e. calling SPROCs that update an entity by validating the timestamp).

     

    When a business object is requested, the corresponding entity is requested from the cache (or hydrated from the data store) and passed as a clone to the inside data island for the business object. The business object in turn refers to the internal entity data island whenever somebody asks a property (i.e. CmsUserBusinessObject.UserName forwards the get accessor to the internal CmsUser entity).

     

    Whenever changes are commited to the data store, the internal entities are used during the transaction. This pattern works very well and provides a very high performance.

     

    As mentioned previously, keeping the hydration process (as well as entity definition) synchronized with the relational model is a real pain. I am looking at replacing my handcrafted entities with LINQ to SQL entities.

     

    Because the creation of I was thinking about keeping a static DataContext inside the data tier and re-use my current caching pattern.

    1. Is it recommended practice to keep a reference to the DataContext in a central static class (i.e. a data manager)?
      1. If "no" - what is the recommended practice for caching entities?
      2. If "yes" - considering that retrieving cached entities within the DataContext is quite slow (because the Expression Tree evaluation is slow), should the developer go with a custom lookup table or simply wait for the enabling of compiled queries (or better performance)?
    2. Are there any drawbacks at using the LINQ to SQL generated entities as data islands within business objects (provided entities are cloned in order to prevent dangling references to the central DataContext)?

    Thanks for reading this far. I've been wondering about these questions for some days now and finally decided to voice them in the central LINQ forum. Hopefully somebody can provide some feedback.

     

    Thanks in advance

    Anders Borum / SphereWorks

    Thursday, April 5, 2007 1:01 PM

Answers

  • None of the performance work we've done so far have shown up in the CTP's.  Compiled queries should exist in Beta1 unless I'm mistaken.  Optimized object materialization and mapping will appear by beta2.  The developement his is far ahead of the delivery vehicles by 3-4 months. We have performance tests showing very good results.  The most optimized case is executing with approximately 15% overhead over DataReader.  Other non-optimized cases perform worse of course.
    Saturday, April 7, 2007 1:07 AM
  • Okay, to answer some of your other questions.  DataContext's are intended to be short lived, so you should not keep one in a static variable. The general usage scenario is something akin to a page request.  In future versions you will be able to cache the mapping so the cost of creating a new context will be negligible.  In fact, the default code-generation will set up this static mapping for you automatically. 

     

    The entities are not generally intended to out-live the DataContext either.  Though, nothing is stopping you from doing it.  With deferred loading enabled (the default) your objects will likely refer back to the DataContext and call into it when you navigate through the associations for the first time.  This can cause your DataContext to kick back into life, re-openning the connection and executing queries.  Unless, of course, you dispose the DataContext before that.  You can also use the DataContext with deferred loading disabled.  In this mode your objects will never refer back to the DataContext so it would be safe to keep a hold of them for long periods of time.  If you do this, you can later re-attach your objects to a new instance of a DataContext, make changes and submit them.

     

     

    Saturday, April 7, 2007 7:30 PM

All replies

  • Interesting performance metrics. I have seen mention about adding the ability to prepare the query and use the prepared version for subsequent requests. I don't know if it has made it in the current bits yet. I haven't found it in the help file yet.

    As for your test, there are a couple things you could do to optomize the results.

    1. Your LINQ query will open and close the connection on each loop whereas the other version keeps a single connection open. Either open and close it on each iteration on the datareader version, or pass a already opened connection object into the DataContext object and see if it makes any difference.
    2. Try flipping the order of the tests. I have found compile on first use will give some deceptive results at times.
    3. The first test is searching for a different value each time due to the evaulation of NewGuid in the query. The second test evalutes the NewGuid once and performs the same query each time.
    4. Since you are searching for records with an ID of a newGuid, which likely isn't in the database, are you actually fetching any data?
    5. I would recommend changing your datareader syntax to the following as it should bump your performance even more.: result.UserName = reader.GetString(0); (substitute the 0 with the proper index value)

    I would be interested in seeing how some of these changes would effect your results. I do expect optomized direct datareader code would outperform LINQ, but think an apples to apples comparison should be a bit closer.

     

    Jim Wooley

    http://devauthority.com/blogs/jwooley

    http://linqinaction.net

    Friday, April 6, 2007 3:33 AM
  • Hi Jim

     

    I don't think your observations are 100% accurate. In response to your comments:

    1. The LINQ query is opening / closing the connection on each iteration. However, this is also the case with the second test (note that I'm explicitly opening / closing the connection before / after executing the SqlDataReader on the SqlCommand instance. I'm leaving all the work to the connection pooling in this example).
    2. I already tried flipping the tests with no difference in the test results.
    3. Both tests are evaluating Guid.NewGuid() on each iteration. The query construction (be it LINQ or regular T-SQL) is placed inside the loop thus subject to evaluation in each iteration.
    4. You're correct about the results - I'm not actually fetching any data. However, please note that I carefully placed the initialization outside the reader.Read() block resulting in an initialization regardless of any data being returned. This actually favours the direct T-SQL example because the LINQ example returns null if the result set is empty (hence the SingleOrDefault expression).
    5. Chaning the SqlDataReader syntax to indexers widens the gap between the two tests (although I never program using indexers and have no idea whether LINQ hydrates entities by string or integer lookups).

    Based on your feedback I decided to update the tests a bit. Even though the tests now return actual data from the database there's no difference in the measurements. The LINQ to SQL query is ~500% slower than the direct T-SQL implementation. I am hoping that somebody from the LINQ team is able to provide some additional information - in particular about whether Query Compilation (caching) is available in the current March 2007 CTP.

     

    The updated test constructs a list of IDs that reflect actual values in the database (170 entries taken from the table CMS_Page). This reflects the usage pattern of many applications with fragmented reads. As explained in my original post, I have a central mechanism for caching entities - and it's still the performance of the hydration process in LINQ to SQL I'm trying to benchmark. Once the entities are in the cache, performance is lightning fast but I would like to minimize the lag whenever somebody clears the cache and a lot of entities needs to be hydrated from the data store.

     

    Two methods represent the LINQ to SQL and SqlDataReader / SqlCommand tests. They accept a source list of IDs as well as the connection stirng and whether the connection should be opened / closed for each iteration.

    1. 0.6600693 seconds // Linq to Sql query (connection explicit opened)
    2. 0.5737961 seconds // Linq to Sql query (connection controlled by Linq)
    3. 0.1194771 seconds // SqlCommand / SqlDataReader (connection stays open)
    4. 0.1422770 seconds // SqlCommand / SqlDataReader (connection opened / closed for each iteration)

    These benchmarks align with the original benchmarks presented in the previous post. 0.660 seconds vs. 0.142 seconds.

     

    Code Snippet

    // Initialize list of identifiers for all tests

    List<Guid> idList = new List<Guid>();

    idList.Add(new Guid("ef518593-2110-42c6-860b-027e37b3f1c0"));

    idList.Add(new Guid("c5e9b70c-f026-448d-b3be-04fbb5616732"));

    idList.Add(new Guid("f96e0bfe-9ffb-42bc-8c9f-0531542c53f6"));

    idList.Add(new Guid("e5ce8432-e4b4-40bc-b5e3-0571fb1fcd49"));

    idList.Add(new Guid("d686ccae-fbd6-4cbe-9647-069d2480f530"));

    idList.Add(new Guid("dbacc89d-604f-4f62-9d3d-071e52011db4"));

    idList.Add(new Guid("f5e3018e-9ae2-4a48-91d2-0b6847ae6243"));

    idList.Add(new Guid("eac9c49f-f61e-40d8-b6fe-0c02a875e863"));

    idList.Add(new Guid("704f1479-5a20-4c14-94ec-0c85e74eded3"));

    idList.Add(new Guid("e023fe13-ff7f-498d-92c1-0ceeced2c699"));

    idList.Add(new Guid("7b87c742-f3ff-427e-8eec-0d04c4d5810b"));

    idList.Add(new Guid("f716e10d-d34e-4d99-9101-0d639e318d70"));

    idList.Add(new Guid("56bccb1b-397a-46e4-8e25-0e1368e6f753"));

    idList.Add(new Guid("a0e97ad3-e6e4-4175-8dcb-0f7644d84e78"));

    idList.Add(new Guid("d5b622d0-57ab-4a24-a249-12016fc1dc3e"));

    idList.Add(new Guid("6da7eea8-6f87-4078-950c-1213b6ecd020"));

    idList.Add(new Guid("80799056-2a96-4305-accb-148948a1ea81"));

    idList.Add(new Guid("34631238-bdbf-49ec-88ae-17717a3a45fa"));

    idList.Add(new Guid("ae6d02a1-cb30-4452-8b7c-17e5ddae7372"));

    idList.Add(new Guid("5747873c-5c14-4afc-86e2-19183b9d8ea8"));

    idList.Add(new Guid("5311c55a-775b-49ab-8d92-1b8d23fea5d2"));

    idList.Add(new Guid("1e5e4e3d-cfd2-4fce-a17e-1b9c4d59daa2"));

    idList.Add(new Guid("005cf0c9-04e4-4e85-becd-1daf21e805ca"));

    idList.Add(new Guid("0b9be9c4-c99d-4626-bb3d-1f64dc7cfcc0"));

    idList.Add(new Guid("245f5ef4-bb7d-4bbf-96d8-2016fdd8f7d5"));

    idList.Add(new Guid("2ba04010-8c10-47e6-aa2c-2054797b4699"));

    idList.Add(new Guid("c1398e91-0240-4bcd-a80b-22b0b2b48dd5"));

    idList.Add(new Guid("6e6f9c55-d72e-48dd-bc8b-22ee1dfaa51d"));

    idList.Add(new Guid("811a6a63-28d5-43ca-9f8e-23b0998616e9"));

    idList.Add(new Guid("43ce9355-58d4-4749-b384-269e1813ed4e"));

    idList.Add(new Guid("67bb92a6-be80-4681-b417-28180d06cc65"));

    idList.Add(new Guid("326cf3fb-da2b-48ea-b5c8-299332d3e3c8"));

    idList.Add(new Guid("7c7154fe-d1e3-4440-a723-29dd103f36a6"));

    idList.Add(new Guid("1f1003e7-1da9-46a1-8761-2c16ea68f33c"));

    idList.Add(new Guid("855fb245-8306-4e1d-86b3-2c6e1bb547d8"));

    idList.Add(new Guid("0897404d-454b-430b-b431-2e777ac0b4ff"));

    idList.Add(new Guid("3e3ec7b9-f6c0-4aac-b0f0-3213b087c6d7"));

    idList.Add(new Guid("9bf5c931-06ee-47fa-8fc2-34ce8bb9e78e"));

    idList.Add(new Guid("7365c00e-8b0c-459c-b8bc-3638e157ada7"));

    idList.Add(new Guid("aa4f13fe-2f96-4052-ad47-365e39b97c25"));

    idList.Add(new Guid("226b01ba-1e9e-4176-a8ca-37be4899da20"));

    idList.Add(new Guid("7f4ebfce-44db-490b-b2e8-38fceb1b3c15"));

    idList.Add(new Guid("e604c7ca-6fab-44b9-a78a-3b4da4da8e93"));

    idList.Add(new Guid("c1978942-fcd3-4710-ad80-3dd2e47baa32"));

    idList.Add(new Guid("e0d268f6-e74c-44e5-80a1-42b6f891205b"));

    idList.Add(new Guid("1bdcb127-430e-4843-a27b-432dd2300ba8"));

    idList.Add(new Guid("fc972969-2367-4af4-8b4c-43f5ffb2c79b"));

    idList.Add(new Guid("a7560ed2-add8-47ab-956a-45a326892eef"));

    idList.Add(new Guid("c18eaeff-0b4b-4dd9-b689-45e5f5720dad"));

    idList.Add(new Guid("404b653a-1dc4-4ae3-ae27-464b54633f48"));

    idList.Add(new Guid("55ea5fdd-f165-4b3e-9de1-46715c1caa58"));

    idList.Add(new Guid("ecdced90-f130-4da5-860a-480e33e835fa"));

    idList.Add(new Guid("a81b1706-fe7d-4226-8c96-48db90a702ea"));

    idList.Add(new Guid("a81b1706-fe7d-4226-8c96-48db90a702ef"));

    idList.Add(new Guid("d81b1706-fe7d-4226-8c96-48db90a702ef"));

    idList.Add(new Guid("df1b1706-fe7d-4226-8c96-48db90a702ef"));

    idList.Add(new Guid("f81b1706-fe7d-4226-8c96-48db90a702ef"));

    idList.Add(new Guid("dfeb1706-fe7d-4226-8c96-48db90a702ef"));

    idList.Add(new Guid("dfee1706-fe7d-4226-8c96-48db90a702ef"));

    idList.Add(new Guid("dfeee706-fe7d-4226-8c96-48db90a702ef"));

    idList.Add(new Guid("d8fcc4c9-42b3-49b3-acb6-4a2bde70ec9c"));

    idList.Add(new Guid("fa6f2888-f6d0-408b-b2c3-4b6eff71b52b"));

    idList.Add(new Guid("4dcd422f-49a9-4a70-ba2e-4b950a5d30d7"));

    idList.Add(new Guid("cb86ff0d-86d0-4866-9781-4c367a10fae4"));

    idList.Add(new Guid("0785efcb-e922-4401-a3c7-4dd56971414e"));

    idList.Add(new Guid("5099f697-5fa9-4f7a-acc2-4ea08c9c760b"));

    idList.Add(new Guid("69d1374a-ad2f-4501-bbd8-4f4fc7c53720"));

    idList.Add(new Guid("02689147-3dbe-4b29-aa09-534ab0032aa8"));

    idList.Add(new Guid("69a1102f-ba4e-468c-bd92-560073e0c283"));

    idList.Add(new Guid("0e541e6b-71dc-49f0-b785-5788d800248d"));

    idList.Add(new Guid("7b89fa56-44f3-4bfd-9cae-5888987eba76"));

    idList.Add(new Guid("2273c4a6-6fee-439b-9273-595ad3fdb06f"));

    idList.Add(new Guid("3f163bf1-6232-45ef-ad6a-5becff7f901f"));

    idList.Add(new Guid("ef3c5588-0611-488a-b124-5c8093061009"));

    idList.Add(new Guid("30210222-cadb-4534-a653-60d996eb6efa"));

    idList.Add(new Guid("53fc79df-6ccf-4872-b6f5-60f4ee8947b3"));

    idList.Add(new Guid("253e93f0-6498-4c3b-ac73-62e8525091c1"));

    idList.Add(new Guid("b4f165e6-f69d-403b-b592-63f424c4c7e7"));

    idList.Add(new Guid("a1c6c982-858a-473a-9128-647912e6f57a"));

    idList.Add(new Guid("2051071c-2550-475a-8586-65d105ff3a2c"));

    idList.Add(new Guid("517c7dac-2705-48d5-aebb-66191dc5c92a"));

    idList.Add(new Guid("7bdc159b-222b-4345-a66c-663067b39823"));

    idList.Add(new Guid("7663d3bc-62af-495c-b92e-666fd4118fc0"));

    idList.Add(new Guid("620385fe-179b-4b3d-957b-671f32ee63e5"));

    idList.Add(new Guid("5cda2970-fb6f-4336-9db0-677c2121733b"));

    idList.Add(new Guid("8e228b65-d1a5-44e4-889e-6a2f53b88747"));

    idList.Add(new Guid("b076e8c5-d52d-406b-845a-6b553b27d3a2"));

    idList.Add(new Guid("b86041a0-dc39-4cc4-a498-6cd217232e42"));

    idList.Add(new Guid("ddd3d026-00be-483e-885c-6d3b1cc28081"));

    idList.Add(new Guid("324e0314-0e3a-4a9a-b980-6fd3f7a30ad7"));

    idList.Add(new Guid("9f9f6ed0-385a-4e2b-8eac-730a7ad611c1"));

    idList.Add(new Guid("cd1ad4b9-67d1-4339-9e1e-75d9b5067147"));

    idList.Add(new Guid("79964a97-bfc2-43e9-8024-77de55f1017f"));

    idList.Add(new Guid("c8eaca8b-7c7d-400e-a0e7-7bed86b691ba"));

    idList.Add(new Guid("097cfba4-0d4a-4a8e-803f-7c888fb7c93a"));

    idList.Add(new Guid("c68f34bc-7ede-448a-966d-7ca884994e00"));

    idList.Add(new Guid("70747e13-1e8e-409b-8c90-7ec788657ab0"));

    idList.Add(new Guid("4c9f8431-23b3-4fdb-b5d5-81c9c474893a"));

    idList.Add(new Guid("68ca8c4c-bab4-4cb4-a0ee-82af86b63d7b"));

    idList.Add(new Guid("67a59a33-a875-4cf6-a1e7-83e5c5358a97"));

    idList.Add(new Guid("3a1d1307-0744-4871-93ee-888235220c4d"));

    idList.Add(new Guid("f705ab93-becf-4601-9878-8f39690a8538"));

    idList.Add(new Guid("14b92731-d83a-47e7-abaa-957423140e8e"));

    idList.Add(new Guid("1494fe7f-ce13-40f0-bd8d-96b8618bd8bf"));

    idList.Add(new Guid("8ec1b4db-780c-43d1-a351-96cf373e56bf"));

    idList.Add(new Guid("f682bb88-0879-4ce5-a3da-96f096619275"));

    idList.Add(new Guid("afc69624-cf2c-4b59-b93f-9ae98b43d666"));

    idList.Add(new Guid("72489478-9d01-4bbe-bc30-9d06582012f1"));

    idList.Add(new Guid("79ce881b-5f87-405b-a099-9ded1abfba44"));

    idList.Add(new Guid("e70524c2-e0f9-414e-9cc4-9eb8cad78780"));

    idList.Add(new Guid("11f3bcfa-83c9-46cd-ad3e-9f3e78e67130"));

    idList.Add(new Guid("9fb37eda-6249-4df1-abc9-a1abcb21ea1f"));

    idList.Add(new Guid("947f2b18-1b11-4357-8919-a1d63295126d"));

    idList.Add(new Guid("66b8c06d-70c4-4fcc-96be-a3371c159763"));

    idList.Add(new Guid("80593a60-15f5-4072-8def-a49020bec412"));

    idList.Add(new Guid("6f8573b1-d7b3-4176-8bac-a4ea5726984a"));

    idList.Add(new Guid("aec7ebe9-f28d-484b-b7b4-aaff2bd02df1"));

    idList.Add(new Guid("bfa75bfd-58d9-43b5-9319-ac0289b8deec"));

    idList.Add(new Guid("dcf50fc4-b6fb-46b3-8b2c-ac3020ede0a7"));

    idList.Add(new Guid("c71ee857-2717-499d-8cf4-ad2a461f6b8d"));

    idList.Add(new Guid("58a26318-725e-458f-b9d1-ae0b3bd684a9"));

    idList.Add(new Guid("11db165c-0ea8-4398-913c-afa939b9f60f"));

    idList.Add(new Guid("d2cf399a-2e57-42ac-8e3c-b16646556388"));

    idList.Add(new Guid("e6d429a7-e411-4978-ae5f-b397a4efe437"));

    idList.Add(new Guid("1e1d3f18-0809-40c2-b9e2-b426f589c070"));

    idList.Add(new Guid("93c35b57-402a-4288-9989-bb4d566e5d5b"));

    idList.Add(new Guid("5a16d5b7-dc22-4d6c-8a07-bb672051c5e7"));

    idList.Add(new Guid("2d0b61dd-f04b-4b3f-8df3-bc13a9513478"));

    idList.Add(new Guid("2208c259-57ae-465c-b10e-bd14f89637be"));

    idList.Add(new Guid("748bdd1f-7693-4ee2-b511-be3a86b295a5"));

    idList.Add(new Guid("3394c610-2d70-4f72-8440-c02a68c5c162"));

    idList.Add(new Guid("8ea1c59f-5561-4d89-9341-c13dee7ae6d0"));

    idList.Add(new Guid("d36ad646-b36b-49f8-a360-c1fa69a55e32"));

    idList.Add(new Guid("08e168e6-cbc2-46c9-9e03-c4987a33ce75"));

    idList.Add(new Guid("89fc011d-6aef-4362-b549-c49cda9d0b65"));

    idList.Add(new Guid("58d65247-06a5-4839-9b11-c4f6cd85a9bb"));

    idList.Add(new Guid("c19bfd46-896f-4302-b97a-c9dbec71a286"));

    idList.Add(new Guid("01cb5214-0631-4b53-85e4-cbc0f7fe9fd4"));

    idList.Add(new Guid("c3728109-5186-4a19-a724-ce32d8641172"));

    idList.Add(new Guid("a460b4a2-8b92-4845-b337-ce6cb117589d"));

    idList.Add(new Guid("7e3d481e-cf90-47f3-b6ff-cf9ea84699e4"));

    idList.Add(new Guid("361a33ee-7ccf-4f86-9488-d29a811e4cb1"));

    idList.Add(new Guid("b1c99d84-b3ef-4cd1-bb52-d31d893dd713"));

    idList.Add(new Guid("ec3f8f10-dd36-47fe-9f44-d4b4250e7fdf"));

    idList.Add(new Guid("41f8735c-2529-40a2-a3e5-d7d6e0997946"));

    idList.Add(new Guid("d982637f-047d-471a-b2a8-d861e75d9f68"));

    idList.Add(new Guid("1b0eee91-c4ab-4511-83f0-db5bc636d138"));

    idList.Add(new Guid("36f7a35b-efd3-4dff-ad82-e0c4459f85d2"));

    idList.Add(new Guid("71340433-7875-4dde-bb5c-e12f51f9db78"));

    idList.Add(new Guid("706a44fa-b189-454b-b89a-e12f7e90dba7"));

    idList.Add(new Guid("a5c92f08-4938-46f2-86df-e1b1696bb416"));

    idList.Add(new Guid("c2c9038f-94b5-4e12-b802-e490117c6c99"));

    idList.Add(new Guid("bfd483f7-fbca-4003-8071-e8e28ccc0c9a"));

    idList.Add(new Guid("fe296ee1-2164-4944-b89b-e9c22e8fccd4"));

    idList.Add(new Guid("b7bbd274-e8f0-4f59-ae89-ea0cb4a9d852"));

    idList.Add(new Guid("9e796441-3a24-4255-bf44-ea2a1374fce0"));

    idList.Add(new Guid("af172d69-ce5a-47bc-8335-eba38ac54b6b"));

    idList.Add(new Guid("00fe0215-7cae-455f-8dec-ec23edb747d1"));

    idList.Add(new Guid("724220da-074c-4790-954b-eccff01e7310"));

    idList.Add(new Guid("7b360922-7ec1-4b68-b135-ed029db7c14d"));

    idList.Add(new Guid("6542fe95-eda9-4efa-8496-f08f39ed212b"));

    idList.Add(new Guid("7f13772c-acee-4be5-98eb-f0a7095d776b"));

    idList.Add(new Guid("ee49f862-84d7-4cc6-80ab-f288bf34ec61"));

    idList.Add(new Guid("4b51c75b-7f0d-42d2-9473-f2d12f9024fb"));

    idList.Add(new Guid("7baab392-1f62-4c71-968b-f30357066329"));

    idList.Add(new Guid("cf85a6ca-e944-4ab8-ba63-f4f9c01d4ebc"));

    idList.Add(new Guid("10187564-d5e0-4cff-916c-f5b27ea66cd0"));

    idList.Add(new Guid("cd7b01a4-c6e2-458c-8308-f6a259c08aeb"));

    idList.Add(new Guid("cd34e301-b5ea-401d-a226-f934e40e531d"));

    idList.Add(new Guid("6fd00347-8569-4bea-9e3a-fae81617fa5a"));

     

    string cn = "Data Source=sphereworks;Initial Catalog=Elementum Controls NET;Integrated Security=True";

     

    // Linq to Sql query (connection explicit opened)

    SqlToLinq(idList, cn, true);

     

    // Linq to Sql query (connection controlled by Linq)

    SqlToLinq(idList, cn, false);

     

    // SqlCommand / SqlDataReader (connection stays open)

    SqlDirect(idList, cn, true);

     

    // SqlCommand / SqlDataReader (connection opened / closed for each iteration)

    SqlDirect(idList, cn, false);

     

    Console.ReadLine();

     

    private static void SqlToLinq(List<Guid> idList, string cn, bool open)

    {

    Stopwatch watch = new Stopwatch();

    watch.Start();

    using (SqlConnection connection = new SqlConnection(cn))

    {

    if (open) { connection.Open(); }

    using (CmsDataContext db = new CmsDataContext(connection))

    {

    db.DeferredLoadingEnabled = true;

    for (int i = 0; i < idList.Count; i++)

    {

    CmsPage result = (from c in db.CmsPages where c.PageID == idList[i] select c).FirstOrDefault();

    }

    }

    if (open) { connection.Close(); }

    }

    watch.Stop();

    Console.WriteLine(watch.Elapsed.ToString());

    }

     

    private static void SqlDirect(List<Guid> idList, string cn, bool open)

    {

    Stopwatch watch = new Stopwatch();

    watch.Start();

    using (SqlConnection connection = new SqlConnection(cn))

    {

    if (open) { connection.Open(); }

    for (int i = 0; i < idList.Count; i++)

    {

    SqlCommand command = new SqlCommand("SELECT * FROM CMS_Page WHERE PageID = '" + idList[i] + "'", connection);

    command.CommandType = System.Data.CommandType.Text;

     

    if (!open) { connection.Open(); }

     

    using (SqlDataReader reader = command.ExecuteReader())

    {

    while (reader.Read())

    {

    CmsPage result = new CmsPage();

    result.PageID = (Guid) reader["PageID"];

    result.Name = (string) reader["Name"];

    result.DisplayName = (string) reader["DisplayName"];

    result.Description = (string) reader["Description"].ToString();

    result.CoreXML = (string) reader["CoreXML"].ToString();

    result.CoreCreated = (DateTime) reader["CoreCreated"];

    result.CoreModified = (DateTime) reader["CoreModified"];

    }

    }

    if (!open) { connection.Close(); }

    }

    if (open) { connection.Close(); }

    }

    watch.Stop();

    Console.WriteLine(watch.Elapsed.ToString());

    }

     

    Friday, April 6, 2007 4:12 PM
  • Indeed, I did mis read your original code regarding the GUID initialization and connection opening. (damn brackets...) I knew that the last option would widen the gap, which is why I suggested it. Trying to get the best baseline against which to measure performance.

    I would expect LINQ to SQL to perform slower than the hand-coded version, but would hope for better than 500% poorer performance however. Levels of abstraction typically carry a performance penalty which we try to offset by the increased maintainability of the system. The art of coding is determining where the sweet spot lies. You are going down the right road to see if the technology is a good fit for your needs.

    I would be interested if you compared the results against the LINQ to Entities as well, although that would be a bit overkill for the task at hand.

     

    Jim

    Friday, April 6, 2007 4:27 PM
  • There is also a possibility that when a DataContext instance is created, it will use relection to work out how to map the database table to entity. I am not sure how this could affect the performance of Linq.
    Friday, April 6, 2007 7:47 PM
  • Jim,

     

    I'm hoping that someone from the LINQ team may notice this thread when they return from easter. Perhaps they can explain what's going on under the covers in the LINQ to SQL framework and determine whether further optimization is possible.

     

    When I wrote the second version of the test, I noticed something that may explain (some of) the performance differences. The entity in question (the CmsPage type) maps a column (CoreTimestamp of type SqlTimestamp) as a property with a get accessor (note the missing set accessor). This points in the direction of LINQ to SQL using reflection to hydrate the entity.

     

    Suggestion:

    Perhaps they could provide the entities with a "LoadState(SqlDataReader reader)" method that contained a mapping between the SqlDataReader and the mapped columns. I'm not sure how this actually relates to the support for providing the DataContext with a mapping schema, but I would suspect that this design pattern could improve performance tremendously (i.e. bring it closer to the entity hydration pattern presented previously).

    It would be nice to have the option of instructing the LINQ to SQL framework to rely on a mapping file (the dynamic approach) or an automatically generated mapping schema based on the design in the LINQ Designer. The developer could then choose one approach over the other (instead of having to rely on a generic internal mapping framework that uses a rather expensive reflection implementation).

     

    Code Snippet

    [global::System.Data.Linq.Column(Storage="_CoreTimestamp", Name="CoreTimestamp", DBType="rowversion NOT NULL", IsDBGenerated=true, IsVersion=true, CanBeNull=false)]

    public byte[] CoreTimestamp {

    get { return this._CoreTimestamp; }

    }

     

    I'll see if I can do a similar comparison against LINQ to Entities. However, it is not my impression that LINQ to Entities was created as a high performance (component) tier - which I why I also asked a few questions on entity caching in the starting post.

     

    I'd really like if you could post a few comments on my questions about entity caching. I'd be more than happy to elaborate on the concrete implementation I have in mind if you need further information. Not sure whether the entities generated by LINQ to SQL was supposed to live in a central cache.

     

    Thanks in advance.

     

    Friday, April 6, 2007 8:02 PM
  • Anders,

     

    Did you have any test with a single DataContext instance? With each DataContext instantiation, Linq will collect the internal mapping model using reflection and create all the required accessors. Thus I think most of time was consumed by building this mapping model. If you can have some tests with a single DataContext instance, that will be the performance of Linq itself (the model was only created once).

     

    Unfortunately, DataContext.Clone() does not work properly at the moment. Otherwise, using this function should always using the existing mapping model.

     

    Linq internally works in a clever way that it uses the Storage Accessor to map the database column to the corresponding data field (not property). The performance bottle neck should not be here.

    Friday, April 6, 2007 9:45 PM
  • None of the performance work we've done so far have shown up in the CTP's.  Compiled queries should exist in Beta1 unless I'm mistaken.  Optimized object materialization and mapping will appear by beta2.  The developement his is far ahead of the delivery vehicles by 3-4 months. We have performance tests showing very good results.  The most optimized case is executing with approximately 15% overhead over DataReader.  Other non-optimized cases perform worse of course.
    Saturday, April 7, 2007 1:07 AM
  • In response to the question on the tests using a new DataContext instance for each iteration, the answer is that I'm using a single DataContext for all of the requests. I already tried initializing the DataContext in each iteration and quickly realized it is a costly operation (hence the requirement for the .Clone() method that provides a new DataContext instance with the original mapping).

     

    I'll look into the Storage Accessor you're talking about.

    Saturday, April 7, 2007 7:39 AM
  • Matt,

     

    Thanks for joining the thread. I'm very happy to read that the read performance of LINQ to SQL is executing with ~15 - 25% overhead compared to a a SqlDataReader (there's a huge difference between that number and 500%). The low overhead is easily traded in favour of ease of maintenance - and I guess the overhead can be remedied by spending more time working a more efficient caching model in the concrete application.

     

    I am still looking for comments on the second part of my original post. Could you please comment on these questions (which are probably raised by many developers these days).

     

    Thanks in advance.

    Saturday, April 7, 2007 7:47 AM
  • Okay, to answer some of your other questions.  DataContext's are intended to be short lived, so you should not keep one in a static variable. The general usage scenario is something akin to a page request.  In future versions you will be able to cache the mapping so the cost of creating a new context will be negligible.  In fact, the default code-generation will set up this static mapping for you automatically. 

     

    The entities are not generally intended to out-live the DataContext either.  Though, nothing is stopping you from doing it.  With deferred loading enabled (the default) your objects will likely refer back to the DataContext and call into it when you navigate through the associations for the first time.  This can cause your DataContext to kick back into life, re-openning the connection and executing queries.  Unless, of course, you dispose the DataContext before that.  You can also use the DataContext with deferred loading disabled.  In this mode your objects will never refer back to the DataContext so it would be safe to keep a hold of them for long periods of time.  If you do this, you can later re-attach your objects to a new instance of a DataContext, make changes and submit them.

     

     

    Saturday, April 7, 2007 7:30 PM
  •  Matt Warren - MSFT wrote:

    The entities are not generally intended to out-live the DataContext either.  Though, nothing is stopping you from doing it.  With deferred loading enabled (the default) your objects will likely refer back to the DataContext and call into it when you navigate through the associations for the first time.  This can cause your DataContext to kick back into life, re-openning the connection and executing queries.  Unless, of course, you dispose the DataContext before that.  You can also use the DataContext with deferred loading disabled.  In this mode your objects will never refer back to the DataContext so it would be safe to keep a hold of them for long periods of time.  If you do this, you can later re-attach your objects to a new instance of a DataContext, make changes and submit them.

     

    How do you re-attach an object to a DataContext to enable tracking?  I don't see any methods to do this.  Refresh(item, RefreshMode.KeepChanges) might be a candidate, but that doesn't seem very intuitive.  Is this another beta 2 feature?

    Tuesday, April 10, 2007 11:42 PM
  • I was working researching something else and happened upon the revised LINQ to SQL documentation at http://msdn2.microsoft.com/en-us/library/bb425822.aspx. It does include a section on the System.Data.Linq.CompiledQuery() method. Based on Matt's statement, I assumed it wouldn't work, but I was able to use it. I'm not sure if it really offered any optomization yet, but it does point to where this option may be headed. I did see that the code in the documentation is not valid at this point, so it was obviously not fully tested, but the method does work (in C# with the current build). The VB code leverages the new anonymous function syntax (akin to lambda expressions) which is not currently available in the March CTP.

     

    With the March CTP, I was able to use the following code:

     

            public static Func<NWindSharp.Nwind1, string, IQueryable<Customer>>
               CustomersByCity = CompiledQuery.Compile((NWindSharp.Nwind1 db, string city) =>
                  from c in db.Customers where c.City == city select c);
           
            public IEnumerable<NWindSharp.Customer> GetCustomersByCity(string city)
            {
                NWindSharp.Nwind1 db = new NWindSharp.Nwind1();
                return CustomersByCity(db, city);
            }

            private void Form1_Load(object sender, EventArgs e)
            {
               Nwind1 dc = new Nwind1();

                dc.Log = Console.Out;
                var query = GetCustomersByCity("London");
                this.dataGridView1.DataSource = query.ToList();
            }

    If anyone one the documentation team is reading this, the following C# documentation is invalid:

    public IEnumerable<Customer> GetCustomersByCity(string city) {
             Northwind db = new Northwind();
             return Queries.CustomersByCity(myDb, city);
    }

    The last line should read "return Queries.CustomersByCity(db, city);" not "myDb, city"

    As for the VB example, the First line of the compilation method reads:

    public Shared Function(Of Northwind, String, IQueryable(Of Customer))

    It is obvious this wasn't tested as the public keyword should have been capitolized.

     

    Jim Wooley

    http://devauthority.com/blogs/jwooley

    http://linqinaction.net

    Wednesday, April 11, 2007 1:09 AM
  • I'll answer my own question, its very easy.  You use the Table.Attach method.  Here is an example...

     

    // user object is detached
    // make sure to set the id and rowversion first
     
    SampleDataContext db = new SampleDataContext();
    db.Users.Attach(user);
     
    // make updates so context can track
    user.FirstName = "blah";
     
    db.SubmitChanges();
     
    Wednesday, April 11, 2007 2:16 AM
  • Jim,

     

    I also noticed the CompiledQuery feature in the System.Data.Linq namespace. It's good to see you got it working (I assume your managed to compile and run the sample).

     

    I'm looking at implementing the CompiledQuery in the test previously posted - just for the exercise and because I'm curious about the performance impact. I'll post the updated benchmarks here when they become available.

     

    As indicated by Matt Warren (MSFT), the team is already aware of the performance benefits of using compiled queries (as well as caching the model meta data). Let's hope they come up with a clean and performant solution.

     

    Thanks for sharing the code snippet and comments.

    Wednesday, April 11, 2007 8:09 AM
  • It would be nice if someone from either the Linq to SQL or the ASP.Net team could blog a bit about caching linq objects in the application cache.  Given your statement about how DataContext's should be short-lived, some concrete examples would help illustrate how to mix cached reference data (like site taxonomies, hierarchical data or scalar objects like article bodies) with mutable objects that should be persisted.

     

    Take the example of a threaded discussion where each comment might have a collection of children and a parent (or null for a root).  If one wanted to cache the entire thread, what's the best way to force all items to be loaded so it can be detached from the context? 

     

    Related to this, how does object equality work with objects created from different contexts?  Within a DataContext, two queries that return the same item will return the same reference.  Comparisons work easily as expected and only a single item exists in memory and is shared.  If the owning DataContext is closed, and the same item is requested by a new DataContext, will it return the same reference as the first two?  If not, it would seem to add a lot of work to users as they'll need to create their own Equals overrides in the partial class for each item.  It's also less efficient in some cases as there are now two copies of the same data in memory instead of one.  If you consider that an object might have a large graph of connected objects, then you now have the difficult problem of comparing items and duplication of memory. 

     

    More insight into all of this would be greatly appreciated.

     

    Thanks,

    --Oren

    Wednesday, April 11, 2007 9:40 PM
  • I just ran into a great use of compiled queries in the wild, myself. Read about it on my blog: http://jdconley.com/blog/archive/2007/11/28/linq-to-sql-surprise-performance-hit.aspx
    Thursday, November 29, 2007 6:01 AM
  • LINQ to SQL CompiledQuery is good option, but things that needs to be taken into consideration while using the same is that first time when it is called it takes more time than a routine query would take. Refer to below link...

    • Edited by Expert Comment Saturday, September 11, 2010 2:14 PM Solution
    Saturday, September 11, 2010 2:13 PM