Tips to optimize Clarion 9 + ODBC PostgreSQL

questions tips
1

Hello everyone, I’m converting a program from C5 to C9.1, which we already ran some programs, however, our main focus is to change from TPS to ODBC, and we opted for PostgreSQL, but I would like to receive opinions, such as, for example, Is MSSQL a MUCH BETTER alternative?
Another point, I would like tips to optimize table readings, they slow down browses when opening with ODBC

2

I would say Postgres is a much better alternative to MSSQL :slight_smile: There’s a discussion out this in the newsgroups at the moment.

Tips for postgres:

  • Use Clarion 11.1
  • Add to your connect string: ;usedeclarefetch=1;Fetch=5000;
  • Where possible filter the browse to begin with to reduce the result set.
  • Set transform_null_equals = True in the conf.
1 Like
3

The problem is that only one table has more than 5000 records… could it not be because I use relationships in the Dictionary?

4

There are a few things that can cause browses to be slow to open:
1: ordering on things you don’t have an index on. A common one is sorting on string fields. Clarion will generally try to do this in a case-insensitive way, I think by doing an UPPER() on the column. If your index in Postgres is case-insensitive that requires the sever to grab the whole data set and sort it in memory before returning anything.
2: Not having indexes on the parent side of a join field
3: Doing something that causes Clarion to do a client-side join. In the past Clarion has had some weird ideas about what databases can do. For example, if you ended up with a query with more than one left join Clarion would decide it was beyond the server and would get data row-by-row and join it on the client.
You need to put tracing on and see what is actually being sent as a query, then test that query using PgAdmin to see if that shows you the problem (#3 there would show a huge # of single row queries being sent to the database).

5

You will never look back. Ask questions here, lots of PG experience to respond.

1 Like
6

What the best pratice to use PSQL with Clarion?

7

a) For more complex views, create in PG and add to your dct as you would any other file.
b) Have a mindset of putting as much code in PG as possible. Clarion displays your data and allows user entry/edit. Processing and validity checks are in PG.
c) Learn to trace what SQL statement Clarion is passing to PG.
d) Find an SQL management tool for PG that you really like. PG Admin might not be your favorite.

1 Like
9

Clarion when creating the table automatically, it does not define any relationship, why? it only creates INDEX

10

That’s understandable. Clarion works on table by table so can’t be sure if other table exist.
If you want more you need to use a create script yourself.

I do that anyway because Clarion does not always create tables quite the way I want, and I have way more control.

1 Like
11

With PG it is not necessary to maintain relationships in the Clarion dct. I have left the ones that were established during my tps days, but have not bothered to keep up as PG tables and views were added.

1 Like
12

To add my two cents, here is how I set up the owner name for PG tables in the DCT. It might be different than most others, but it works for us.

GLO:OwnerName = 'DRIVER=PostgreSQL Unicode' & |
					';SERVER=' & clip (GLO:ServerName) & |
					';PORT=' & GLO:PortNo & |
					';DATABASE=' & clip (GLO:DatabaseName) & |
					';UID=' & clip (GLO:UserID) & |
					';PWD=' & clip (GLO:Password) & |
					';SSLMODE= prefer;LFConversion=0;UseDeclareFetch=1;RowVersioning=0;UseServerSidePrepare=0;UseUnicode=true'