view - 从花费大量时间的视图中选择

Question

我使用下表创建了一个视图。此视图需要大量时间才能返回输出。我通过左连接将此视图与其他表一起使用。有什么办法可以提高下面视图的性能？

DDL 命令：

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

这些表的非聚集索引详细信息：

CREATE NONCLUSTERED INDEX IX_PROD_TEXT ON PRODUCT_TEXT (TEXT_CODE, PRODUCT)
CREATE NONCLUSTERED INDEX IX_PROD_TEXT_1 ON PROD_TEXT (DATA_CODE, PRODUCT)
CREATE NONCLUSTERED INDEX IX_PHRASE_LINK ON PHRASE_LINK (TEXT_CODE)
CREATE NONCLUSTERED INDEX IX_PHRASE_Translations ON PHRASE_transaltions(phrase_id)
CREATE NONCLUSTERED INDEX IX_PHRASE_Translations_LANG ON PHRASE_transaltions(LANGUAGE)

查看定义：

CREATE VIEW [dbo].[MANU_TEST] 
AS
    SELECT 
        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 
        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'

这个视图需要很长时间才能返回输出。

标签： viewquery-optimizationsql-server-2014