英文:
Entity Framework Core skip and take from the back?
问题
我有以下查询：
IQueryable<SellerProductDTO> result = _dbContext.Products
.Where(product => EF.Property<Guid?>(product, "SellerId") != null && EF.Property<Guid?>(product, "SellerId") == sellerId
&& (!string.IsNullOrEmpty(phrase) ? product.SearchVector.Matches(EF.Functions.ToTsQuery("english", phrase)) : true))
.OrderByDescending(td => td.SearchVector.Rank(EF.Functions.ToTsQuery("english", phrase)));
List<Product> products = await result.Skip(page * pageSize).Take(pageSize).ToListAsync();
我想要优化它，以便如果结果很大且客户端提供了较小的分页页面大小，从第一页导航到最后一页不会花费太长时间。我正在考虑如果客户端请求的页面越过了一半点，就从后面跳过和获取。我该如何实现这一点？谢谢。
英文:
I have the following query:
IQueryable<SellerProductDTO> result = _dbContext.Products
.Where(product => EF.Property<Guid?>(product, "SellerId") != null && EF.Property<Guid?>(product, "SellerId") == sellerId
&& (!string.IsNullOrEmpty(phrase) ? product.SearchVector.Matches(EF.Functions.ToTsQuery("english", phrase)) : true))
.OrderByDescending(td => td.SearchVector.Rank(EF.Functions.ToTsQuery("english", phrase)));
List<Product> products = await result.Skip(page * pageSize).Take(pageSize).ToListAsync();
I would like to optimize it so that if the result is big and the client provides small page size for pagination, navigating from 1st to the last page should not take a long time. I am thiking if the page that the client requests crosses the half point, skip and take from the back instead. How could I achieve that? Thanks.
答案1
得分: 0
选择 1
基本上，所有内置的文本搜索与（表面上的）任何数据库都在速度方面表现不佳。你基本上必须查询整个表格 - 对所有记录进行排名，然后对记录进行排序并返回。
过去，我曾使用 http://sphinxsearch.com/ 创建索引，然后进行查询（带有页码/数量等），获取一些行的标识符，这是创建真正强大的搜索的方法。
选择 2
这有点不太正规，但基本上一旦搜索完成，存储搜索的详细信息和结果（ID 列表应该足够），然后在会话中利用它来获取用户所需的结果。
如果你只是选择了 ID 并存储...也许在会话中，或者也许在数据库中，这可能会起作用。我以前没有做过这个，选择 1 可能是行业标准。
英文:
You've basically got 2 choices
Choice 1
Pretty much all inbuilt text searches with (seemingly) any database suck in terms of speed. Your basically having to query the whole table - rank all the records and then order the records and return.
In the past I've used http://sphinxsearch.com/ to create an index and then you query that (with page/amount etc), get some ids for the rows you want and that's the way to create a truely killer search.
Choice 2
This is a bit more hacky, but basically once the search is done store the details and the results of the search (the list of ids should suffice) and then utilise that for the session to get the desired results for the user.
If you just did select the ids and store ... maybe in a session or maybe in a database that might just work. I've not done this before, and Choice 1 is probably the industry standard.
答案2
得分: -1
使用分布式Redis缓存并设置滑动过期时间。
英文:
Use distributed redis cache with sliding expiration
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论