Discussions

Performance and scalability: Handeling large data sets.

Bookmark and Share Get thread feed

  1. Handeling large data sets. (2 messages)

    Hi I'm currently looking into updating one of my applications which collects a set of data from the DB, formats it to a positional based file and sends it to a 3rd party for processing.

    Currently the application calls a stored procedure which retrieves the data from a table and formats it as required. The stored procedure then returns a recordset where each row represents a line in the file. The application then streams the recordset to file and submits it to the 3rd party by ftp.

    Is this DB suicide? Is there a better way to process large data to produce my file?

    Thanks
    Reply to this

    Threaded Messages (2)

  2. More information required.[ Go to top ]

    what database are you using? are you using straight jdbc, a object/relational mapper? what kinda load is on it?? is this just a one time hit, or how many hits/hr is it occuring??

    based on these scenarios your mileage will vary based on implementation details.

    www.binaryfrost.com
    Hi I'm currently looking into updating one of my applications which collects a set of data from the DB, formats it to a positional based file and sends it to a 3rd party for processing.Currently the application calls a stored procedure which retrieves the data from a table and formats it as required. The stored procedure then returns a recordset where each row represents a line in the file. The application then streams the recordset to file and submits it to the 3rd party by ftp.Is this DB suicide? Is there a better way to process large data to produce my file?Thanks
    Reply to this Reply to original

  3. Re: Handling large data sets.[ Go to top ]

    If it ain't broke, don't fix it
    What throughput is your current system able to achieve ?
    Requests/second (or minute)
    and Rows/second (or minute)

    And what throughput do you want to achieve ?

    And have you done any stress test to see what it can possibly achieve.

    There are several things you can do to improve performance, but they'd be based upon where your bottleneck is.
    A few being ...
    batch your selects (don't retrieve one row at a time)
    tune the SQL queries
    Mimimize temporary object creation when you build the record in Java rather than the stored procedure

    A simple question ... Why are you building the record in a stored procedure as opposed to straight SQL (where you can concatenate the columns and/or expressions)

    Regards,

    Virag

    http://www.auptyma.com
    Reply to this Reply to original