Hi Larry!

Using IBO for quite some time, I've become used to proxy columns containing the uppercase equivalent of case-sensitive columns, and I think a similar idea may help you in your situation. The below code is written using Microsoft Outlook, which is lousy at preparing Firebird code. Hence, it will contain errors, but hopefully you get the idea:

CREATE TABLE CON(
ID INTEGER NOT NULL PRIMARY KEY,
LarryField VARCHAR(32),
LarrySortField VARCHAR(64));

CREATE TRIGGER PopulateLarrySortField ON CON
BEFORE INSERT OR UPDATE
AS
DECLARE VARIABLE Counter INTEGER;
DECLARE VARIABLE LarryLength INTEGER;
DECLARE VARIABLE TempString CHAR(1)
BEGIN
IF (NEW.LarryField IS NOT NULL) THEN
BEGIN
LarryLength = LENGTH(NEW.LarryField);
NEW.LarrySortField = '';
Counter = 1;
WHILE (Counter <= LarryLength) DO
BEGIN
TempString = SUBSTRING(LarryField FROM :LarryLength FOR 1);
IF NOT (TempString BETWEEN 'a' and 'z'
OR TempString BETWEEN 'A' and 'Z') THEN
NEW.LarrySortField = NEW.LarrySortField || '?';
//'?' representing the sign of your choice sorting before characters
NEW.LarrySortField = NEW.LarrySortField || TempString;
Counter = Counter + 1;
END
END
END;

Hence, if you had something like (assuming '@' being before characters, and '$' being after):

LarryField

'L@rryFieldStringSortsBefore?'
'LarryFieldString'
'LarryField$tringSortsAfter?'

you would get:

LarrySortField

'L?@rryFieldStringSortsBefore?'
'LarryField?$tringSortsAfter?'
'LarryFieldString'

This way, you could achieve sorting (on the proxy column LarrySortField) any way you like without ever having to modify other records than the record in question.

HTH,
Set

-----Original Message-----
From: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] On Behalf Of inoffensive_2006
Sent: 5. september 2007 08:51
To: firebird-support@yahoogroups.com
Subject: [firebird-support] Re: Should updates be this slow?

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






++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Visit http://www.firebirdsql.org and click the Resources item
on the main (top) menu. Try Knowledgebase and FAQ links !

Also search the knowledgebases at http://www.ibphoenix.com

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Yahoo! Groups Links