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">
[assembly: global::System.Data.Objects.DataClasses.EdmSchemaAttribute()]
namespace LinqToEntityWithSQLite
{
public partial class SchoolEntities : global::System.Data.Objects.ObjectContext
{
public SchoolEntities() :
base("name=SchoolEntities", "SchoolEntities")
{
this.OnContextCreated();
}
public SchoolEntities(string connectionString) :
base(connectionString, "SchoolEntities")
{
this.OnContextCreated();
}
public SchoolEntities(global::System.Data.EntityClient.EntityConnection connection) :
base(connection, "SchoolEntities")
{
this.OnContextCreated();
}
partial void OnContextCreated();
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;
public void AddToClass(Class @class)
{
base.AddObject("Class", @class);
}
}
[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
{
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;
}
[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();
[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();
[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.
Submit an article or tip