On 20.06.2018 o 13:41, Venus Software Operations venussoftop@...
[firebird-support] wrote:
> Hi Thomasz
>
> You have understood me correctly, that is what I want to do and your suggestion
> works thanks.
>
> Please see my reply to SET as the query has slowed down to 10 minutes with your
> code. The fault is in my setup but if have anything to suggest that would be great

Probably an indexing issue. First, make sure TRIM is really needed
(usually it isn't, padding is omitted in string comparison anyway, but
IIRC only right padding - someone correct me please if my memory is
playing tricks on me).
Second, create indices on vwTA.cNameCity01 and tSI.tDt.
Third, if possible, try to get rid of the CASTs (unless tSI.tDt is not a
date field, in which case try to pass your condition values in a form
matching the data in tSI.tDt instead of converting the column to DATE;
e.g. if it's a timestamp, maybe pass '2018-03-01 00:00:00' and
'2018-03-31 23:59:59' and throw the CAST away [pun intended ;)]).
If you really need to transform the data on the fly in WHERE conditions,
you make it virtually impossible for Firebird to use indices to your
advantage.
If nothing of the above helps, send your query execution plan (plus the
record counts of the tables involved and index defs).

cheers
Tomasz

Thanks Tomasz.  the tDt is a datetime field and I am only comparing it with the date literal.  Also, I have changed code to use the literal integer PK instead of the string so the TRIM() is now totally avoided.  See my response to SET with the codes.  Please advise

Kind regards
Bhavbhuti