Hi all,

> But 300K records is tiddly. With bad indexing and inefficient
> queries, sure, even querying 300K could be a dog. If your prof knows
> his stuff, he will see straight through the "blame the hardware"
> argument. I'm not a prof, but I can see through it already. Don't

Well I was not trying to *blame* the hardware; I was actually thinking
that it's the real reason; I can tell you what kinds of queries I'm
talking about and maybe you tell me whethere I'm doing anything in a
way that it could be done better:

1) imagine a database with a table called 'dicentries' that has one
primary key and couple more fields. now my prof said he needed the
grid component (yes i'm working with delphi; and yes i know grids are
not good for server-client apps)..so in order to not have firebird
send the client the content of the whole database, what i did was send
the database in slices of 20 records; i.e. you open the database, you
get your (id-wise) first 20 entries. if you push page-down, the client
requests the next 20; if you push page up, you get your prior 20..i
have written 2 procedures for that, which are as follows:

CREATE PROCEDURE GET_NEXT_20_DICENTRIES_BY_ID(
IDINPUT BIGINT)
RETURNS (
DATUM DATE,
AUTOR VARCHAR(31),
ZSQUELLE VARCHAR(15),
ZSDEF BLOB SUB_TYPE 1 SEGMENT SIZE 4096,
ASQUELLE VARCHAR(15),
ZSSEM VARCHAR(80),
UPDAUTOR VARCHAR(31),
ZSQCODE BIGINT,
ZSPRGM VARCHAR(20),
ASQCODE BIGINT,
ZSVERW VARCHAR(80),
ZSABK VARCHAR(10),
UPDDATUM DATE,
ZSAUDIO VARCHAR(20),
ASVERW VARCHAR(80),
ZSTERM VARCHAR(80),
ZSVIDEO VARCHAR(20),
ZSABBILDUNG VARCHAR(20),
ASVIDEO VARCHAR(20),
ASABBILDUNG VARCHAR(20),
PROJ VARCHAR(20),
ASDEF BLOB SUB_TYPE 1 SEGMENT SIZE 4096,
ASAUDIO VARCHAR(20),
REV VARCHAR(1),
ASSEM VARCHAR(80),
ASPRGM VARCHAR(20),
ASABK VARCHAR(10),
ASTERM VARCHAR(80),
ID BIGINT,
AUT BIGINT,
UPDAUT BIGINT)
AS
begin
for
select first 20 d.id, d.asterm, d.asabk, d.asprgm, d.assem, d.zsterm,
d.zsabk, d.zsprgm, d.zssem, d.datum, d.proj, d.rev, d.upddatum,
d.asdef,
d.zsdef, d.asaudio, d.asvideo, d.asabbildung, d.zsabbildung,
d.zsaudio,
d.zsvideo, asmain.asterm, zsmain.zsterm, a.aut, ua.aut, aslit.qcode,
zslit.qcode, d.asqcode, d.zsqcode, d.aut, d.updaut
from dicentries d
left join dicentries asmain on d.asverw = asmain.id
left join dicentries zsmain on d.zsverw = zsmain.id
left join aut a on d.aut = a.id
left join aut ua on d.updaut = ua.id
left join lit aslit on d.asqcode = aslit.id
left join lit zslit on d.zsqcode = zslit.id
where d.id >= :idinput order by d.id ascending
into :id, :asterm, :asabk, :asprgm, :assem, :zsterm, :zsabk, :zsprgm,
:zssem, :datum, :proj, :rev, :upddatum, :asdef, :zsdef, :asaudio,
:asvideo,
:asabbildung, :zsabbildung, :zsaudio, :zsvideo, :asverw, :zsverw,
:autor,
:updautor, :asquelle, :zsquelle, :asqcode, :zsqcode, :aut, :updaut
do
suspend;

end

