[Freeswitch-users] Some help with my post-paid billing project

[Muhammad Shahzad]

I fully agree that direct matching is much faster then pattern matching in
SQL.

One of my clients had same problem, he had around 12 million number prefixes
in a table and during each call an AGI script use to query that table to
find longest prefix match, but this use to take like 3-5 seconds even with
indexed columns. So after a lots of R & D we come up with following logic,

1. We break the destination number length-wise, e.g. suppose number is
923344224088 then length chunks would be,

923344224088, 92334422408, 9233442240, 923344224, 92334422, 9233442, 923344,
92334, 9233, 923, 92, 9

2. Then use SQL LIKE function in WHERE clause (you can also use SQL OR
function if your DBMS doesn't support SQL LIKE function), and pass all these
chunks to it, e.g.

WHERE prefix LIKE (923344224088, 92334422408, 9233442240, 923344224,
92334422, 9233442, 923344, 92334, 9233, 923, 92, 9)

3. Lastly we ORDER the result by prefix length, e.g.

ORDER BY LENGTH(prefix) DESC LIMIT 1

4. The complete query will be,

SELECT * FROM prefixes
WHERE prefix LIKE (923344224088, 92334422408, 9233442240, 923344224,
92334422, 9233442, 923344, 92334, 9233, 923, 92, 9)
ORDER BY LENGTH(prefix) DESC LIMIT 1

Now the query takes less then 150 ms to execute. :-)

Here is an STL method that can generate this query, i am sure you can
convert it to any programming language of your choice easily.

=========================================================
*std::string GetQuery(std::string destination) {
        std::string query = "SELECT * FROM prefixes WHERE prefix LIKE ('" +
destination;

        for(int i=1; i<destination.length(); i++) {
                query += "','" + destination.substr(0, (i * -1));

        }

        query += "') ORDER BY LENGTH(prefix) DESC LIMIT 1";
        return query;
}
*=========================================================

I am pretty sure this query is 100% ANSI SQL compatible (
http://en.wikipedia.org/wiki/SQL).

Thank you.


On Wed, Oct 14, 2009 at 10:15 AM, Michael Giagnocavo <mgg at giagnocavo.net>wrote:

>  In our testing with SQL Server, we found that executing several queries
> for direct matches yielded far better performance than one query trying to
> check prefixes. (The column was also part of the clustered index, but AFAIK
> MySQL doesn’t support defining your own clustered indexes; you get the PK
> always.)
>
>
>
> -Michael
>
>
>
> *From:* freeswitch-users-bounces at lists.freeswitch.org [mailto:
> freeswitch-users-bounces at lists.freeswitch.org] *On Behalf Of *Diego Viola
> *Sent:* Tuesday, October 13, 2009 7:54 PM
> *To:* freeswitch-users at lists.freeswitch.org
> *Subject:* Re: [Freeswitch-users] Some help with my post-paid billing
> project
>
>
>
> Wrong question.
>
> Is there a way to compare numbers with prefixes without using the prefix
> module?
>
> Diego
>
> On Wed, Oct 14, 2009 at 1:36 AM, Diego Viola <diego.viola at gmail.com>
> wrote:
>
> I'm using MySQL now but I will try PostgreSQL with the prefix module, is
> there a way to do that without the prefix module and with regular SQL?
>
> Any examples?
>
> Diego
>
>
>
> On Tue, Oct 13, 2009 at 10:45 PM, Even André Fiskvik <grevenx at me.com>
> wrote:
>
> What database are you using?
> You could do this with regular SQL, but it would by a costly operation,
> for PostgreSQL we're using the prefix module:
> http://pgfoundry.org/projects/prefix/
>
> You can then match the closest prefix by using something like
> "WHERE myprefix_col @> caller_destination_number ORDER BY LENGTH
> (myprefix_col::text) LIMIT 1;"
>
>
> Best regards,
> Even André
>
>
>
> On 13. okt. 2009, at 23.53, Diego Viola wrote:
>
> > Hello,
> >
> > I'm trying to write a post-paid billing script, I have the CDR on my
> > database and also a "rates" table, the CDR contains fields like
> > caller_destination_number, variable_duration, etc. and the rates
> > table contains: destination, prefix, rate (cost).
> >
> > The problem is that I can't just strip the destination number to
> > take the prefix from it because I have to deal with destination
> > numbers from different countries and they all have different prefix
> > lengths... so I need to find another way to take the prefix from the
> > destination number.
> >
> > Any ideas how to do this?
> >
> > Thanks,
> >
> > Diego
> >
>
> > _______________________________________________
> > 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
>
>
> _______________________________________________
> 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
>
>
>
>
>
> _______________________________________________
> 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
>
>


-- 
________________________________________________________
|
                     |
| FATAL ERROR                                                           ---
O X |
|_______________________________________________________|
|                        You have moved the mouse.
 |
| Windows must be restarted for the changes to take effect.   |
|                                        <OK>
               |
####################################/


Muhammad Shahzad
-----------------------------------
CISCO Rich Media Communication Specialist (CRMCS)
CISCO Certified Network Associate (CCNA)
Cell: +92 334 422 40 88
MSN: shari_786pk at hotmail.com
Email: shaheryarkh at googlemail.com
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.freeswitch.org/pipermail/freeswitch-users/attachments/20091014/f73f73f4/attachment-0002.html