Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: C# Sqlite
Last week, when I used SQLite as database for my application logs, everything is ok when the log records are less then 1000.
When the records up to 20000, it is slow when I use Queryable.Skip() extension method to fetch paged records.
Then I was determined to write a demo to find the problem.
 
Development enviroment:VS 2008 + SP1, Win7 Ultimate
Db connector:System.Data.SQLite.dll,download from here
Db create sql:
CREATE TABLE Class(
    [ClassID] integer PRIMARY KEY AUTOINCREMENT NOT NULL,
    [ClassName] varchar(50) NOT NULL,
    [GradeID] integer NOT NULL);
 
Linq to entity with SQLite auto generate code
<pre lang="xml">//------------------------------------------------------------------------------
// <auto-generated>
//     This code was generated by a tool.
//     Runtime Version:2.0.50727.5444
//
//     Changes to this file may cause incorrect behavior and will be lost if
//     the code is regenerated.
// </auto-generated>
//------------------------------------------------------------------------------

[assembly: global::System.Data.Objects.DataClasses.EdmSchemaAttribute()]
 
// Original file name:
// Generation date: 2011/6/8 22:08:41
namespace LinqToEntityWithSQLite
{
 
    /// <summary>
    /// There are no comments for SchoolEntities in the schema.
    /// </summary>
    public partial class SchoolEntities : global::System.Data.Objects.ObjectContext
    {
        /// <summary>
        /// Initializes a new SchoolEntities object using the connection string found in the 'SchoolEntities' section of the application configuration file.
        /// </summary>
        public SchoolEntities() :
                base("name=SchoolEntities", "SchoolEntities")
        {
            this.OnContextCreated();
        }
        /// <summary>
        /// Initialize a new SchoolEntities object.
        /// </summary>
        public SchoolEntities(string connectionString) :
                base(connectionString, "SchoolEntities")
        {
            this.OnContextCreated();
        }
        /// <summary>
        /// Initialize a new SchoolEntities object.
        /// </summary>
        public SchoolEntities(global::System.Data.EntityClient.EntityConnection connection) :
                base(connection, "SchoolEntities")
        {
            this.OnContextCreated();
        }
        partial void OnContextCreated();
        /// <summary>
        /// There are no comments for Class in the schema.
        /// </summary>
        public global::System.Data.Objects.ObjectQuery<Class> Class
        {
            get
            {
                if ((this._Class == null))
                {
                    this._Class = base.CreateQuery<Class>("[Class]");
                }
                return this._Class;
            }
        }
        private global::System.Data.Objects.ObjectQuery<Class> _Class;
        /// <summary>
        /// There are no comments for Class in the schema.
        /// </summary>
        public void AddToClass(Class @class)
        {
            base.AddObject("Class", @class);
        }
    }
    /// <summary>
    /// There are no comments for SchoolModel.Class in the schema.
    /// </summary>
    /// <KeyProperties>
    /// ClassID
    /// </KeyProperties>
    [global::System.Data.Objects.DataClasses.EdmEntityTypeAttribute(NamespaceName="SchoolModel", Name="Class")]
    [global::System.Runtime.Serialization.DataContractAttribute(IsReference=true)]
    [global::System.Serializable()]
    public partial class Class : global::System.Data.Objects.DataClasses.EntityObject
    {
        /// <summary>
        /// Create a new Class object.
        /// </summary>
        /// <param name="classID">Initial value of ClassID.</param>
        /// <param name="className">Initial value of ClassName.</param>
        /// <param name="gradeID">Initial value of GradeID.</param>
        public static Class CreateClass(long classID, string className, long gradeID)
        {
            Class @class = new Class();
            @class.ClassID = classID;
            @class.ClassName = className;
            @class.GradeID = gradeID;
            return @class;
        }
        /// <summary>
        /// There are no comments for Property ClassID in the schema.
        /// </summary>
        [global::System.Data.Objects.DataClasses.EdmScalarPropertyAttribute(EntityKeyProperty=true, IsNullable=false)]
        [global::System.Runtime.Serialization.DataMemberAttribute()]
        public long ClassID
        {
            get
            {
                return this._ClassID;
            }
            set
            {
                this.OnClassIDChanging(value);
                this.ReportPropertyChanging("ClassID");
                this._ClassID = global::System.Data.Objects.DataClasses.StructuralObject.SetValidValue(value);
                this.ReportPropertyChanged("ClassID");
                this.OnClassIDChanged();
            }
        }
        private long _ClassID;
        partial void OnClassIDChanging(long value);
        partial void OnClassIDChanged();
        /// <summary>
        /// There are no comments for Property ClassName in the schema.
        /// </summary>
        [global::System.Data.Objects.DataClasses.EdmScalarPropertyAttribute(IsNullable=false)]
        [global::System.Runtime.Serialization.DataMemberAttribute()]
        public string ClassName
        {
            get
            {
                return this._ClassName;
            }
            set
            {
                this.OnClassNameChanging(value);
                this.ReportPropertyChanging("ClassName");
                this._ClassName = global::System.Data.Objects.DataClasses.StructuralObject.SetValidValue(value, false);
                this.ReportPropertyChanged("ClassName");
                this.OnClassNameChanged();
            }
        }
        private string _ClassName;
        partial void OnClassNameChanging(string value);
        partial void OnClassNameChanged();
        /// <summary>
        /// There are no comments for Property GradeID in the schema.
        /// </summary>
        [global::System.Data.Objects.DataClasses.EdmScalarPropertyAttribute(IsNullable=false)]
        [global::System.Runtime.Serialization.DataMemberAttribute()]
        public long GradeID
        {
            get
            {
                return this._GradeID;
            }
            set
            {
                this.OnGradeIDChanging(value);
                this.ReportPropertyChanging("GradeID");
                this._GradeID = global::System.Data.Objects.DataClasses.StructuralObject.SetValidValue(value);
                this.ReportPropertyChanged("GradeID");
                this.OnGradeIDChanged();
            }
        }
        private long _GradeID;
        partial void OnGradeIDChanging(long value);
        partial void OnGradeIDChanged();
    }
}
 
 
Demo application code:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Diagnostics;
namespace LinqToEntityWithSQLite
{
    class Program
    {
        static void Main(string[] args)
        {
            InsertRecords();
            QueryWithoutSkip();
            QueryWithSkip();
            Console.ReadKey();
        }
        static void QueryWithoutSkip()
        {
            Console.WriteLine("Time elapsed of query without Skip() method:");
            SchoolEntities context = new SchoolEntities();
            for (int i = 0; i < context.Class.Count(); i +=1000)
            {
                Stopwatch sw = new Stopwatch();
                sw.Start();
                var query = context.Class.OrderBy(a => a.ClassID).Take(100).ToList();
                sw.Stop();
                Console.WriteLine(sw.Elapsed);
            } 
        }
        static void QueryWithSkip()
        {
            Console.WriteLine("Time elapsed of  query with Skip() method:");
            SchoolEntities context = new SchoolEntities();
            int totalCount = context.Class.Count();
            for (int i = 0; i < totalCount; i+=1000)
            {
                Stopwatch sw = new Stopwatch();
                sw.Start();
                var query = context.Class.OrderBy(a => a.ClassID).Skip(i).Take(100).ToList();
                sw.Stop();
                Console.WriteLine(sw.Elapsed);
            }
        }
        static void InsertRecords()
        {
            SchoolEntities context = new SchoolEntities();
            if (context.Class.Count() < 10000)
            {
                for (int i = 0; i < 10000; i++)
                {
                    context.AddToClass(Class.CreateClass(0, i.ToString(), 1));
                    Console.WriteLine("Insert {0} record", i);
                }
                context.SaveChanges();
            }        
        }
    }
}
 
 
Demo application running output:
Time elapsed of query without Skip() method:
00:00:00.0227269
00:00:00.0020756
00:00:00.0018700
00:00:00.0018592
00:00:00.0028412
00:00:00.0033459
00:00:00.0028028
00:00:00.0028038
00:00:00.0027993
00:00:00.0028181
Time elapsed of  query with Skip() method:
00:00:00.0116832
00:00:01.2537569
00:00:04.9896806
00:00:10.8717937
00:00:18.7825623
00:00:30.3160950
00:00:42.8874262
00:00:59.2448775
00:01:17.8825198
 

 
My question:
Why is the time elapsed so much in the condition of querying with Skip() method?
This situation only show in the linq to entity, not also in the linq to sql.
Please show me the answer.
Thank you very much.
Posted 7-Jun-11 3:45am
Edited 12-Jun-11 22:31pm
v6
Comments
S Mewara at 7-Jun-11 11:24am
   
