在进行实际搜索之前计算行数 [英] Counting rows before proceeding to actual searching

问题描述

给定一个 web 应用程序(Java、Spring、Hibernate 和 Sybase 作为 DB)，有几个，比如 5 个不同的搜索屏幕，如果基于用户的条件的搜索结果将超过限制，比如 1000 行，我想先计算.即使用户提供了合理的过滤器和标准，也可能发生超过 1000 次的巨大结果.

Given an web app (Java, Spring, Hibernate and Sybase as DB) with several, say 5 different search screens, I want to count first if the search result based on the user's criteria will be exceeding a limit, say 1000 rows. Results that are huge, going past 1000 can happen even if user provides reasonable filters and criteria.

是否推荐这样做:

1. 从表中选择 count(*) --clauses 等
2. 如果 > 1000，则不进行实际搜索，返回并显示限制错误(告诉用户细化搜索)
3. else if <1000，进行实际搜索并将结果集返回给用户

或者有更好的解决方案来处理这个问题吗?

Or is there a better solution to handle this?

如果这是要走的路，我的后续问题是，我们如何避免重复 sql 查询?因为我理解这样做，将要求我声明相同的搜索 sql，除了 select 子句将只包含 count(*).

If this is the way to go, my followup question would be, how can we avoid duplicating the sql query? Because I understand doing this, will require me to declare the same search sql except the select clause will only contain count(*).

更新

另外，我想避免两件事:1.从执行实际sql的处理2. 通过 ORM 加载/映射域对象(在这种情况下为 Hibernate)* 1 &当我检测到计数 > 1000 时避免使用 2.

Additionally, I want to avoid 2 things: 1. processing from executing the actual sql 2. loading/mapping of the domain objects by the ORM (Hibernate in this case) * both 1 & 2 are avoided when I detect that the count is > 1000.

推荐答案

我根本不会运行 COUNT(*)，只运行带有 LIMIT 1001 的查询代码>.您很可能在计数中生成完全相同的结果集(即，要执行 COUNT，您必须生成结果集)并且下一次命中将来自缓存，或者最坏的情况是您必须重新计算.你只是在做同样的工作两次

I wouldn't run a COUNT(*) at all, just run the query with a LIMIT 1001. It's likely you are generating the exact same result set (i.e., to do the COUNT, you have to generate the result set) in the count and the next hit will be from the cache, or at worst you'll have to recalculate. You're just doing the same work twice

这篇关于在进行实际搜索之前计算行数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！