Oops, you are right, this is what happens when you are doing more then one thing at a time, i was writing a db function while replying in this email thread and confused LIKE with IN. There is one more mistake in my email if any one noticed, that is, string class in STL does not support negative length in method substr.<br>
<br>Thanks for correction.<br><br><br><div class="gmail_quote">On Wed, Oct 14, 2009 at 6:43 PM, Michael Giagnocavo <span dir="ltr">&lt;<a href="mailto:mgg@giagnocavo.net">mgg@giagnocavo.net</a>&gt;</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;">









<div link="blue" vlink="purple" lang="EN-US">

<div>

<p class="MsoNormal"><span style="font-size: 11pt; color: rgb(31, 73, 125);">Wouldn’t that be SQL “IN” instead of LIKE? </span></p>

<p class="MsoNormal"><span style="font-size: 11pt; color: rgb(31, 73, 125);"> </span></p>

<p class="MsoNormal"><span style="font-size: 11pt; color: rgb(31, 73, 125);">First off, see if you can do this in memory. If it’s just a
“rate sheet” list of prefixes + billing, it’s probably not that much data. I’d
guess maybe 64-128 bytes per record (in Ruby it’s probably more, maybe 256
bytes?) inside a hashtable. Even so, we’re only talking about 1.5 to 3GB of
RAM. Stick it in a nice performing dictionary of some sort and you’re set. </span></p>

<p class="MsoNormal"><span style="font-size: 11pt; color: rgb(31, 73, 125);"> </span></p>

