使用临时表或嵌套选择从多个表中检索数据? [英] Use Temporary Tables or Nested Select in retrieving data from multiple table?

问题描述

我有一个类似于下面的查询，其中数据是从多个表中检索的.问题是如果这个表是检索多个数据……这个过程肯定会更好或更有效地使用嵌套选择或临时表来优化我的选择语句...以及我应该如何对我的连接进行分组...

I've got a query similar to this below where data are retrieve from multiple tables.. The problem is if this table is to retrieve multiple data... the process would definitely would it be better or more efficient to use nested select or temp table to optimize my select statement... and how should I be grouping my joins...

 Select a.Name,
    b.type,
    c.color,
    d.group,
    e.location
    f.quantity
    g.cost
from Table1 a 
INNER JOIN Table2 b ON a.ID=b.ItemCode
INNER JOIN TABLE3 c ON b.ItemCOde = c.groupID
INNER JOIN TABLE4 d ON c.groupID  = d.batchID
LEFT JOIN TABLE5 e ON d.batchID = e.PostalID
LEFT JOIN TABLE6 f ON e.PostalID = f.CountID
LEFT JOIN TABLE7 g ON f.CountID = g.InventoryNo 

推荐答案

1. 连接的顺序可能很重要:从选择性最强的表开始，然后是选择性最少的表.

1. The order of join could be important: start with the most selective table(s) and continue with least selective table(s).

嵌套查询与临时表:这是一个老问题，没有神奇"的解决方案.在某些情况下，临时表可以提高性能.事实是:每个查询都是不同的故事.尝试使用两种解决方案并分析查询执行计划.

Nested queries vs. temp table: it's old dilemma and there is no "magic" solution. In some cases temp table can improve performance. The truth is: every query is different story. Try with both solution and analyze query execution plan.

这篇关于使用临时表或嵌套选择从多个表中检索数据?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！