Most Effective way to insert into an Indexed field

[Chris Jones]

Sorry I don't use Livecode.  I just executed the sql command "Commit".


On Mon, Nov 18, 2013 at 9:06 AM, william humphrey <
bill at bluewatermaritime.com> wrote:

> 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
> _______________________________________________
> Valentina mailing list
> Valentina at lists.macserve.net
> http://lists.macserve.net/mailman/listinfo/valentina
>



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