Need SQL Server Performance Help

ALTER PROCEDURE [dbo].[usp_get_fixtures]
      @SYSTEM_ID INT,
      @FINISH_ID INT= 7,
      @INPUT_ID INT= 3,
      @OUTPUT_ID INT= 1,
      @MOUNT_ID INT= NULL,
      @SUB_SYSTEM2_ID INT= 0,
      @IS_CONFIG BIT= 1
AS

BEGIN

      SET NOCOUNT ON;

      SELECT DISTINCT 
            p.NAME,
            xpis.IS_NEW,
            xpis.IS_LED,
            xpis.IS_CF,
            xpis.IS_ADA,
            xpis.IS_ENERGY_STAR,
            xpis.IS_RAILTONES,
            xpis.PRODUCT_NAME_ID, 
            xfpc.FINISH_CHILD_ID,
            p.SUB_SYSTEM2_ID,
            p.ACCESSORY_REQUIRED,
            p.CATALOG_DESCRIPTIONAS PRODUCT_DESCRIPTION,
            p.CATALOG_DESCRIPTION2AS PRODUCT_DESCRIPTION2,
            p.ELG_SPECSHEETAS SPECSHEET,
            c.COLOR_IDAS DEFAULT_COLOR_ID,
            c.NAMEAS DEFAULT_COLOR
      FROM 
            dbo.xref_SKU_SYSTEMAS xss INNER JOIN
            dbo.tbl_SKUAS sku INNER JOIN
            dbo.tbl_PRODUCT_NAME AS p ON sku.PRODUCT_NAME_ID = p.PRODUCT_NAME_ID ON xss.SKU_ID =sku.SKU_ID LEFT OUTER JOIN
            dbo.tbl_COLORAS c ON p.DEFAULT_COLOR_ID = c.COLOR_ID LEFT OUTER JOIN
            dbo.xref_FINISHES_PARENT_CHILDAS xfpc LEFT OUTER JOIN
            dbo.tbl_FINISHAS f ON xfpc.FINISH_PARENT_ID = f.FINISH_ID ON sku.FINISH_ID = xfpc.FINISH_CHILD_ID LEFT OUTER JOIN
            dbo.xref_PRODUCTNAME_ICONS_SYSTEMSAS xpis ON p.PRODUCT_NAME_ID =xpis.PRODUCT_NAME_ID LEFT OUTER JOIN
            dbo.xref_PRODUCTNAME_MOUNT AS xpm ON p.PRODUCT_NAME_ID =xpm.PRODUCT_NAME_ID

      WHERE 
            (p.SUB_SYSTEM_ID = 9) AND
            (p.IS_OBSOLETE = 0) AND
            (sku.IS_OBSOLETE = 0) AND
            (p.NAME NOT LIKE '%dummy%') AND
            (xss.SYSTEM_ID = @SYSTEM_ID OR xpis.SYSTEM_ID= @SYSTEM_ID) AND
            (
                  (
                        CASE @IS_CONFIG
                              WHEN 0 THEN 0
                              ELSE sku.IS_OPTION
                        END
                  ) = @IS_CONFIG
            ) AND
            (
                  (
                        CASE @SUB_SYSTEM2_ID
                              WHEN 0 THEN 0
                              ELSE p.SUB_SYSTEM2_ID
                        END
                  ) = @SUB_SYSTEM2_ID
            ) AND
            (
                  (
                        CASE @FINISH_ID
                              WHEN 0 THEN 0
                              ELSE sku.FINISH_ID
                        END
                  ) = @FINISH_ID OR
                  (
                        CASE @FINISH_ID
                              WHEN 0 THEN 0
                              ELSE xfpc.FINISH_PARENT_ID
                        END
                  ) = @FINISH_ID
            ) AND
            (
                  (
                        CASE @OUTPUT_ID
                              WHEN 0 THEN 0
                              ELSE sku.OUTPUT_VOLTAGE_ID
                        END
                  ) = @OUTPUT_ID OR sku.OUTPUT_VOLTAGE_ID ISNULL
            ) AND
            (
                  (
                        CASE @INPUT_ID
                              WHEN 0 THEN 0
                              ELSE sku.INPUT_VOLTAGE_ID
                        END
                  ) = @INPUT_ID OR sku.INPUT_VOLTAGE_ID ISNULL

            ) AND
            (
                  (
                        CASE @MOUNT_ID
                              WHEN 0 THEN 0
                              ELSE xpm.MOUNT_ID
                        END
                  ) = @MOUNT_ID OR xpm.MOUNT_ID ISNULL
            )

Select allOpen in new window

CREATE TABLE [dbo].[xref_FINISHES_PARENT_CHILD](
      [ID] [int] IDENTITY(1,1) NOT NULL,
      [FINISH_PARENT_ID] [int] NULL,
      [FINISH_CHILD_ID] [int] NULL,
 CONSTRAINT[PK_xref_FINISHES_PARENT_CHILD] PRIMARY KEY CLUSTERED 
(
      [ID] ASC

) WITH(PAD_INDEX = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

) ON [PRIMARY]

GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'The primary key.' ,@level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'xref_FINISHES_PARENT_CHILD',@level2type=N'COLUMN',@level2name=N'ID'

GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'The foreign key to the finish table. This is the parentfinish.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'xref_FINISHES_PARENT_CHILD',@level2type=N'COLUMN',@level2name=N'FINISH_PARENT_ID'

GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'The foreign key to the finish table. This is the childfinish to the parent.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'xref_FINISHES_PARENT_CHILD',@level2type=N'COLUMN',@level2name=N'FINISH_CHILD_ID'

GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'A cross reference table that relates finishes to a parentfinish.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'xref_FINISHES_PARENT_CHILD'

GO

ALTER TABLE [dbo].[xref_FINISHES_PARENT_CHILD]  WITH CHECK ADD CONSTRAINT [FK_xref_FINISHES_PARENT_CHILD_tbl_FINISH]FOREIGN KEY([FINISH_PARENT_ID])

REFERENCES [dbo].[tbl_FINISH] ([FINISH_ID])

GO

ALTER TABLE [dbo].[xref_FINISHES_PARENT_CHILD]CHECK CONSTRAINT[FK_xref_FINISHES_PARENT_CHILD_tbl_FINISH]

GO

ALTER TABLE [dbo].[xref_FINISHES_PARENT_CHILD]  WITH CHECK ADD  CONSTRAINT[FK_xref_FINISHES_PARENT_CHILD_tbl_FINISH1] FOREIGNKEY([FINISH_CHILD_ID])

REFERENCES [dbo].[tbl_FINISH] ([FINISH_ID])

GO

ALTER TABLE [dbo].[xref_FINISHES_PARENT_CHILD]CHECK CONSTRAINT[FK_xref_FINISHES_PARENT_CHILD_tbl_FINISH1]

GO

Select allOpen in new window