2

I have a ~300 million row table with a column that has 9 different values. I need to be able to SELECT INTO 9 different new tables based on this column.

At the moment it has to do 9 separate table scans, as I just run 9 separate queries. Is there a way I could do this in one table scan with a CASE statement that defines the table to go into, or something similar?

I'm using SQL Server 2012 x64 Standard Edition SP1. There is no covering index as the column list is almost all of them. Example of what I'm trying to achieve:

SELECT [col1]
    ,[col2]
    ,[col3]
    ,[etc]
INTO CASE 
        WHEN col1=N'tbl1' THEN [dbo].[tbl1]
        WHEN col1=N'tbl2' THEN [dbo].[tbl2]
    END
FROM [dbo][tblBase]

There is no clustered index on the base table. There is an index on col1 but it chooses not to use it. Forcing it increases the cost dramatically as it does an RID Lookup.

The base table is 68GB, with SQL Server max server memory being 30GB (although that's increasing to ~50GB soon).

4
  • 1
    I suggest you also add the CREATE TABLE statement for tblBase in the question, including all indexes. Nov 19, 2015 at 12:55
  • 1
    My best guess would be to convert the table to clustered from heap, using the col1 as the first column in the clustered index. Then run the 9 statements. I suppose you plan to delete the table after this (one-time) operation? Nov 19, 2015 at 12:57
  • Looks like a perfect fit for a SSIS package rather than a T-SQL statement. Nov 19, 2015 at 15:51
  • 1
    I believe this should be fairly easy to do with a single scan in a small .NET app, but I need more info first. So, as @ypercube suggested, please post the DDL for this table. Also, do you really need to copy [col1] into the new tables since all rows will have that same value? That seems a bit useless and wasteful. Nov 19, 2015 at 16:23

2 Answers 2

1

SELECT INTO <table-decided-by-code>

You can't dynamically set the destination table like that. If the target tables are part of a partition set partitioned on that column then SQL Server would do this for you, but you are using 2012 standard edition so that won't be possible (partitioned tables were an enterprise only feature until 2016sp1).

Forcing it increases the cost dramatically as it does an RID Lookup.

Have you verified that this produces a slower result? Sometimes the query cost derived for plans by the planner doesn't not match actual performance.

If you are only copying a few columns from the target table, perhaps you could include those in the index with something like CREATE INDEX [ix_tblBase_col1_plus] ON ([col1]) INCLUDE ([col2] ,[col3]). This will increase the size of the index but remove the need for the extra page lookups by RID, but as you say you are taking most of the rows this is unlikely to be practical.

There is no clustered index on the base table.

Adding any old clustered index would not help, you'll just replace the extra lookups by RID with extra lookups by clustering key.

You could try defining [col1] as your clustering key. When clustering on a non-unique key SQL Server adds a 4-byte "uniquifier" column which is appended to the indexed values. If your column is 4 bytes long or less this will result in a clustering key no larger than RID values. If the column contains wider data then space efficiency may be an issue as that extra data will be added to every row in every non-clustered index. You would need to experiment to see what difference this makes to the size of the table+indexes and any impact on other parts of your application (we could offer educated guesses if you provide a full table definition, and if your "col1" is variable length some information on the pattern of its values, but trying it in a dev copy of your DB would be more definitive).

-1

it not use INDEX because no any WHERE condition in SELECT statement, so from SQL server point of view - it always make FULL-SCAN of table, so no reason for use index.

possible make changes

declare cursor as (it is pseudo code)
SELECT DISTINCT col1 as tbl_name FROM [dbo][tblBase]
fetch col1 INTO @tbl_name
and after LOOP for each from LIST

SELECT [col1]
    ,[col2]
    ,[col3]
    ,[etc]
INTO @tbl_name
FROM [dbo][tblBase] WHERE col1 = @tbl_name

but need test - it could reduce loading of server memory, but may not give benefits in total speed

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.