MySQL Query or Stored Procedure to efficiently rate CDR's

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:

Call rates:

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.

Habilidades: MySQL

Veja mais: finding match, challenge s, stored, mysql query, cdr, call record, database procedure, mysql procedure, finding calls, transform query stored procedure, stored procedure performance mysql, takes challenge, destination number cdr, mysql cdr rating, cdr records, multiple table rate, mysql rating, mysql rating table, php email message stored mysql, script query records mysql send emails, write query update mysql, send query remote mysql, convert sql stored procedure oracle stored procedures, searching query php mysql, perl parse html stored mysql

Acerca do Empregador:
( 0 comentários ) New Zealand

ID do Projeto: #6810306

15 freelancers estão ofertando em média $1182 para esse trabalho


1. Vollks Australian Online Store In these days we are complete re-designing and reimplementing an online store for our Australian client in which we are using X-Cart latest stable release to implement his all require Mais

$1247 NZD in 12 dias
(27 Comentários)

Hi- I'd like to work on your project. By your description, it seems that your existing setup is amenable to optimization. It's quite probable that the 80x speedup you are aiming for can be achieved. You are correct tha Mais

$800 NZD in 2 dias
(87 Comentários)

Hello, I have read your post and understood your requirement. I have great experience in handling Wordpress /Magento/Joomla/Drupal/PHP/Java/node.js/AngularJS Python/Django/Javascript/MySQL/ HTML5/CSS3/ Mais

$1546 NZD in 15 dias
(20 Comentários)

Hello. I am a Mysql Expert. I can work for you. Please look my reviews and send me a message. Thank you.

$1200 NZD in 20 dias
(45 Comentários)

Hello, Scope of work includes - - Optimizing MySql query/stored procedure to optimize the performance and bring down the call rate 0.1s Let's take it to the next step and start on this ASAP. Thanks, Techmart Mais

$1060 NZD in 15 dias
(43 Comentários)

Dear Sir, This is Sikander. I am having 12+ years of experience in software development and I am ready to start this job. Please send me the project specification so I can give you exact time and budget for this pro Mais

$1444 NZD in 20 dias
(7 Comentários)

Dear sir, Our primary expertise lies in PHP (5.0), Drupal, and JAVA/J2EE, HTML, Adobe Flash, Android development,iphone development, CSS, JQuery, mySQL, .Net, CI, Cake PHP and Ajax. We are also experts in using vario Mais

$1317 NZD in 25 dias
(6 Comentários)

Hey, I am an expert MySQL DBA and an optimization expert. I have a core knowledge on MySQL and its internals. I have very good command over MySQL query optimization too. I am a MySQL tech speaker and a blogger too ( Mais

$780 NZD em 1 dia
(10 Comentários)

Hi, I am DB developer with good DB/query tuning and stored procedure development experience. I propose to work with you in improving CDR rating process. You requirement seems 80 times speed up. (looking to imp Mais

$750 NZD in 7 dias
(2 Comentários)

Hello, I am IT engineer with European diploma since 2005 Certified as Technical specialist in Microsoft SQL Server databases, i have more than 8 years of experience working around databases and SQL development arou Mais

$777 NZD in 6 dias
(2 Comentários)

I have a background as System admin and DBA, focusing almost exclusively on MySQL in the last 4 years, building highly scalable environments where performance and high availability are the main focus. I am always re Mais

$2000 NZD in 20 dias
(1 Comentário)

10 years+ Experience in ---Programming----Full ERP---Developing Games----Android App---IPhone App---Mac App-- Inventory & Account Software---Online Store---Website Development(Ruby on Rails, Durpal, HTML, mysql,java, j Mais

$1184 NZD in 20 dias
(1 Comentário)

I have a lot of experience in databases optimization. During last project I took part in (for logistics company Mearsk) we have optimized their report generating from over 8 hours to 2.5 hour. It wasn't done on MySQL Mais

$999 NZD in 7 dias
(0 Comentários)

We have years of experience working with database administration, and more specifically, in MySQL. We have developed detailed matching queries/procedures, similar to the one you are asking for, within our time worki Mais

$1480 NZD in 4 dias
(0 Comentários)

Hello, I am a .Net programmer having 8+ years of experience in Angular.js, Javascript, RESTful, Software Architecture,ASP.NET, jQuery / Prototype, Microsoft SQL Server, MVC and 100% confident to take up the task an Mais

$1444 NZD in 20 dias
(0 Comentários)

Hi, I'm a professional DataBase Administrator. I'm used to work for big companies like french goverment and i can handle your optimization needs efficiently. I'll ask for a first payment of 80$ for the theoretical Mais

$750 NZD in 4 dias
(0 Comentários)

Good afternoon Interesting project and definitely something I can help with. Your brief is clear and I understand what you're wanting to achieve, but it is difficult to guarantee the 0.01s benchmark without more inf Mais

$1333 NZD in 5 dias
(0 Comentários)