SQLite：优化计数
在我的应用程序中，我需要显示搜索结果。它获取前 250 个结果，但还需要显示结果总数。
这会减慢一切速度，因为我不能在前 250 行之后停止获取结果，我需要循环遍历所有结果（数百万行）只是为了对它们进行计数。
另一种方法是在前 250 行之后退出循环，然后执行第二个 COUNT() 查询。但一些快速基准测试表明 COUNT() 查询没有利用它之前执行过相同查询的事实。
有什么办法可以优化这样的场景吗？如果我将计数和获取合并在一个查询中而不是两个单独的查询中，会有帮助吗？如果是这样，我该怎么做？
编辑：抱歉，重复 如何计算 C# 中 SQLite 读取器返回的行数？
In my app I need to display search results. It fetches the first 250 results, but also needs to display the total number of results.
This slows everything down, because I can't just stop fetching results after the first 250 rows, I need to loop through them all (millions) just to count them.
Another approach is to exit the loop after the first 250 rows, and do a second COUNT() query after that. But some quick benchmarking showed that the COUNT() query doesn't take advantage of the fact that it has executed the same query before.
Is there any way to optimize a scenario like this? Would it help if I combined the counting and the fetching in one query instead of two seperate ones? And if so, how would I do that?
Edit: Sorry, duplicate of How do I count the number of rows returned in my SQLite reader in C#?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
如果这是在单个表中，您可以使用触发器来保持计数更新。
每个插入触发器都会增加计数；每个删除触发器都会减少它。
请参阅这个类似的问题&回答
If this is within a single table, you can use a trigger to keep the count updated.
Each insert trigger increments the count; each delete trigger decrements it.
See this similar SO question & answer