How to optimize the T-SQL query

I am writing a T-SQL Query, I am developing the e-commerce website, in that I am using 4 major tables:

1. ProductCategory
2. Product
3. OrderLineItem
4. Order

I have a one page in my admin section for manage the orders, Now I want to filter by ProductCategory i.e. which Order contains the Product (my productId is in OrderLineItem table) which is related to the selected ProductCategory, I am doing this via below query:

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  

My this query return the correct result but the query times out every time, can any one tell me how to optimize this query so this will not time out?

following is the tables schema:

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]

Product T开发者_Python百科able:

CREATE TABLE [dbo].[Product](
[ProductID] [int] IDENTITY(1,1) NOT NULL,
[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, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF,        ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
   ) ON [PRIMARY]

ProductCategory Table:

CREATE TABLE [dbo].[ProductCategory](
[CategoryID] [int] IDENTITY(1,1) NOT NULL,
[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, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF,        ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
  ) ON [PRIMARY]

OrderLineItem Table:

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

---

A couple of things to start with:

1. Define indexes on the join and where columns.
2. Join from the smaller tables to the bigger ones.

I suggest reading up on performance and how to find causes - here is a good article on the subject: part 1 and part 2.

---

This is not tested so I'm not sure if it still does what you intended with your query.

It will fetch the Orders that has an OrderLineItem with a Product with a CategoryID that is equal to @CategoryID or a child category to @CategoryID.

SELECT O.OrderID,
       O.[OrderDate],
       O.[StatusID]
FROM   [Order] AS O
WHERE  O.OrderID IN (SELECT OD.OrderID
                     FROM   OrderLineItem AS OD
                            INNER JOIN Product AS P
                              ON OD.ProductID = P.ProductID
                            INNER JOIN (SELECT PC.CategoryID
                                        FROM   ProductCategory
                                        WHERE  ParentID = @CategoryID
                                        UNION ALL
                                        SELECT @CategoryID) AS C
                              ON P.CategoryID = C.CategoryID)

With regards to performance you just have to test it to find out.

Indexes is a good thing and you should make sure that you have indexes on your foreign key columns.

---

First try to make longer timeout to see it work. Than take a loot at your execution plan. Move tables with less elements to the left of the join.