Optimize/PIMP my TSQL code please

Question

Optimize/PIMP my TSQL code please

NBD4K27

SSC-Addicted

Points: 456
More actions
July 14, 2010 at 7:17 pm

#224848

/*ORIGINAL QUERY. TOTAL ROWS AT THE END ARE 24424 */
DROP TABLE #GMTVisionTemp
GO
DROP TABLE #GMTVisionMetaTemp
GO
CREATE TABLE #GMTVisionTemp
(
[GMTVisionDataConversionID] [int] IDENTITY(1,1) NOT NULL,
[Channel] [int] NOT NULL,
[AirDate] [datetime] NULL,
[StartTime] [varchar](5) NULL,
[Duration] [varchar](3) NULL,
[Title] [varchar](MAX) NULL,--MAX
[EpisodeTitle] [varchar](MAX) NULL,--MAX
[Year] [varchar](4) NULL,
[ProgramType] [varchar](100) NULL,
[Genre] [varchar](MAX) NULL,--MAX
[Credits] [varchar](MAX) NULL,--MAX
[ParentalRating] [varchar](8) NULL,
[Description] [varchar](MAX) NULL,--MAX
[Countries] [varchar](MAX) NULL,--MAX
[Stereo] [varchar](6) NULL,
[UniqueID] [varchar](30) NULL,
[Updated] [bit] NOT NULL,
[RecordDate] [datetime] NOT NULL,
[DupIdentifier] [varchar](15) NULL
)
GO
CREATE TABLE #GMTVisionMetaTemp (
[MetadataId] [int] IDENTITY(1,1) NOT NULL,
[GMTVisionDataConversionID] [int] NULL,
[SeriesAttributes] [varchar](100) NULL,
[ProgramAttributes] [varchar](100) NULL,
[IsLive] [bit] NULL,
[IsPremiere] [bit] NULL,
[ProgramAdvisory] [varchar](100) NULL,
[SeriesId] [bigint] NULL,
[ProgramId] [bigint] NULL,
[ProgramEpisodeSeason] [varchar](100) NULL
)
GO
/*1***********************************************************************/
/***** GMTVISIONTEMP ****/
/*************************************************************************/
/* --1- INSERT SPORTS INTO TEMP -- */
INSERT INTO
#GMTVisionTemp
(
[Channel],
[AirDate],
[StartTime],
[Duration],
[Title],
[EpisodeTitle],
[Year],
[ProgramType],
[Genre],
[Credits],
[ParentalRating],
[Description],
[Countries],
[Stereo],
[UniqueID],
[Updated],
[RecordDate],
[DupIdentifier]
)
SELECT
1 AS Channel
, DmcSql.fn_Registry_ConvertPSTToUTC([_Schedule_Item_Date_Time]) AS AirDate
, CONVERT(varchar(5), DmcSql.fn_Registry_ConvertPSTToUTC([_Schedule_Item_Date_Time]), 108) AS StartTime
, [_Schedule_Item_Duration] AS Duration
, ISNULL([_Series_Listing_Title], [_Program_Listing_Title]) AS Title
, [_Program_Episode_Title] AS EpisodeTitle
, CASE
WHEN [_Program_Year_Produced] = 0 THEN NULL
ELSE CONVERT(varchar(4), [_Program_Year_Produced])
END AS [Year]
, ISNULL([_Series_Category], [_Program_Category]) AS ProgramType
, ISNULL([_Program_Genre_List], [_Series_Genre_List]) AS Genre
, NULL AS Credits
, [dbo].fnValidateRating(CONVERT(varchar(8), [_Schedule_Item_VChip_Code])) AS ParentalRating
, [_Program_Synopsis] AS [Description]
, [_Scheduled_Version_House_Number_List] AS Countries
, NULL AS Stereo
, CONVERT(varchar(30), [_Schedule_Item_ID]) AS UniqueID
, 0 AS Updated
, GETDATE() as RecordDate
, NULL AS DupIdentifier
FROM
[Channels].[Sports]
WHERE
([IsPending] = 0)
GO
/* --2- INSERT ATLANTIC INTO TEMP -- */
INSERT INTO
#GMTVisionTemp
(
[Channel],
[AirDate],
[StartTime],
[Duration],
[Title],
[EpisodeTitle],
[Year],
[ProgramType],
[Genre],
[Credits],
[ParentalRating],
[Description],
[Countries],
[Stereo],
[UniqueID],
[Updated],
[RecordDate],
[DupIdentifier]
)
SELECT
2 AS Channel
, DmcSql.fn_Registry_ConvertPSTToUTC([_Schedule_Item_Date_Time]) AS AirDate
, CONVERT(varchar(5), DmcSql.fn_Registry_ConvertPSTToUTC([_Schedule_Item_Date_Time]), 108) AS StartTime
, CONVERT(varchar(3), [_Schedule_Item_Duration]) AS Duration
, ISNULL([_Series_Listing_Title], [_Program_Listing_Title]) AS Title
, [_Program_Episode_Title] AS EpisodeTitle
, CASE
WHEN [_Program_Year_Produced] = 0 THEN NULL
ELSE CONVERT(varchar(4), [_Program_Year_Produced])
END AS [Year]
, ISNULL([_Series_Category], [_Program_Category]) AS ProgramType
, ISNULL([_Program_Genre_List], [_Series_Genre_List]) AS Genre
, NULL AS Credits
, [dbo].fnValidateRating(CONVERT(varchar(8), [_Schedule_Item_VChip_Code])) AS ParentalRating
, [_Program_Synopsis] AS [Description]
, [_Scheduled_Version_House_Number_List] AS Countries
, NULL AS Stereo
, CONVERT(varchar(30), [_Schedule_Item_ID]) AS UniqueID
, 0 AS Updated
, GETDATE() as RecordDate
, NULL AS DupIdentifier
FROM
[Channels].[Atlantic]
WHERE
([IsPending] = 0)
GO
/* --3- INSERT SPECTRUM INTO TEMP -- */
INSERT INTO
#GMTVisionTemp
(
[Channel],
[AirDate],
[StartTime],
[Duration],
[Title],
[EpisodeTitle],
[Year],
[ProgramType],
[Genre],
[Credits],
[ParentalRating],
[Description],
[Countries],
[Stereo],
[UniqueID],
[Updated],
[RecordDate],
[DupIdentifier]
)
SELECT
3 AS Channel
, DmcSql.fn_Registry_ConvertPSTToUTC([_Schedule_Item_Date_Time]) AS AirDate
, CONVERT(varchar(5), DmcSql.fn_Registry_ConvertPSTToUTC([_Schedule_Item_Date_Time]), 108) AS StartTime
, CONVERT(varchar(3), [_Schedule_Item_Duration]) AS Duration
, ISNULL([_Series_Listing_Title], [_Program_Listing_Title]) AS Title
, [_Program_Episode_Title] AS EpisodeTitle
, CASE
WHEN [_Program_Year_Produced] = 0 THEN NULL
ELSE CONVERT(varchar(4), [_Program_Year_Produced])
END AS [Year]
, ISNULL([_Series_Category], [_Program_Category]) AS ProgramType
, ISNULL([_Program_Genre_List], [_Series_Genre_List]) AS Genre
, NULL AS Credits
, [dbo].fnValidateRating(CONVERT(varchar(8), [_Schedule_Item_VChip_Code])) AS ParentalRating
, [_Program_Synopsis] AS [Description]
, [_Scheduled_Version_House_Number_List] AS Countries
, NULL AS Stereo
, CONVERT(varchar(30), [_Schedule_Item_ID]) AS UniqueID
, 0 AS Updated
, GETDATE() as RecordDate
, NULL AS DupIdentifier
FROM
[Channels].[Spectrum]
WHERE
([IsPending] = 0)
GO
/* --4- INSERT PACIFIC INTO TEMP -- */
INSERT INTO
#GMTVisionTemp
(
[Channel],
[AirDate],
[StartTime],
[Duration],
[Title],
[EpisodeTitle],
[Year],
[ProgramType],
[Genre],
[Credits],
[ParentalRating],
[Description],
[Countries],
[Stereo],
[UniqueID],
[Updated],
[RecordDate],
[DupIdentifier]
)
SELECT
4 AS Channel
, DmcSql.fn_Registry_ConvertPSTToUTC([_Schedule_Item_Date_Time]) AS AirDate
, CONVERT(varchar(5), DmcSql.fn_Registry_ConvertPSTToUTC([_Schedule_Item_Date_Time]), 108) AS StartTime
, CONVERT(varchar(3), [_Schedule_Item_Duration]) AS Duration
, ISNULL([_Series_Listing_Title], [_Program_Listing_Title]) AS Title
, [_Program_Episode_Title] AS EpisodeTitle
, CASE
WHEN [_Program_Year_Produced] = 0 THEN NULL
ELSE CONVERT(varchar(4), [_Program_Year_Produced])
END AS [Year]
, ISNULL([_Series_Category], [_Program_Category]) AS ProgramType
, ISNULL([_Program_Genre_List], [_Series_Genre_List]) AS Genre
, NULL AS Credits
, [dbo].fnValidateRating(CONVERT(varchar(8), [_Schedule_Item_VChip_Code])) AS ParentalRating
, [_Program_Synopsis] AS [Description]
, [_Scheduled_Version_House_Number_List] AS Countries
, NULL AS Stereo
, CONVERT(varchar(30), [_Schedule_Item_ID]) AS UniqueID
, 0 AS Updated
, GETDATE() as RecordDate
, NULL AS DupIdentifier
FROM
[Channels].[Pacific]
WHERE
([IsPending] = 0)
GO
/* --5- INSERT NEWS INTO TEMP -- */
INSERT INTO
#GMTVisionTemp
(
[Channel],
[AirDate],
[StartTime],
[Duration],
[Title],
[EpisodeTitle],
[Year],
[ProgramType],
[Genre],
[Credits],
[ParentalRating],
[Description],
[Countries],
[Stereo],
[UniqueID],
[Updated],
[RecordDate],
[DupIdentifier]
)
SELECT
5 AS Channel
, DmcSql.fn_Registry_ConvertPSTToUTC([_Schedule_Item_Date_Time]) AS AirDate
, CONVERT(varchar(5), DmcSql.fn_Registry_ConvertPSTToUTC([_Schedule_Item_Date_Time]), 108) AS StartTime
, CONVERT(varchar(3), [_Schedule_Item_Duration]) AS Duration
, ISNULL([_Series_Listing_Title], [_Program_Listing_Title]) AS Title
, [_Program_Episode_Title] AS EpisodeTitle
, CASE
WHEN [_Program_Year_Produced] = 0 THEN NULL
ELSE CONVERT(varchar(4), [_Program_Year_Produced])
END AS [Year]
, ISNULL([_Series_Category], [_Program_Category]) AS ProgramType
, ISNULL([_Program_Genre_List], [_Series_Genre_List]) AS Genre
, NULL AS Credits
, [dbo].fnValidateRating(CONVERT(varchar(8), [_Schedule_Item_VChip_Code])) AS ParentalRating
, [_Program_Synopsis] AS [Description]
, [_Scheduled_Version_House_Number_List] AS Countries
, NULL AS Stereo
, CONVERT(varchar(30), [_Schedule_Item_ID]) AS UniqueID
, 0 AS Updated
, GETDATE() as RecordDate
, NULL AS DupIdentifier
FROM
[Channels].[News]
WHERE
([IsPending] = 0)
GO
/* --6- INSERT XTRA INTO TEMP -- */
INSERT INTO
#GMTVisionTemp
(
[Channel],
[AirDate],
[StartTime],
[Duration],
[Title],
[EpisodeTitle],
[Year],
[ProgramType],
[Genre],
[Credits],
[ParentalRating],
[Description],
[Countries],
[Stereo],
[UniqueID],
[Updated],
[RecordDate],
[DupIdentifier]
)
SELECT
6 AS Channel
, DmcSql.fn_Registry_ConvertPSTToUTC([_Schedule_Item_Date_Time]) AS AirDate
, CONVERT(varchar(5), DmcSql.fn_Registry_ConvertPSTToUTC([_Schedule_Item_Date_Time]), 108) AS StartTime
, CONVERT(varchar(3), [_Schedule_Item_Duration]) AS Duration
, ISNULL([_Series_Listing_Title], [_Program_Listing_Title]) AS Title
, [_Program_Episode_Title] AS EpisodeTitle
, CASE
WHEN [_Program_Year_Produced] = 0 THEN NULL
ELSE CONVERT(varchar(4), [_Program_Year_Produced])
END AS [Year]
, ISNULL([_Series_Category], [_Program_Category]) AS ProgramType
, ISNULL([_Program_Genre_List], [_Series_Genre_List]) AS Genre
, NULL AS Credits
, [dbo].fnValidateRating(CONVERT(varchar(8), [_Schedule_Item_VChip_Code])) AS ParentalRating
, [_Program_Synopsis] AS [Description]
, [_Scheduled_Version_House_Number_List] AS Countries
, NULL AS Stereo
, CONVERT(varchar(30), [_Schedule_Item_ID]) AS UniqueID
, 0 AS Updated
, GETDATE() as RecordDate
, NULL AS DupIdentifier
FROM
[Channels].[Xtra]
WHERE
([IsPending] = 0)
GO
/* --7- INSERT FAMILY INTO TEMP -- */
INSERT INTO
#GMTVisionTemp
(
[Channel],
[AirDate],
[StartTime],
[Duration],
[Title],
[EpisodeTitle],
[Year],
[ProgramType],
[Genre],
[Credits],
[ParentalRating],
[Description],
[Countries],
[Stereo],
[UniqueID],
[Updated],
[RecordDate],
[DupIdentifier]
)
SELECT
9 AS Channel
, DmcSql.fn_Registry_ConvertPSTToUTC([_Schedule_Item_Date_Time]) AS AirDate
, CONVERT(varchar(5), DmcSql.fn_Registry_ConvertPSTToUTC([_Schedule_Item_Date_Time]), 108) AS StartTime
, CONVERT(varchar(3), [_Schedule_Item_Duration]) AS Duration
, ISNULL([_Series_Listing_Title], [_Program_Listing_Title]) AS Title
, [_Program_Episode_Title] AS EpisodeTitle
, CASE
WHEN [_Program_Year_Produced] = 0 THEN NULL
ELSE CONVERT(varchar(4), [_Program_Year_Produced])
END AS [Year]
, ISNULL([_Series_Category], [_Program_Category]) AS ProgramType
, ISNULL([_Program_Genre_List], [_Series_Genre_List]) AS Genre
, NULL AS Credits
, [dbo].fnValidateRating(CONVERT(varchar(8), [_Schedule_Item_VChip_Code])) AS ParentalRating
, [_Program_Synopsis] AS [Description]
, [_Scheduled_Version_House_Number_List] AS Countries
, NULL AS Stereo
, CONVERT(varchar(30), [_Schedule_Item_ID]) AS UniqueID
, 0 AS Updated
, GETDATE() as RecordDate
, NULL AS DupIdentifier
FROM
[Channels].[Family]
WHERE
([IsPending] = 0)
GO
/* --8- INSERT MOVIE INTO TEMP -- */
INSERT INTO
#GMTVisionTemp
(
[Channel],
[AirDate],
[StartTime],
[Duration],
[Title],
[EpisodeTitle],
[Year],
[ProgramType],
[Genre],
[Credits],
[ParentalRating],
[Description],
[Countries],
[Stereo],
[UniqueID],
[Updated],
[RecordDate],
[DupIdentifier]
)
SELECT
10 AS Channel
, DmcSql.fn_Registry_ConvertPSTToUTC([_Schedule_Item_Date_Time]) AS AirDate
, CONVERT(varchar(5), DmcSql.fn_Registry_ConvertPSTToUTC([_Schedule_Item_Date_Time]), 108) AS StartTime
, CONVERT(varchar(3), [_Schedule_Item_Duration]) AS Duration
, ISNULL([_Series_Listing_Title], [_Program_Listing_Title]) AS Title
, [_Program_Episode_Title] AS EpisodeTitle
, CASE
WHEN [_Program_Year_Produced] = 0 THEN NULL
ELSE CONVERT(varchar(4), [_Program_Year_Produced])
END AS [Year]
, ISNULL([_Series_Category], [_Program_Category]) AS ProgramType
, ISNULL([_Program_Genre_List], [_Series_Genre_List]) AS Genre
, NULL AS Credits
, [dbo].fnValidateRating(CONVERT(varchar(8), [_Schedule_Item_VChip_Code])) AS ParentalRating
, [_Program_Synopsis] AS [Description]
, [_Scheduled_Version_House_Number_List] AS Countries
, NULL AS Stereo
, CONVERT(varchar(30), [_Schedule_Item_ID]) AS UniqueID
, 0 AS Updated
, GETDATE() as RecordDate
, NULL AS DupIdentifier
FROM
[Channels].[Movie]
WHERE
([IsPending] = 0)
GO
/* --9- INSERT PACIFIC INTO TEMP -- */
INSERT INTO
#GMTVisionTemp
(
[Channel],
[AirDate],
[StartTime],
[Duration],
[Title],
[EpisodeTitle],
[Year],
[ProgramType],
[Genre],
[Credits],
[ParentalRating],
[Description],
[Countries],
[Stereo],
[UniqueID],
[Updated],
[RecordDate],
[DupIdentifier]
)
SELECT
11 AS Channel
, DATEADD(mi, [AFNCMS].[dbo].fnGetDelayedBias(11, DmcSql.fn_Registry_ConvertPSTToUTC([_Schedule_Item_Date_Time])),DmcSql.fn_Registry_ConvertPSTToUTC([_Schedule_Item_Date_Time])) AS [AirDate]
, CONVERT(varchar(5), DmcSql.fn_Registry_ConvertPSTToUTC([_Schedule_Item_Date_Time]), 108) AS StartTime
, CONVERT(varchar(3), [_Schedule_Item_Duration]) AS Duration
, ISNULL([_Series_Listing_Title], [_Program_Listing_Title]) AS Title
, [_Program_Episode_Title] AS EpisodeTitle
, CASE
WHEN [_Program_Year_Produced] = 0 THEN NULL
ELSE CONVERT(varchar(4), [_Program_Year_Produced])
END AS [Year]
, ISNULL([_Series_Category], [_Program_Category]) AS ProgramType
, ISNULL([_Program_Genre_List], [_Series_Genre_List]) AS Genre
, NULL AS Credits
, [dbo].fnValidateRating(CONVERT(varchar(8), [_Schedule_Item_VChip_Code])) AS ParentalRating
, [_Program_Synopsis] AS [Description]
, [_Scheduled_Version_House_Number_List] AS Countries
, NULL AS Stereo
, 'F' + CONVERT(varchar(29), [_Schedule_Item_ID]) AS UniqueID
, 0 AS Updated
, GETDATE() as RecordDate
, NULL AS DupIdentifier
FROM
[Channels].[Pacific]
WHERE
([IsPending] = 0)
GO
/**10* PENTAGON **/
INSERT INTO
#GMTVisionTemp
(
[Channel],
[AirDate],
[StartTime],
[Duration],
[Title],
[EpisodeTitle],
[Year],
[ProgramType],
[Genre],
[Credits],
[ParentalRating],
[Description],
[Countries],
[Stereo],
[UniqueID],
[Updated],
[RecordDate],
[DupIdentifier]
)
SELECT
8 AS Channel
, [AirDate]
, [StartTime]
, [Duration]
, [Title]
, NULL AS EpisodeTitle
, NULL AS [Year]
, NULL AS ProgramType
, NULL AS Genre
, NULL AS Credits
, NULL AS ParentalRating
, NULL AS [Description]
, NULL AS Countries
, NULL AS Stereo
, '8' + CAST(DATEPART(MM,[AirDate]) AS VARCHAR(2)) + CAST(DATEPART(DD,[AirDate]) AS VARCHAR(2)) + CAST(DATEPART(YY,[AirDate]) AS VARCHAR(4)) + [StartTime] AS UniqueID
, 0 AS Updated
, GETDATE() AS RecordDate
, NULL AS DupIdentifier
FROM
[Channels].[Pentagon]
GO
/*1***********************************************************************/
/* -- INSERT INTO GMTVisionMeta -- */
/*************************************************************************/
/*1*/
INSERT INTO #GMTVisionMetaTemp
(
[GMTVisionDataConversionID],
[SeriesAttributes],
[ProgramAttributes],
[IsLive],
[IsPremiere],
[ProgramAdvisory],
[SeriesId],
[ProgramId],
[ProgramEpisodeSeason]
)
SELECT
b.[GMTVisionDataConversionID] AS GMTVisionDataConversionID
, a.[_Series_Attribute_List] AS SeriesAttributes
, a.[_Program_Attribute_List] AS ProgramAttributes
, a.[_Schedule_Item_Live_Flag] AS IsLive
, a.[_Schedule_Item_Premiere_Flag] AS IsPremiere
, a.[_Program_Advisory_List] AS ProgramAdvisory
, a.[_Series_ID] AS SeriesId
, a.[_Program_Episode_ID] AS ProgramId
, a.[_Program_Episode_Season] AS ProgramEpisodeSeason
FROM
[AFNVisionData].[Channels].[Sports] a
INNER JOIN #GMTVisionTemp b ON b.[UniqueID] = CONVERT(varchar(30), a.[_Schedule_Item_ID])
GO
/*2*/
INSERT INTO #GMTVisionMetaTemp
(
[GMTVisionDataConversionID],
[SeriesAttributes],
[ProgramAttributes],
[IsLive],
[IsPremiere],
[ProgramAdvisory],
[SeriesId],
[ProgramId],
[ProgramEpisodeSeason]
)
SELECT
b.[GMTVisionDataConversionID] AS GMTVisionDataConversionID
, a.[_Series_Attribute_List] AS SeriesAttributes
, a.[_Program_Attribute_List] AS ProgramAttributes
, a.[_Schedule_Item_Live_Flag] AS IsLive
, a.[_Schedule_Item_Premiere_Flag] AS IsPremiere
, a.[_Program_Advisory_List] AS ProgramAdvisory
, a.[_Series_ID] AS SeriesId
, a.[_Program_Episode_ID] AS ProgramId
, a.[_Program_Episode_Season] AS ProgramEpisodeSeason
FROM
[AFNVisionData].[Channels].[Atlantic] a
INNER JOIN #GMTVisionTemp b ON b.[UniqueID] = CONVERT(varchar(30), a.[_Schedule_Item_ID])
GO
/*3*/
INSERT INTO #GMTVisionMetaTemp
(
[GMTVisionDataConversionID],
[SeriesAttributes],
[ProgramAttributes],
[IsLive],
[IsPremiere],
[ProgramAdvisory],
[SeriesId],
[ProgramId],
[ProgramEpisodeSeason]
)
SELECT
b.[GMTVisionDataConversionID] AS GMTVisionDataConversionID
, a.[_Series_Attribute_List] AS SeriesAttributes
, a.[_Program_Attribute_List] AS ProgramAttributes
, a.[_Schedule_Item_Live_Flag] AS IsLive
, a.[_Schedule_Item_Premiere_Flag] AS IsPremiere
, a.[_Program_Advisory_List] AS ProgramAdvisory
, a.[_Series_ID] AS SeriesId
, a.[_Program_Episode_ID] AS ProgramId
, a.[_Program_Episode_Season] AS ProgramEpisodeSeason
FROM
[AFNVisionData].[Channels].[Spectrum] a
INNER JOIN #GMTVisionTemp b ON b.[UniqueID] = CONVERT(varchar(30), a.[_Schedule_Item_ID])
GO
/*4*/
INSERT INTO #GMTVisionMetaTemp
(
[GMTVisionDataConversionID],
[SeriesAttributes],
[ProgramAttributes],
[IsLive],
[IsPremiere],
[ProgramAdvisory],
[SeriesId],
[ProgramId],
[ProgramEpisodeSeason]
)
SELECT
b.[GMTVisionDataConversionID] AS GMTVisionDataConversionID
, a.[_Series_Attribute_List] AS SeriesAttributes
, a.[_Program_Attribute_List] AS ProgramAttributes
, a.[_Schedule_Item_Live_Flag] AS IsLive
, a.[_Schedule_Item_Premiere_Flag] AS IsPremiere
, a.[_Program_Advisory_List] AS ProgramAdvisory
, a.[_Series_ID] AS SeriesId
, a.[_Program_Episode_ID] AS ProgramId
, a.[_Program_Episode_Season] AS ProgramEpisodeSeason
FROM
[AFNVisionData].[Channels].[Pacific] a
INNER JOIN #GMTVisionTemp b ON b.[UniqueID] = CONVERT(varchar(30), a.[_Schedule_Item_ID])
GO
/*5*/
INSERT INTO #GMTVisionMetaTemp
(
[GMTVisionDataConversionID],
[SeriesAttributes],
[ProgramAttributes],
[IsLive],
[IsPremiere],
[ProgramAdvisory],
[SeriesId],
[ProgramId],
[ProgramEpisodeSeason]
)
SELECT
b.[GMTVisionDataConversionID] AS GMTVisionDataConversionID
, a.[_Series_Attribute_List] AS SeriesAttributes
, a.[_Program_Attribute_List] AS ProgramAttributes
, a.[_Schedule_Item_Live_Flag] AS IsLive
, a.[_Schedule_Item_Premiere_Flag] AS IsPremiere
, a.[_Program_Advisory_List] AS ProgramAdvisory
, a.[_Series_ID] AS SeriesId
, a.[_Program_Episode_ID] AS ProgramId
, a.[_Program_Episode_Season] AS ProgramEpisodeSeason
FROM
[AFNVisionData].[Channels].[News] a
INNER JOIN #GMTVisionTemp b ON b.[UniqueID] = CONVERT(varchar(30), a.[_Schedule_Item_ID])
GO
/*6*/
INSERT INTO #GMTVisionMetaTemp
(
[GMTVisionDataConversionID],
[SeriesAttributes],
[ProgramAttributes],
[IsLive],
[IsPremiere],
[ProgramAdvisory],
[SeriesId],
[ProgramId],
[ProgramEpisodeSeason]
)
SELECT
b.[GMTVisionDataConversionID] AS GMTVisionDataConversionID
, a.[_Series_Attribute_List] AS SeriesAttributes
, a.[_Program_Attribute_List] AS ProgramAttributes
, a.[_Schedule_Item_Live_Flag] AS IsLive
, a.[_Schedule_Item_Premiere_Flag] AS IsPremiere
, a.[_Program_Advisory_List] AS ProgramAdvisory
, a.[_Series_ID] AS SeriesId
, a.[_Program_Episode_ID] AS ProgramId
, a.[_Program_Episode_Season] AS ProgramEpisodeSeason
FROM
[AFNVisionData].[Channels].[Xtra] a
INNER JOIN #GMTVisionTemp b ON b.[UniqueID] = CONVERT(varchar(30), a.[_Schedule_Item_ID])
GO
/*7*/
INSERT INTO #GMTVisionMetaTemp
(
[GMTVisionDataConversionID],
[SeriesAttributes],
[ProgramAttributes],
[IsLive],
[IsPremiere],
[ProgramAdvisory],
[SeriesId],
[ProgramId],
[ProgramEpisodeSeason]
)
SELECT
b.[GMTVisionDataConversionID] AS GMTVisionDataConversionID
, a.[_Series_Attribute_List] AS SeriesAttributes
, a.[_Program_Attribute_List] AS ProgramAttributes
, a.[_Schedule_Item_Live_Flag] AS IsLive
, a.[_Schedule_Item_Premiere_Flag] AS IsPremiere
, a.[_Program_Advisory_List] AS ProgramAdvisory
, a.[_Series_ID] AS SeriesId
, a.[_Program_Episode_ID] AS ProgramId
, a.[_Program_Episode_Season] AS ProgramEpisodeSeason
FROM
[AFNVisionData].[Channels].[Family] a
INNER JOIN #GMTVisionTemp b ON b.[UniqueID] = CONVERT(varchar(30), a.[_Schedule_Item_ID])
GO
/*8*/
INSERT INTO #GMTVisionMetaTemp
(
[GMTVisionDataConversionID],
[SeriesAttributes],
[ProgramAttributes],
[IsLive],
[IsPremiere],
[ProgramAdvisory],
[SeriesId],
[ProgramId],
[ProgramEpisodeSeason]
)
SELECT
b.[GMTVisionDataConversionID] AS GMTVisionDataConversionID
, a.[_Series_Attribute_List] AS SeriesAttributes
, a.[_Program_Attribute_List] AS ProgramAttributes
, a.[_Schedule_Item_Live_Flag] AS IsLive
, a.[_Schedule_Item_Premiere_Flag] AS IsPremiere
, a.[_Program_Advisory_List] AS ProgramAdvisory
, a.[_Series_ID] AS SeriesId
, a.[_Program_Episode_ID] AS ProgramId
, a.[_Program_Episode_Season] AS ProgramEpisodeSeason
FROM
[AFNVisionData].[Channels].[Movie] a
INNER JOIN #GMTVisionTemp b ON b.[UniqueID] = CONVERT(varchar(30), a.[_Schedule_Item_ID])
GO
/*9*/
INSERT INTO #GMTVisionMetaTemp
(
[GMTVisionDataConversionID],
[SeriesAttributes],
[ProgramAttributes],
[IsLive],
[IsPremiere],
[ProgramAdvisory],
[SeriesId],
[ProgramId],
[ProgramEpisodeSeason]
)
SELECT
b.[GMTVisionDataConversionID] AS GMTVisionDataConversionID
, a.[_Series_Attribute_List] AS SeriesAttributes
, a.[_Program_Attribute_List] AS ProgramAttributes
, a.[_Schedule_Item_Live_Flag] AS IsLive
, a.[_Schedule_Item_Premiere_Flag] AS IsPremiere
, a.[_Program_Advisory_List] AS ProgramAdvisory
, a.[_Series_ID] AS SeriesId
, a.[_Program_Episode_ID] AS ProgramId
, a.[_Program_Episode_Season] AS ProgramEpisodeSeason
FROM
[AFNVisionData].[Channels].[Pacific] a
INNER JOIN #GMTVisionTemp b ON b.[UniqueID] = 'F' + CONVERT(varchar(29), a.[_Schedule_Item_ID])
GO
/* -- TRUNCATE GMTVision LIVE TABLE -- */
TRUNCATE TABLE [AFNVisionData].[dbo].[GMTVision];
GO
/* -- TRUNCATE GMTVisionMeta LIVE TABLE -- */
TRUNCATE TABLE [AFNVisionData].[dbo].[GMTVisionMeta];
GO
/*INSERT INTO GMTVision LIVE TABLE*/
INSERT INTO [AFNVisionData].[dbo].[GMTVision]
([GMTVisionDataConversionID]
,[Channel]
,[AirDate]
,[StartTime]
,[Duration]
,[Title]
,[EpisodeTitle]
,[Year]
,[ProgramType]
,[Genre]
,[Credits]
,[ParentalRating]
,[Description]
,[Countries]
,[Stereo]
,[UniqueID]
,[Updated]
,[RecordDate]
,[DupIdentifier])
SELECT
[GMTVisionDataConversionID]
,[Channel]
,[AirDate]
,[StartTime]
,[Duration]
,[Title]
,[EpisodeTitle]
,[Year]
,[ProgramType]
,[Genre]
,[Credits]
,[ParentalRating]
,[Description]
,[Countries]
,[Stereo]
,[UniqueID]
,[Updated]
,[RecordDate]
,[DupIdentifier]
FROM #GMTVisionTemp
GO
/*INSERT INTO GMTVisionMeta LIVE TABLE*/
INSERT INTO [AFNVisionData].[dbo].[GMTVisionMeta]
([MetadataId]
,[GMTVisionDataConversionID]
,[SeriesAttributes]
,[ProgramAttributes]
,[IsLive]
,[IsPremiere]
,[ProgramAdvisory]
,[SeriesId]
,[ProgramId]
,[ProgramEpisodeSeason])
SELECT [MetadataId]
,[GMTVisionDataConversionID]
,[SeriesAttributes]
,[ProgramAttributes]
,[IsLive]
,[IsPremiere]
,[ProgramAdvisory]
,[SeriesId]
,[ProgramId]
,[ProgramEpisodeSeason]
FROM #GMTVisionMetaTemp
GO
/* EXECUTION PLAN SHOWS
INSERT INTO [AFNVisionData].[dbo].[GMTVision] -- BEING 85% OF THE COST!
Profiler and Exec Plan are attached.
Thanks for the help 🙂
*/

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Jim McLeod SSCarpal Tunnel Points: 4057 More actions · Answer 1

