--- In firebird-support@yahoogroups.com, Michael Weissenbacher
<mw@...> wrote:

>
> having looked over your post i have two questions:
> - Why don't you use FIRST/SKIP syntax and instead submitting something

I had never heard of skip before but I just googled it and it seems
it's not useful for my purpose because something like "skip X" means
"skip the first X records" but what i need is "those records whose id
is smaller than X"; which are not necessarily (even probably) X
records in numbers

> like a starting ID. I've used that in many applications and it usually
> runs pretty fast.
> - Have you tried looking at the PLAN's of the queries you do?
> Understanding PLANS's and adding the approriate indexes is the key to
> speeding up things considerably.

i get a plan every time i launch a query in ibexpert (well, at least
as long as my trial period hasn't run out ;-) ) and even though i
don't really understand these plans, i thought i wouldn't have to
worry about it because

1.) i have all my queries formulated in sp's; so every time i launch a
query the only type of query i do is "select * from procedure_name" in
a few cases followed by "order by field_name"

2.) this is the plan i get from ibexpert after launching
"select * from get_next_20_dicentries_by_asdsk('kfz',40)":

Plan
PLAN JOIN (JOIN (JOIN (JOIN (JOIN (JOIN (JOIN (D INDEX
(RDB$PRIMARY1),ASMAIN INDEX (RDB$PRIMARY1)),ZSMAIN INDEX
(RDB$PRIMARY1)),A INDEX (PK_AUT)),UA INDEX (PK_AUT)),ASLIT INDEX
(PK_LIT)),ZSLIT INDEX (PK_LIT)))

Adapted Plan
PLAN JOIN (JOIN (JOIN (JOIN (JOIN (JOIN (JOIN (D INDEX
(INTEG_16),ASMAIN INDEX (INTEG_16)),ZSMAIN INDEX (INTEG_16)),A INDEX
(PK_AUT)),UA INDEX (PK_AUT)),ASLIT INDEX (PK_LIT)),ZSLIT INDEX (PK_LIT)))

i don't understand that but it looks to me like there's an index for
just about everything needed (i did have a couple thoughts before
about which indices i might need ;)

> - If all fails, you could just define something like "results table"
> which includes exactly the columns you expect from your query plus a
> "query id" column. You would generate this "query id" every time a user
> requests results and fill your results into the "results table" with the
> same "query id". Then you should be able to easily navigate very fast in
> this table via primary key and "query id". The user would only have to
> wait for the results the first time, not after paging. I hope this
> doesn't sound only glibberish to you ;)

now that sounds interesting; so you mean a table that has all the
result fields ready for each and every record of the main table? that
would increase the amount of space needed for the database
considerably, right? not that it would be a problem..and it would have
to be updated every time the other tables change, too...
i think i should give it a try..where can i read up on this?

kind regards,

martin