Using PostgreSQL
On a browse of a PG view, the sort of one column is 4x slower than sorting by other columns. If I use trace to extract the select statement sent by Clarion for various sort columns and paste into my PG management tool, all select statements return the ~100 rows within ms of each other.
Any ideas as to what Clarion ODBC (I assume) could be doing that would increase re-sort time from less than 1 sec to over 4 sec?
I had a significant improvement when I changed my command line there in the dictionary, adding as a forum colleague gave me an example, here it is:
@seanh
/BUSYHANDLING=2 /ISOLATIONLEVEL=2 /JOINTYPE='ODBC 3.0' /NESTING=TRUE
Hi Douglas,
I think you must be overlooking something in the trace file. If Clarion is doing: here’s the query, here’s a bunch of nexts to get the data, then yes, the time to get the data can vary a bit based on the order by (indexed/not indexed) but you should see that same difference in PgAdmin too.
That said, even 1 second is very slow for 100 records. Have you looked at buffer?
It’s not possible that in your slow case Clarion, for whatever reason decided it needed to do the joining itself (which you see in the trace files, with a bunch of single row requests)?
Page loaded/File loaded? I know this used to be the page-loaded strategy, not sure if it still is…if you show 20 rows in your browse and from your starting position the query only returns, say, 18 rows up to the end of the file, then Clarion will create a new query, starting from the end of the file and try to get its 20 records moving backwards. So you might expect the elapsed time to filling your browse to be about double in that case (two queries).
Jon
Jon - Thanks for the thoughts. The browse is file loaded and Clarion does not know about anything else. The PG view itself is not simple as it includes some aggregate subqueries (ie string_agg, sum). Other than the select with the appropriate sort itself, what else might I be overlooking in the trace that might provide a clue.
Only other things that come to mind:
case-insensitive sorting (i.e. clarion will want to sort on a case-insensitivised column even if there is no index for that)
I would expect sorting on aggregates (sum and string_agg) to be slower, because all the group by stuff needs to be done before sorting can happen, but you should see that slowness using pgadmin too.
If clarion thinks its a single file obviously clint-side joins are not going to be a problem.
Date sorting or filtering using string representations?