Postgresql browses with incremental locators and no filter


#1

If you do not specify a filter or range on a browse using postgresql you may find that your browses are very slow. This is because postgresql does not have a end range and will retrieve all the data from the starting point of your browse to the end of the file. There are two solutions to this. The first option is to use UseDeclareFetch=1 in your connection string. This will limit the number of records returned to 100 when using cursors to fetch data. This works but has its down side. All other operations (mainly fetches) are much slower. The other option is to add the LIMIT statement to your SQL filter. The problem here is that LIMIT is after the order statement. You can work around this limitation by using the PROP:SQLOrder property. The trick is to add a field to the order that wont change the result set and then add the LIMIT statement after this field. The following is an example: BRW1::View:Browse{PROP:SQLOrder} = '+ A.systemid LIMIT 20'. The ‘+’ sign will force the SQLOrder statement to be added to any other order statements followed by a comma and then the field you added in the SQLOrder statement. The ‘A’ before the systemid is the Alias used by the ODBC driver and is required since you are adding raw sql code. The LIMIT 20 is added as a side effect of this statement.