>
> Thanks Again Alan:
>
> I've never tried stored procedures, but I've been reading about
> PSQL in Helen's book. I'll try to code this on the server side
> tonight.
>
> > what calc are you doing on this index field?
>
> I'm glad you asked. Every new row will receive a 64 bit key
> from a generator named for the table that has the new row. I
> want to assign a number indicating an alphabetical sort order to
> each row. The sort order is referred to in an ascending and a
> descending index.
>
> Records are going to be added regularly, so I want an index
> scheme that allows me to assign a sort order to a new row that
> often needs to fit between the sort order of two existing rows.
> So I've defined a constant of my C++ code called MAX_INT_64 and
> assigned it the max value of a signed 64 bit int.
>
> The first item inserted into the database will have a sort
> order of 1/2 of MAX_INT_64's value, and the next item will have a
> sort order of either 1/4 or 3/4 of MAX_INT_64. And so on...
>
> This should give me many inserts before a collision on the
> sort order, but such a thing is possible if 64 sorted rows are
> imported. A collision will result in re-calculating the sort
> order values.
>
> Re-indexing the alpha sort order will involve assigning
> new sort order values to each row. These values will be evenly
> distributed from 0 to MAX_INT_64.
>
> The sort order is mine, because I don't believe an existing
> collation will give me what I want.
>
> If there is a simpler method that will do what I want, I'm
> interested.
>
> > Updates are slower than selects especially if the cache already has the
> > records you are retrieving, it's still hard to understand the exact
> process
> > you are using.
>
> Having the records in cache slows upates?
>
> > What language?
>
> The language is C++. "STL vector" is a Standard Template
> Library vector. Everything is dynamic SQL passed to Firebird
> through IBPP's Execute().
>
> > What connectivity components?
>
> Connectivity? Superserver 2.0.1 running on the same machine
> as the app. The DatabaseFactory() used to open the database is
> passed a blank text for the server, and the database path and
> name is identified as "C:\....".
>
> Does that answer your question? I'm not sure.
>
> > What metadata do you have?
>
> I'm not sure what the term "Metadata" means.
>
> Here is some code from CREATE TABLE SQL:
>
> CREATE TABLE CON
> (
> CON_KEY BIGINT NOT NULL, /* 64 Bit */
> ...
> CON_ALPHA_SORT_ORDER BIGINT NOT NULL, /* This is the index */
> /* I'm building. */
> ...
> CON_SORT_NAME VARCHAR(1024) NOT NULL, /* Encrypted last, */
> /* first, M.I. */
> ...
> PRIMARY KEY
> (
> CON_KEY
> )
> };
>
> The contact table also has the following indexes defined.
>
> CREATE ASCENDING INDEX
> ASCENDING_ALPHA_INDEX
> ON
> CON (CON_ALPHA_SORT_ORDER);
>
> CREATE DESCENDING INDEX
> DESCENDING_ALPHA_INDEX
> ON
> CON (CON_ALPHA_SORT_ORDER);
>
> I know you are trying to help me, and I hope this
> answers your questions.
>
> All of the times I've presented in this thread were
> calculated from runs after the index had been altered to
> inactive.
>
> I've just added "NOT NULL" to CON_ALPHA_SORT_ORDER and
> CON_SORT_NAME. And re-built the database, with the old
> code that now puts "0" in the CON_ALPHA_SORT_ORDER column.
>
> That improved the updates to 1540 per second. Almost
> 5 times faster. Calculating and updating the index for
> 8000 records now takes 5.3 Seconds.
>
> That's a big improvement. Updating CON_ALPHA_SORT_ORDER
> now takes about 5 times as long as the SELECT on the key and
> sort name.
>
> Is this approaching the performance you would expect from
> a client side dynamic SQL modify of a single value? Or is
> there room for improvement?
>
> I'll try my first stored procedure to see what effect that
> has.
>
> I appreciate everybody's suggestions.
>
> Thanks
> Larry

so let me get this straight. The contact person's name never changes but you
want to regularly recalculate a field to imply a sort order over the
records. Forgive me but this all sounds wacky.
Are you sure there is no collation which would do all this with no overhead?
Alan