Results 1 to 11 of 11

Thread: Bulk Fetch C++

  1. #1
    Join Date
    Jan 2010
    Posts
    21

    Unanswered: Bulk Fetch C++

    Hi,

    I am trying to fetch 1 million rows from the remote DB2 server (HP-UX) on my windows platform.

    I want to know what is the fastest way to fetch bulk data from the server.

    I tried using ADO and was quite disappointed with the time it took.

    Regards.

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Other than reporting (where database performance has never been "the weakest link" in the process), I've never seen a case where it made sense to fetch a million rows from a database. I'm almost certain that you need to review your design, it probably needs some re-thinking.

    Without knowing your client/server/network in detail, there is little if any chance that I'd guess the fastest way to get rows between them. ADO is likely to be the fastest technology, but beneath the ADO using OLE-DB can be more than an order of magnitude faster than using ODBC, and often two orders of magnitude faster than JDBC.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    how about unload/export?

  4. #4
    Join Date
    Jan 2009
    Location
    Zoetermeer, Holland
    Posts
    746
    how about showing your db2cli.ini?
    Somewhere between " too small" and " too large" lies the size that is just right.
    - Scott Hayes

  5. #5
    Join Date
    Jan 2010
    Posts
    21
    Here is the ini file: -


    ; Comment lines start with a semi-colon.

    [tstcli1x]
    uid=userid
    pwd=password
    autocommit=0
    TableType="'TABLE','VIEW','SYSTEM TABLE'"

    [tstcli2x]
    ; Assuming dbalias2 is a database in DB2 for MVS.
    SchemaList="'OWNER1','OWNER2',CURRENT SQLID"

    [MyVeryLongDBALIASName]
    dbalias=dbalias3
    SysSchema=MYSCHEMA

  6. #6
    Join Date
    Jan 2009
    Location
    Zoetermeer, Holland
    Posts
    746
    Quote Originally Posted by Pat Phelan View Post
    more than an order of magnitude faster than using ODBC, and often two orders of magnitude faster than JDBC.-PatP
    Is that a fact or an opinion? Based upon ..... ???
    Somewhere between " too small" and " too large" lies the size that is just right.
    - Scott Hayes

  7. #7
    Join Date
    Jan 2009
    Location
    Zoetermeer, Holland
    Posts
    746
    Quote Originally Posted by salman83 View Post
    Here is the ini file: -
    http://www.dbforums.com/db2/1673376-...equired-2.html
    Somewhere between " too small" and " too large" lies the size that is just right.
    - Scott Hayes

  8. #8
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by dr_te_z View Post
    Is that a fact or an opinion? Based upon ..... ???
    My comment was based on a benchmark that three of us did at a SQL Saturday a year or two ago, comparing the client side performance of different windows based access methods (like ODBC, JDBC, OLE-DB, and native libraries where they were available). Granted that this didn't cover every possible combination, but it did measure and report client performance using a common set of data sources (mostly database servers) with clients running on different versions of windows using at least two different client machines.

    Using the ADO methods (which are highly optimized for the windows platform) and the OLE-DB drivers (again, highly optimized for windows) turned out to be the best performers in every test we ran on every client we tested.

    You can't necessarily extend these results to other platforms, and we didn't test Windows 8 because it wasn't in general release yet when we ran the tests.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  9. #9
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by Pat Phelan View Post
    My comment was based on a benchmark that three of us did at a SQL Saturday
    I guess this implies MS SQL Server, no? For DB2 the CLI driver (which implements ODBC) is the native library, and everything else (OLE-DB, ADO.Net, whatever else except JDBC) builds on top of that, so it's hardly possible it'd perform better than simple ODBC.

    The IBM JDBC driver implements the same transport using pure Java and it usually performs on par with the CLI driver.
    ---
    "It does not work" is not a valid problem statement.

  10. #10
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    No, we benchmarked multiple Windows clients accessing a number of different data sources (most of which were SQL databases of some flavor). SQL Server figured prominently in our tests, but it was only one data source among many.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  11. #11
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by n_i View Post
    so it's hardly possible it'd perform better than simple ODBC.
    As an interesting side note, Microsoft's OLE-DB driver for SQL Server was more than fifteen times faster than their ODBC drivers. This was surprising, but consistent across all of the Windows clients that supported OLE-DB.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •