Most Effective way to insert into an Indexed field

[Chris Jones]

This makes me think of a feature request ( unless its already there then I
would love to use it).  I would love a mechanism that through the C++ ADK
that I could write a plug-in for the server.  The idea being a database
licensor would ship a plug-in with the server that provides one or more
"user" functions/procedures that the client can then call.  When the client
calls this function, the server would hand it off to the plug-in which
could do what it needed to the active database through ADK API calls
allowing quick powerful functionality and centralized logic.


On Sat, Nov 16, 2013 at 10:09 AM, Chris Jones <cjones at daz3d.com> wrote:

> Inserting many items is fine seems to be fine but because of the exception
> I need to make sure the items are not there before I need to add them.
> Thinking about this I thought I would just make a simple user function that
> checked to see if the item was there and returned its RecID if it was, or
> inserted it and then returned the new rec id.  I could then send a
> select/execute that executes a bunch of these at once and minimize the
> traffic between client and server.
>
> 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.  The simple query I was executing in the
> user function took 100s of milliseconds while just doing the check query
> only takes about a 1 ms.  Furthermore, if calling the function actually
> resulted in inserting an item into the table future calls to that function
> took full seconds to execute.  I did find that if later in the session I
> called "Commit" the function returned to its normal execution time, but in
> all it seems this takes longer than executing a query followed by an insert
> if it is needed.  Are user functions usually this slow? It seemed like a
> very simple function: a query, and an if that returns the query result or
> inserts and returns the last record id for that table.
>
>
> On Sat, Nov 16, 2013 at 1:30 AM, Ivan Smahin <
> ivan_smahin at paradigmasoft.com> wrote:
>
>>
>> On Nov 14, 2013, at 2:58 AM, Chris Jones <cjones at daz3d.com> wrote:
>>
>> Hi,
>>
>> I am running into a slow down when I try to insert about a 100 records
>> into a table.  The table has only one column, a var char, and has a unique
>> index on that column.  The test table I am working with has about 320000
>> records currently in it.  Also I am accessing the database as a client ( db
>> is running as a service).
>>
>> Is there a better way to do this than "AddRecord" on a table object?  I
>> need to ensure that the inserted items are unique and quickly searchable,
>> hence the unique index.  Thank you for any help.
>>
>>
>> One more possibility is to use row-constructor syntax:
>>
>> insert into t1 VALUES (1, '1'), (2, '2')
>>
>> It means - add 2 rows at once:
>>
>> 1, '1'
>> 2, '2'
>>
>> --
>> Best regards,
>> Ivan Smahin
>> Senior Software Engineer
>> Paradigma Software, Inc
>> Valentina - The Ultra-Fast Database
>> http://www.valentina-db.com
>>
>>
>> _______________________________________________
>> 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/20131116/4536e286/attachment.html>