<div dir="ltr">Here is the user function I was trying to make:<div><br></div><div><div>CREATE OR REPLACE FUNCTION kywdCFV1 ( keyWord VARCHAR )</div><div>RETURNS ULONG</div><div>BEGIN</div><div><span class="" style="white-space:pre"> </span>DECLARE keyRecID ULONG DEFAULT 0;</div>
<div><span class="" style="white-space:pre"> </span></div><div><span class="" style="white-space:pre"> </span>SELECT RecID INTO keyRecID FROM tblKeyword WHERE fldKeyword=keyWord LIMIT 1;</div><div><span class="" style="white-space:pre"> </span>IF keyRecID>0 THEN</div>
<div><span class="" style="white-space:pre"> </span>RETURN keyRecID;</div><div><span class="" style="white-space:pre"> </span>ELSE</div><div><span class="" style="white-space:pre"> </span>INSERT INTO tblKeyword (fldKeyword) VALUES (keyWord);</div>
<div><span class="" style="white-space:pre"> </span>RETURN Last_RecID_Of_Table( 'tblKeyword' );</div><div><span class="" style="white-space:pre"> </span>END IF</div><div><span class="" style="white-space:pre"> </span></div>
<div><span class="" style="white-space:pre"> </span>RETURN 0;</div><div>END</div></div><div><br></div><div>This is on a "tblKeyword" that has one column "fldKeyword". fldKeyword is a VarChar(2044), indexed, unique, with kSecondary collation. I was running my test on it when it had 300 thousand or so records. </div>
<div><br></div><div>Executing the sql: "SELECT RecID FROM tblKeyword WHERE fldKeyword='keywordValue' LIMIT 1;" takes at couple milliseconds or less.</div><div>Executing the user function : "SELECT kywdCFV1('keywordValue');" takes 200 to 300 milliseconds. If the 'keywordValue' value is there. It takes 300+ milliseconds if 'keywordValue' value is not there and needs to be inserted. However after it cause an insert, it takes 2-3 seconds to execute "SELECT kywdCFV1('keywordValue');" on the same value. Until I close the database or run the sql: "Commit". Then it returns to the 200 to 300 ms range.</div>
<div><br></div><div><br></div></div><div class="gmail_extra"><br><br><div class="gmail_quote">On Mon, Nov 18, 2013 at 7:40 AM, Chris Jones <span dir="ltr"><<a href="mailto:cjones@daz3d.com" target="_blank">cjones@daz3d.com</a>></span> wrote:<br>
<blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><div dir="ltr">Hi <span style="color:rgb(80,0,80);font-family:arial,sans-serif;font-size:13.333333969116211px">Ruslan</span>,<div>
<br></div><div>Yes our users work on localhost. Sorry if I was unclear about that earlier.</div>
</div><div class="gmail_extra"><div><div class="h5"><br><br><div class="gmail_quote">On Mon, Nov 18, 2013 at 2:36 AM, Ruslan Zasukhin <span dir="ltr"><<a href="mailto:ruslan_zasukhin@valentina-db.com" target="_blank">ruslan_zasukhin@valentina-db.com</a>></span> wrote:<br>
<blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><div>On 11/16/13, 7:09 PM, "Chris Jones" <<a href="mailto:cjones@daz3d.com" target="_blank">cjones@daz3d.com</a>> wrote:<br>
<br>
> However, I got some weird results testing this plan in Valentina Studio. <br>
> First, this is all done on local host, so latency is not as big an issue as it<br>
> could be. However our users all use this database on localhost too, so this<br>
> is the correct enviroment. <br>
<br>
</div>Hi Chris,<br>
<br>
I want to clarify this -- so your users work<br>
<br>
app - vserver on localhost ?<br>
<br>
You told before it seems - remove vserver,<br>
and 100 records take time one by one<br>
<div><br>
<br>
<br>
--<br>
Best regards,<br>
<br>
Ruslan Zasukhin<br>
VP Engineering and New Technology<br>
</div>Paradigma Software, Inc<br>
<div><br>
Valentina - Joining Worlds of Information<br>
<a href="http://www.paradigmasoft.com" target="_blank">http://www.paradigmasoft.com</a><br>
<br>
[I feel the need: the need for speed]<br>
<br>
<br>
</div><div><div>_______________________________________________<br>
Valentina mailing list<br>
<a href="mailto:Valentina@lists.macserve.net" target="_blank">Valentina@lists.macserve.net</a><br>
<a href="http://lists.macserve.net/mailman/listinfo/valentina" target="_blank">http://lists.macserve.net/mailman/listinfo/valentina</a><br>
</div></div></blockquote></div><br><br clear="all"><div><br></div></div></div><div class="im">-- <br>Christopher Jones<br>Software Developer<br>DAZ 3D<br>
</div></div>
</blockquote></div><br><br clear="all"><div><br></div>-- <br>Christopher Jones<br>Software Developer<br>DAZ 3D<br>
</div>