asterisk2billing.org
http://forum.asterisk2billing.org/

ratecard page very slow
http://forum.asterisk2billing.org/viewtopic.php?f=34&t=7105		 Page 1 of 1
Author:  bifa [ Mon Feb 15, 2010 4:26 pm ]
Post subject:  ratecard page very slow
Hello,
I had A2billing 1.3.3 and i've just updated in version 1.5.1.
Everything is ok except one thing: the page ratecard.
This one is very very slow, the page appears in maybe 2 minutes.

I searched in the database and i found this page request the view cc_callplan_lcr. This view is created with table cc_ratecard and cc_prefix.
With 18000 entries in table cc_prefix, the request in cc_callplan_lcr is very slow (2 minutes to display the page).
With 283 entries in table cc_prefix, no problem the page ratecard appears in 1 second.

Am i the only one to have this problem ?
How many entries do you have in cc_prefix table ?
Isthere a fix for my problem ?

Thank you !
Author:  iconsa [ Wed Feb 17, 2010 1:07 pm ]
Post subject:  Re: ratecard page very slow
Hi

NO I was having the same issue.. Question: Are you using IE? With IE I was getting Error: Failed to open file.
Try Firefox it seems to work better for uploading the rate cards. Same rate cards cvs file uploaded correctly with firefox.

Marcias
Author:  bifa [ Wed Feb 17, 2010 3:23 pm ]
Post subject:  Re: ratecard page very slow
Hello Marcias, thanks for your answer.
But my problem is not when i want to generate the cvs file to create the ratecard table.

My problem appears, when i want to go in a2billing application for customer, and i click on "ratecard" in the menu.
The page appears 2 minutes later.

I saw the problem is here when i have many entries in cc_ratecard table (21,204 entries) and cc_prefix table (18,694 entries).
Author:  bifa [ Thu Feb 18, 2010 10:21 am ]
Post subject:  Re: ratecard page very slow
I think there is a problem in the request called to display ratecards. I think there is no limit and the request is done on all the entire view.
But i can't find this in the code. Any help ?
Author:  bifa [ Fri Feb 19, 2010 8:25 am ]
Post subject:  Re: ratecard page very slow
I updated in version 1.6.0, and same problem appears again.
Author:  gromm [ Wed Jun 20, 2012 5:25 pm ]
Post subject:  Re: ratecard page very slow - now with more detail!
I'm resurrecting this thread because I've done some research into why and how this is happening. On our system, the process is unbearably slow - it takes more than 8 minutes to do the database query, and this is often further exacerbated by either A2Billing's logout timer, or the fact that anyone who actually tries this goes off to play Solitaire while they wait.

I found that the problem was with the database query being run when initially requesting the ratecard page. In MySQL I ran the following query while I was forced to wait for the new page:

Code:
SHOW FULL PROCESSLIST;


which told me that the following query was running:

Code:
SELECT SQL_CALC_FOUND_ROWS cc_prefix.destination, cc_ratecard.dialprefix, cc_ratecard.buyrate, cc_ratecard.rateinitial, cc_ratecard.startdate, cc_ratecard.stopdate, cc_ratecard.initblock, cc_ratecard.connectcharge, cc_ratecard.id_trunk , cc_ratecard.idtariffplan , cc_ratecard.id FROM cc_ratecard LEFT JOIN cc_prefix ON prefix=cc_ratecard.destination ORDER BY  dialprefix ASC LIMIT 0,10;


Further research revealed that left joins are particularly expensive, and the time it takes to do them really adds up when doing them on thousands or tens of thousands of rows (we have about 40,000 rows in our ratecard table). Therefore, it is important to reduce the number of rows that we're doing right joins on. It's not feasible to reduce the actual size of the ratecard table, since allowing our customers to dial anywhere in the world is our business plan. And clearly, setting a limit on the results returned isn't doing anyone any good. So after tinkering a bit, I found that the best way to reduce the processing time in this query is to add a WHERE clause to narrow the results from the SELECT statement.

In the MySQL command line, I obtained excellent results by using some kind of default dialprefix from the cc_ratecard table. After all, the whole point of the initial rates page isn't to display all of the rates, but to ask the user for a rate to display. It would probably be an even better idea to display no results at all, and wait for the user to search for a dial prefix.

This is definitely a bug. I would post a patch if I had either the time or the expertise, but I suspect one of the regular developers on the project would be able to do it *much* faster than I could in any case. FYI, issuing a patch would be much appreciated, since our version of A2Billing has been customized somewhat, making upgrading to a new version painful at best.
Author:  gromm [ Wed Jun 20, 2012 6:16 pm ]
Post subject:  Re: ratecard page very slow
Actually, there's an easy quick and dirty way of fixing this:

  • edit $WEBROOT/a2billing/admin/Public/templates/default/main.tpl
  • Find the line ending with the text "echo gettext("Rates"). This line has the html link that goes to the rates page.
  • Remove the text "A2B_entity_def_ratecard.php?atmenu=ratecard&section=6" and replace it with "A2B_entity_def_ratecard.php?atmenu=ratecard&section=6&filterprefix=01120&filterfield=dialprefix" This replacement link searches for the dialprefix 01120, which is the country code for Egypt. You can replace "01120" with any dialprefix you like that may be more relevant to you.
  • Save the file and test.

Note: On our dev server, we needed to make $WEBROOT/a2billing/templates_c writeable for the userid running the web daemon after making this change, but this wasn't the case on our production server. You may get an error from A2Billing after making this change, complaining about permissions on this directory, but the solution is fairly simple.
Page 1 of 1 All times are UTC
Powered by phpBB © 2000, 2002, 2005, 2007 phpBB Group
http://www.phpbb.com/