Could not agree more with Rupa. mod_lcr is an awesome piece of software. Thank you Rupa for that.<br><br>JM<br><br><div class="gmail_quote">On Wed, Oct 14, 2009 at 10:36 AM, Rupa Schomaker <span dir="ltr"><<a href="mailto:rupa@rupa.com">rupa@rupa.com</a>></span> wrote:<br>
<blockquote class="gmail_quote" style="border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;">I would still suggest using mod_lcr for this... If you have any real<br>
volume, use postgresql with the prefix module.<br>
<br>
It also supports IN lists, OR lists, optional quoting (since mysql is<br>
retarded), and custom sql so you can interface with whatever stored<br>
proc or deal with whatever database table you may need to support.<br>
<br>
It also supports multiple profiles (so you can charge different<br>
amounts based on some other criteria like account code) and...<br>
interstate/intrastate/intralata rating. probably other stuff I forgot<br>
to mention...<br>
<br>
Let me just recommend to reuse what already exists and improve on that.<br>
<div><div></div><div class="h5"><br>
On Wed, Oct 14, 2009 at 8:22 AM, Muhammad Shahzad<br>
<<a href="mailto:shaheryarkh@googlemail.com">shaheryarkh@googlemail.com</a>> wrote:<br>
> Oops, you are right, this is what happens when you are doing more then one<br>
> thing at a time, i was writing a db function while replying in this email<br>
> thread and confused LIKE with IN. There is one more mistake in my email if<br>
> any one noticed, that is, string class in STL does not support negative<br>
> length in method substr.<br>
><br>
> Thanks for correction.<br>
><br>
><br>
> On Wed, Oct 14, 2009 at 6:43 PM, Michael Giagnocavo <<a href="mailto:mgg@giagnocavo.net">mgg@giagnocavo.net</a>><br>
> wrote:<br>
>><br>
>> Wouldn’t that be SQL “IN” instead of LIKE?<br>
>><br>
>><br>
>><br>
>> First off, see if you can do this in memory. If it’s just a “rate sheet”<br>
>> list of prefixes + billing, it’s probably not that much data. I’d guess<br>
>> maybe 64-128 bytes per record (in Ruby it’s probably more, maybe 256 bytes?)<br>
>> inside a hashtable. Even so, we’re only talking about 1.5 to 3GB of RAM.<br>
>> Stick it in a nice performing dictionary of some sort and you’re set.<br>
>><br>
>><br>
>><br>
>> Just as a quick test, on one core of a Core2, it takes about 150ms to do<br>
>> 100K lookups against a 1M item hashtable (F#) – that’s just a 1 line loop I<br>
>> tried out with no optimization, doing about 650K/sec. For comparison on the<br>
>> same machine, looping on a simple “SELECT @@VERSION” command only achieved<br>
>> 5000 executions/second on a single thread – that’s just the SQL overhead (to<br>
>> a local SQL instance!).<br>
>><br>
>><br>
>><br>
>> If the memory usage is too excessive, consider caching only subsets of the<br>
>> prefixes. Keep track of which leading prefix (1 or 2 digits) are the most<br>
>> used, and keep those in memory. Have a reasonably fast DB to fall back to.<br>
>><br>
>><br>
>><br>
>> If you’re sure the storage requirements or the change frequency truly<br>
>> eliminates keeping it in your own memory, here are some suggestions for<br>
>> working on DB performance:<br>
>><br>
>><br>
>><br>
>> A little perf gain is to pre-calculate possible lengths, so you’re<br>
>> performing less lookups. Just have a table based on the first 2 or 3 digits,<br>
>> giving you the valid prefix lengths. That can easily cut down the number of<br>
>> lookups from 10-12 to, say, 3-4. Compare the perf of temp tables, CTEs, and<br>
>> dynamic SQL “IN”.<br>
>><br>
>><br>
>><br>
>> But I would be hesitant to do this from a higher level ORM or via dynamic<br>
>> SQL queries:<br>
>><br>
>> - You’ll have a much harder time doing DB-specific<br>
>> optimizations/hints<br>
>><br>
>> - Dynamic SQL needs to be parsed each time (possibly minor issue)<br>
>><br>
>> - If the ad hoc queries are not properly parameterized, query<br>
>> plans might not get reused very well<br>
>><br>
>> - You can easily send many times more data to the server (your<br>
>> entire SQL, versus just the sproc name + parameters)<br>
>><br>
>><br>
>><br>
>> Really look into doing programmability on the server. There’s all sorts of<br>
>> things you can do to minimize what you need to send to the database, and let<br>
>> the DB engine figure out the most efficient way. You might want to use a<br>
>> product that’s a bit more advanced than MySQL.<br>
>><br>
>><br>
>><br>
>> And finally, test, test, test. You should have a nice benchmark with<br>
>> several million rows and be able to execute, say, 10-50K lookups (on<br>
>> multiple threads) after each change. Modify your query, execute it, _inspect<br>
>> the execution plan_. Check your indexes – make sure they are covering the<br>
>> query in the right way. (For example, if the table is (ID, Prefix, Price),<br>
>> you want an index of (Prefix, Price).) Review possible hints (for example,<br>
>> SQL Server usually needs a hint to choose a indexed view). Make sure your<br>
>> transaction level is set appropriately (can you do the query without<br>
>> locking?). Can you batch up several numbers in one command, saving on the<br>
>> per-command overhead?<br>
>><br>
>><br>
>><br>
>> Also consider it in light of the rest of your application. If everything<br>
>> is in the same database, then you can probably do the entire rate call/save<br>
>> cdr/update balances in a single command and transaction.<br>
>><br>
>><br>
>><br>
>> For us, our initial draft of routing inside SQL Server performed well<br>
>> under 100 calls/sec – not usable. After a few days of playing with things,<br>
>> it went over 1000 calls/sec, which is comfortable enough for a $500 server.<br>
>> This is matching 100 gateways and dialplans with several thousand entries<br>
>> per dialplan, across 4 million+ routes, doing QBR/LCR in the process.<br>
>><br>
>><br>
>><br>
>> -Michael<br>
>><br>
>><br>
>><br>
>><br>
>><br>
>> From: <a href="mailto:freeswitch-users-bounces@lists.freeswitch.org">freeswitch-users-bounces@lists.freeswitch.org</a><br>
>> [mailto:<a href="mailto:freeswitch-users-bounces@lists.freeswitch.org">freeswitch-users-bounces@lists.freeswitch.org</a>] On Behalf Of Muhammad<br>
>> Shahzad<br>
>> Sent: Wednesday, October 14, 2009 1:55 AM<br>
>> To: <a href="mailto:freeswitch-users@lists.freeswitch.org">freeswitch-users@lists.freeswitch.org</a><br>
>> Subject: Re: [Freeswitch-users] Some help with my post-paid billing<br>
>> project<br>
>><br>
>><br>
>><br>
>> I fully agree that direct matching is much faster then pattern matching in<br>
>> SQL.<br>
>><br>
>> One of my clients had same problem, he had around 12 million number<br>
>> prefixes in a table and during each call an AGI script use to query that<br>
>> table to find longest prefix match, but this use to take like 3-5 seconds<br>
>> even with indexed columns. So after a lots of R & D we come up with<br>
>> following logic,<br>
>><br>
>> 1. We break the destination number length-wise, e.g. suppose number is<br>
>> 923344224088 then length chunks would be,<br>
>><br>
>> 923344224088, 92334422408, 9233442240, 923344224, 92334422, 9233442,<br>
>> 923344, 92334, 9233, 923, 92, 9<br>
>><br>
>> 2. Then use SQL LIKE function in WHERE clause (you can also use SQL OR<br>
>> function if your DBMS doesn't support SQL LIKE function), and pass all these<br>
>> chunks to it, e.g.<br>
>><br>
>> WHERE prefix LIKE (923344224088, 92334422408, 9233442240, 923344224,<br>
>> 92334422, 9233442, 923344, 92334, 9233, 923, 92, 9)<br>
>><br>
>> 3. Lastly we ORDER the result by prefix length, e.g.<br>
>><br>
>> ORDER BY LENGTH(prefix) DESC LIMIT 1<br>
>><br>
>> 4. The complete query will be,<br>
>><br>
>> SELECT * FROM prefixes<br>
>> WHERE prefix LIKE (923344224088, 92334422408, 9233442240, 923344224,<br>
>> 92334422, 9233442, 923344, 92334, 9233, 923, 92, 9)<br>
>> ORDER BY LENGTH(prefix) DESC LIMIT 1<br>
>><br>
>> Now the query takes less then 150 ms to execute. :-)<br>
>><br>
>> Here is an STL method that can generate this query, i am sure you can<br>
>> convert it to any programming language of your choice easily.<br>
>><br>
>> =========================================================<br>
>> std::string GetQuery(std::string destination) {<br>
>> std::string query = "SELECT * FROM prefixes WHERE prefix LIKE ('"<br>
>> + destination;<br>
>><br>
>> for(int i=1; i<destination.length(); i++) {<br>
>> query += "','" + destination.substr(0, (i * -1));<br>
>><br>
>> }<br>
>><br>
>> query += "') ORDER BY LENGTH(prefix) DESC LIMIT 1";<br>
>> return query;<br>
>> }<br>
>> =========================================================<br>
>><br>
>> I am pretty sure this query is 100% ANSI SQL compatible<br>
>> (<a href="http://en.wikipedia.org/wiki/SQL" target="_blank">http://en.wikipedia.org/wiki/SQL</a>).<br>
>><br>
>> Thank you.<br>
>><br>
>> On Wed, Oct 14, 2009 at 10:15 AM, Michael Giagnocavo <<a href="mailto:mgg@giagnocavo.net">mgg@giagnocavo.net</a>><br>
>> wrote:<br>
>><br>
>> In our testing with SQL Server, we found that executing several queries<br>
>> for direct matches yielded far better performance than one query trying to<br>
>> check prefixes. (The column was also part of the clustered index, but AFAIK<br>
>> MySQL doesn’t support defining your own clustered indexes; you get the PK<br>
>> always.)<br>
>><br>
>><br>
>><br>
>> -Michael<br>
>><br>
>><br>
>><br>
>> From: <a href="mailto:freeswitch-users-bounces@lists.freeswitch.org">freeswitch-users-bounces@lists.freeswitch.org</a><br>
>> [mailto:<a href="mailto:freeswitch-users-bounces@lists.freeswitch.org">freeswitch-users-bounces@lists.freeswitch.org</a>] On Behalf Of Diego<br>
>> Viola<br>
>> Sent: Tuesday, October 13, 2009 7:54 PM<br>
>> To: <a href="mailto:freeswitch-users@lists.freeswitch.org">freeswitch-users@lists.freeswitch.org</a><br>
>> Subject: Re: [Freeswitch-users] Some help with my post-paid billing<br>
>> project<br>
>><br>
>><br>
>><br>
>> Wrong question.<br>
>><br>
>> Is there a way to compare numbers with prefixes without using the prefix<br>
>> module?<br>
>><br>
>> Diego<br>
>><br>
>> On Wed, Oct 14, 2009 at 1:36 AM, Diego Viola <<a href="mailto:diego.viola@gmail.com">diego.viola@gmail.com</a>><br>
>> wrote:<br>
>><br>
>> I'm using MySQL now but I will try PostgreSQL with the prefix module, is<br>
>> there a way to do that without the prefix module and with regular SQL?<br>
>><br>
>> Any examples?<br>
>><br>
>> Diego<br>
>><br>
>><br>
>><br>
>> On Tue, Oct 13, 2009 at 10:45 PM, Even André Fiskvik <<a href="mailto:grevenx@me.com">grevenx@me.com</a>><br>
>> wrote:<br>
>><br>
>> What database are you using?<br>
>> You could do this with regular SQL, but it would by a costly operation,<br>
>> for PostgreSQL we're using the prefix module:<br>
>> <a href="http://pgfoundry.org/projects/prefix/" target="_blank">http://pgfoundry.org/projects/prefix/</a><br>
>><br>
>> You can then match the closest prefix by using something like<br>
>> "WHERE myprefix_col @> caller_destination_number ORDER BY LENGTH<br>
>> (myprefix_col::text) LIMIT 1;"<br>
>><br>
>><br>
>> Best regards,<br>
>> Even André<br>
>><br>
>> On 13. okt. 2009, at 23.53, Diego Viola wrote:<br>
>><br>
>> > Hello,<br>
>> ><br>
>> > I'm trying to write a post-paid billing script, I have the CDR on my<br>
>> > database and also a "rates" table, the CDR contains fields like<br>
>> > caller_destination_number, variable_duration, etc. and the rates<br>
>> > table contains: destination, prefix, rate (cost).<br>
>> ><br>
>> > The problem is that I can't just strip the destination number to<br>
>> > take the prefix from it because I have to deal with destination<br>
>> > numbers from different countries and they all have different prefix<br>
>> > lengths... so I need to find another way to take the prefix from the<br>
>> > destination number.<br>
>> ><br>
>> > Any ideas how to do this?<br>
>> ><br>
>> > Thanks,<br>
>> ><br>
>> > Diego<br>
>> ><br>
>><br>
>> > _______________________________________________<br>
>> > FreeSWITCH-users mailing list<br>
>> > <a href="mailto:FreeSWITCH-users@lists.freeswitch.org">FreeSWITCH-users@lists.freeswitch.org</a><br>
>> > <a href="http://lists.freeswitch.org/mailman/listinfo/freeswitch-users" target="_blank">http://lists.freeswitch.org/mailman/listinfo/freeswitch-users</a><br>
>> > UNSUBSCRIBE:<a href="http://lists.freeswitch.org/mailman/options/freeswitch-" target="_blank">http://lists.freeswitch.org/mailman/options/freeswitch-</a><br>
>> > users<br>
>> > <a href="http://www.freeswitch.org" target="_blank">http://www.freeswitch.org</a><br>
>><br>
>><br>
>> _______________________________________________<br>
>> FreeSWITCH-users mailing list<br>
>> <a href="mailto:FreeSWITCH-users@lists.freeswitch.org">FreeSWITCH-users@lists.freeswitch.org</a><br>
>> <a href="http://lists.freeswitch.org/mailman/listinfo/freeswitch-users" target="_blank">http://lists.freeswitch.org/mailman/listinfo/freeswitch-users</a><br>
>> UNSUBSCRIBE:<a href="http://lists.freeswitch.org/mailman/options/freeswitch-users" target="_blank">http://lists.freeswitch.org/mailman/options/freeswitch-users</a><br>
>> <a href="http://www.freeswitch.org" target="_blank">http://www.freeswitch.org</a><br>
>><br>
>><br>
>><br>
>><br>
>><br>
>> _______________________________________________<br>
>> FreeSWITCH-users mailing list<br>
>> <a href="mailto:FreeSWITCH-users@lists.freeswitch.org">FreeSWITCH-users@lists.freeswitch.org</a><br>
>> <a href="http://lists.freeswitch.org/mailman/listinfo/freeswitch-users" target="_blank">http://lists.freeswitch.org/mailman/listinfo/freeswitch-users</a><br>
>> UNSUBSCRIBE:<a href="http://lists.freeswitch.org/mailman/options/freeswitch-users" target="_blank">http://lists.freeswitch.org/mailman/options/freeswitch-users</a><br>
>> <a href="http://www.freeswitch.org" target="_blank">http://www.freeswitch.org</a><br>
>><br>
>><br>
>> --<br>
>> ________________________________________________________<br>
>> |<br>
>> |<br>
>> | FATAL ERROR<br>
>> --- O X |<br>
>> |_______________________________________________________|<br>
>> | You have moved the mouse.<br>
>> |<br>
>> | Windows must be restarted for the changes to take effect. |<br>
>> | <OK><br>
>> |<br>
>> ####################################/<br>
>><br>
>><br>
>> Muhammad Shahzad<br>
>> -----------------------------------<br>
>> CISCO Rich Media Communication Specialist (CRMCS)<br>
>> CISCO Certified Network Associate (CCNA)<br>
>> Cell: +92 334 422 40 88<br>
>> MSN: <a href="mailto:shari_786pk@hotmail.com">shari_786pk@hotmail.com</a><br>
>> Email: <a href="mailto:shaheryarkh@googlemail.com">shaheryarkh@googlemail.com</a><br>
>><br>
>> _______________________________________________<br>
>> FreeSWITCH-users mailing list<br>
>> <a href="mailto:FreeSWITCH-users@lists.freeswitch.org">FreeSWITCH-users@lists.freeswitch.org</a><br>
>> <a href="http://lists.freeswitch.org/mailman/listinfo/freeswitch-users" target="_blank">http://lists.freeswitch.org/mailman/listinfo/freeswitch-users</a><br>
>> UNSUBSCRIBE:<a href="http://lists.freeswitch.org/mailman/options/freeswitch-users" target="_blank">http://lists.freeswitch.org/mailman/options/freeswitch-users</a><br>
>> <a href="http://www.freeswitch.org" target="_blank">http://www.freeswitch.org</a><br>
>><br>
><br>
><br>
><br>
> --<br>
> ________________________________________________________<br>
> |<br>
> |<br>
> | FATAL ERROR ---<br>
> O X |<br>
> |_______________________________________________________|<br>
> | You have moved the mouse.<br>
> |<br>
> | Windows must be restarted for the changes to take effect. |<br>
> | <OK><br>
> |<br>
> ####################################/<br>
><br>
><br>
> Muhammad Shahzad<br>
> -----------------------------------<br>
> CISCO Rich Media Communication Specialist (CRMCS)<br>
> CISCO Certified Network Associate (CCNA)<br>
> Cell: +92 334 422 40 88<br>
> MSN: <a href="mailto:shari_786pk@hotmail.com">shari_786pk@hotmail.com</a><br>
> Email: <a href="mailto:shaheryarkh@googlemail.com">shaheryarkh@googlemail.com</a><br>
><br>
> _______________________________________________<br>
> FreeSWITCH-users mailing list<br>
> <a href="mailto:FreeSWITCH-users@lists.freeswitch.org">FreeSWITCH-users@lists.freeswitch.org</a><br>
> <a href="http://lists.freeswitch.org/mailman/listinfo/freeswitch-users" target="_blank">http://lists.freeswitch.org/mailman/listinfo/freeswitch-users</a><br>
> UNSUBSCRIBE:<a href="http://lists.freeswitch.org/mailman/options/freeswitch-users" target="_blank">http://lists.freeswitch.org/mailman/options/freeswitch-users</a><br>
> <a href="http://www.freeswitch.org" target="_blank">http://www.freeswitch.org</a><br>
><br>
><br>
<br>
<br>
<br>
</div></div>--<br>
<font color="#888888">-Rupa<br>
</font><div><div></div><div class="h5"><br>
_______________________________________________<br>
FreeSWITCH-users mailing list<br>
<a href="mailto:FreeSWITCH-users@lists.freeswitch.org">FreeSWITCH-users@lists.freeswitch.org</a><br>
<a href="http://lists.freeswitch.org/mailman/listinfo/freeswitch-users" target="_blank">http://lists.freeswitch.org/mailman/listinfo/freeswitch-users</a><br>
UNSUBSCRIBE:<a href="http://lists.freeswitch.org/mailman/options/freeswitch-users" target="_blank">http://lists.freeswitch.org/mailman/options/freeswitch-users</a><br>
<a href="http://www.freeswitch.org" target="_blank">http://www.freeswitch.org</a><br>
</div></div></blockquote></div><br>