How to optimize SELECT .. INTO

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

How to optimize SELECT .. INTO

Author

Topic

SQLMark
Starting Member

18 Posts

Posted - 2009-05-20 : 04:25:35

Hi all
I have to copy data between two SQL databases. I use the "SELECT..INTO" statement and all it is OK

But ...

How can I optimize the data transfert, since I always have to copy the ENTIRE table and not only some records? Is there any T-SQL command that could help me?

Thanks!

waterduck
Aged Yak Warrior

982 Posts

Posted - 2009-05-20 : 04:26:45

insert tablename1(column)
select column from tablename

SQLMark
Starting Member

18 Posts

Posted - 2009-05-20 : 04:39:54

Ok, I already use this statement:

SELECT *
INTO DestTable
FROM OrigTable

but what I want to know is if there is a command for optimize this operation...

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-05-20 : 04:54:31

It looks all fine.
If there is no problem then there is no need to optimize because optimization needs to set a target...

Webfred

No, you're never too old to Yak'n'Roll if you're too young to die.

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-05-20 : 05:37:57

Think you might be a bit confused here. So lets split the problem into 2 parts...

1) Selectivity

You said

quote:
I always have to copy the ENTIRE table and not only some records? Is there any T-SQL command that could help me?

Well of course there is:


SELECT *
INTO destTable
FROM OrigTable
WHERE
    <some condition>

Then only rows that meet the conditions in your WHERE clause will be copied over to the new table.

--------------

2) Performance


SELECT * INTO x FROM y

Is I think a Row based operation rather than a set based op. I think that INSERT x SELECT y FROM z will be more efficient for bigger data sets.

Experts will confirm or deny I'm sure.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

SQLMark
Starting Member

18 Posts

Posted - 2009-05-20 : 05:57:07

Thanks Charlie
my only question is if SQL Server can copy an entire table at one time rather then copy row by row when I invoke the SELECT..INTO command.
Perhaps SQL is already optimized when in the SELECT INTO is missing the WHERE command and behind the scenes it copys all the table.

I suppose that SQL has a command like

SET HUGE_DATA ON

that predispose the database for bulk operation...

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-05-20 : 06:07:09

To be honest I'm not sure.

The SELECT INTO reference in BOL states how to use it and what the end result of it is but not how it does it.

I've just done a few test with a sample table with a million rows and the SELECT INTO method seems to outperform the INSERT INTO method by a factor of 2.

There are BULK operations for getting data into and out of the database (BCP and BULK INSERT) so I don't know what SELECT INTO is doing behind the scenes.

Could be that I'm totally wrong thinking its a row based op -- or that it used to be and now is done differently in 2005 and up.

Someone will know and post I am sure.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

SQLMark
Starting Member

18 Posts

Posted - 2009-05-20 : 07:35:07

I meant exactly this...

robvolk
Most Valuable Yak

15732 Posts

Posted - 2009-05-20 : 07:48:51

The SELECT INTO operation is minimally logged when the database is in SIMPLE or BULK LOGGED recovery mode. This means only page allocations are logged, not each row. INSERT INTO will log rows regardless of recovery model. Unless you are using FULL recovery, SELECT INTO is as optimized as possible.

The benefits and drawbacks for SELECT INTO are listed here (and in your local copy of Books Online):

http://msdn.microsoft.com/en-us/library/ms188029.aspx

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-05-20 : 08:43:01

Cheers Rob.

That'll explain the difference I was seeing between testing on a production set-up and on development environments!

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

SQLMark
Starting Member

18 Posts

Posted - 2009-05-20 : 09:14:25

What a brainstorming we have made...!
Thank you.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources