View Query Taking more time

• jkramprakash

  SSCommitted

  Points: 1870

  More actions

  January 12, 2019 at 3:58 am

  #409430

  I have created a view using below tables. This view takes a lot of time for returning output. I am using this view with other table by left join. Is there any way to increase the performance of below view?

  DDL commands:
  
  CREATE TABLE [dbo].[PRODUCT_TEXT]
(
  [Product] [nvarchar](50) NOT NULL,
  [Format] [nvarchar](3) NOT NULL,
  [Text_Code] [nvarchar](8) NOT NULL,
  [F_Date_Stamp] [datetime] NULL,
  [DATA_CODE] [nvarchar](8) NULL,
  CONSTRAINT [PK_Staging_T_PROD_TEXT] PRIMARY KEY CLUSTERED
(
  [Product] ASC,
  [Format] ASC,
  [Text_Code] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]
GO

  CREATE TABLE [dbo].PHRASE_LINK]
(
  [LINK_ID] [int] NOT NULL,
  [PHRASE_ID] [int] NOT NULL,
  [DATA_CODE] [nvarchar](8) NOT NULL,
  [TEXT_CODE] [nvarchar](8) NOT NULL,
  CONSTRAINT [PK_Staging_T_PHRASE_LINKAGE] PRIMARY KEY CLUSTERED
(
  [LINK_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] TEXTIMAGE_ON [PRIMARY]
GO

  CREATE TABLE [dbo].[PHRASE_TRANSLATIONS](
  [F_TRANSLATION_ID] [int] NOT NULL,
  [F_PHRASE_ID] [int] NOT NULL,
  [F_LANGUAGE] [nvarchar](2) NOT NULL,
  [F_PHRASE] [nvarchar](max) NULL,

  CONSTRAINT [PK_PHRASE_TRANSLATIONS] PRIMARY KEY CLUSTERED
(
  [F_TRANSLATION_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] TEXTIMAGE_ON [PRIMARY]
GO

  CREATE TABLE [dbo].[PROD_ALIAS_TEXT]
(
  [ALIAS] [varchar](50) NOT NULL,
  [FORMAT] [varchar](3) NOT NULL,
  [DATA_CODE] [varchar](8) NULL,
  [TEXT_CODE] [varchar](8) NOT NULL,

  CONSTRAINT [PROD_ALIAS_TEXT] PRIMARY KEY CLUSTERED
(
  [F_ALIAS] ASC,
  [F_FORMAT] ASC,
  [F_TEXT_CODE] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
) ON [PRIMARY]
GO

  View definition:

  CREATE VIEW [dbo].[MANU_TEST]
AS
  SELECT DISTINCT
   PT.PRODUCT AS PRODUCT, PT.TEXT_CODE AS TEXT_CODE,
   PHT.PHRASE AS F_PHRASE
  FROM
   PRODUCT_TEXT PT
  LEFT JOIN
   PHRASE_LINK PHL ON PT.TEXT_CODE = PHL.TEXT_CODE
  INNER JOIN
   PHRASE_TRANSLATIONS PHT ON PHL.PHRASE_ID = PHT.PHRASE_ID
  WHERE
   PT.DATA_CODE = 'MANU' AND PHT.LANGUAGE = 'EN'

    UNION ALL SELECT DISTINCT
   PT.F_ALIAS AS PRODUCT, PT.TEXT_CODE AS TEXT_CODE,
   PHT.PHRASE AS F_PHRASE
  FROM
   PROD_ALIAS_TEXT PT
  LEFT JOIN
   PHRASE_LINK PHL ON PT.TEXT_CODE = PHL.TEXT_CODE
  INNER JOIN
   PHRASE_TRANSLATIONS PHT ON PHL.PHRASE_ID = PHT.PHRASE_ID
  WHERE
   PT.DATA_CODE = 'MANU' AND PHT.LANGUAGE = 'EN'


• muthukkumaran Kaliyamoorthy

  SSCoach

  Points: 19026

  More actions

  January 12, 2019 at 4:35 am

  #2018748

  jkramprakash - Saturday, January 12, 2019 3:58 AM

  I have created a view using below tables. This view takes a lot of time for returning output. I am using this view with other table by left join. Is there any way to increase the performance of below view?

  DDL commands:
  
  CREATE TABLE [dbo].[PRODUCT_TEXT]
(
  [Product] [nvarchar](50) NOT NULL,
  [Format] [nvarchar](3) NOT NULL,
  [Text_Code] [nvarchar](8) NOT NULL,
  [F_Date_Stamp] [datetime] NULL,
  [DATA_CODE] [nvarchar](8) NULL,
  CONSTRAINT [PK_Staging_T_PROD_TEXT] PRIMARY KEY CLUSTERED
(
  [Product] ASC,
  [Format] ASC,
  [Text_Code] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]
GO

  CREATE TABLE [dbo].PHRASE_LINK]
(
  [LINK_ID] [int] NOT NULL,
  [PHRASE_ID] [int] NOT NULL,
  [DATA_CODE] [nvarchar](8) NOT NULL,
  [TEXT_CODE] [nvarchar](8) NOT NULL,
  CONSTRAINT [PK_Staging_T_PHRASE_LINKAGE] PRIMARY KEY CLUSTERED
(
  [LINK_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] TEXTIMAGE_ON [PRIMARY]
GO

  CREATE TABLE [dbo].[PHRASE_TRANSLATIONS](
  [F_TRANSLATION_ID] [int] NOT NULL,
  [F_PHRASE_ID] [int] NOT NULL,
  [F_LANGUAGE] [nvarchar](2) NOT NULL,
  [F_PHRASE] [nvarchar](max) NULL,

  CONSTRAINT [PK_PHRASE_TRANSLATIONS] PRIMARY KEY CLUSTERED
(
  [F_TRANSLATION_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] TEXTIMAGE_ON [PRIMARY]
GO

  CREATE TABLE [dbo].[PROD_ALIAS_TEXT]
(
  [ALIAS] [varchar](50) NOT NULL,
  [FORMAT] [varchar](3) NOT NULL,
  [DATA_CODE] [varchar](8) NULL,
  [TEXT_CODE] [varchar](8) NOT NULL,

  CONSTRAINT [PROD_ALIAS_TEXT] PRIMARY KEY CLUSTERED
(
  [F_ALIAS] ASC,
  [F_FORMAT] ASC,
  [F_TEXT_CODE] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
) ON [PRIMARY]
GO

  View definition:

  CREATE VIEW [dbo].[MANU_TEST]
AS
  SELECT DISTINCT
   PT.PRODUCT AS PRODUCT, PT.TEXT_CODE AS TEXT_CODE,
   PHT.PHRASE AS F_PHRASE
  FROM
   PRODUCT_TEXT PT
  LEFT JOIN
   PHRASE_LINK PHL ON PT.TEXT_CODE = PHL.TEXT_CODE
  INNER JOIN
   PHRASE_TRANSLATIONS PHT ON PHL.PHRASE_ID = PHT.PHRASE_ID
  WHERE
   PT.DATA_CODE = 'MANU' AND PHT.LANGUAGE = 'EN'

    UNION ALL SELECT DISTINCT
   PT.F_ALIAS AS PRODUCT, PT.TEXT_CODE AS TEXT_CODE,
   PHT.PHRASE AS F_PHRASE
  FROM
   PROD_ALIAS_TEXT PT
  LEFT JOIN
   PHRASE_LINK PHL ON PT.TEXT_CODE = PHL.TEXT_CODE
  INNER JOIN
   PHRASE_TRANSLATIONS PHT ON PHL.PHRASE_ID = PHT.PHRASE_ID
  WHERE
   PT.DATA_CODE = 'MANU' AND PHT.LANGUAGE = 'EN'


  Please post the execution plan of the view.

  Muthukkumaran Kaliyamoorthy
  https://www.sqlserverblogforum.com/

• Jonathan AC Roberts

  SSCoach

  Points: 18263

  More actions

  January 12, 2019 at 7:17 am

  #2018751

  If you have performance problems with a query like this it's probably due to missing indexes. Do you have indexes on the tables?
  Have you tried executing a query and getting the Estimated Execution Plan? If you do there will probably be some green text indicating the details of the missing index.
  If you open the execution plan XML you will probably find there is more than one missing index. I suggest you try that, create any missing indexes and see if performance is improved.

  Also, your code smells. You have a INNER JOIN to PHRASE_TRANSLATIONS on the results from a LEFT JOIN (PHL.PHRASE_ID). So it's not a LEFT JOIN, it is an INNER JOIN.

  Try creating the following indexes and see if they improve performance:
  CREATE INDEX IX_PRODUCT_TEXT_1 ON PRODUCT_TEXT(DATA_CODE, TEXT_CODE) INCLUDE (PRODUCT);
CREATE INDEX IX_PROD_ALIAS_TEXT_1 ON PROD_ALIAS_TEXT(DATA_CODE, TEXT_CODE) INCLUDE (ALIAS);
CREATE INDEX IX_PHRASE_LINK_1 ON PHRASE_LINK(TEXT_CODE, PHRASE_ID);
CREATE INDEX IX_PHRASE_TRANSLATIONS_1 ON PHRASE_TRANSLATIONS(F_LANGUAGE, F_PHRASE_ID) INCLUDE (F_PHRASE);

• Jeff Moden

  SSC Guru

  Points: 1003875

  More actions

  January 12, 2019 at 8:00 am

  #2018754

  The presence of the keyword DISTINCT normally means that the code has failed to work with 1:1 relationships and so creates 1:many or many:many relationships.  Another name for those relationships is "accidental cross joins".

  With the understanding that I don't have your data and know nothing about it and so this is a shot in the dark, the key here seems to be the "bridge" table that you call "PHRASE_LINK".  If we look at the first SELECT in your view...
  
  SELECT DISTINCT
    PT.PRODUCT AS PRODUCT, PT.TEXT_CODE AS TEXT_CODE,
    PHT.PHRASE AS F_PHRASE
  FROM
    PRODUCT_TEXT PT
  LEFT JOIN
    PHRASE_LINK PHL ON PT.TEXT_CODE = PHL.TEXT_CODE
  INNER JOIN
    PHRASE_TRANSLATIONS PHT ON PHL.PHRASE_ID = PHT.PHRASE_ID
  WHERE
    PT.DATA_CODE = 'MANU' AND PHT.LANGUAGE = 'EN'

  ... it's the likely source of the many:many joins because you're only joining it to the Product_Table on the Text_Code even though the Product_Table also has a Data_Code column.  I suspect that the combination of Text_Code and Data_Code columns would be the best candidate for the PK of the Phrase_Link table.

  Again, without knowing the data, it would seem that (especially since you're using it in the WHERE clause) that the join between the Product_Text table and the Phrase_Link table should include the Data_Code column and that should eliminate the many:many problem.  Of course and as Scott Pletcher would suggest along with a reminder that I can't tell from the DDL that has been provided, I also believe that the combination of the Data_Code and Text_Code columns is (or should be) a should be unique in the Phrase_Link table and, in this case, should be the Unique Clustered Index key.

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)
  

• jkramprakash

  SSCommitted

  Points: 1870

  More actions

  January 17, 2019 at 8:56 am

  #2019219

  Jeff Moden - Saturday, January 12, 2019 8:00 AM

  The presence of the keyword DISTINCT normally means that the code has failed to work with 1:1 relationships and so creates 1:many or many:many relationships.  Another name for those relationships is "accidental cross joins".

  With the understanding that I don't have your data and know nothing about it and so this is a shot in the dark, the key here seems to be the "bridge" table that you call "PHRASE_LINK".  If we look at the first SELECT in your view...
  
  SELECT DISTINCT
    PT.PRODUCT AS PRODUCT, PT.TEXT_CODE AS TEXT_CODE,
    PHT.PHRASE AS F_PHRASE
  FROM
    PRODUCT_TEXT PT
  LEFT JOIN
    PHRASE_LINK PHL ON PT.TEXT_CODE = PHL.TEXT_CODE
  INNER JOIN
    PHRASE_TRANSLATIONS PHT ON PHL.PHRASE_ID = PHT.PHRASE_ID
  WHERE
    PT.DATA_CODE = 'MANU' AND PHT.LANGUAGE = 'EN'

  ... it's the likely source of the many:many joins because you're only joining it to the Product_Table on the Text_Code even though the Product_Table also has a Data_Code column.  I suspect that the combination of Text_Code and Data_Code columns would be the best candidate for the PK of the Phrase_Link table.

  Again, without knowing the data, it would seem that (especially since you're using it in the WHERE clause) that the join between the Product_Text table and the Phrase_Link table should include the Data_Code column and that should eliminate the many:many problem.  Of course and as Scott Pletcher would suggest along with a reminder that I can't tell from the DDL that has been provided, I also believe that the combination of the Data_Code and Text_Code columns is (or should be) a should be unique in the Phrase_Link table and, in this case, should be the Unique Clustered Index key.

  Thank you.

• jkramprakash

  SSCommitted

  Points: 1870

  More actions

  January 17, 2019 at 8:57 am

  #2019220

  Jonathan AC Roberts - Saturday, January 12, 2019 7:17 AM

  If you have performance problems with a query like this it's probably due to missing indexes. Do you have indexes on the tables?
  Have you tried executing a query and getting the Estimated Execution Plan? If you do there will probably be some green text indicating the details of the missing index.
  If you open the execution plan XML you will probably find there is more than one missing index. I suggest you try that, create any missing indexes and see if performance is improved.

  Also, your code smells. You have a INNER JOIN to PHRASE_TRANSLATIONS on the results from a LEFT JOIN (PHL.PHRASE_ID). So it's not a LEFT JOIN, it is an INNER JOIN.

  Try creating the following indexes and see if they improve performance:
  CREATE INDEX IX_PRODUCT_TEXT_1 ON PRODUCT_TEXT(DATA_CODE, TEXT_CODE) INCLUDE (PRODUCT);
CREATE INDEX IX_PROD_ALIAS_TEXT_1 ON PROD_ALIAS_TEXT(DATA_CODE, TEXT_CODE) INCLUDE (ALIAS);
CREATE INDEX IX_PHRASE_LINK_1 ON PHRASE_LINK(TEXT_CODE, PHRASE_ID);
CREATE INDEX IX_PHRASE_TRANSLATIONS_1 ON PHRASE_TRANSLATIONS(F_LANGUAGE, F_PHRASE_ID) INCLUDE (F_PHRASE);

  I created index and performance is improved.

• jkramprakash

  SSCommitted

  Points: 1870

  More actions

  January 17, 2019 at 8:57 am

  #2019221

  Jonathan AC Roberts - Saturday, January 12, 2019 7:17 AM

  If you have performance problems with a query like this it's probably due to missing indexes. Do you have indexes on the tables?
  Have you tried executing a query and getting the Estimated Execution Plan? If you do there will probably be some green text indicating the details of the missing index.
  If you open the execution plan XML you will probably find there is more than one missing index. I suggest you try that, create any missing indexes and see if performance is improved.

  Also, your code smells. You have a INNER JOIN to PHRASE_TRANSLATIONS on the results from a LEFT JOIN (PHL.PHRASE_ID). So it's not a LEFT JOIN, it is an INNER JOIN.

  Try creating the following indexes and see if they improve performance:
  CREATE INDEX IX_PRODUCT_TEXT_1 ON PRODUCT_TEXT(DATA_CODE, TEXT_CODE) INCLUDE (PRODUCT);
CREATE INDEX IX_PROD_ALIAS_TEXT_1 ON PROD_ALIAS_TEXT(DATA_CODE, TEXT_CODE) INCLUDE (ALIAS);
CREATE INDEX IX_PHRASE_LINK_1 ON PHRASE_LINK(TEXT_CODE, PHRASE_ID);
CREATE INDEX IX_PHRASE_TRANSLATIONS_1 ON PHRASE_TRANSLATIONS(F_LANGUAGE, F_PHRASE_ID) INCLUDE (F_PHRASE);

  Thank you.

• Jonathan AC Roberts

  SSCoach

  Points: 18263

  More actions

  January 17, 2019 at 9:07 am

  #2019224

  jkramprakash - Thursday, January 17, 2019 8:57 AM

  Jonathan AC Roberts - Saturday, January 12, 2019 7:17 AM

  If you have performance problems with a query like this it's probably due to missing indexes. Do you have indexes on the tables?
  Have you tried executing a query and getting the Estimated Execution Plan? If you do there will probably be some green text indicating the details of the missing index.
  If you open the execution plan XML you will probably find there is more than one missing index. I suggest you try that, create any missing indexes and see if performance is improved.

  Also, your code smells. You have a INNER JOIN to PHRASE_TRANSLATIONS on the results from a LEFT JOIN (PHL.PHRASE_ID). So it's not a LEFT JOIN, it is an INNER JOIN.

  Try creating the following indexes and see if they improve performance:
  CREATE INDEX IX_PRODUCT_TEXT_1 ON PRODUCT_TEXT(DATA_CODE, TEXT_CODE) INCLUDE (PRODUCT);
CREATE INDEX IX_PROD_ALIAS_TEXT_1 ON PROD_ALIAS_TEXT(DATA_CODE, TEXT_CODE) INCLUDE (ALIAS);
CREATE INDEX IX_PHRASE_LINK_1 ON PHRASE_LINK(TEXT_CODE, PHRASE_ID);
CREATE INDEX IX_PHRASE_TRANSLATIONS_1 ON PHRASE_TRANSLATIONS(F_LANGUAGE, F_PHRASE_ID) INCLUDE (F_PHRASE);

  I created index and performance is improved.

  How much difference did the indexes make?
  Check the execution plan. If any of the new indexes are not being used then drop them.

• jkramprakash

  SSCommitted

  Points: 1870

  More actions

  January 17, 2019 at 9:18 am

  #2019226

  Jonathan AC Roberts - Thursday, January 17, 2019 9:07 AM

  jkramprakash - Thursday, January 17, 2019 8:57 AM

  Jonathan AC Roberts - Saturday, January 12, 2019 7:17 AM

  If you have performance problems with a query like this it's probably due to missing indexes. Do you have indexes on the tables?
  Have you tried executing a query and getting the Estimated Execution Plan? If you do there will probably be some green text indicating the details of the missing index.
  If you open the execution plan XML you will probably find there is more than one missing index. I suggest you try that, create any missing indexes and see if performance is improved.

  Also, your code smells. You have a INNER JOIN to PHRASE_TRANSLATIONS on the results from a LEFT JOIN (PHL.PHRASE_ID). So it's not a LEFT JOIN, it is an INNER JOIN.

  Try creating the following indexes and see if they improve performance:
  CREATE INDEX IX_PRODUCT_TEXT_1 ON PRODUCT_TEXT(DATA_CODE, TEXT_CODE) INCLUDE (PRODUCT);
CREATE INDEX IX_PROD_ALIAS_TEXT_1 ON PROD_ALIAS_TEXT(DATA_CODE, TEXT_CODE) INCLUDE (ALIAS);
CREATE INDEX IX_PHRASE_LINK_1 ON PHRASE_LINK(TEXT_CODE, PHRASE_ID);
CREATE INDEX IX_PHRASE_TRANSLATIONS_1 ON PHRASE_TRANSLATIONS(F_LANGUAGE, F_PHRASE_ID) INCLUDE (F_PHRASE);

  I created index and performance is improved.

  How much difference did the indexes make?
  Check the execution plan. If any of the new indexes are not being used then drop them.

  Ok. Now response time is reduced from 36 sec to 9 sec.

• jkramprakash

  SSCommitted

  Points: 1870

  More actions

  January 17, 2019 at 9:23 am

  #2019228

  jkramprakash - Thursday, January 17, 2019 8:56 AM

  Jeff Moden - Saturday, January 12, 2019 8:00 AM

  The presence of the keyword DISTINCT normally means that the code has failed to work with 1:1 relationships and so creates 1:many or many:many relationships.  Another name for those relationships is "accidental cross joins".

  With the understanding that I don't have your data and know nothing about it and so this is a shot in the dark, the key here seems to be the "bridge" table that you call "PHRASE_LINK".  If we look at the first SELECT in your view...
  
  SELECT DISTINCT
    PT.PRODUCT AS PRODUCT, PT.TEXT_CODE AS TEXT_CODE,
    PHT.PHRASE AS F_PHRASE
  FROM
    PRODUCT_TEXT PT
  LEFT JOIN
    PHRASE_LINK PHL ON PT.TEXT_CODE = PHL.TEXT_CODE
  INNER JOIN
    PHRASE_TRANSLATIONS PHT ON PHL.PHRASE_ID = PHT.PHRASE_ID
  WHERE
    PT.DATA_CODE = 'MANU' AND PHT.LANGUAGE = 'EN'

  ... it's the likely source of the many:many joins because you're only joining it to the Product_Table on the Text_Code even though the Product_Table also has a Data_Code column.  I suspect that the combination of Text_Code and Data_Code columns would be the best candidate for the PK of the Phrase_Link table.

  Again, without knowing the data, it would seem that (especially since you're using it in the WHERE clause) that the join between the Product_Text table and the Phrase_Link table should include the Data_Code column and that should eliminate the many:many problem.  Of course and as Scott Pletcher would suggest along with a reminder that I can't tell from the DDL that has been provided, I also believe that the combination of the Data_Code and Text_Code columns is (or should be) a should be unique in the Phrase_Link table and, in this case, should be the Unique Clustered Index key.

  Thank you.

  Removed the distinct also.