CREATE PROCEDURE GET_PRIOR_20_DICENTRIES_BY_ASTE(
ASTERMINPUT VARCHAR(80))
RETURNS (
DATUM DATE,
AUTOR VARCHAR(31),
ZSQUELLE VARCHAR(15),
ZSDEF BLOB SUB_TYPE 1 SEGMENT SIZE 4096,
ASQUELLE VARCHAR(15),
ZSSEM VARCHAR(80),
UPDAUTOR VARCHAR(31),
ZSQCODE BIGINT,
ZSPRGM VARCHAR(20),
ASQCODE BIGINT,
ZSVERW VARCHAR(80),
ZSABK VARCHAR(10),
UPDDATUM DATE,
ZSAUDIO VARCHAR(20),
ASVERW VARCHAR(80),
ZSTERM VARCHAR(80),
ZSVIDEO VARCHAR(20),
ZSABBILDUNG VARCHAR(20),
ASVIDEO VARCHAR(20),
ASABBILDUNG VARCHAR(20),
PROJ VARCHAR(20),
ASDEF BLOB SUB_TYPE 1 SEGMENT SIZE 4096,
ASAUDIO VARCHAR(20),
REV VARCHAR(1),
ASSEM VARCHAR(80),
ASPRGM VARCHAR(20),
ASABK VARCHAR(10),
ASTERM VARCHAR(80),
ID BIGINT,
AUT BIGINT,
UPDAUT BIGINT)
AS
begin
for
select first 20 d.id, d.asterm, d.asabk, d.asprgm, d.assem, d.zsterm,
d.zsabk, d.zsprgm, d.zssem, d.datum, d.proj, d.rev, d.upddatum,
d.asdef,
d.zsdef, d.asaudio, d.asvideo, d.asabbildung, d.zsabbildung,
d.zsaudio,
d.zsvideo, asmain.asterm, zsmain.zsterm, a.aut, ua.aut, aslit.qcode,
zslit.qcode, d.asqcode, d.zsqcode, d.aut, d.updaut
from dicentries d
left join dicentries asmain on d.asverw = asmain.id
left join dicentries zsmain on d.zsverw = zsmain.id
left join aut a on d.aut = a.id
left join aut ua on d.updaut = ua.id
left join lit aslit on d.asqcode = aslit.id
left join lit zslit on d.zsqcode = zslit.id
where d.asterm < :asterminput order by d.asterm descending
into :id, :asterm, :asabk, :asprgm, :assem, :zsterm, :zsabk, :zsprgm,
:zssem, :datum, :proj, :rev, :upddatum, :asdef, :zsdef, :asaudio,
:asvideo,
:asabbildung, :zsabbildung, :zsaudio, :zsvideo, :asverw, :zsverw,
:autor,
:updautor, :asquelle, :zsquelle, :asqcode, :zsqcode, :aut, :updaut
do
suspend;

end

I also have a couple other procedures that return the database 20-wise
which are very similar to these 2 procedures and I do have an
ascending and a descending index for those fields. Also note that
these procedures also return fields from other tables but be sure that
these are declared foreign fields, i.e. they're indexed. Now, as I
said, I have generated a test database where this one table has around
300T records and getting 20 records via these two procedures takes
around 3 seconds. Do you think that can be speeded up? I do need all
the fields so leaving some out is not an option..

