How to optimize the T-SQL query

Question

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 Table:

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]

Answer 1

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.