Most Effective way to insert into an Indexed field

[william humphrey]

Chris

Can you post here an example of your "Commit to database" SQL call for
Livecode? I've been having trouble with that.

Bill

On Mon, Nov 18, 2013 at 11:12 AM, Chris Jones <cjones at daz3d.com> wrote:
> 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
>
> _______________________________________________
> Valentina mailing list
> Valentina at lists.macserve.net
> http://lists.macserve.net/mailman/listinfo/valentina
>



-- 
http://www.bluewatermaritime.com