将Linq2SQL简单查询转换为CompiledQueries以提高应用程序性能 [英] Converting Linq2SQL simple queries to CompiledQueries to increase app performance
问题描述
我正在编写Windows Phone(SDK 7.1)的Silverlight应用程序，并在Windows Phone的Silverlight工具包的LongListSelector控件中显示来自CompactSQL数据库的数据.
列表长约150个项目后，该应用程序确实会减慢加载数据的速度，在页面之间导航以及动画无法显示(我知道使用后台线程将有助于释放UI线程的动画). /p>
我目前经常使用三个查询-每次LongListSelector中的数据更新或页面导航到.我已经将MoviesByTitle转换为CompiledQuery，这对我们有很大帮助，所以我希望对其他两个查询(类型为List<Group<Movie>>的groupedMovies和LongListSelector.ItemSource)执行相同的操作，但是我不能似乎找出正确的语法.
关于如何提高这些查询效率的任何建议-通过使用CompiledQuery还是其他方式?
MoviesByTitle在另一个名为Queries
public static Func<MovieDBContext, IOrderedQueryable<Movies>> MoviesByTitle = CompiledQuery.Compile((MovieDBContext db) => from m in db.Movies orderby m.Title,m.Year select m);
主页中的字段
private static List<String> characters = new List<String> { "#", "a", "b", "c", "d", "e", "f", "g", "h", "i", "j", "k", "l", "m", "n", "o", "p", "q", "r", "s", "t", "u", "v", "w", "x", "y", "z" };
public static List<Group<Movies>> emptyGroups = new List<Group<Movies>>();
在MainPage中的LoadDB()方法内部-在更新数据库时，该方法在OnNavigatedTo和其他几个地方被调用.
//Populates the 'empty' Groups of Movies objects only once.
if (emptyGroups.Count < 1)
{
characters.ForEach(x => emptyGroups.Add(new Group<Movies>(x, new List<Movies>())));
}
IEnumerable<Movies> query = Queries.MoviesByTitle(App.db);
//Groups the objects
IEnumerable<Group<Movies>> groupedMovies = (from t in query
group t by t.GroupHeader into grp
orderby grp.Key
select new Group<Movies>(grp.Key.ToString(), grp));
//Joins the 'empty' group and groupedMovies together for the LongListSelector control
moviesLongList.ItemsSource = (from t in groupedMovies.AsEnumerable().Union(emptyGroups)
orderby t.Title
select t).ToList();
GroupHeader是Movies的属性，是数据库中的一个实体
[Column(CanBeNull=true, UpdateCheck = UpdateCheck.Never)]
public char GroupHeader
{
get
{
char l;
//For Alphabetized Grouping, titles do NOT start with "The ..."
if (this.Title.ToLowerInvariant().StartsWith("the "))
{
l = this.Title.ToLowerInvariant()[4];
}
else
{
l = this.Title.ToLower()[0];
}
if (l >= 'a' && l <= 'z')
{
return l;
}
return '#';
}
set { }
}
Group类如下
public class Group<T> : IEnumerable<T>
{
public Group(string name, IEnumerable<T> items)
{
this.Title = name;
this.Items = new List<T>(items);
}
public string Title
{
get;
set;
}
public IList<T> Items
{
get;
set;
}
...
}
我假定GroupHeader是存储在数据库中的实体，与Movie实体具有1-n的关系.
首先，我在这里看不到3个数据库查询. LINQ表达式并不总是数据库查询(例如，有LINQ to Objects).有时，确定真正发生的事情非常具有挑战性.在这种情况下，最好的朋友是数据库探查器工具或IntelliTrace-它们显示在运行时正在数据库上执行哪些查询.
据我了解的代码，您实际上有1+N个查询:第一个是MoviesByTitle，然后在表达式中有N个查询，这些查询按标题将电影分组.它是N而不是1，因为您将query强制转换为IEnumerable<>，这使不再使它成为查询，而是一个简单的CLR对象，该对象简单地以类似于foreach的方式进行迭代每次需要GroupHeader实体(它是一个实体，不是吗?)时，都会向数据库循环发送查询.
尝试将2个查询合并为一个.您甚至可能不需要使用CompiledQuery.这是一个大概的代码:
// I left this without changes
if (emptyGroups.Count < 1)
{
characters.ForEach(x => emptyGroups.Add(new Group<Movies>(x, new List<Movies>())));
}
// I put var here, but it actually is an IQueryable<Movie>
var query = from m in App.db.Movies orderby m.Title, m.Year select m;
// Here var is IQueryable<anonymous type>, I just can't use anything else but var here
var groupedMoviesQuery = from t in query
group t by t.GroupHeader into grp
orderby grp.Key
select new
{
Movies = grp,
Header = grp.Key
};
// I use AsEnumerable to mark that what goes after AsEnumerable is to be executed on the client
IEnumerable<Group<Movie>> groupedMovies = groupedMoviesQuery.AsEnumerable()
.Select(x => new Group<Movie>(x.Header, x.Movies))
.ToList();
//No changes here
moviesLongList.ItemsSource = (from t in groupedMovies.AsEnumerable().Union(emptyGroups)
orderby t.Title
select t).ToList();
此代码应该可以更好地工作.我实际上所做的是，将您的query从IEnumerable(这是一个仅迭代的CLR对象)变成了IQueryable，该IQueryable可以进一步包装为更复杂的查询.现在只有一个查询可以按标题对所有电影进行分组.应该很快.
我将对代码进行更多的改进以使其运行更快:
- 您使用从数据库读取的实体和某些默认列表的
Union.您随后订购.您可以从Linq2Sql代码中安全删除所有其他顺序("query"和"groupedMoviesQuery") - 与联接相比，这里的分组似乎不是最有效的.为什么不只查询
GroupHeader及其相关的Movie?那应该在db查询中产生一个JOIN，它应该比GROUP BY更有效. - 如果仍然存在性能问题，可以将查询转换为编译查询.
我将为您显示一个原始查询的编译查询示例，其中对上面列表的第一项进行了优化:
class Result
{
public GroupHeader Header {get;set;}
public IEnumerable<Movie> Movies {get;set;}
}
public static Func<MovieDBContext, IQueryable<Result>> GetGroupHeadersWithMovies =
CompiledQuery.Compile((MovieDBContext x) =>
from m in x.Movies
group m by m.GroupHeader into grp
select new Result
{
Movies = grp,
Header = grp.Key
});
I am writing a Silverlight for Windows Phone (SDK 7.1) app and I am displaying data from a CompactSQL DB in a LongListSelectorcontrol from the Silverlight Toolkit for Windows Phone.
Once the list becomes about 150 items long, The app really slows down loading data, navigating to and from pages and animations fail to display (I know using a background thread would help with freeing up the UI thread for animations).
I currently have three queries that I use constantly - everytime the data from LongListSelector is updated or the page is NavigatedTo. I have converted MoviesByTitle into a CompiledQuery and that has helped quite a lot, so I was looking to do the same for my other two queries(groupedMovies and LongListSelector.ItemSource of type List<Group<Movie>>), however I cannot seem to figure out the correct syntax.
Any suggestions on how I might make these queries more efficient - Through the use of CompiledQuery or otherwise?
MoviesByTitle is in another Class called Queries
public static Func<MovieDBContext, IOrderedQueryable<Movies>> MoviesByTitle = CompiledQuery.Compile((MovieDBContext db) => from m in db.Movies orderby m.Title,m.Year select m);
Fields in MainPage
private static List<String> characters = new List<String> { "#", "a", "b", "c", "d", "e", "f", "g", "h", "i", "j", "k", "l", "m", "n", "o", "p", "q", "r", "s", "t", "u", "v", "w", "x", "y", "z" };
public static List<Group<Movies>> emptyGroups = new List<Group<Movies>>();
inside a LoadDB() method in MainPage - this method is called in OnNavigatedTo and in several other places when the DB is updated.
//Populates the 'empty' Groups of Movies objects only once.
if (emptyGroups.Count < 1)
{
characters.ForEach(x => emptyGroups.Add(new Group<Movies>(x, new List<Movies>())));
}
IEnumerable<Movies> query = Queries.MoviesByTitle(App.db);
//Groups the objects
IEnumerable<Group<Movies>> groupedMovies = (from t in query
group t by t.GroupHeader into grp
orderby grp.Key
select new Group<Movies>(grp.Key.ToString(), grp));
//Joins the 'empty' group and groupedMovies together for the LongListSelector control
moviesLongList.ItemsSource = (from t in groupedMovies.AsEnumerable().Union(emptyGroups)
orderby t.Title
select t).ToList();
GroupHeader is a property of Movies and an entity in the DB
[Column(CanBeNull=true, UpdateCheck = UpdateCheck.Never)]
public char GroupHeader
{
get
{
char l;
//For Alphabetized Grouping, titles do NOT start with "The ..."
if (this.Title.ToLowerInvariant().StartsWith("the "))
{
l = this.Title.ToLowerInvariant()[4];
}
else
{
l = this.Title.ToLower()[0];
}
if (l >= 'a' && l <= 'z')
{
return l;
}
return '#';
}
set { }
}
The Group class is as follows
public class Group<T> : IEnumerable<T>
{
public Group(string name, IEnumerable<T> items)
{
this.Title = name;
this.Items = new List<T>(items);
}
public string Title
{
get;
set;
}
public IList<T> Items
{
get;
set;
}
...
}
I assume that GroupHeader is an entity stored in the DB with 1-n relationship to the Movie entity.
First of all, I don't see 3 DB queries here. A LINQ expression is not always a DB query (e.g. there's LINQ to Objects). Sometimes determining what really is going on is quite challenging. The best friend in such cases is a DB profiler tool or the IntelliTrace - they show what queries are being executed on the DB at run time.
As far as I understand the code, you actually have 1+N queries: the first is MoviesByTitle, and then you have N queries in the expression that gets the movies grouped by their headers. It's N instead of 1 because you cast query to IEnumerable<> which makes it no longer a query but a simple CLR object, which is simply iterated in a foreach-like loop sending queries to the DB each time it needs a GroupHeader entity (it's an entity, isn't it?).
Try to combine 2 queries into one. You might even not need to use CompiledQuery. Here's an approximate code:
// I left this without changes
if (emptyGroups.Count < 1)
{
characters.ForEach(x => emptyGroups.Add(new Group<Movies>(x, new List<Movies>())));
}
// I put var here, but it actually is an IQueryable<Movie>
var query = from m in App.db.Movies orderby m.Title, m.Year select m;
// Here var is IQueryable<anonymous type>, I just can't use anything else but var here
var groupedMoviesQuery = from t in query
group t by t.GroupHeader into grp
orderby grp.Key
select new
{
Movies = grp,
Header = grp.Key
};
// I use AsEnumerable to mark that what goes after AsEnumerable is to be executed on the client
IEnumerable<Group<Movie>> groupedMovies = groupedMoviesQuery.AsEnumerable()
.Select(x => new Group<Movie>(x.Header, x.Movies))
.ToList();
//No changes here
moviesLongList.ItemsSource = (from t in groupedMovies.AsEnumerable().Union(emptyGroups)
orderby t.Title
select t).ToList();
This code should work way better. What I actually did is that I turned your query from IEnumerable which is an iterate-only CLR object to an IQueryable which can be further wrapped into a more complex query. Now there's only one query which gets all movies grouped by headers. It should be fast.
I would introduce even more improvements to the code to make it work even faster:
- You use
Unionof entities read from the DB and of some default list. You order it afterwards. You can safely remove all other orderings from your Linq2Sql code ('query' and 'groupedMoviesQuery') - Grouping seems to be not the most efficient here as compared to a join. Why don't you just query
GroupHeaders including their relatedMovies? That should produce a JOIN in the db query which should be more efficient than a GROUP BY. - If you still have issues with performance, you can turn the query into a compiled query.
I'll show an example of a compiled query for you original logic with optimization of the first item of the list above:
class Result
{
public GroupHeader Header {get;set;}
public IEnumerable<Movie> Movies {get;set;}
}
public static Func<MovieDBContext, IQueryable<Result>> GetGroupHeadersWithMovies =
CompiledQuery.Compile((MovieDBContext x) =>
from m in x.Movies
group m by m.GroupHeader into grp
select new Result
{
Movies = grp,
Header = grp.Key
});
这篇关于将Linq2SQL简单查询转换为CompiledQueries以提高应用程序性能的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
