Loading data into a large volume database 
Author Message
 Loading data into a large volume database

Hello
        Does anyone have suggestions to increase the rate of loading
data into a large volume database(>10M rows) from flat files.
The files may contain data for multiple tables, and also updates and
inserts. We have tried these methods
        * creating dynamic stored procs. for tables that are part of
        the load. The stored proc. finds a row based on prim.key.
        and then performs an insert/update. The prim keys in all
        tables are indexed.
        * using bcp for inserting rows into tables that do not
        have triggers.
Has anyone tried this? If so, what rates have you attained?
TIA,
manju



Tue, 29 Feb 2000 03:00:00 GMT
 Loading data into a large volume database

Quote:

> Hello
>         Does anyone have suggestions to increase the rate of loading
> data into a large volume database(>10M rows) from flat files.
> The files may contain data for multiple tables, and also updates and
> inserts. We have tried these methods
>         * creating dynamic stored procs. for tables that are part of
>         the load. The stored proc. finds a row based on prim.key.
>         and then performs an insert/update. The prim keys in all
>         tables are indexed.
>         * using bcp for inserting rows into tables that do not
>         have triggers.
> Has anyone tried this? If so, what rates have you attained?
> TIA,
> manju


Platinum technology (www.platinum.com) offers a set of fast utilities
(FastLoad and FastUnload) that greatly increase the throughput of
loading and unloading tables.  FastLoad is generally rated between 2x -
3x faster than bcp when loading tables.  It does this by bypassing the
Sybase kernel and writing directly to the data device, which also
eliminates logging.  Indexes are also built in a single pass instead of
multiple passes with BCP.  The current release is limited to loading
entire tables, i.e., you need to start with an empty table.  FastUnload
also bypasses the kernel and also allows selective unloading (e.g., bcp
with a where clause) and allows creation of multiple output files.

Kyle Hachey,
Platinum technology



Tue, 29 Feb 2000 03:00:00 GMT
 Loading data into a large volume database

Quote:

> Hello
>         Does anyone have suggestions to increase the rate of loading
> data into a large volume database(>10M rows) from flat files.

        It helps when using bcp to be able to load in native format
        rather than ascii, but this isn't always practical.

Quote:
> The files may contain data for multiple tables, and also updates and
> inserts. We have tried these methods

        This is a grey area. You may want to look at splitting
        things up and loading some data with bcp and using sprocs
        to do the updates etc.

Quote:
>         * creating dynamic stored procs. for tables that are part of
>         the load. The stored proc. finds a row based on prim.key.
>         and then performs an insert/update. The prim keys in all
>         tables are indexed.
>         * using bcp for inserting rows into tables that do not
>         have triggers.

        There are a lot of tunable parameters you can try with
        bcp (network bandwidth and packet sizes, OAM and IAM
        grabs, etc). Definitely bcp would be faster than sprocs.
        Indexes are a problematic with bcp, however.

-am



Sat, 04 Mar 2000 03:00:00 GMT
 Loading data into a large volume database

Quote:

> Platinum technology (www.platinum.com) offers a set of fast utilities
> (FastLoad and FastUnload) that greatly increase the throughput of
> loading and unloading tables.  FastLoad is generally rated between 2x -
> 3x faster than bcp when loading tables.

        How about unloading?

Quote:
> It does this by bypassing the
> Sybase kernel and writing directly to the data device, which also
> eliminates logging.

        Sounds a little dicey? Can this be done with other users active?

Quote:
> Indexes are also built in a single pass instead of
> multiple passes with BCP.

        I don't think bcp builds indexes (at least, not the last time
        I tried it).

Quote:
>  The current release is limited to loading
> entire tables, i.e., you need to start with an empty table.

        This seems a bit restrictive.

Quote:
> FastUnload
> also bypasses the kernel and also allows selective unloading (e.g., bcp
> with a where clause) and allows creation of multiple output files.

        At least this option is reasonably flexible.

-am



Sat, 04 Mar 2000 03:00:00 GMT
 
 [ 4 post ] 

 Relevant Pages 

1. Moving Large Volumes of Data between 2 Oracle Databases

2. Suitable database for large volumes of data

3. Attachable subscriptions, merge replicaton, and large data volume

4. Performance with Large Volumes of Data

5. Performance with Large Volumes of Data

6. Ann: Sorting large data volumes with complex criteria

7. Deleting Large Volumes of Data

8. Extracting large volumes of data from oracle to flat files - Poor Performance

9. Question: copying large volume of Image data to second table

10. Large volume of data display!

11. Paradox with large data volumes

12. Loading large data sets using INSERT & OSQL


 
Powered by phpBB® Forum Software