asp.net – 如何优化T-SQL查询

> ProductCategory
>产品
> OrderLineItem
>订单

我在管理部分有一个页面用于管理订单,现在我想按ProductCategory过滤,即哪个订单包含与所选ProductCategory相关的Product(我的productId在OrderLineItem表中),我这样做是通过以下查询：

SELECT  
    O.OrderID,O.[OrderDate],O.[StatusID]                          
FROM [Order] O                              
INNER JOIN [Dyve_User] U ON U.[UserID] = O.[UserID]                               
INNER JOIN (SELECT OD.OrderID 
            FROM OrderLineItem OD
            LEFT OUTER JOIN [Product] P ON OD.ProductID = P.ProductID
            LEFT OUTER JOIN [ProductCategory] PC ON PC.CategoryID = P.CategoryID
            WHERE 
               (P.CategoryID = COALESCE(@CategoryID,P.CategoryID)                              
                OR P.CategoryID IN (SELECT CategoryID 
                                    FROM ProductCategory                              
                                    WHERE ParentID = COALESCE(@CategoryID,ParentID)
                                   )
               )                              
           ) AS T  ON O.OrderID = T.OrderID

我的这个查询返回正确的结果,但查询每次都超时,任何人都可以告诉我如何优化这个查询,这样就不会超时吗？

以下是表模式：

CREATE TABLE [dbo].[Order](
[OrderID] [int] IDENTITY(1,1) NOT NULL,[OrderDate] [datetime] NULL,[OrderTax] [money] NULL,[OrderTotal] [money] NULL,[ShippingCharge] [money] NULL,[TrackingNumber] [varchar](50) NULL,[TransactionStatusID] [int] NULL,[UserID] [int] NULL,[PromotionCode] [varchar](50) NULL
[ExpiryDate] [datetime] NULL,[PaymentType] [tinyint] NULL
  CONSTRAINT [Order_PK] PRIMARY KEY CLUSTERED 
  (
[OrderID] ASC
 )WITH (PAD_INDEX  = OFF,STATISTICS_NORECOMPUTE  = OFF,IGNORE_DUP_KEY = OFF,ALLOW_ROW_LOCKS  = ON,ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
  ) ON [PRIMARY]

产品表：

CREATE TABLE [dbo].[Product](
[ProductID] [int] IDENTITY(1,[CategoryID] [int] NULL,[ProductName] [nvarchar](600) NULL,[ManufacturerID] [int] NULL,[UnitPrice] [money] NULL,[RetailPrice] [money] NULL,[IsOnSale] [bit] NOT NULL,[ExpiryDate] [datetime] NULL,[IsElectrical] [bit] NULL,[IsActive] [bit] NULL,[ProductType] [int] NULL,[AllowBackOrder] [bit] NULL
   CONSTRAINT [Product_PK] PRIMARY KEY CLUSTERED 
   (
[ProductID] ASC
    )WITH (PAD_INDEX  = OFF,ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
   ) ON [PRIMARY]

ProductCategory表：

CREATE TABLE [dbo].[ProductCategory](
[CategoryID] [int] IDENTITY(1,[Name] [nvarchar](100) NOT NULL,[Description] [nvarchar](max) NULL,[ParentID] [int] NULL,[IsActive] [bit] NULL
    CONSTRAINT [ProductCategory_PK] PRIMARY KEY CLUSTERED 
   (
[CategoryID] ASC
   )WITH (PAD_INDEX  = OFF,ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
  ) ON [PRIMARY]

OrderLineItem表：

CREATE TABLE [dbo].[OrderLineItem](
[OrderDetailID] [int] IDENTITY(1,[OrderID] [int] NOT NULL,[ProductID] [int] NOT NULL
[TotalPrice] [money] NULL,[Quantity] [int] NULL,[Discount] [money] NULL,CONSTRAINT [OrderLineItem_PK] PRIMARY KEY CLUSTERED 
      (
[OrderDetailID] ASC
   )WITH (PAD_INDEX  = OFF,ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
   ) ON [PRIMARY]