<p class="MsoNormal"><span style="font-size: 11pt; color: rgb(31, 73, 125);">Just as a quick test, on one core of a Core2, it takes about
150ms to do 100K lookups against a 1M item hashtable (F#) – that’s just a 1
line loop I tried out with no optimization, doing about 650K/sec. For
comparison on the same machine, looping on a simple “SELECT @@VERSION” command
only achieved 5000 executions/second on a single thread – that’s just the SQL
overhead (to a local SQL instance!).</span></p>

<p class="MsoNormal"><span style="font-size: 11pt; color: rgb(31, 73, 125);"> </span></p>

<p class="MsoNormal"><span style="font-size: 11pt; color: rgb(31, 73, 125);">If the memory usage is too excessive, consider caching only
subsets of the prefixes. Keep track of which leading prefix (1 or 2 digits) are
the most used, and keep those in memory. Have a reasonably fast DB to fall back
to.</span></p>

<p class="MsoNormal"><span style="font-size: 11pt; color: rgb(31, 73, 125);"> </span></p>

<p class="MsoNormal"><span style="font-size: 11pt; color: rgb(31, 73, 125);">If you’re sure the storage requirements or the change frequency
truly eliminates keeping it in your own memory, here are some suggestions for
working on DB performance:</span></p>

<p class="MsoNormal"><span style="font-size: 11pt; color: rgb(31, 73, 125);"> </span></p>

<p class="MsoNormal"><span style="font-size: 11pt; color: rgb(31, 73, 125);">A little perf gain is to pre-calculate possible lengths, so
you’re performing less lookups. Just have a table based on the first 2 or 3
digits, giving you the valid prefix lengths. That can easily cut down the
number of lookups from 10-12 to, say, 3-4. Compare the perf of temp tables,
CTEs, and dynamic SQL “IN”.</span></p>

<p class="MsoNormal"><span style="font-size: 11pt; color: rgb(31, 73, 125);"> </span></p>

<p class="MsoNormal"><span style="font-size: 11pt; color: rgb(31, 73, 125);">But I would be hesitant to do this from a higher level ORM or
via dynamic SQL queries:</span></p>

<p style="margin-left: 0.75in;"><span style="font-size: 11pt; color: rgb(31, 73, 125);"><span>-<span style="font-family: &quot;Times New Roman&quot;; font-style: normal; font-variant: normal; font-weight: normal; font-size: 7pt; line-height: normal; font-size-adjust: none; font-stretch: normal;">         
</span></span></span><span style="font-size: 11pt; color: rgb(31, 73, 125);">You’ll have a much harder time doing DB-specific
optimizations/hints</span></p>

<p style="margin-left: 0.75in;"><span style="font-size: 11pt; color: rgb(31, 73, 125);"><span>-<span style="font-family: &quot;Times New Roman&quot;; font-style: normal; font-variant: normal; font-weight: normal; font-size: 7pt; line-height: normal; font-size-adjust: none; font-stretch: normal;">         
</span></span></span><span style="font-size: 11pt; color: rgb(31, 73, 125);">Dynamic SQL needs to be parsed each time (possibly minor issue)</span></p>

<p style="margin-left: 0.75in;"><span style="font-size: 11pt; color: rgb(31, 73, 125);"><span>-<span style="font-family: &quot;Times New Roman&quot;; font-style: normal; font-variant: normal; font-weight: normal; font-size: 7pt; line-height: normal; font-size-adjust: none; font-stretch: normal;">         
</span></span></span><span style="font-size: 11pt; color: rgb(31, 73, 125);">If the ad hoc queries are not properly parameterized, query
plans might not get reused very well</span></p>

<p style="margin-left: 0.75in;"><span style="font-size: 11pt; color: rgb(31, 73, 125);"><span>-<span style="font-family: &quot;Times New Roman&quot;; font-style: normal; font-variant: normal; font-weight: normal; font-size: 7pt; line-height: normal; font-size-adjust: none; font-stretch: normal;">         
</span></span></span><span style="font-size: 11pt; color: rgb(31, 73, 125);">You can easily send many times more data to the server (your
entire SQL, versus just the sproc name + parameters)</span></p>

<p class="MsoNormal"><span style="font-size: 11pt; color: rgb(31, 73, 125);"> </span></p>

<p class="MsoNormal"><span style="font-size: 11pt; color: rgb(31, 73, 125);">Really look into doing programmability on the server. There’s
all sorts of things you can do to minimize what you need to send to the
database, and let the DB engine figure out the most efficient way. You might
want to use a product that’s a bit more advanced than MySQL. </span></p>

<p class="MsoNormal"><span style="font-size: 11pt; color: rgb(31, 73, 125);"> </span></p>

<p class="MsoNormal"><span style="font-size: 11pt; color: rgb(31, 73, 125);">And finally, test, test, test. You should have a nice benchmark
with several million rows and be able to execute, say, 10-50K lookups (on
multiple threads) after each change. Modify your query, execute it, _<i>inspect
the execution plan</i>_. Check your indexes – make sure they are covering the
query in the right way. (For example, if the table is (ID, Prefix, Price), you
want an index of (Prefix, Price).) Review possible hints (for example, SQL
Server usually needs a hint to choose a indexed view). Make sure your
transaction level is set appropriately (can you do the query without locking?).
Can you batch up several numbers in one command, saving on the per-command
overhead?</span></p>

<p class="MsoNormal"><span style="font-size: 11pt; color: rgb(31, 73, 125);"> </span></p>

<p class="MsoNormal"><span style="font-size: 11pt; color: rgb(31, 73, 125);">Also consider it in light of the rest of your application. If
everything is in the same database, then you can probably do the entire rate
call/save cdr/update balances in a single command and transaction. </span></p>

<p class="MsoNormal"><span style="font-size: 11pt; color: rgb(31, 73, 125);"> </span></p>

<p class="MsoNormal"><span style="font-size: 11pt; color: rgb(31, 73, 125);">For us, our initial draft of routing inside SQL Server performed
well under 100 calls/sec – not usable. After a few days of playing with things,
it went over 1000 calls/sec, which is comfortable enough for a $500 server.
This is matching 100 gateways and dialplans with several thousand entries per
dialplan, across 4 million+ routes, doing QBR/LCR in the process. </span></p>

<p class="MsoNormal"><span style="font-size: 11pt; color: rgb(31, 73, 125);"> </span></p>

<p class="MsoNormal"><span style="font-size: 11pt; color: rgb(31, 73, 125);">-Michael</span></p>

<p class="MsoNormal"><span style="font-size: 11pt; color: rgb(31, 73, 125);"> </span></p>

<p class="MsoNormal"><span style="font-size: 11pt; color: rgb(31, 73, 125);"> </span></p>

<div style="border-style: solid none none; border-color: rgb(181, 196, 223) -moz-use-text-color -moz-use-text-color; border-width: 1pt medium medium; padding: 3pt 0in 0in;">

<p class="MsoNormal"><b><span style="font-size: 10pt;">From:</span></b><span style="font-size: 10pt;">
<a href="mailto:freeswitch-users-bounces@lists.freeswitch.org" target="_blank">freeswitch-users-bounces@lists.freeswitch.org</a>
[mailto:<a href="mailto:freeswitch-users-bounces@lists.freeswitch.org" target="_blank">freeswitch-users-bounces@lists.freeswitch.org</a>] <b>On Behalf Of </b>Muhammad
Shahzad<br>
<b>Sent:</b> Wednesday, October 14, 2009 1:55 AM<div><div></div><div class="h5"><br>
<b>To:</b> <a href="mailto:freeswitch-users@lists.freeswitch.org" target="_blank">freeswitch-users@lists.freeswitch.org</a><br>
<b>Subject:</b> Re: [Freeswitch-users] Some help with my post-paid billing
project</div></div></span></p>

</div><div><div></div><div class="h5">

<p class="MsoNormal"> </p>

<p class="MsoNormal" style="margin-bottom: 12pt;">I fully agree that direct
matching is much faster then pattern matching in SQL.<br>
<br>
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 &amp; D we come up with following logic,<br>
<br>
1. We break the destination number length-wise, e.g. suppose number is
923344224088 then length chunks would be,<br>
<br>
923344224088, 92334422408, 9233442240, 923344224, 92334422, 9233442, 923344,
92334, 9233, 923, 92, 9<br>
<br>
2. Then use SQL LIKE function in WHERE clause (you can also use SQL OR function
if your DBMS doesn&#39;t support SQL LIKE function), and pass all these chunks to
it, e.g.<br>
<br>
WHERE prefix LIKE (923344224088, 92334422408, 9233442240, 923344224, 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, 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 convert
it to any programming language of your choice easily.<br>
<br>
=========================================================<br>
<i>std::string GetQuery(std::string destination) {<br>
        std::string query = &quot;SELECT *
FROM prefixes WHERE prefix LIKE (&#39;&quot; + destination;<br>
<br>
        for(int i=1;
i&lt;destination.length(); i++) {<br>
               
query += &quot;&#39;,&#39;&quot; + destination.substr(0, (i * -1));<br>
<br>
        }<br>
<br>
        query += &quot;&#39;) ORDER BY
LENGTH(prefix) DESC LIMIT 1&quot;;<br>
        return query;<br>
}<br>
</i>=========================================================<br>
<br>
I am pretty sure this query is 100% ANSI SQL compatible (<a href="http://en.wikipedia.org/wiki/SQL" target="_blank">http://en.wikipedia.org/wiki/SQL</a>).<br>
<br>
Thank you.</p>

<div>

<p class="MsoNormal">On Wed, Oct 14, 2009 at 10:15 AM, Michael Giagnocavo &lt;<a href="mailto:mgg@giagnocavo.net" target="_blank">mgg@giagnocavo.net</a>&gt; wrote:</p>

<div>

<div>

<p class="MsoNormal"><span style="font-size: 11pt; color: rgb(31, 73, 125);">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.)</span></p>

<p class="MsoNormal"><span style="font-size: 11pt; color: rgb(31, 73, 125);"> </span></p>

<p class="MsoNormal"><span style="font-size: 11pt; color: rgb(31, 73, 125);">-Michael</span></p>

<p class="MsoNormal"><span style="font-size: 11pt; color: rgb(31, 73, 125);"> </span></p>

<div style="border-style: solid none none; border-color: -moz-use-text-color; border-width: 1pt medium medium; padding: 3pt 0in 0in;">

<p class="MsoNormal"><b><span style="font-size: 10pt;">From:</span></b><span style="font-size: 10pt;"> <a href="mailto:freeswitch-users-bounces@lists.freeswitch.org" target="_blank">freeswitch-users-bounces@lists.freeswitch.org</a>
[mailto:<a href="mailto:freeswitch-users-bounces@lists.freeswitch.org" target="_blank">freeswitch-users-bounces@lists.freeswitch.org</a>] <b>On Behalf
Of </b>Diego Viola<br>
<b>Sent:</b> Tuesday, October 13, 2009 7:54 PM<br>
<b>To:</b> <a href="mailto:freeswitch-users@lists.freeswitch.org" target="_blank">freeswitch-users@lists.freeswitch.org</a><br>
<b>Subject:</b> Re: [Freeswitch-users] Some help with my post-paid billing
project</span></p>

</div>

<div>

<div>

<p class="MsoNormal"> </p>

<p class="MsoNormal" style="margin-bottom: 12pt;">Wrong
question.<br>
<br>
Is there a way to compare numbers with prefixes without using the prefix
module?<br>
<br>
Diego</p>

<div>

<p class="MsoNormal">On
Wed, Oct 14, 2009 at 1:36 AM, Diego Viola &lt;<a href="mailto:diego.viola@gmail.com" target="_blank">diego.viola@gmail.com</a>&gt;
wrote:</p>

<p class="MsoNormal">I&#39;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?<br>
<br>
Any examples?<br>
<span style="color: rgb(136, 136, 136);"><br>
Diego</span></p>

<div>

<div>

<p class="MsoNormal" style="margin-bottom: 12pt;"> </p>

<div>

<p class="MsoNormal">On
Tue, Oct 13, 2009 at 10:45 PM, Even André Fiskvik &lt;<a href="mailto:grevenx@me.com" target="_blank">grevenx@me.com</a>&gt; wrote:</p>

<p class="MsoNormal">What
database are you using?<br>
You could do this with regular SQL, but it would by a costly operation,<br>
for PostgreSQL we&#39;re using the prefix module: <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>
&quot;WHERE myprefix_col @&gt; caller_destination_number ORDER BY LENGTH<br>
(myprefix_col::text) LIMIT 1;&quot;<br>
<br>
<br>
Best regards,<br>
Even André</p>

<div>

<div>

<p class="MsoNormal"><br>
<br>
On 13. okt. 2009, at 23.53, Diego Viola wrote:<br>
<br>
&gt; Hello,<br>
&gt;<br>
&gt; I&#39;m trying to write a post-paid billing script, I have the CDR on my<br>
&gt; database and also a &quot;rates&quot; table, the CDR contains fields like<br>
&gt; caller_destination_number, variable_duration, etc. and the rates<br>
&gt; table contains: destination, prefix, rate (cost).<br>
&gt;<br>
&gt; The problem is that I can&#39;t just strip the destination number to<br>
&gt; take the prefix from it because I have to deal with destination<br>
&gt; numbers from different countries and they all have different prefix<br>
&gt; lengths... so I need to find another way to take the prefix from the<br>
&gt; destination number.<br>
&gt;<br>
&gt; Any ideas how to do this?<br>
&gt;<br>
&gt; Thanks,<br>
&gt;<br>
&gt; Diego<br>
&gt;</p>

</div>

</div>

<p class="MsoNormal">&gt;
_______________________________________________<br>
&gt; FreeSWITCH-users mailing list<br>
&gt; <a href="mailto:FreeSWITCH-users@lists.freeswitch.org" target="_blank">FreeSWITCH-users@lists.freeswitch.org</a><br>
&gt; <a href="http://lists.freeswitch.org/mailman/listinfo/freeswitch-users" target="_blank">http://lists.freeswitch.org/mailman/listinfo/freeswitch-users</a><br>
&gt; UNSUBSCRIBE:<a href="http://lists.freeswitch.org/mailman/options/freeswitch-" target="_blank">http://lists.freeswitch.org/mailman/options/freeswitch-</a><br>
&gt; users<br>
&gt; <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" target="_blank">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></p>

</div>

<p class="MsoNormal"> </p>

</div>

</div>

</div>

<p class="MsoNormal"> </p>

</div>

</div>

</div>

</div>

<p class="MsoNormal" style="margin-bottom: 12pt;"><br>
_______________________________________________<br>
FreeSWITCH-users mailing list<br>
<a href="mailto:FreeSWITCH-users@lists.freeswitch.org" target="_blank">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></p>

</div>

<p class="MsoNormal"><br>
<br clear="all">
<br>
-- <br>
________________________________________________________<br>
|                      
                     
                     
                     
       |<br>
| FATAL ERROR                  
                     
                  --- O X |<br>
|_______________________________________________________|<br>
|                      
 You have moved the mouse.            
               |<br>
| Windows must be restarted for the changes to take effect.   |<br>
|                      
                 &lt;OK&gt;  
                     
                     |<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" target="_blank">shari_786pk@hotmail.com</a><br>
Email: <a href="mailto:shaheryarkh@googlemail.com" target="_blank">shaheryarkh@googlemail.com</a></p>

</div></div></div>

</div>


<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></blockquote></div><br><br clear="all"><br>-- <br>________________________________________________________<br>|                                                                                                |<br>| FATAL ERROR                                                           --- O X |<br>
|_______________________________________________________|<br>|                        You have moved the mouse.                            |<br>| Windows must be restarted for the changes to take effect.   |<br>|                                        &lt;OK&gt;                                              |<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>