Help in SQL Script Performance

Transact-SQL
1

I have two tables:
Table 1 - Temp_SM_RES_STATE_FACT_Dual has 131239 rows

CREATE TABLE [bcd].[Temp_SM_RES_STATE_FACT_Dual](
	[RESOURCE_KEY] [int] NULL,
	[DATE_TIME_KEY] [int] NULL,
	[MEDIA_TYPE_KEY] [int] NULL,
	[START_TS] [int] NULL,
	[END_TS] [int] NULL,
	[STATE_NAME] [varchar](50) NULL
) 

CREATE NONCLUSTERED INDEX [idx_TS] ON [bcd].[Temp_SM_RES_STATE_FACT_Dual]
(
	[START_TS] ASC,
	[END_TS] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

CREATE NONCLUSTERED INDEX [NC_Idx_All] ON [bcd].[Temp_SM_RES_STATE_FACT_Dual]
(
	[RESOURCE_KEY] ASC,
	[DATE_TIME_KEY] ASC,
	[MEDIA_TYPE_KEY] ASC,
	[START_TS] ASC,
	[END_TS] ASC,
	[STATE_NAME] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

Table 2 Temp_START_END_TS has 33042 rows

    CREATE TABLE [bcd].[Temp_START_END_TS](
    	[START_TS] [int] NULL,
    	[END_TS] [int] NULL
    ) ON [PRIMARY]

    GO

    CREATE NONCLUSTERED INDEX [NC_IDX_STS_ETS] ON [bcd].[Temp_START_END_TS]
    (
    	[START_TS] ASC,
    	[END_TS] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    GO

I have written the following script which is taking long time

    SELECT E.RESOURCE_KEY, E.DATE_TIME_KEY, E.MEDIA_TYPE_KEY, B.START_TS, B.END_TS, E.STATE_NAME
    FROM 
    	BCD.Temp_START_END_TS AS B
        INNER JOIN BCD.Temp_SM_RES_STATE_FACT_Dual AS E ON E.START_TS <= B.START_TS AND B.START_TS < E.END_TS
    ORDER BY E.RESOURCE_KEY, E.DATE_TIME_KEY

Estimate Execution plan
Exec%20Plan

Any suggestion for making my query fast.

2

Hi

Idea 1 ) Break the data into half and process each half at a time

If you need explanation on how to do .. let me know

3

START_TS and END_TS are null-able columns. you want to include those in your query result?
why are you creating 2 indices on same table? what is for example the purpose of this one
NC_Idx_All

4

Nonclustered indexes are worthless for this. Drop the indexes you have on those tables and create clustered ones instead:

CREATE CLUSTERED INDEX [idx_TS] ON bcd.[Temp_SM_RES_STATE_FACT_Dual]
(
[START_TS] ASC,
[END_TS] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
CREATE CLUSTERED INDEX [IDX_STS_ETS] ON bcd.[Temp_START_END_TS]
(
[START_TS] ASC,
[END_TS] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO