Subject: | Re: Best way to Add from Paradox Table to AWS Postgres Table
| Date: | Mon, 11 May 2020 08:02:49 -0500
| From: | Mark Bannister <markRemove@THISinjection-moldings.com>
| Newsgroups: | pnews.paradox-client_server
|
I've been playing with this and Tom is right. Keep the BDE out as much
as possible. For one time updates the CSV import works. Tcursors work
but are slow. Queries work and are quick.
Some things I've done that work fairly quickly:
1.) Send the data in an insert/update or upsert style query. Use only
the fields you are addressing.
Update queries: https://www.postgresql.org/docs/current/sql-update.html
Upsert - insert with "ON CONFLICT" statement:
https://www.postgresql.org/docs/12/sql-insert.html
Fields you are not working with ignore, just leave them out of the field
list of the query. Fields that you are sending "blank" data to send the
keyword NULL (not in quotes).
2.) send data as a function parameter such that Postgresql( PG) parses
the data in a function. I actually sent it to function that creates a
temporary table in PG and then parses the data appropriately to the
proper table(s).
Queries have a limited length coming through Paradox/BDE. I imagine is
varies with systems but my seems to be around 80K characters and then it
just gets chopped off. Just run multiple queries in this case.
Also mismatched single and double quotes in strings in the query flummox
the BDE. Even with pass through SQL the BDE obviously tries to parse
the query. See my post earlier and use escapes:
news://pnews.thdebcommunity.com:119/5eb1d654$1@pnews.thedbcommunity.com
On 5/10/2020 2:38 AM, Robert MacMillan wrote:
> So adding works just fine using a standard add. Just like in Liz's
> question from years ago. It is pretty slow though so I am rapidly going
> to run into performance problems.
>
> I am wondering whether a scan reading each record into a dynamic array
> then inserting a blank record at the other end and then copyfromarray
> will work better and faster?
>
> Also if one uses this method does it also mean that as long as the field
> names are identical then the order of fields in the two tables can be
> different?
>
> And supplementary question. Does that also mean that as long as every
> field is in the AWS table then that table could contain additional
> fields that the rows will fill in correctly and fields that are not in
> the Paradox Table but are in the AWS Table will be left blank? I presume
> if the AWS Postgresql Table has fields that are not allowed to be null
> that it wont work?
|