The fast way to save RS(ResultSet) into a file

  1. 6 weeks ago

    changwon l

    Jul 14 Pre-Release Testers

    My application receives a query result from Oracle and uses PreparedStatement to issue INSERT statements(into PostgreSQL).
    It works but in case the number of result is over 3000 rows then it slows.

    So, I am considering to save the result into a local file and runs PostgreSQL's direct load feature to speed it up.

    Database resultSet: rs.
    I know how to create a file and iterates the result set until EOF.
    But I want to check if there is any efficient method to save the ResultSet into a file fast.

    Do you have any idea on it?

  2. changwon l

    Jul 15 Pre-Release Testers

    Looping rs and writing each record into a file, then run direct loading to PostgreSQL.
    It looks better.

    With 4000 rows, PreparedStatement takes 8 seconds, but the way of making a file just takes 2 seconds.

    Thanks.

  3. Jean-Yves P

    Jul 15 Pre-Release Testers, Xojo Pro Europe (France, Besancon)

    yes the key is to reduce the most possible every traffic between client and server.
    so better send one file with 4000 inserts queries, than 4000 requests with one insert in each.

  4. changwon l

    Jul 15 Pre-Release Testers

    Yes, I believe so.

  5. 5 weeks ago

    luciano m

    Jul 15 Pre-Release Testers, Xojo Pro

    This is the code that I'm using with Postgresql.

    Use Truncate if you want to clear the table before copy.

      dim sql As String
      
      sql ="TRUNCATE TABLE ""tblCommesse"""
      rdb.SQLExecute(sql)
      
      rdb.SQLExecute("COPY ""tblCommesse"" FROM '/Users/lto_slave/Library/Application Support/Postgres/var-9.6/file.csv' DELIMITER ';' ;")
      
      if rdb.Error then
        App.ElapsedTime = rdb.ErrorMessage
        

    The file.csv must be in var-9.6 in order to be visible to postgresql.

  6. changwon l

    Jul 16 Pre-Release Testers

    Interesting.
    file.csv can be in a remote server or just in local file system?

  7. luciano m

    Jul 16 Pre-Release Testers, Xojo Pro
    Edited 5 weeks ago by luciano m

    remote or local, doesn't matter, but must be in postgresql directory.
    With a copy I can insert 66.000 records in 2/3 second.

  8. changwon l

    Jul 17 Pre-Release Testers

    Wonderful. I would like to test it soon.

or Sign Up to reply!