Elaborate a little more. What are you looking for here. We see code, some result and then?
guang810828 at 7-Jun-11 21:05pm
   
Sorry,
I want to know why the consumed time increasing so fast when the input parameter "i" increasing.
I'm from china,english is my second language,so forgive me if any error in demonstration.
S Mewara at 8-Jun-11 0:42am
   
Thats ok. Having complete info might help others who are aware of it and can help. :thumbsup:
guang810828 at 8-Jun-11 11:36am
   
Mr Mewara,
May i have your email? So i can send my question and demo to you,hope sincerely you can get me out of puzzle.
Thank you very much!
S Mewara at 8-Jun-11 11:57am
   
It would be better if you share the stuff here with everyone. This would get you must better chance of having answers that can help.
guang810828 at 8-Jun-11 21:45pm
   
Ok,i have improved my question,thank you.
Dalek Dave at 13-Jun-11 3:32am
   
Edited for Grammar, Syntax and Spelling.

1 solution

Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

Hello Dear
I Create table 'class' in SQL server and in VS 2010 I create an entity from class , code which was generated from that entity was like your generated code
just below was different
 
public ObjectSet<Class> Class
{
    get
    {
        if ((_Class == null))
        {
            _Class = base.CreateObjectSet<Class>("Class");
        }
        return _Class;
    }
}
private ObjectSet<Class> _Class;
 
instead of ObjectQuery<Class> my VS generate ObjectSet<Class>
 
I change a little your code and instead of inserting 10000 record I insert 20000 record and run program , result which i saw was never like what you saw
this is result :
 
Time elapsed of query without Skip() method:
00:00:00.0724459
00:00:00.0153574
00:00:00.0155165
00:00:00.0292526
00:00:00.0151775
00:00:00.0639635
00:00:00.0153134
00:00:00.0154977
00:00:00.0150147
00:00:00.0167784
00:00:00.0399328
00:00:00.0214512
00:00:00.0150444
00:00:00.0153898
00:00:00.0148485
00:00:00.0158250
00:00:00.0159237
00:00:00.0156329
00:00:00.0153042
00:00:00.0148778
Time elapsed of  query with Skip() method:
00:00:00.0175573
00:00:00.0308169
00:00:00.0195496
00:00:00.0296008
00:00:00.0230691
00:00:00.0242020
00:00:00.0280368
00:00:00.0261236
00:00:00.0273193
00:00:00.0288516
00:00:00.0295745
00:00:00.0315337
00:00:00.0327198
00:00:00.0330120
00:00:00.0381269
00:00:00.0360055
00:00:00.0399509
00:00:00.0378671
00:00:00.0385484
00:00:00.0415859
 
time consuming in each two query was smaller than 1 seconds
 
I run program again and again but result was almost that . perhaps it is because I use .NET framework 4 or because i use Sql Server 2008 Management Studio , I suggess you change your application setting a run it again
  Permalink  
Comments
Dalek Dave at 13-Jun-11 3:32am
   
A Hard Work Answer.
Have 5.
guang810828 at 13-Jun-11 9:33am
   
Thank you for you help,i also do it with MySQL and SQLServer2000 database,the results are like yours,
but in the condition of SQLite,problem continue.so i think maybe the problem is in the implementation of linq to entity for SQLite,i download "ADO.NET provider for the SQLite database engine
" from here "http://sqlite.phxsoftware.com/",version is 1.0.66.0,i can't be sure whether the inplementation of Skip method in this provider has bug,so may i get you help again,can you do it again with this provider and sqlite database,thank you very much!

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
0 George Jonsson 215
1 Kornfeld Eliyahu Peter 169
2 OriginalGriff 120
3 PIEBALDconsult 110
4 BillWoodruff 85
0 OriginalGriff 6,165
1 DamithSL 4,658
2 Maciej Los 4,087
3 Kornfeld Eliyahu Peter 3,649
4 Sergey Alexandrovich Kryukov 3,294


Advertise | Privacy | Mobile
Web02 | 2.8.141220.1 | Last Updated 13 Jun 2011
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100