如何在具有大量数据的表上使用自联接编写查询? [英] How to write a query using self join on a table with huge data?

问题描述

具有以下表结构:

表 1:客户

CustID(primary key)  |  CustName(indexed)
----------------------------------
C1                       Cust1
C2                       Cust2
.                        Cust3
.                        Cust.
.                        Cust.
C10000                   Cust10000

表 2:自定义字段

FieldID (primary key) |  ID (indexed)                | FieldValue
---------------------------------------------------------------------
1                        C1                              Test 
2                        C2                              Test 
3                        C3                              Test 
4                        C4                              Test 
.                        .                               Test 
.                        .                               Test 
few millions             Z1                              Test   

"ID" column is indexed.

尝试输出以下内容；

CustID  |  Field 1  |  Field 2 | Field 3 | .... | Field N
----------------------------------------------------------

我试着写一个像

Select 
    CustID, A1.FieldValue as [Field 1], A2.FieldValue as [Field 2]
from 
    Customers 
left outer join 
    CustomFields A1 on Customers.custID = A1.ID
left outer join 
    CustomFields A2 on Customers.custID = A2.ID
left outer join 
    CustomFields An on Customers.custID = An.ID
where 
    custName like 'C%'

由于 CustomFields 表保存了几百万条记录，上述查询效果不佳.现在大约需要 10-12 秒(对于 500 个客户和 6 个字段)

Since the CustomFields table holds a few million records, the above query does not perform well. Now it takes about 10-12 seconds (for 500 customers and 6 fields)

我认为左外连接在这里增加了时间.任何解决问题的想法真的会有帮助吗?

I think the left outer joins are adding time here. Any thoughts to solve the problem would be really helpful?

平台:SQL Server 2005

Platform : SQL Server 2005

更新:

CustomFields 表是一个通用表，它可以包含任何其他实体(供应商、商品等)的字段.

CustomFields table is a generic table and it can contain fields of any other entities (vendors, items etc..).

推荐答案

您可以使用单个连接来获取自定义字段，并使用聚合函数来转置它们.这样，与表 CustomFields 的连接只完成一次.

You can use a single join to get the customfields, and use aggregate functions to transpose them. That way, the join with table CustomFields is only done once.

像这样.

SELECT
  Customers.CustID,
  MAX(CASE WHEN CF.FieldID = 1 THEN CF.FieldValue ELSE NULL END) AS Field1,
  MAX(CASE WHEN CF.FieldID = 2 THEN CF.FieldValue ELSE NULL END) AS Field2,
  MAX(CASE WHEN CF.FieldID = 3 THEN CF.FieldValue ELSE NULL END) AS Field3,
  MAX(CASE WHEN CF.FieldID = 4 THEN CF.FieldValue ELSE NULL END) AS Field4
  -- Add more...

  FROM Customers 

  LEFT OUTER JOIN CustomFields CF
  ON CF.ID = Customers.CustID

  WHERE Customers.CustName like 'C%'

  GROUP BY Customers.CustID

这篇关于如何在具有大量数据的表上使用自联接编写查询?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！