何时使用查询或代码 [英] When to use a query or code

问题描述

我要求Java + JPA / Hibernate + Mysql的具体案例，但我认为你可以将这个问题应用到多种语言中。

I am asking for a concrete case for Java + JPA / Hibernate + Mysql, but I think you can apply this question to a great number of languages.

有时候我必须对数据库执行查询以获取某些实体，例如员工。假设你需要一些特定的员工（以'John'作为他们的名字的那些员工），你宁愿做一个返回这个确切的员工组的查询，或者你更愿意搜索所有员工然后使用编程语言来检索你感兴趣的那些？ 为什么（轻松，高效）？
哪个（通常）效率更高？

Sometimes I have to perform a query on a database to get some entities, such as employees. Let's say you need some specific employees (the ones with 'John' as their firstname), would you rather do a query returning this exact set of employees, or would you prefer to search for all the employees and then use a programming language to retrieve the ones that you are interested with? why (ease, efficiency)? Which is (in general) more efficient?

根据表格大小，一种方法比另一种更好吗？

Is one approach better than the other depending on the table size?

考虑：

• 两种情况下的复杂性和可重用性相同。

推荐答案

编程中经常使用一般的技巧 - 用内存支付操作加速。如果你有很多员工，并且你要逐个查询它们中的很大一部分（比如，一次或另一次会查询75％），然后查询所有内容，缓存它（非常重要！），并在内存中完成查找。下次查询时，跳过RDBMS之旅，直接进入缓存，并快速查找：与内存中的哈希查找相比，往数据库的往返非常昂贵。

There is a general trick often used in programming - paying with memory for operation speedup. If you have lots of employees, and you are going to query a significant portion of them, one by one (say, 75% will be queried at one time or the other), then query everything, cache it (very important!), and complete the lookup in memory. The next time you query, skip the trip to RDBMS, go straight to the cache, and do a fast look-up: a roundtrip to a database is very expensive, compared to an in-memory hash lookup.

另一方面，如果您访问的是一小部分员工，您应该只查询一名员工：从RDBMS到您的程序的数据传输需要花费大量时间，大量的网络带宽，你身边有很多内存，而且RDBMS方面有很多内存。查询大量行以丢弃除了一行之外的所有行都没有意义。

On the other hand, if you are accessing a small portion of employees, you should query just one employee: data transfer from the RDBMS to your program takes a lot of time, a lot of network bandwidth, a lot of memory on your side, and a lot of memory on the RDBMS side. Querying lots of rows to throw away all but one never makes sense.

这篇关于何时使用查询或代码的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！