Inserts of large amounts of data.

Inserts of large amounts of data.

Post by Wayne Hap » Sat, 28 Sep 1996 04:00:00



I have an application that inserts large amount of data into a Sybase
database at a single time. 500,000 to 1,000,000 rows at a time is not
unsual.

        My boss claims that bcp'ing it in chunks at a time is a good
strategy.

        I interested in what methods people out there use to insert largs
amounts of data at a single time.

        Wayne

 
 
 

Inserts of large amounts of data.

Post by Pablo Sanch » Mon, 30 Sep 1996 04:00:00



> I have an application that inserts large amount of data into a Sybase
> database at a single time. 500,000 to 1,000,000 rows at a time is not
> unsual.

>            My boss claims that bcp'ing it in chunks at a time is a good
> strategy.

>    I interested in what methods people out there use to insert largs
> amounts of data at a single time.

Your boss seems to be hip.  Use bcp and look at moving the buff wash
marker closer to the MRU side of the chain when doing the
bcp's... this will even out your I/O's... I'm assuming that during
this period you are only doing the bcp load.

Oh yeah, if you are doing a *slow* bcp look at sorting your data
prior to doing the bcp.

Pablo Sanchez              | Ph # (415) 933.3812        Fax # (415) 933.2821

===============================================================================
I am accountable for my actions.   http://reality.sgi.com/pablo [ /Sybase_FAQ ]

 
 
 

Inserts of large amounts of data.

Post by RGCramot » Mon, 30 Sep 1996 04:00:00



>I have an application that inserts large amount of data into a Sybase
>database at a single time. 500,000 to 1,000,000 rows at a time is not
>unsual.

Please provide more information. Where is this data? Is the data in flat
files, another Sybase database, another vendors database?

Richard Cramotte
DBA

 
 
 

Inserts of large amounts of data.

Post by Chuck » Mon, 30 Sep 1996 04:00:00



Quote:>I have an application that inserts large amount of data into a Sybase
>database at a single time. 500,000 to 1,000,000 rows at a time is not
>unsual.

>            My boss claims that bcp'ing it in chunks at a time is a good
>strategy.

My experience has been that it pays to break long-running database
access processes into a series of processes. Wrap them up in a single
script so you can stop the main process and let the children complete.
You can continue the process where you left off.  Very handy when you
need to bounce the server.

--

 
 
 

Inserts of large amounts of data.

Post by Wayne Hap » Tue, 01 Oct 1996 04:00:00




> >I have an application that inserts large amount of data into a Sybase
> >database at a single time. 500,000 to 1,000,000 rows at a time is not
> >unsual.

> Please provide more information. Where is this data? Is the data in flat
> files, another Sybase database, another vendors database?

> Richard Cramotte
> DBA

        The data is in flat text file and they are ftp'd from a vendor to my
firewall and from there they are deposited onto one of my machines.
(Sparc 1000) This machine is physically seperate from the machine that
the Sybase server runs on.
 
 
 

Inserts of large amounts of data.

Post by Joseph Weinste » Tue, 01 Oct 1996 04:00:00


I'd get the flat file on the server machine if possible.
Make sure your TCP packet size isn't too small.
bcp is the way to get it into the server.
What version of the server are you running? If sysXI, you should
partition the table and separate the data file into chunks and run
simultaneous bcp's into the server.
Make sure there's no index on the target table(s). Build the indexes
after the load. Note you need enough memory in the database to hold
two copies of the table if your creating a clustered index.

Use UNIX tools to sort the data and do a dbcc tune (ascinserts, 1, <tablename>)
so page splits are minimized.

--

                                                              510-922-3620
If trees could scream, would we be so cavalier about cutting them down?
We might, if they screamed all the time, for no good reason. Jack Handey

 
 
 

Inserts of large amounts of data.

Post by Bjorn Bjerkel » Tue, 01 Oct 1996 04:00:00



> I have an application that inserts large amount of data into a Sybase
> database at a single time. 500,000 to 1,000,000 rows at a time is not
> unsual.

>         My boss claims that bcp'ing it in chunks at a time is a good
> strategy.

>         I interested in what methods people out there use to insert largs
> amounts of data at a single time.

But what if you need to transfer data from a table that contains 500,000
to 1000,000 rows into another table?, and you only want to insert the
tuples that does not exist in the target table. I suppose this is
relevant as well, and bulkcopy is not applicable for this problem.
--
Bj?rn Bjerkeli  3W http://www.idt.ntnu.no/~bjerkeli/  
Office1.:+45 73593046        
Office2.:+45 73597097
 
 
 

Inserts of large amounts of data.

Post by H.S. Prasad 4-839 » Thu, 03 Oct 1996 04:00:00


Wayne

Also batch you bcps appropriately so as not to fill up the log. Having the
batch size too small will leave holes in pages because each batch starts on a
new page.

- Prasad



>> I have an application that inserts large amount of data into a Sybase
>> database at a single time. 500,000 to 1,000,000 rows at a time is not
>> unsual.

>>        My boss claims that bcp'ing it in chunks at a time is a good
>> strategy.

>>        I interested in what methods people out there use to insert largs
>> amounts of data at a single time.

>Your boss seems to be hip.  Use bcp and look at moving the buff wash
>marker closer to the MRU side of the chain when doing the
>bcp's... this will even out your I/O's... I'm assuming that during
>this period you are only doing the bcp load.

>Oh yeah, if you are doing a *slow* bcp look at sorting your data
>prior to doing the bcp.

>Pablo Sanchez              | Ph # (415) 933.3812        Fax # (415) 933.2821

>===============================================================================
>I am accountable for my actions.   http://reality.sgi.com/pablo [ /Sybase_FAQ ]

--
/***********************************************************************/
H.S. Prasad                                     Standard Disclaimer

Phone: (302)634-8398
Fax  : (302)634-8563
/***********************************************************************/