improving performance of SQL Server temp table selection

Question

I have a temp table its structure is given below. Its returning 3.8 million records. Its taking 5 minutes. Is there any way to improve performance. What should be the proper index for this temp table? Do we need to use partition? If so how to use it properly

CREATE TABLE  #FinalResultTable                
 (              
    RowNum INT  PRIMARY KEY NONCLUSTERED IDENTITY(1,1),              
    [Disclosure Category] NVARCHAR(250) NULL,              
    [Line #] INT NULL,                 
    [AllocationProcessId] BIGINT NULL,              
    [Allocation Name] VARCHAR(50) NULL,                
    [Line Description (Long)] NVARCHAR(3000) NULL,               
    [Line Description (Short)]  NVARCHAR(500) NULL,         
    [UniqueTransactionId] INT NULL,         
    [TransactionName] VARCHAR(125) NULL,                
    [Partner #] INT NULL,                
    [Partner Name] VARCHAR(2000) NULL,                
    [RuleName] VARCHAR(128) NULL,                
    [Tag Group1] NVARCHAR(75) NULL,                
    [Tag Group2] NVARCHAR(75) NULL,                
    [Tag Group3] NVARCHAR(75) NULL,                
    [Tag Group4] NVARCHAR(75) NULL,                
    [Segment Start Date] DATE NULL,                
    [Segment End Date] DATE NULL,                
)   


CREATE CLUSTERED INDEX IX_FinalResultTable 
    ON #FinalResultTable ([Partner #],[Disclosure Category]);

before below selection data is inserting into this temp table from many other tables.

SELECT 
 [RowNum],              
[Disclosure Category],              
[Line #],                 
[AllocationProcessId],              
[Allocation Name],                
[Line Description (Long)],               
[Line Description (Short)],         
[UniqueTransactionId],         
[TransactionName],                
[Partner #],                
[Partner Name],                
[RuleName],                
[Tag Group1],                
[Tag Group2],                
[Tag Group3],                
[Tag Group4],                
[Segment Start Date],                
[Segment End Date]                
FROM #FinalResultTable

Answer 1

You can't speed up a SELECT with indexes if you plan on retrieving all records. Indexes are good for selecting specific rows with a determined criteria, or fetching rows in a particular order (your SELECT doesn't have any ORDER BY also).

One thing worth mentioning is how you load your table. Using a CREATE TABLE + INSERT INTO makes the SQL Server log each row inserted and takes a lot longer than doing a SELECT INTO (without creating the table first) which has minimal logging. Make sure to properly cast your data types from the SELECT if you switch to this approach.

So instead of:

IF OBJECT_ID('tempdb..#FinalResultTable') IS NOT NULL
    DROP TABLE #FinalResultTable

CREATE TABLE  #FinalResultTable                
(              
    /*Columns*/              
)

INSERT INTO #FinalResultTable
(
    /*Columns*/  
)
SELECT
    /*Columns*/
FROM
    /*Tables*/

You switch to:

IF OBJECT_ID('tempdb..#FinalResultTable') IS NOT NULL
    DROP TABLE #FinalResultTable

SELECT
    /*
    Columns with the proper data type cast (if needed), for example:

        Column1 = CONVERT(VARCHAR(100), Column1 + 'SomeText'),
        Column2 = CONVERT(INT, VarcharColumn)
    */
INTO
    #FinalResultTable
FROM
    /*Tables*/

If you don't order your data and you are just using this temporary table to select all rows later, you can avoid creating any index on it, since it will take a while to generate one or reorder all data (if it's clustered). So don't create your clustered index IX_FinalResultTable and also don't declare your PRIMARY KEY for your IDENTITY column, use RowNum INT IDENTITY. This will make your temporary table a heap instead of an actual table, beware that most of your filtered select will greatly decrease on performance if you used to search by [Partner #].

If you do need your clustered index by [Partner #], [Disclosure Category], loading the table already ordered by these 2 will actually decrease the index creating time. Make sure to create the index after the table is loaded and not before, as it's faster.

SELECT
    /*
    Columns with the proper data type cast (if needed), for example:

        Column1 = CONVERT(VARCHAR(100), Column1 + 'SomeText'),
        Column2 = CONVERT(INT, VarcharColumn)
    */
INTO
    #FinalResultTable
FROM
    /*Tables*/
ORDER BY
    [Partner #],            -- Or the expression that resolves as this column
    [Disclosure Category]   -- Or the expression that resolves as this column

Also it's worth mentioning that the less columns you SELECT the faster the SELECT will be. The speed of the transfer will also depend on networking between your client and your server, and the connection type it's using.