After a quick look, and assuming that the 85% query cost is correct, I've a couple of observations.

You're truncating the GMTVision table. It is probably worth disabling the indexes at this time, and rebuilding them after the 24,000 rows have been inserted. This may speed up the process, and avoid a lot of fragmentation. The indexes are where the 20% values are coming from. The full text index (nested loop returning 298 million rows!) may also be a concern, but it's harder to see the effect while those indexes are still there.

Can you give a table definition of GMTVision, including the full text index details? You may also want to put an clustered index on your temporary tables in the same way that GMTVision is structured. This will avoid the sort operation in the 85% query.

NBD4K27 SSC-Addicted Points: 456 More actions · Answer 2

Excellent! I shall try what you suggest. Here's what you asked for I hope.

/****** Object: Table [dbo].[GMTVision] Script Date: 07/14/2010 19:57:55 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_PADDING ON

GO

CREATE TABLE [dbo].[GMTVision](

[GMTVisionDataConversionID] [int] NOT NULL,

[Channel] [int] NOT NULL,

[AirDate] [datetime] NULL,

[StartTime] [varchar](5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[Duration] [varchar](3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[Title] [varchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[EpisodeTitle] [varchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[Year] [varchar](4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[ProgramType] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[Genre] [varchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[Credits] [varchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[ParentalRating] [varchar](8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[Description] [varchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[Countries] [varchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[Stereo] [varchar](6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[UniqueID] [varchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[Updated] [bit] NOT NULL CONSTRAINT [DF_GMTVision_Updated] DEFAULT ((0)),

[RecordDate] [datetime] NOT NULL CONSTRAINT [DF_GMTVision_RecordDate] DEFAULT (getdate()),

[DupIdentifier] [varchar](15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

CONSTRAINT [PK_GMTVision] PRIMARY KEY CLUSTERED

(

[GMTVisionDataConversionID] ASC

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

) ON [PRIMARY]

GO

SET ANSI_PADDING OFF

/****** Object: Index [PK_GMTVision] Script Date: 07/14/2010 19:58:54 ******/

ALTER TABLE [dbo].[GMTVision] ADD CONSTRAINT [PK_GMTVision] PRIMARY KEY CLUSTERED

(

[GMTVisionDataConversionID] ASC

)WITH (PAD_INDEX = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]

/****** Object: Index [IX_GMTVision] Script Date: 07/14/2010 19:59:47 ******/

CREATE NONCLUSTERED INDEX [IX_GMTVision] ON [dbo].[GMTVision]

(

[GMTVisionDataConversionID] ASC

)WITH (PAD_INDEX = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]

/****** Object: Index [IX_GMTVision_AirDate] Script Date: 07/14/2010 20:00:03 ******/

CREATE NONCLUSTERED INDEX [IX_GMTVision_AirDate] ON [dbo].[GMTVision]

(

[AirDate] ASC

)WITH (PAD_INDEX = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]

/****** Object: Index [IX_GMTVision_Channel] Script Date: 07/14/2010 20:00:22 ******/

CREATE NONCLUSTERED INDEX [IX_GMTVision_Channel] ON [dbo].[GMTVision]

(

[Channel] ASC

)WITH (PAD_INDEX = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]

/****** Object: Index [IX_GMTVision_ParentalRating] Script Date: 07/14/2010 20:00:41 ******/

CREATE NONCLUSTERED INDEX [IX_GMTVision_ParentalRating] ON [dbo].[GMTVision]

(

[ParentalRating] ASC

)WITH (PAD_INDEX = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]

/****** Object: Index [IX_GMTVision_UniqueID] Script Date: 07/14/2010 20:00:59 ******/

CREATE NONCLUSTERED INDEX [IX_GMTVision_UniqueID] ON [dbo].[GMTVision]

(

[UniqueID] ASC

)

INCLUDE ( [GMTVisionDataConversionID]) WITH (PAD_INDEX = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]

/****** Object: Index [PK_GMTVision] Script Date: 07/14/2010 20:01:16 ******/

ALTER TABLE [dbo].[GMTVision] ADD CONSTRAINT [PK_GMTVision] PRIMARY KEY CLUSTERED

(

[GMTVisionDataConversionID] ASC

)WITH (PAD_INDEX = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]