--- In firebird-support@yahoogroups.com, "Alan McDonald" <alan@...> wrote:

>
> >
> > 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?

Thanks Again Alan:

I want a case insensitive collation that will place all
non-alpha characters before alpha characters. I'm not too
concerned where numbers are, as long as they are in order
and proceed alphas.

Alexandre Benson Smith suggested I try PT_BR, but I can't
find a chart that shows me the collation order. Do the
alphas follow all non-alphas?

What does the column definition look like in the CREATE
TABLE SQL?

There are different collations, so there must be methods
to define them. Is this something a mortal can do? Or is
this a task only for the Firebird Gods?

The contact person's name might change, but it would be
uncommon.

I'd prefer to never re-index the sort order, and if
random names are entered, I don't think I'd ever have to.

The default charset for the database is UTF8. I like the
potential this offers for internationalizing the application.
Though the immediate audience will be English speakers.

Again, and again and again, I do appreciate your advice.

And yes, my approach may be wacky. Help me see the light.

Thanks
Larry