So much for the easy queries. There's another table in the database
called 'dskrpts' (which might contain no more than around 30 records
in a real-world situation) which has 2 fields asdskrpt and zsdskrpt
(both varchar fields) and which is linked to 'dicentries' via a third
table 'dskrptlink_dicentries' (because there's a
many-to-many-relationship between 'dicentries' and 'dskrpts'..so:

dskrptlink_dicentries.id_dicentry is foreign key on dicentries.id
and
dskrptlink_dicentries.id_dskrpt is foreign key on dskrpts.id

Now the problem to solve:
PROCEDURE GET_NEXT_20_DICENTRIES_BY_ASDSK(ASDSKS VARCHAR(10000),IDIN
BIGINT)

this procedure is supposed to return the first 20 records from
'dicentries' from 'IDIN' on that are linked to 'ASDSKS', but note:
ASDSKS is a string containing tokens separated by '.' meaning that
GET_NEXT_20_DICENTRIES_BY_ASDSK('abc.def.ghi', 5) must return the
first 20 records where 'dicentries.id' >= 5 and which are ALL linked
via 'dskrptlink_dicentries' to 'dskrpts.asdskrt' = 'abc',
'dskrpts.asdskrt' = 'def' and 'dskrpts.asdskrt' = 'ghi' (just an example)

to accomplish that, i've first written a procedure that accepts as
arguments 2 parameters one being idin and the other asdskrptinput;
this procedure is supposed to return me the next (ONE!) entry from
idin on that is linked to the SINGLE deskrpts.asdskrpt = ASDSKRPTINPUT:

CREATE PROCEDURE GET_NEXT_DICENTRY_BY_ASDSKRPT(
IDIN BIGINT,
ASDSKRPTINPUT VARCHAR(15))
RETURNS (
IDOUT BIGINT)
AS
begin
select first 1 dic.id
from dicentries dic
inner join dskrptlink_dicentries dl on dic.id = dl.id_dicentry
inner join dskrpts d on dl.id_dskrpt = d.id
where dic.id >= :idin
and dic.asverw is null /* if these 2 fields are not null then */
and dic.zsverw is null /* the entry can be discarded */
and d.asdskrpt = :asdskrptinput
into idout;
suspend;
end

Next, I've written GET_NEXT_BY_ALL_ASDSKR which relies on the before
procedure and which accepts idin and asdsks as arguments where asdsks
can already be such a tokenized string and which returns the next
record from idin on that is linked to all dskrpts.asdskrpt contained
as tokens in parameter asdsks

CREATE PROCEDURE GET_NEXT_BY_ALL_ASDSKR(
IDIN BIGINT,
ASDSKS VARCHAR(10000))
RETURNS (
IDOUT BIGINT)
AS
DECLARE VARIABLE TOKEN VARCHAR(10000);
DECLARE VARIABLE CNT BIGINT;
DECLARE VARIABLE TKNID INTEGER;
DECLARE VARIABLE IDTHIS BIGINT;
DECLARE VARIABLE IDNEXT BIGINT;
DECLARE VARIABLE IDOFLASTDICENTRY BIGINT;
begin
select count(id) from tokenize(:asdsks, '.') into cnt;
idthis = :idin;
select first 1 id from dicentries order by id descending into
idoflastdicentry;
tknid = 1;
while ((:tknid <= :cnt) and (:idthis <= :idoflastdicentry)) do
begin
select tkn from tokenize(:asdsks, '.') where id = :tknid into token;
idnext = null;
select idout from get_next_dicentry_by_asdskrpt(:idthis, :token)
into idnext;
if (:idnext is null) then
exit;
if (:idthis = :idnext) then
begin
if (:tknid = :cnt) then
begin
idout = :idnext;
suspend;
exit;
end
tknid = :tknid + 1;
end
else
begin
idthis = :idnext;
tknid = 1;
end
end
end

for tokenze (used in this procedure) see:
http://groups.yahoo.com/group/firebird-support/message/74352

and then i wrote GET_NEXT_20_DICENTRIES_BY_ASDSK which returns not the
next ONE but the next 20 recs from dicentries that meet this same
criterion:

CREATE PROCEDURE GET_NEXT_20_DICENTRIES_BY_ASDSK(
ASDSKS VARCHAR(10000),
IDIN BIGINT)
RETURNS (
DATUM DATE,
AUTOR VARCHAR(31),
ZSQUELLE VARCHAR(15),
ZSDEF BLOB SUB_TYPE 1 SEGMENT SIZE 4096,
ASQUELLE VARCHAR(15),
ZSSEM VARCHAR(80),
UPDAUTOR VARCHAR(31),
ZSQCODE BIGINT,
ZSPRGM VARCHAR(20),
ASQCODE BIGINT,
ZSVERW VARCHAR(80),
ZSABK VARCHAR(10),
UPDDATUM DATE,
ZSAUDIO VARCHAR(20),
ASVERW VARCHAR(80),
ZSTERM VARCHAR(80),
ZSVIDEO VARCHAR(20),
ZSABBILDUNG VARCHAR(20),
ASVIDEO VARCHAR(20),
ASABBILDUNG VARCHAR(20),
PROJ VARCHAR(20),
ASDEF BLOB SUB_TYPE 1 SEGMENT SIZE 4096,
ASAUDIO VARCHAR(20),
REV VARCHAR(1),
ASSEM VARCHAR(80),
ASPRGM VARCHAR(20),
ASABK VARCHAR(10),
ASTERM VARCHAR(80),
ID BIGINT,
AUT BIGINT,
UPDAUT BIGINT)
AS
DECLARE VARIABLE IDTRY BIGINT;
DECLARE VARIABLE CNT INTEGER;
begin
cnt = 0;
idtry = :idin;
while (0 = 0) do
begin
id = null;
select d.id, d.asterm, d.asabk, d.asprgm, d.assem, d.zsterm,
d.zsabk, d.zsprgm, d.zssem, d.datum, d.proj, d.rev, d.upddatum,
d.asdef,
d.zsdef, d.asaudio, d.asvideo, d.asabbildung, d.zsabbildung,
d.zsaudio,
d.zsvideo, asmain.asterm, zsmain.zsterm, a.aut, ua.aut, aslit.qcode,
zslit.qcode, d.asqcode, d.zsqcode, d.aut, d.updaut
from dicentries d
inner join get_next_by_all_asdskr(:idtry, :asdsks) t on t.idout
= d.id
left join dicentries asmain on d.asverw = asmain.id
left join dicentries zsmain on d.zsverw = zsmain.id
left join aut a on d.aut = a.id
left join aut ua on d.updaut = ua.id
left join lit aslit on d.asqcode = aslit.id
left join lit zslit on d.zsqcode = zslit.id
into :id, :asterm, :asabk, :asprgm, :assem, :zsterm, :zsabk,
:zsprgm,
:zssem, :datum, :proj, :rev, :upddatum, :asdef, :zsdef,
:asaudio, :asvideo,
:asabbildung, :zsabbildung, :zsaudio, :zsvideo, :asverw,
:zsverw, :autor,
:updautor, :asquelle, :zsquelle, :asqcode, :zsqcode, :aut, :updaut;
if (:id is null) then
break;
else
begin
cnt = :cnt + 1;
idtry = :id + 1;
suspend;
end
if (:cnt = 20) then
break;
end
end

Now, if I execute CREATE PROCEDURE
GET_NEXT_20_DICENTRIES_BY_ASDSK('abc', 30) it's still okay for
execution time but as you can probably imagine, the more tokens the
argument contains, the longer the search takes..so in my test database
with around 300000 recs in dicentries a query with three tokens like
GET_NEXT_20_DICENTRIES_BY_ASDSK('abc.def.ghi', 30) may take around 3
minutes (or even more, depending on where in the db the records are found)

> 3. You don't say what your application environment is, but BDE +
> paradox suggests you have Delphi in the picture somewhere. Are you
> still using the BDE now that you have switched the database to
> Firebird? Tell us more about it.

No, I'm not using the BDE any more. I use the standard ibx
components..i was told before to skip them and use other components
like uib but since i can tell you that my queries are just as slow
under ibexpert, you can be sure that my speed problem is not due to
the components i use.

just a bit more on what i'm doing: i'm not exactly doing an it course
but actually translation and interpreting which i combined with
computer science as a minor course (a twist of the traditional german
types of academic degrees allows you to combine your subjects nearly
at will given that the respective courses are available at your
college) and when i asked my translation prof to give me a theme for
my thesis and heard i'd done computer science as well he just asked me
if i knew how to program and i said i'd done a bit of it before so he
came up with this terminology system which he'd written himself in the
80s under dos and which a friend of his had ported to bde+paradox with
delphi in the 90s and asked me whether i was able to make it a
client-server application..so i said i'd give it a try but i'd never
actually had database programming before so all i know about databases
is what i taught myself in these past couple months so that's just why
i might sometimes ask not so intelligent questions..the downside is
this is all a lot more work than my mates have to do for their degree
but then on the other hand if i get it all well done and my prof feels
comfortable with the final app, he's very like to *like* me; not a bad
thing *lol

ok, hope at least some of you takes the time to have a look at what i
wrote here ;)

regards,

martin