none
querying memory using linq vs querying DB RRS feed

  • Question

  • Hi,

    I am going over a scenario to explain my problem. here it is;

    I have table of cities for example and I was doing an sql query select * from city where city_id=1234 via dataset (xsd). Now I stored those cities into memory at the time of application load. then using simple linq query, I tried to fetch from memory. something like

    Dim mycities = (from item in SharedCitiesDatatable where item.City_ID=1234)

    after doing that I experienced with some performance issue. actually I realized that requesting to memory with linq was slower than requesting to database directly. 

    First test was querying datatable with linq, then I tried to use POCO object and loaded this datatable into myCity object. 

    same linq query was faster than querying directly datatable.

    Then I wanted to know what was causing that slowness when using datatable with linq. I have seen that there is an event called "datarowchanged" event was fired very often. I am not changing any datarow. I am doing only the scenario above but I dont know why this is called.

    Anyone knows why querying datatable is slower than POCO and even slower than querying DB although everything is in memory.

     

    thanks, 


    "Debugging is twice as hard as writing the code in the first place. Therefore, if you write the code as cleverly as possible, you are, by definition, not smart enough to debug it."
    Friday, April 15, 2011 5:32 PM

All replies

  • Did you add a index on the City_ID column in the database? That would speed up the database query.

    If you do in-memory search using the default linq to object you lose the ability to utilize database indexes. You can build an in-memory index but you have to keep the properties and index values in sync.



    The following is signature, not part of post
    Please mark the post answered your question as the answer, and mark other helpful posts as helpful, so they will appear differently to other users who are visiting your thread for the same problem.
    Visual C++ MVP
    Friday, April 15, 2011 7:45 PM
  • yes, I think indexing is difference for large tables with many rows and columns as I have in my case but on the other hand, I tried to accomplish this using EF code First approach and I see that also EF load data slower than dataset (xsd). what is the reason for that?
    "Debugging is twice as hard as writing the code in the first place. Therefore, if you write the code as cleverly as possible, you are, by definition, not smart enough to debug it."
    Saturday, April 16, 2011 6:40 PM