[Freeswitch-users] Mod LCR SQL sub-query for better routing
Victor Chukalovskiy
victor.chukalovskiy at gmail.com
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
Join us at ClueCon 2013 Aug 6-8, 2013
More information about the FreeSWITCH-users
mailing list