<html xmlns:v="urn:schemas-microsoft-com:vml" xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:w="urn:schemas-microsoft-com:office:word" xmlns:x="urn:schemas-microsoft-com:office:excel" xmlns:p="urn:schemas-microsoft-com:office:powerpoint" xmlns:a="urn:schemas-microsoft-com:office:access" xmlns:dt="uuid:C2F41010-65B3-11d1-A29F-00AA00C14882" xmlns:s="uuid:BDC6E3F0-6DA3-11d1-A2A3-00AA00C14882" xmlns:rs="urn:schemas-microsoft-com:rowset" xmlns:z="#RowsetSchema" xmlns:b="urn:schemas-microsoft-com:office:publisher" xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet" xmlns:c="urn:schemas-microsoft-com:office:component:spreadsheet" xmlns:odc="urn:schemas-microsoft-com:office:odc" xmlns:oa="urn:schemas-microsoft-com:office:activation" xmlns:html="http://www.w3.org/TR/REC-html40" xmlns:q="http://schemas.xmlsoap.org/soap/envelope/" xmlns:rtc="http://microsoft.com/officenet/conferencing" xmlns:D="DAV:" xmlns:Repl="http://schemas.microsoft.com/repl/" xmlns:mt="http://schemas.microsoft.com/sharepoint/soap/meetings/" xmlns:x2="http://schemas.microsoft.com/office/excel/2003/xml" xmlns:ppda="http://www.passport.com/NameSpace.xsd" xmlns:ois="http://schemas.microsoft.com/sharepoint/soap/ois/" xmlns:dir="http://schemas.microsoft.com/sharepoint/soap/directory/" xmlns:ds="http://www.w3.org/2000/09/xmldsig#" xmlns:dsp="http://schemas.microsoft.com/sharepoint/dsp" xmlns:udc="http://schemas.microsoft.com/data/udc" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:sub="http://schemas.microsoft.com/sharepoint/soap/2002/1/alerts/" xmlns:ec="http://www.w3.org/2001/04/xmlenc#" xmlns:sp="http://schemas.microsoft.com/sharepoint/" xmlns:sps="http://schemas.microsoft.com/sharepoint/soap/" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:udcs="http://schemas.microsoft.com/data/udc/soap" xmlns:udcxf="http://schemas.microsoft.com/data/udc/xmlfile" xmlns:udcp2p="http://schemas.microsoft.com/data/udc/parttopart" xmlns:wf="http://schemas.microsoft.com/sharepoint/soap/workflow/" xmlns:dsss="http://schemas.microsoft.com/office/2006/digsig-setup" xmlns:dssi="http://schemas.microsoft.com/office/2006/digsig" xmlns:mdssi="http://schemas.openxmlformats.org/package/2006/digital-signature" xmlns:mver="http://schemas.openxmlformats.org/markup-compatibility/2006" xmlns:m="http://schemas.microsoft.com/office/2004/12/omml" xmlns:mrels="http://schemas.openxmlformats.org/package/2006/relationships" xmlns:spwp="http://microsoft.com/sharepoint/webpartpages" xmlns:ex12t="http://schemas.microsoft.com/exchange/services/2006/types" xmlns:ex12m="http://schemas.microsoft.com/exchange/services/2006/messages" xmlns:pptsl="http://schemas.microsoft.com/sharepoint/soap/SlideLibrary/" xmlns:spsl="http://microsoft.com/webservices/SharePointPortalServer/PublishedLinksService" xmlns:Z="urn:schemas-microsoft-com:" xmlns:st="" xmlns="http://www.w3.org/TR/REC-html40">
<head>
<meta http-equiv=Content-Type content="text/html; charset=us-ascii">
<meta name=Generator content="Microsoft Word 12 (filtered medium)">
<style>
<!--
/* Font Definitions */
@font-face
{font-family:Calibri;
panose-1:2 15 5 2 2 2 4 3 2 4;}
@font-face
{font-family:Tahoma;
panose-1:2 11 6 4 3 5 4 4 2 4;}
@font-face
{font-family:Consolas;
panose-1:2 11 6 9 2 2 4 3 2 4;}
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
{margin:0cm;
margin-bottom:.0001pt;
font-size:12.0pt;
font-family:"Times New Roman","serif";}
a:link, span.MsoHyperlink
{mso-style-priority:99;
color:blue;
text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed
{mso-style-priority:99;
color:purple;
text-decoration:underline;}
pre
{mso-style-priority:99;
mso-style-link:"HTML Vorformatiert Zchn";
margin:0cm;
margin-bottom:.0001pt;
font-size:10.0pt;
font-family:"Courier New";}
span.HTMLVorformatiertZchn
{mso-style-name:"HTML Vorformatiert Zchn";
mso-style-priority:99;
mso-style-link:"HTML Vorformatiert";
font-family:Consolas;}
span.E-MailFormatvorlage19
{mso-style-type:personal-reply;
font-family:"Calibri","sans-serif";
color:#1F497D;}
.MsoChpDefault
{mso-style-type:export-only;
font-size:10.0pt;}
@page WordSection1
{size:612.0pt 792.0pt;
margin:70.85pt 70.85pt 2.0cm 70.85pt;}
div.WordSection1
{page:WordSection1;}
-->
</style>
<!--[if gte mso 9]><xml>
<o:shapedefaults v:ext="edit" spidmax="1026" />
</xml><![endif]--><!--[if gte mso 9]><xml>
<o:shapelayout v:ext="edit">
<o:idmap v:ext="edit" data="1" />
</o:shapelayout></xml><![endif]-->
</head>
<body lang=DE-CH link=blue vlink=purple style='word-wrap: break-word;
-webkit-nbsp-mode: space;-webkit-line-break: after-white-space'>
<div class=WordSection1>
<p class=MsoNormal><span lang=EN-US style='font-size:11.0pt;font-family:"Calibri","sans-serif";
color:#1F497D'>Hi,<o:p></o:p></span></p>
<p class=MsoNormal><span lang=EN-US style='font-size:11.0pt;font-family:"Calibri","sans-serif";
color:#1F497D'><o:p> </o:p></span></p>
<p class=MsoNormal><span lang=EN-US style='font-size:11.0pt;font-family:"Calibri","sans-serif";
color:#1F497D'>thanks a lot for these details, but I am not sure how the
freeswitch.dbh interface behaves, if there are many threads which will try to
use the connection. Does it then build up more connections or does it block
till the connection will then be released from the other thread? <o:p></o:p></span></p>
<p class=MsoNormal><span lang=EN-US style='font-size:11.0pt;font-family:"Calibri","sans-serif";
color:#1F497D'><o:p> </o:p></span></p>
<p class=MsoNormal><span lang=EN-US style='font-size:11.0pt;font-family:"Calibri","sans-serif";
color:#1F497D'>Currently its not possible to me to create the luasql.postges test
to access the database with the native driver but I am sure, that the
postgres driver should be much faster than the ODBC. Could someone test that?<o:p></o:p></span></p>
<p class=MsoNormal><span lang=EN-US style='font-size:11.0pt;font-family:"Calibri","sans-serif";
color:#1F497D'><o:p> </o:p></span></p>
<p class=MsoNormal><span lang=EN-US style='font-size:11.0pt;font-family:"Calibri","sans-serif";
color:#1F497D'>Best regards,<o:p></o:p></span></p>
<p class=MsoNormal><span lang=EN-US style='font-size:11.0pt;font-family:"Calibri","sans-serif";
color:#1F497D'>Bernhard<o:p></o:p></span></p>
<p class=MsoNormal><span lang=EN-US style='font-size:11.0pt;font-family:"Calibri","sans-serif";
color:#1F497D'><o:p> </o:p></span></p>
<div>
<div style='border:none;border-top:solid #B5C4DF 1.0pt;padding:3.0pt 0cm 0cm 0cm'>
<p class=MsoNormal><b><span lang=EN-US style='font-size:10.0pt;font-family:
"Tahoma","sans-serif"'>Von:</span></b><span lang=EN-US style='font-size:10.0pt;
font-family:"Tahoma","sans-serif"'> freeswitch-dev-bounces@lists.freeswitch.org
[mailto:freeswitch-dev-bounces@lists.freeswit</span><span lang=DE
style='font-size:10.0pt;font-family:"Tahoma","sans-serif"'>ch.org] <b>Im
Auftrag von </b>Leon de Rooij<br>
<b>Gesendet:</b> Dienstag, 28. September 2010 16:11<br>
<b>An:</b> freeswitch-dev@lists.freeswitch.org<br>
<b>Betreff:</b> Re: [Freeswitch-dev] luasql or freeswitch.dbh<o:p></o:p></span></p>
</div>
</div>
<p class=MsoNormal><o:p> </o:p></p>
<p class=MsoNormal>Hi Bernhard,<o:p></o:p></p>
<div>
<p class=MsoNormal><o:p> </o:p></p>
</div>
<div>
<p class=MsoNormal>I tried out several ways for connecting to a database from
inside FreeSWITCH myself before writing the freeswitch.Dbh (which is rather
simple actually - it only exposes already existing FreeSWITCH functionality in
mod_lua).<o:p></o:p></p>
</div>
<div>
<p class=MsoNormal><o:p> </o:p></p>
</div>
<div>
<p class=MsoNormal>My findings were that caching the connection as is provided
by FreeSWITCH gives a huge speed increase compared to re-connecting all the
time as must be done with LuaSQL. Perhaps there are ways to have a connection
pool in ODBC but I didn't investigate that.<o:p></o:p></p>
</div>
<div>
<p class=MsoNormal><o:p> </o:p></p>
</div>
<div>
<p class=MsoNormal><o:p> </o:p></p>
</div>
<div>
<p class=MsoNormal>Here's a test with LuaSQL with ODBC driver:<o:p></o:p></p>
</div>
<div>
<div>
<div><pre><span style='color:#B1B100'>require</span> <span style='color:red'>"luasql.odbc"</span><span
style='color:#66CC66'>;</span><o:p></o:p></pre><pre> <o:p></o:p></pre><pre><span
style='color:#B1B100'>local</span> env <span style='color:#66CC66'>=</span> <span
style='color:#B1B100'>assert</span><span style='color:#66CC66'>(</span>luasql.odbc<span
style='color:#66CC66'>())</span><o:p></o:p></pre><pre><span style='color:#B1B100'>local</span> con <span
style='color:#66CC66'>=</span> <span style='color:#B1B100'>assert</span><span
style='color:#66CC66'>(</span>env:connect<span style='color:#66CC66'>(</span><span
style='color:red'>"dsn","user","pass"</span><span
style='color:#66CC66'>))</span><o:p></o:p></pre><pre><span style='color:#B1B100'>local</span> cur <span
style='color:#66CC66'>=</span> <span style='color:#B1B100'>assert</span><span
style='color:#66CC66'>(</span>con:<span style='color:#B1B100'>execute</span> <span
style='color:#66CC66'>(</span><span style='color:red'>"select 1 as foo, 2 as bar"</span><span
style='color:#66CC66'>))</span><o:p></o:p></pre><pre> <o:p></o:p></pre><pre><span
style='color:#B1B100'>local</span> row <span style='color:#66CC66'>=</span> <span
style='color:#66CC66'>{}</span><o:p></o:p></pre><pre><span style='color:#B1B100'>while</span> <span
style='color:#66CC66'>(</span>cur:fetch<span style='color:#66CC66'>(</span>row, <span
style='color:red'>"a"</span><span style='color:#66CC66'>))</span> <span
style='color:#B1B100'>do</span><o:p></o:p></pre><pre> <span
style='color:#B1B100'>for</span> key, val <span style='color:#B1B100'>in</span> <span
style='color:#B1B100'>pairs</span><span style='color:#66CC66'>(</span>row<span
style='color:#66CC66'>)</span> <span style='color:#B1B100'>do</span><o:p></o:p></pre><pre> stream:<span
style='color:#B1B100'>write</span><span style='color:#66CC66'>(</span><span
style='color:#B1B100'>string.format</span><span style='color:#66CC66'>(</span><span
style='color:red'>"%25s : %s</span><b><span style='color:#000099'>\n</span></b><span
style='color:red'>"</span>, key, val<span style='color:#66CC66'>))</span><o:p></o:p></pre><pre> <span
style='color:#B1B100'>end</span><o:p></o:p></pre><pre> stream:<span
style='color:#B1B100'>write</span><span style='color:#66CC66'>(</span><span
style='color:red'>"</span><b><span style='color:#000099'>\n</span></b><span
style='color:red'>"</span><span style='color:#66CC66'>)</span><o:p></o:p></pre><pre><span
style='color:#B1B100'>end</span><o:p></o:p></pre><pre> <o:p></o:p></pre><pre>cur:close<span
style='color:#66CC66'>()</span><o:p></o:p></pre><pre>con:close<span
style='color:#66CC66'>()</span><o:p></o:p></pre><pre>env:close<span
style='color:#66CC66'>()</span><o:p></o:p></pre><pre><o:p> </o:p></pre><pre>freeswitch@fs-dev> hammer 1000 lua test_luasql_odbc.lua<o:p></o:p></pre><pre>+OK Finished in 38952 ms (min 29 ms, max 107 ms)<o:p></o:p></pre></div>
</div>
</div>
<div>
<div>
<div>
<p class=MsoNormal><o:p> </o:p></p>
</div>
<div>
<p class=MsoNormal><o:p> </o:p></p>
</div>
<div>
<p class=MsoNormal>The same test with freeswitch.Dbh:<o:p></o:p></p>
</div>
<div>
<p class=MsoNormal><o:p> </o:p></p>
</div>
<div><pre><span style='color:#B1B100'>local</span> dbh <span style='color:#66CC66'>=</span> <span
style='color:#B1B100'>assert</span><span style='color:#66CC66'>(</span>freeswitch.Dbh<span
style='color:#66CC66'>(</span><span style='color:red'>"dsn"</span>,<span
style='color:red'>"user"</span>,<span style='color:red'>"pass"</span><span
style='color:#66CC66'>))</span><o:p></o:p></pre><pre> <o:p></o:p></pre><pre><span
style='color:#B1B100'>assert</span><span style='color:#66CC66'>(</span>dbh:query<span
style='color:#66CC66'>(</span><span style='color:red'>"select 1 as foo, 2 as bar"</span>, <span
style='color:#B1B100'>function</span><span style='color:#66CC66'>(</span>row<span
style='color:#66CC66'>)</span> <o:p></o:p></pre><pre> <span
style='color:#B1B100'>for</span> key, val <span style='color:#B1B100'>in</span> <span
style='color:#B1B100'>pairs</span><span style='color:#66CC66'>(</span>row<span
style='color:#66CC66'>)</span> <span style='color:#B1B100'>do</span><o:p></o:p></pre><pre> stream:<span
style='color:#B1B100'>write</span><span style='color:#66CC66'>(</span><span
style='color:#B1B100'>string.format</span><span style='color:#66CC66'>(</span><span
style='color:red'>"%25s : %s</span><b><span style='color:#000099'>\n</span></b><span
style='color:red'>"</span>, key, val<span style='color:#66CC66'>))</span><o:p></o:p></pre><pre> <span
style='color:#B1B100'>end</span><o:p></o:p></pre><pre> stream:<span
style='color:#B1B100'>write</span><span style='color:#66CC66'>(</span><span
style='color:red'>"</span><b><span style='color:#000099'>\n</span></b><span
style='color:red'>"</span><span style='color:#66CC66'>)</span><o:p></o:p></pre><pre><span
style='color:#B1B100'>end</span><span style='color:#66CC66'>))</span><o:p></o:p></pre><pre> <o:p></o:p></pre><pre><span
style='color:#B1B100'>assert</span><span style='color:#66CC66'>(</span>dbh:release<span
style='color:#66CC66'>())</span><o:p></o:p></pre></div>
<div>
<p class=MsoNormal><o:p> </o:p></p>
</div>
<div><pre>freeswitch@fs-dev> hammer 1000 lua test_lua_dbh.lua<o:p></o:p></pre><pre>+OK Finished in 2723 ms (min 1 ms, max 25 ms)<o:p></o:p></pre></div>
<div>
<p class=MsoNormal><o:p> </o:p></p>
</div>
<div>
<p class=MsoNormal>As you can see, with these tests the freeswitch.Dbh is about
14 times as fast as luasql.odbc. Of course this test is single threaded (the
scripts are executed serially), so you'd get different results in real-world,
and if you have 'long' running scripts with many queries on the same handle,
the results would even out a bit.<o:p></o:p></p>
</div>
<div>
<p class=MsoNormal><o:p> </o:p></p>
</div>
<div>
<p class=MsoNormal>(btw mod_hammer is a simple app in contrib/ledr/c that
executes another app n times and shows how long it took)<o:p></o:p></p>
</div>
<div>
<p class=MsoNormal><o:p> </o:p></p>
</div>
<div>
<p class=MsoNormal>I just tried compiling LuaSQL with Postgres driver to do the
same tests, but that borked. Maybe you can give results of that ? I am curious
how it behaves.<o:p></o:p></p>
</div>
<div>
<p class=MsoNormal><o:p> </o:p></p>
</div>
<div>
<p class=MsoNormal>About having a native driver or odbc: odbc is just an
abstraction layer which uses the native drivers itself, so I'd reckon it should
be almost as fast - correct me if I'm wrong ?<o:p></o:p></p>
</div>
<div>
<p class=MsoNormal><o:p> </o:p></p>
</div>
<div>
<p class=MsoNormal>I wrote some more about these tests on my blog at <a
href="http://www.toyos.nl">http://www.toyos.nl</a><o:p></o:p></p>
</div>
<div>
<p class=MsoNormal><o:p> </o:p></p>
</div>
<div>
<p class=MsoNormal>Kind regards,<o:p></o:p></p>
</div>
<div>
<p class=MsoNormal><o:p> </o:p></p>
</div>
<div>
<p class=MsoNormal>Leon<o:p></o:p></p>
</div>
<div>
<p class=MsoNormal><o:p> </o:p></p>
</div>
<div>
<p class=MsoNormal><o:p> </o:p></p>
</div>
<div>
<p class=MsoNormal>On Sep 16, 2010, at 8:52 PM, Bernhard Suttner wrote:<o:p></o:p></p>
</div>
<p class=MsoNormal><br>
<br>
<o:p></o:p></p>
<div>
<p class=MsoNormal>Hi,<br>
<br>
from performance point of view, what should be used to connect to many
different databases, luasql or freeswitch.dbh which does use ODBC?<br>
<br>
The problem is, that is not only one database but 3-5 databases which are on
2-3 different hosts. Lua will be used to fetch the directory and dialplan
directly from the db. Also live-data will be stored on the different databases.
The advantage of freeswitch.dbh would be, that the db connections will be
re-used. LUAsql would use the native e.g. postgres or mysql driver.<br>
<br>
Thanks for any hint. <br>
<br>
Best Regards,<br>
Bernhard<br>
<br>
_______________________________________________<br>
FreeSWITCH-dev mailing list<br>
<a href="mailto:FreeSWITCH-dev@lists.freeswitch.org">FreeSWITCH-dev@lists.freeswitch.org</a><br>
http://lists.freeswitch.org/mailman/listinfo/freeswitch-dev<br>
UNSUBSCRIBE:http://lists.freeswitch.org/mailman/options/freeswitch-dev<br>
http://www.freeswitch.org<o:p></o:p></p>
</div>
</div>
<p class=MsoNormal><o:p> </o:p></p>
</div>
</div>
</body>
</html>