Hi guys,
I've had to drop it to pick up something else but not before changing it to Jeff's intermediate solution with regards the WHILE loop. This made a difference. We also played around with a CLR version of the function. Current times are ...
CLR - avg 1.04 mins to process 3700 records from the main table
SQL - avg 1.09 mins to do the same.
There is an awful lot of associated records on an awful lot of tables and this is way faster than they currently have in production.
We are looking at reducing the number of calls to this function that we have to make and I'm hoping that should bring it down to around 40secs. If we get chance then we might look at the tally tables.
I'll let you guys know. Your input has been very much appreciated.
Mark