200K records suggests a rethink of the query structure. I was expecting a few hundred records.
Would the 200K records be for members of perhaps a few hundred organisation units with each unit appearing many times?
If so, create a list of the full unit name using a SELECT DISTINCT query. Join this query to the main table on the Unit field. Apply either the function or the query alternative to the distinct list.
This way the heavyweight processing is applied just once for each different unit name. Then the outcome is propogated to the main table via the join. This will be much faster.
Would the 200K records be for members of perhaps a few hundred organisation units with each unit appearing many times?
If so, create a list of the full unit name using a SELECT DISTINCT query. Join this query to the main table on the Unit field. Apply either the function or the query alternative to the distinct list.
This way the heavyweight processing is applied just once for each different unit name. Then the outcome is propogated to the main table via the join. This will be much faster.
