|Subject:||Re: Postgresql indexes issue with lower function
|Date:||Fri, 13 Mar 2020 12:21:33 +1100
|From:||Tom Krieg <REMOVEtomkCAPITALS@sdassociates.com.au>
Mark, see inline.
On 13/03/2020 1:32 am, Mark Bannister wrote:
> I see that and agree but two questions:
> 1.) I copy the PG results to PRIV. But to display in a form it would be
> easier if I had indexes on the PRIV tables, or I just do sub-query for
> the children separately I guess?
Either. You can index a table that's returned from a query with Opal.
> 2.) I was trying to get to a hybrid stage quickly so that if Microsoft
> forces me off SMB1 I have a viable option. It's going to take a lot of
> work to move all the logic to PG and I'm afraid I will run out of time.
> This also gives me a chance to learn as I go and work on small chunks
> at a time.
Connecting to the database directly means you're relying on the BDE to
do the translation between "Paradox-ese" and SQL. You don't get the full
capabilities of SQL. And pgPSql (the function language) is another 2
> 3.) OK I lied about the number of questions. Do you suggest using
> tcursors on PG server tables at all? They seem to work just fine.
Again,you're relying on the BDE to do the translations and interact with
ODBC. I'd prefer to do it directly.
Connecting directly is like
Tcursor/Tableframe --> BDE --> SQL Commands --> ODBC --> PG Database
Tableframes are worse than tcursors because every move, refresh, unlock,
post, most any action, causes the above to happen. Given that the BDE
SQL generator is inefficient at best compared to a well designed SQL
statement you're taking a performance hit. TCursors are a little better
but if you're doing a switchindex/setrange watch what happens. And
Paradox can't handle GIN indexes (stands for Generalized Inverted Index)
which is used for amongst other things, searching for text or strings in
documents or very large tables.
If you connect directly, you're relying on Paradox/BDE to format the SQL
statements for you based on a 20 year old Interbase model.
How large is your application? (I converted a bus line's timetable,
reservations and scheduling system (fairly large Paradox application) to
SQL Server in about 10 months with a couple of months support. That
included redesigning every form and report.) YMMV but you can avoid
Win10 updates for that long.