英文:
How to design good search funcionality using CriteriaQuery when the number of columns is high and customized?
问题
Sure, here's the translated content:
简介
我正在使用Spring Boot（带有Spring Data JPA）与Angular作为前端。Plant表目前有大约100列。
我正在做的事情，起点
我目前正在处理的任务是优化性能方面的搜索。
我正在尝试实现的用例
Angular搜索结果页面/组件仅显示6列，但用户可以选择用不同的6列进行替换（自定义）。
现在，对我来说，允许用户从100列中进行选择没有意义，所以我们决定将列表限制为较小的范围，比如约30列。
这是代码：
CriteriaBuilder cb = em.getCriteriaBuilder();
CriteriaQuery<Plant> cq = cb.createQuery(Plant.class);
Root<Plant> plant = cq.from(Plant.class);
用户执行搜索并在表中看到6个默认列，如果他更改列，则会返回服务器，还是在每次调用中提取（选择）30列。
英文:
Intro
I'm using spring boot (with spring-data-jpa) with Angular as the frontend. Plant table has currently ~100 columns.
What I'm doing, the starting point
The task I'm working on at the moment is to optimize the search in term of performance.
The use case I'm trying to implement
The Angular search result page/component show only 6 columns but the user has option to replace these columns with different 6 columns (customization).
Now, to me it doesn't make sense to allow the user to select from 100 columns so we decided to limit the list to smaller, let's say ~30.
This is the code:
CriteriaBuilder cb = em.getCriteriaBuilder();
CriteriaQuery<Plant> cq = cb.createQuery(Plant.class);
Root<Plant> plant = cq.from(Plant.class);
The user perform search and see the 6 default columns in a table, if he change the columns to others, would you go back to the server or you fetch (select) the 30 in each call.
答案1
得分: 0
以下是已翻译好的内容：
-
从数据库角度来看：如果您在同一张表上查询6列或30列，性能差异不大。但是，如果额外的列需要与其他表进行连接，情况会有所改变。问题在于如果您选择在自定义期间返回到后端，将会增加数据库访问次数（访问数据库的次数），从而增加了数据库处理时间。
-
从网络角度来看：更大的响应会影响响应时间、数据使用等。因此，考虑用户倾向于多频繁自定义列，如果始终保留30列，将会消耗更多的带宽，可能会增加几毫秒甚至几秒，具体取决于额外数据的大小以及网络条件。如果该功能很少使用，我更倾向于避免这种性能影响（个人意见），但如果经常使用，预加载数据可能会对用户体验产生更好的影响。
-
与性能无关，但我还会分析安全性影响。尽管用户可以自定义列，但始终提供所有数据（30列）可能会引发安全问题，如果存在不应始终共享的信息，但如果是公共领域或低风险数据，则可能是可以接受的。
结论
由于原始问题似乎只涉及数据库角度，我会选择一次性检索所有30列，因为这样可以降低在列自定义时对数据库的访问次数，而不会降低单个查询的延迟（并且问题中没有提到其他限制）。
英文:
That can become very subjective question depending on where your analysis point is focused:
-
From a DB perspective: If you are querying 6 or 30 columns on the same table should not make a big difference in performance.
However, this can changes if additional columns require joins to additional tables.
The issue would be the amount of DB hits (how many times you go to DB) if you choose to go back to the backend during customization as it will increase the time DB is processing something. -
From Network perspective: Bigger responses will have an impact on response time, data usage, etc. So it can be good to think how often users tend to customize columns, having the 30 columns all the time will consume more bandwidth, it may add a few milliseconds or even seconds depending on how big the additional data can be as well as Network conditions. If he feature is rarely used, I would rather avoid this performance impact (personal opinion), but if it is often used, preloading the data may have a better UX impact.
-
Not performance related, but I would also analyze Security impact. Although the user can customize the columns, giving all the data (30 columns) all the time may be a security concern if there is any information that should not be shared all the time, but it could be OK if it is public-domain or low risk data
Conclusion
Since the original question seems to relate to the DB perspective only, I would opt for retrieving all the 30 columns in one go, given that it will lower the DB hits upon column customization without lowering the single query latency (and there is no other restriction enounced in the question).
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论