Most Effective way to insert into an Indexed field

[Chris Jones]

Here is the user function I was trying to make:

CREATE OR REPLACE FUNCTION kywdCFV1 ( keyWord VARCHAR )
RETURNS ULONG
BEGIN
DECLARE keyRecID ULONG DEFAULT 0;
 SELECT RecID INTO keyRecID FROM tblKeyword WHERE fldKeyword=keyWord LIMIT
1;
IF keyRecID>0 THEN
RETURN keyRecID;
ELSE
INSERT INTO tblKeyword (fldKeyword) VALUES (keyWord);
RETURN Last_RecID_Of_Table( 'tblKeyword' );
END IF
 RETURN 0;
END

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.

Executing the sql: "SELECT RecID FROM  tblKeyword WHERE
fldKeyword='keywordValue' LIMIT 1;" takes at couple milliseconds or less.
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.




On Mon, Nov 18, 2013 at 7:40 AM, Chris Jones <cjones at daz3d.com> wrote:

> Hi Ruslan,
>
> Yes our users work on localhost.  Sorry if I was unclear about that
> earlier.
>
>
> On Mon, Nov 18, 2013 at 2:36 AM, Ruslan Zasukhin <
> ruslan_zasukhin at valentina-db.com> wrote:
>
>> On 11/16/13, 7:09 PM, "Chris Jones" <cjones at daz3d.com> wrote:
>>
>> > However, I got some weird results testing this plan in Valentina
>> Studio.
>> > First, this is all done on local host, so latency is not as big an
>> issue as it
>> > could be.  However our users all use this database on localhost too, so
>> this
>> > is the correct enviroment.
>>
>> Hi Chris,
>>
>> I want to clarify this -- so your users work
>>
>>     app - vserver on localhost  ?
>>
>> You told before it seems - remove vserver,
>> and 100 records take time one by one
>>
>>
>>
>> --
>> Best regards,
>>
>> Ruslan Zasukhin
>> VP Engineering and New Technology
>> Paradigma Software, Inc
>>
>> Valentina - Joining Worlds of Information
>> http://www.paradigmasoft.com
>>
>> [I feel the need: the need for speed]
>>
>>
>> _______________________________________________
>> Valentina mailing list
>> Valentina at lists.macserve.net
>> http://lists.macserve.net/mailman/listinfo/valentina
>>
>
>
>
> --
> Christopher Jones
> Software Developer
> DAZ 3D
>



-- 
Christopher Jones
Software Developer
DAZ 3D
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.macserve.net/pipermail/valentina/attachments/20131118/a0215694/attachment.html>