[Freeswitch-users] Mod LCR SQL sub-query for better routing

Tue Dec 17 06:53:52 MSK 2013

Greetings! I was away for a couple days, let me clarify now:

-I did not use default mod_lcr params for a while, so I don't know what 
exactly reorder_by_rate does.

-I use custom_sql, and so I ran into the issues that single 
SELECT...FROM...WHERE...JOIN...ORDER BY query shown in the WiKi is not 
working well.
Seems to be the same problem that Ken and DP mentioned: one needs to 
select only longest prefix for each carrier, and then order the results 
by rate. Below is my example:
Prefix    Rate    Carrier
1718    0.003    Carrier 1
1    0.001    Carrier 1
1718    0.002    Carrier 2
1416    0.004    Carrier 2

ORDER BY prefix, rate will give Carrier 2 for dest 1416XXXXXXX, while 
the best route is Carrier 1 - Bad
ORDER BY rate, prefix will give Carrier 1 for dest 1718XXXXXXX, while 
the best route is Carrier 2 - Also bad

That's why I did the sub-query I showed in the WiKi. Is this problem 
already solved with mod_lcr and custom SQL? Did not see any mention of 
the solution.

On 13-12-16 07:02 PM, Ken Rice wrote:
> Re: [Freeswitch-users] Mod LCR SQL sub-query for better routing You 
> should never simply re-order just by rate... When you select the rate 
> for the route you _must_select the longest prefix... You can just fall 
> back to 1 since it matches 1876 also... This is not valid just for 
> non-US areas in NANPA but for globally... Its not uncommon to see 
> something like +44 @ 0.0095 (UK London Landline)  then see something 
> like +447938 @ 0.0150 (O2 Mobile) if you tried to route this off the 
> cheapest prefix you would always be doing it wrong...
>
> And don't even get started with ignoring things like LRN in the US 
> with a ratedeck that has per NPA-NXX(-X) pricing while respecting LRN
>
>
> On 12/16/13 5:36 PM, "DP ." <hi-tecc at hotmail.com> wrote:
>
>     I agree. This definitely sounds like he simply needed the
>     "reorder_by_rate" param. It will reorder the initial sql results
>     strictly by rate:
>
>       * reorder_by_rate - Forces the LCR module to re-order the query
>         strictly on rate basis. By default this is turned off, but
>         enabling this will always prefer rate over anything else.
>
>
>     Beware this may have an adverse effect! I initially had this
>     turned on then quickly realized it would sometimes try to route
>     ALL calls by the lowest rate found.
>
>     Ex: flowroute lists all calls for the US with a default NPA of "1"
>     at .0098. Now a user trying to call Jamaica with "1876" at a rate
>     of 0.19 (or whatever) will get both flowroute rates returned. The
>     reorder by rate will assume 0.0098 is a valid rate since it will
>     now be the "cheapest" in the list and send the call along its way
>     to flowroute, whom will now bill you at 0.19. Now if you have
>     another carrier in your list with 1876 at 0.15 you can see why
>     this would be a problem.
>
>     In this case you will always want the longest matched NPANXX rate.
>
>     ------------------------------------------------------------------------
>     Date: Fri, 13 Dec 2013 15:58:54 -0500
>     From: intralanman at freeswitch.org
>     To: freeswitch-users at lists.freeswitch.org
>     Subject: Re: [Freeswitch-users] Mod LCR SQL sub-query for better
>     routing
>
>
>     On 12/12/2013 02:42 PM, Victor Chukalovskiy wrote:
>
>
>
>         Hello,
>
>         For those interested, I added a piece to mod lcr wiki. It
>         makes sorting
>         / routing logic better than default logic:
>
>         http://wiki.freeswitch.org/wiki/Mod_lcr#Custom_SQL_with_sub-query_-_for_real-life_ratesheet_complexities
>
>         Why it helps:
>
>         Rates rates can often be given both on per-NPA or per-NPANXX
>         level
>         depending on the carrier and on the NPA. Moreover, some
>         carriers may
>         have NPANXX rate lower than the corresponding NPA rate, while
>         others
>         will have it inverse. Neither simple ORDER BY rate, prefix;
>         nor ORDER BY
>         prefix, rate; give the truly cheapest route. The LCR logic
>         should be
>         two-step process to accommodate this.
>
>         Cheers,
>         -Victor
>
>     Unless I misunderstand what you're saying, this is what the
>     reorder_by_rate param does.
>
>      You'll always want to pick the longest digit match per carrier.
>      Then you probably want to grab the cheapest overall rate of the
>     matches you got back.
>
>      -Ray
>
>
>     _________________________________________________________________________
>     Professional FreeSWITCH Consulting Services:
>     consulting at freeswitch.org http://www.freeswitchsolutions.com
>     
>      Official FreeSWITCH Sites
>     http://www.freeswitch.org http://wiki.freeswitch.org
>     http://www.cluecon.com FreeSWITCH-users mailing list
>     FreeSWITCH-users at lists.freeswitch.org
>     http://lists.freeswitch.org/mailman/listinfo/freeswitch-users
>     UNSUBSCRIBE:http://lists.freeswitch.org/mailman/options/freeswitch-users
>     http://www.freeswitch.org
>
>
>     ------------------------------------------------------------------------
>     _________________________________________________________________________
>     Professional FreeSWITCH Consulting Services:
>     consulting at freeswitch.org
>     http://www.freeswitchsolutions.com
>
>     
>     
>
>     Official FreeSWITCH Sites
>     http://www.freeswitch.org
>     http://wiki.freeswitch.org
>     http://www.cluecon.com
>
>     FreeSWITCH-users mailing list
>     FreeSWITCH-users at lists.freeswitch.org
>     http://lists.freeswitch.org/mailman/listinfo/freeswitch-users
>     UNSUBSCRIBE:http://lists.freeswitch.org/mailman/options/freeswitch-users
>     http://www.freeswitch.org
>
>
> -- 
> Ken
> _http://www.FreeSWITCH.org
> http://www.ClueCon.com
> http://www.OSTAG.org
> _irc.freenode.net #freeswitch
> Twitter: @FreeSWITCH
>
>
>
> _________________________________________________________________________
> Professional FreeSWITCH Consulting Services:
> consulting at freeswitch.org
> http://www.freeswitchsolutions.com
>
> 
> 
>
> Official FreeSWITCH Sites
> http://www.freeswitch.org
> http://wiki.freeswitch.org
> http://www.cluecon.com
>
> FreeSWITCH-users mailing list
> FreeSWITCH-users at lists.freeswitch.org
> http://lists.freeswitch.org/mailman/listinfo/freeswitch-users
> UNSUBSCRIBE:http://lists.freeswitch.org/mailman/options/freeswitch-users
> http://www.freeswitch.org

-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.freeswitch.org/pipermail/freeswitch-users/attachments/20131216/5a960b68/attachment.html 