| Author |
select into or bcp out and bcp in!
|
|
| tartampion 2005-12-12, 8:26 pm |
| Need to copy tables from one database to another on the same
server:
-I have the select into bulk copy option set on the server
-Volume of data is rather important; tables might have up
to a million rows.
I have carried out the following tests:
1: BCP:
- Bcped out the data from sending table
- Created the table on the target database
- Bcped in the data into the table on target database.
2: select into
- Copied the table from sending database to target database
using
Select * into db2..tb1 from db1..tb1
The second solution for a table containing 300,000 rows is
practically twice as fast as the first solution (this
naturally expected).
I have a question in this regards: As both operation are
minimally logged, I do not expect any anomalies if I use
select into instead of double bcp out and in? Am I wrong?
Any recommendations?
| |
| Mark A. Parsons 2005-12-12, 8:26 pm |
| anomalies? such as?
Generally speaking you should end up with the same results ... data in
the target table.
tartampion wrote:
> Need to copy tables from one database to another on the same
> server:
> -I have the select into bulk copy option set on the server
> -Volume of data is rather important; tables might have up
> to a million rows.
> I have carried out the following tests:
> 1: BCP:
> - Bcped out the data from sending table
> - Created the table on the target database
> - Bcped in the data into the table on target database.
> 2: select into
> - Copied the table from sending database to target database
> using
> Select * into db2..tb1 from db1..tb1
>
> The second solution for a table containing 300,000 rows is
> practically twice as fast as the first solution (this
> naturally expected).
>
> I have a question in this regards: As both operation are
> minimally logged, I do not expect any anomalies if I use
> select into instead of double bcp out and in? Am I wrong?
> Any recommendations?
| |
| tartampion 2005-12-13, 1:24 pm |
| Anomalies: Can the log gets full when we do a select into
for example?
[color=darkred]
> anomalies? such as?
>
> Generally speaking you should end up with the same results
> .. data in the target table.
>
>
> tartampion wrote:
>
| |
| tartampion 2005-12-13, 1:24 pm |
| can the sending_db.table get locked and so create problems
for other users?
[color=darkred]
> anomalies? such as?
>
> Generally speaking you should end up with the same results
> .. data in the target table.
>
>
> tartampion wrote:
>
| |
| Mark A. Parsons 2005-12-13, 1:24 pm |
| 'select into' is minimally logged so there will be *some* logging. From
your original post it appears that you were able to run 'select into'
successfully so your log must have been large enough, eh?
bcp may be another issue. If you know for a fact that you are doing a
*fast* bcp (ie, minimally logged) then you should not have any problems.
If you end up doing a *slow* bcp (everything is logged) you would need
to consider the amount of log space available to you.
tartampion wrote:[color=darkred
]
> can the sending_db.table get locked and so create problems
> for other users?
>
>
| |
| Sherlock, Kevin 2005-12-13, 8:25 pm |
| This is a good point. Even if using fast bcp, you can control the frequencies
of commits with "-b" option. Select into offers no such control.
There is a third option if you need the control of the -b option of bcp. BCP
OUT to a FIFO pipe in the background, and then start a BCP IN on the pipe. It
will be slower than SELECT INTO, but faster than the BCP out to file, BCP in
from file method.
At a unix prompt (for example):
# mkfifo bcp_pipe
# bcp mydb.dbo.mytable out bcp_pipe -n -Uxx -Sxxxxx -Pxxxxx -A8192 &
# bcp otherdb.dbo.mytablecopy in bcp_pipe -n -Uxx -Sxxxxx -Pxxxxx -A8192 -b 5000
<tartampion> wrote in message news:439f0cc0.2bc7.1681692777@sybase.com...[color=darkred]
> Anomalies: Can the log gets full when we do a select into
> for example?
>
| |
| Jason L. Froebe [Team Sybase] 2005-12-16, 8:25 pm |
| Mark A. Parsons wrote:
> 'select into' is minimally logged so there will be *some* logging. From
> your original post it appears that you were able to run 'select into'
> successfully so your log must have been large enough, eh?
>
> bcp may be another issue. If you know for a fact that you are doing a
> *fast* bcp (ie, minimally logged) then you should not have any problems.
> If you end up doing a *slow* bcp (everything is logged) you would need
> to consider the amount of log space available to you.
>
One thing to keep in mind: drop all indexes and the like before bcping
to ensure minimal logging. See the utilities guide on
http://sybooks.sybase.com for explicit detail.
Jason L. Froebe
Team Sybase
http://jfroebe.livejournal.com
|
|
|
|