日期范围linq查询效率
我的任务是设计一个页面来为 SQL Server 库存数据库生成库存/销售报告，其中包括以下内容：期初库存、日终库存、已交付商品、给定日期或日期范围内的已售商品。
我最终做的是使用 for 循环来遍历需要统计数据的天数范围，并保留从查询返回的每一天的统计数据的运行记录。
零件表有一个名为“数量”的字段，用于跟踪库存中每个项目的当前库存。交货表包含交货时间的记录，销售表包含销售时间的记录。
由于某些限制，我只能通过获取当前库存数量并从中减去交付数量并加上当天销售的数量来计算出给定商品的某一天的期末库存。然后，我在所需的日期范围内每天执行 x 次，同时保留总数的运行记录。
为了获取一天的统计信息，以下查询可以充分执行，但是当需要一系列天的统计信息时，性能会严重恶化。正如我所说，我对 linq 不是很精通，并且非常感谢任何有助于提高以下查询性能的帮助。
编辑：代码被破坏
I've been tasked with designing a page to generate an inventory/sales report for a sql server inventory database consisting of the following: beginning stock, end of day stock, delivered items, sold items for a given day or range of days.
What I ended up doing is using a for loop to go through the range of days that stats are needed for and keeping a running tally of the stats for each day that is returned from my query.
The parts table has a field named quantity that keeps track of the current stock for each item in the inventory. The deliveries table contains records of when items are delivered and the sales table contains records of when sales are made.
Because of certain restrictions, I've been only able to figure out the ending stock for a certain day for a given item by taking the current stock quantity and subtracting from it the quantity delivered and adding to that number the quantity sold for that day. I then do this x number of times for each day in the range of dates needed while keeping a running tally for the totals.
To get the stats for a single day, the following query performs adequately, but when stats are needed for a range of days, the performance deteriorates quite badly. As I said I'm not very proficient with linq and would really appreciate any help to improve the following query's performance.
edit: code was broken
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
提高性能的最佳方法是将所有这些逻辑移至数据库，例如通过创建视图。在连接和探索数据方面，很难比数据库更快。
The best way to improve performance is to move all this logic to the database, by creating a view, for example. It's very hard to be faster than a DB when it comes to joining and groping data.
对于这种复杂的查询，LINQ 根本不是适合该工作的工具。 “统计”视图将为您提供一些易于查询的内容，以获取获取个人统计数据所需的信息，然后应该可以轻松地从那里获得汇总信息
For a complex query of this kind, LINQ is simply not the right tool for the job. A "Stats" view would give you something easy to query for what you need to get individual statistics, then the aggregate should be easy to get from there