Information générale
Catégorie:
Base de données, Tables, Vues, Index et syntaxe SQL
Titre:
Any ideas to index/speed this up?
I'm rating phone call records based on 2 tables: call records, and rates.
Here's some data for example
Callrecords.dbf
DialedNo Duration Rate Charge
0115526960000 5mins ?? ??
0115089999999 6mins ?? ??
Rates.dbf
DialCode RatePerMin
01155 .10
0115 .05
Currently I index the rates table on the the length of dial code and on dial code(i.e. len(dialcode)+dialcode ). Then I scan call records for a match (starting with the longest dial code), replace the rate and charge with values calculated from the rates table, and temporarily delete the call record (so it can't match a shorter dial code). Then jump to the next rate, scan the call records again etc. for however many rates I have (often 2000 of them) with big call record files (sometimes 1/4 million records) this can take forever.
Anyone have any ideas to speed this up? Is there some way I could relate these tables on the longest match of DialedNo to DialCode? (If so then I could do all my replacements on a single pass through the CallRecords table)
Thanks so much for any ideas,
Steve
Suivant
Répondre
Voir le fil de ce thread
Voir le fil de ce thread à partir de ce message seulement
Voir tous les messages de ce thread
Voir tous les messages de ce thread à partir de ce message seulement