> No, it's WIN1252, and as far as I know WIN1252 is 8-bit. So I guess

that

> index should work then?

Not sure then. I only need to deal in plain ASCII, it was just
something that jumped into my head, the index limitation is about the
number of bytes per value not the number of characters which is a
gotcha when working out the limit. If it is in fact 1 byte/char, then
I would have thought your values will be at worst case 204 bytes, and
I am sure compression would come into play. I know FB 2 increases this
limit to 1/4 * page size (so a default page size of 4096 gives a max
index size of 1024 bytes). Of course as you are aware, it is in beta
so not for production, test only.

> >>UPDATE "Master"
> >>SET "Info" = :Caption
> >>WHERE "Code" = :Code;

This query will benefit equally from

Master (Code)
Master (Code, Info)

But

Any index created against the Info field will need to be updated by
this query, so I imagine the (Code, Info) will be theoretically slower
due to this overhead.

> >>UPDATE "Master" M
> >>SET "Info" = "Info" || '; ' || :Caption
> >>WHERE EXISTS (
> >> SELECT 1
> >> FROM "Detail" D
> >> WHERE D."Id" = M."Id"
> >> AND D."Code" = :Code
> >> )
> >> AND M."Info" IS NOT NULL;

Either of the following indices will be equally useful for the exists
check

Detail (ID, Code)
Detail (Code, ID)

Now assuming you did not have the 'AND M."Info" IS NOT NULL' line, how
many records would be updated (worst case)? If it is only a couple,
then I imagine a table scan over these records will be faster than the
overhead of reading the index. If a lot, then the following index may
be useful

Master (Info)

Of course this index would need to be maintained in your first update
query which may or may not be a consideration.

If you are inside a stored procedure or trigger, then I imagine the
following logic would be faster providing.

FOR SELECT D."Id"
FROM "Detail" D
WHERE D."Code" = :Code
INTO :ID
DO
BEGIN
UPDATE "Master" M
SET "Info" = "Info" || '; ' || :Caption
WHERE M."Id" = :ID
AND M."Info" IS NOT NULL;
END

> >>
> >>UPDATE "Master" M
> >>SET "Info" = :Caption
> >>WHERE EXISTS (
> >> SELECT 1
> >> FROM "Detail" D
> >> WHERE D."Id" = M."Id"
> >> AND D."Code" = :Code
> >> )
> >> AND M."Info" IS NULL;

This is the same except for the AND clause, so the same index will be
useful.

> >>
> >>"Master" ("Id") is primary key.
> >>
> >>"Detail" ("Id", "Code") should be primary key but I just noticed
> >>that it isn't.
> >
> > Hmmm, that wont work. Code/Detail is the same as Detail/Code in
> > uniqueness. Judging from the selectivity of Detail(Code, ID) below,
> > you have duplicates and lots of them. Unless you mean that it should
> > be an ascending index??
>
> What do you mean "wont work"? There are no duplicates in ("Id", "Code").
>
> >>"Detail" ("Code", "Id") has a non-unique ascending index (selectivity
> >>0.002433).
> >>
> >>"Detail" contains up to about five records for each "Master".
> >

How can

Detail(ID, Code) be unique

yet

Detail(Code, ID) be non unique?

> I could just test
> them all and see what performance I get...

I find this method is persistently the most accurate ;)

Adam