I am confused about selecting two approaches.
我对选择两种方法感到困惑。
Scenario
there are two tables Table 1 and Table 2 respectively. Table 1 contains user's data for example first name, last name etc
场景中有两个表表表1和表2。表1包含用户的数据，例如名、姓等
Table 2 contains cars each user has with its description. i.e Color, Registration No etc
表2包含每个用户拥有的汽车及其描述。我。e颜色，注册号等
Now if I want to have all the information of all users then what approach is best to be completed in minimum time?
现在，如果我想拥有所有用户的所有信息，那么什么方法最好在最短时间内完成?
Approach 1.
方法1。
Query for all rows in Table 1 and store them all in a list for ex.
查询表1中的所有行，并将它们全部存储在一个列表中。
then Loop through the list and query it and get data from Table 2 according to user saved in in first step.
然后对列表进行循环查询，根据第一步中保存的用户从表2中获取数据。
Approach 2
方法2
Query for all rows and while saving that row get its all values from table 2 and save them too.
查询所有行并保存该行时，从表2中获取所有值并保存它们。
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
如果还有更好的主意，请告诉我
1 个解决方案
#1
6
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:
这两种方法的性能大致相同(由于N+1查询而变慢)。这样做一个查询会更快:
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.
这只是伪代码，但你可以让它工作。
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.
union-all查询将具有惊人的良好性能，因为sql server将执行“合并联合”，其工作方式类似于合并连接。这种模式也适用于多层次的父子关系，尽管不是很好。
#1
6
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:
这两种方法的性能大致相同(由于N+1查询而变慢)。这样做一个查询会更快:
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.
这只是伪代码，但你可以让它工作。
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.
union-all查询将具有惊人的良好性能，因为sql server将执行“合并联合”，其工作方式类似于合并连接。这种模式也适用于多层次的父子关系，尽管不是很好。