Most Effective way to insert into an Indexed field

[Chris Jones]

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
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.macserve.net/pipermail/valentina/attachments/20131116/eda038e0/attachment-0001.html>