We currently have a query to match call records to the appropriate call rates which takes ~[url removed, login to view] per record to complete the rating. This is problematic as we're needing to rate thousands of calls per day (and the number is steadily increasing month on month) and it's locking up the database while it's happening, as well as using a temp table being stored on disk which is starting to use too much space.
I'd like the query we have optimised or completely re-written.
The biggest challenge in this is keeping it performant while finding the correct match. There may be multiple call rates which could match a given calling destination, and we need to ensure that the match that returns is the longest possible one. e.g:
64 - $0.035
649 - $0.027
64923 - $0.065
Calls to 64211234321 should be rated at $0.035.
Calls to 6492121212 should be rated at $0.027.
Calls to 6492321212 should be rated at $0.065.
Depending on the call plan the user is associated with, there are between 15,000 and 100,000 call rates that need to be compared against to find the appropriate match.
A part of the solution may be that you recommend additional indexes to be applied to various tables if that is required.
This project will only be considered successful if the performance of the call rating can be brought down to less than [url removed, login to view] per call record.