在 SQL Server 中加入子查询的效率

2022-01-23 00:00:00 join sql left-join subquery sql-server

我在 SQL Server 2008 R2 中有一个客户和订单表.两者都有关于客户 ID 的索引(称为 id).我需要返回customers 表中所有客户的详细信息以及orders 表中的信息，例如第一个订单的详细信息.

I have a customers and orders table in SQL Server 2008 R2. Both have indexes on the customer id (called id). I need to return details about all customers in the customers table and information from the orders table, such as details of the first order.

我目前在订单表的子查询中加入了我的客户表，该子查询返回我需要的有关订单的信息.例如:

I currently left join my customers table on a subquery of the orders table, with the subquery returning the information I need about the orders. For example:

SELECT c.id
        ,c.country      
        ,First_orders.product
        ,First_orders.order_id
FROM customers c

LEFT JOIN   SELECT( id, 
                    product 
            FROM (SELECT    id
                            ,product
                            ,order_id
                            ,ROW_NUMBER() OVER (PARTITION BY id ORDER BY Order_Date asc) as order_No 
                        FROM orders) orders
            WHERE Order_no = 1) First_Orders
ON c.id = First_orders.id

我对 SQL 很陌生，想了解我是否有效地执行此操作.我最终在一个选择查询中将很多这样的子查询加入到客户表中，并且可能需要数十分钟才能运行.

I'm quite new to SQL and want to understand if I'm doing this efficiently. I end up left joining quite a few subqueries like this onto the customers table in one select query and it can take tens of minutes to run.

那么我这样做效率高还是可以改进?例如，我不确定我在订单表中的 id 索引是否有任何用途，也许我可以通过首先创建子查询中内容的临时表并在 id 上创建唯一索引来加快查询速度临时表，以便 SQL Server 知道 id 现在是一个唯一列，然后将我的客户表加入这个临时表?我的客户和订单表中通常有一两百万行.

So am I doing this efficiently or can it be improved? For example, I'm not sure if my index on id in the orders table is of any use and maybe I could speed up the query by creating a temporary table of what is in the subquery first and creating a unique index on id in the temporary table so SQL Server knows id is now a unique column and then joining my customers table to this temporary table? I typically have one or two million rows in the customers and orders tables.

非常感谢！

推荐答案

您可以删除其中一个子查询以提高效率:

You can remove one of your subqueries to make it a little more efficient:

SELECT c.id
        ,c.country      
        ,First_orders.product
        ,First_orders.order_id
FROM customers c
   LEFT JOIN  (SELECT id
                    ,product
                    ,order_id
                    ,ROW_NUMBER() OVER (PARTITION BY id ORDER BY Order_Date asc) as order_No 
               FROM orders) First_Orders
     ON c.id = First_orders.id AND First_Orders.order_No = 1

在您的上述查询中，您需要小心放置括号的位置，因为我认为它不会起作用.此外，您在结果中返回产品，但不包括在嵌套子查询中.

In your above query, you need to be careful where you place your parentheses as I don't think it will work. Also, you're returning product in your results, but not including in your nested subquery.

相关文章