Results 1 to 8 of 8
  1. #1
    Join Date
    Feb 2010
    Posts
    3

    Unanswered: Comparing 2 linq applications: Unexpected result

    I developed 2 ASP.NET applications using LINQ. One connects to MS SQL Server, another to some proprietary memory structure.
    Both applications work with tables of 3 fields, having 500 000 records (the memory structure is identical to SQL Server table). The controls used are regular: GridView and ObjectDataSource.
    In the applications I calculate the average time needed for each paging click processing.
    LINQ + MS SQL application demands 0.1 sec per page change.
    LINQ + Memory Structure demands 0.8 sec per page change.
    This Is shocking result. Why the application handling data in memory works 8 times slower than the application using hard drive? Can anybody tell me why that happens?

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Is the data in memory a blob or does it have an indexed structure (e.g. b-tree)?

    Imagine searching a 500K heap of records for a specific one.

    *shrug*
    George
    Home | Blog

  3. #3
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    What code are you analyzing with what instrumentation?
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  4. #4
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    SQL Server caches data in memory as well. Is the proprietary memory structure re-inventing the buffer cache?

  5. #5
    Join Date
    Feb 2010
    Posts
    3
    Quote Originally Posted by gvee View Post
    Is the data in memory a blob or does it have an indexed structure (e.g. b-tree)?
    *shrug*
    No, no blob, iust 3 int fields.

  6. #6
    Join Date
    Feb 2010
    Posts
    3
    Quote Originally Posted by Teddy View Post
    What code are you analyzing with what instrumentation?
    Memory handlin code is here
    Code:
    ...
    namespace LinqApp
    {
        public partial class _Default : System.Web.UI.Page
        {
            float tstart, tend;
            Timer Tmr = new Timer();
    
            protected void Page_Load(object sender, EventArgs e) // calculate page load time
            {
                tstart = DateTime.Now.Second + (float)(DateTime.Now.Millisecond)/1000;
                GridView1.DataSourceID = "ODSmem";
                GridView1.DataBind();
                tend = (float)(DateTime.Now.Second) + (float)(DateTime.Now.Millisecond) / 1000; 
                float ts = tend - tstart;
                Label1.Text = ts.ToString();
            }
            protected void BtnLinqMem_Click(object sender, EventArgs e){}
            protected void GridView1_PageIndexChanging(object sender, GridViewPageEventArgs e){}
            protected void GridView1_PageIndexChanged(object sender, EventArgs e){}
        }
    
        public class Customer2
        {
            public static tst[] t1;
    
            public static void Fill()  // Fill in the table
            {
                Random r = new Random(171717);
                t1 = new tst[500000];
    
                for (int i = 0; i < 500000; i++)
                {
                    t1[i] = new tst();
                    t1[i].Id = i;
                    t1[i].Field1 = r.Next(500000);
                    t1[i].Field2 = r.Next(1000);
                }
            }
            IEnumerable<tst> GetQuery(int value)
            {
                if (t1 == null)
                    Fill();
                var q =
                    from a in t1
                    where a.Field1 > value
                    select a;
                return q;
            }
            public IEnumerable<tst> RetrievePage(string sort, int startRowIndex, int maximumRows, int value)        {
                var q = GetQuery(value);
                return q.OrderBy(a => a.Field1).Skip(startRowIndex).Take(maximumRows); ;
            }
            public int RowCount(string sort, int startRowIndex, int maximumRows, int value)        {
                return GetQuery(value).Count();
            }
        }
        public class tst
        {
            int id;
            public int Id         {
                get { return id; }
                set { id = value; }
            }
            int field1;
            public int Field1        {
                get { return field1; }
                set { field1 = value; }
            }
            int field2;
            public int Field2        {
                get { return field2; }
                set { field2 = value; }
            }
        }
    }
    database code is similar, just connecting to MS SQL database via LINQ

  7. #7
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    We don't have nearly enough information to address the original question. Answering that properly would be a dozen or more hour job depending on the scale of the problem and the complexity of the current solutions.

    The answer probably boils down to some combination of a couple of basic facts. First and foremost, MS-SQL uses storage (be that SSD, local spinners, SAN, or other) for long term storage, but the log write is the only part that truly must happen. For small amounts of data like you've described (1.5 million attributes is trivial for a properly configured server), odds are very good that your data will stay in memory. SQL Server is designed to deal with respectable amounts of data (multiple terrabytes), so the engine is tuned for efficiency. I've often seen SQL Server romp over other database engines that intuitively ought to be quick.

    The short answer is that there isn't a short answer, but the results aren't surprising.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  8. #8
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    That makes a lot of sense as uou have paging and filtering in your query. As already mentioned, those operations will be CONSIDERABLY more efficient in SQL Server compared to iterating over a non-optimized, non-indexed memory resident memory structure. For example, you can't do an index seek on a collection without explicitly creating and maintaining the index.. I was asking about the code itself because LINQ does have some odd caveats to keep in mind, but you're not hitting any of them.

    Also, I would encourage you to use the System.Diagnostics.Stopwatch class for performance instrumentation. When you're talking about resolution to within a few fractions of a second, DateTime.Now() introduces some overhead (it's a class, it has to be instantiated just like anything else, and it's low resolution by default) that I personally find unacceptable. By contrast, the Stopwatch class will look for a hardware supported high resolution timer and only default to DateTime.Now() if it can't find something better. Note this is worthless if you're measuring processes running more than a second, but in this case it could be beneficial.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •