哪种方法最好是从数据库中检索数据 [英] Which approach is better to retrieve data from a database
问题描述
我感到困惑选择两种方法。
I am confused about selecting two approaches.
场景结果
有两个表表1 和分别为表2。 表1 包含用户为例名数据，姓氏等
Scenario
there are two tables Table 1 and Table 2 respectively. Table 1 contains user's data for example first name, last name etc
表2 包含汽车每个用户都有其描述。即颜色，注册号等
Table 2 contains cars each user has with its description. i.e Color, Registration No etc
现在，如果我希望有那么所有的用户用什么办法最好是在最短的时间内完成所有的信息？
Now if I want to have all the information of all users then what approach is best to be completed in minimum time?
方法1。
查询所有行表1 键，将其全部保存在为前一个列表。
Query for all rows in Table 1 and store them all in a list for ex.
然后通过列表循环和查询，并得到从表2 根据用户在第一步保存在
then Loop through the list and query it and get data from Table 2 according to user saved in in first step.
方法2
查询所有行，并同时节省该行获得了从
Query for all rows and while saving that row get its all values from table 2 and save them too.
如果我认为系统进程的话，我想是因为那里是相同的，它可能是相同的记录以两种方法来处理。
If I think of system processes then I think it might be the same because there are same no of records to be processed in both approaches.
如果有任何其他更好的想法，请让我知道
If there is any other better idea please let me know
推荐答案
您的两种方法将具有大约相同的性能（因为较慢的N + 1的查询）。这将是更快地做这样一个查询：
Your two approaches will have about the same performance (slow because of N+1 queries). It would be faster to do a single query like this:
select *
from T1
left join T2 on ...
order by T1.PrimaryKey
您的客户端应用程序可以将其解释结果在一个查询的所有数据。另一种方法是：
Your client app can them interpret the results and have all data in a single query. An alternative would be:
select *, 1 as Tag
from T1
union all
select *, 2 as Tag
from T2
order by T1.PrimaryKey, Tag
这仅仅是伪代码，但你可以使它发挥作用。
This is just pseudo code but you could make it work.
工会，所有的查询都会有出乎意料的好性能，因为SQL Server将做一个合并联盟这就像一个融合连接。这种模式也适用于多层次的父子关系，虽然没有为好。
The union-all query will have surprisingly good performance because sql server will do a "merge union" which works like a merge-join. This pattern also works for multi-level parent-child relationships, although not as well.
这篇关于哪种方法最好是从数据库中检索数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
