一个复杂查询vs多个简单查询 [英] One complex query vs Multiple simple queries

问题描述

什么更好？有复杂查询的类负责加载实例嵌套对象？

对于复杂的查询，你必须减少到数据库，但类将有更多的责任。

或简单的查询，你将需要去更多的数据库。在这种情况下，每个类将负责加载一种类型的对象。

我所处的情况是加载的对象将被发送到Flex应用程序）。

解决方案

这里的一般规则是服务器往返程序是昂贵的（相对于典型查询需要多长时间）指导原则是你想把它们最小化。基本上每个一对多连接都可能会乘以你的结果集，所以我的方法是保持加入，直到结果集太大或查询执行时间太长（一般大约1-5秒）。

根据您的平台，您可能会或可能不能并行执行查询。这是你应该做的一个关键决定因素，因为如果你一次只能执行一个查询，分解查询的障碍要高得多。

有时候值得在内存中保持某些相对恒定的数据（例如国家信息），或者作为单独的查询进行，但在我的经验中，这是非常不寻常的。

共同的是必须修复具有糟糕的性能的系统，在很大程度上是做单独的查询（特别是相关的查询）而不是连接。

What is actually better? Having classes with complex queries responsible to load for instance nested objects? Or classes with simple queries responsible to load simple objects?

With complex queries you have to go less to database but the class will have more responsibility.

Or simple queries where you will need to go more to database. In this case however each class will be responsible for loading one type of object.

The situation I'm in is that loaded objects will be sent to a Flex application (DTO's).

解决方案

The general rule of thumb here is that server roundtrips are expensive (relative to how long a typical query takes) so the guiding principle is that you want to minimize them. Basically each one-to-many join will potentially multiply your result set so the way I approach this is to keep joining until the result set gets too large or the query execution time gets too long (roughly 1-5 seconds generally).

Depending on your platform you may or may not be able to execute queries in parallel. This is a key determinant in what you should do because if you can only execute one query at a time the barrier to breaking up a query is that much higher.

Sometimes it's worth keeping certain relatively constant data in memory (country information, for example) or doing them as a separately query but this is, in my experience, reasonably unusual.

Far more common is having to fix up systems with awful performance due in large part to doing separate queries (particularly correlated queries) instead of joins.

这篇关于一个复杂查询vs多个简单查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！