At 10:44 AM 20/06/2003 +0000, you wrote:

>--- In IBObjects@yahoogroups.com, Helen Borrie <helebor@t...> wrote:
> > In the dataset objects:
> > Change Open to First
> > Use Next to step through the rows
>
>A few more questions Helen,
>Probably I can use ApiFirst ApiNext, as there is no GUI ?

First and Next are not GUI operations, they are dataset operations. Since
you don't have a buffered dataset, why do you think ApiFirst and ApiNext
would be better?

>In the case that there are many TIB_Cursors in the application (1 for
>every database table which counts up to ~ 100 per application)
>should I close or unprepare after getting in the resultset ?

a) you don't close ib_cursors. You shouldn't unprepare them if you are
likely to use them again. What you *should* do is ensure that you don't
keep any transactions open holding cursors that you've finished working with.

>What is for the TIB_Cursor the difference between close and unprepare
>anyway ?

Not a valid question. You don't close ib_cursors since, once you reach
EOF, there is nothing left to close. Same as you don't use Open, but
First, which causes the row buffer to go from "empty" (BOF) to the first
row in the queue.

Prepare is a costly operation that only needs to be done once if you are
reusing the same statement over and over. You should where clauses with
parameters, not reinvent the same SQL property over and over with nothing
changing except the where criteria.

>Or would it be better to let the cursor stay open until the next
>request and unprepare only if the SQL differs

If the SQL differs (which you can totally avoid if the only thing that ever
varies is the value of the where criteria) then the dataset unprepares the
dataset itself. First will cause it to prepare if it needs to.
If you have parameters, using the ParamByName() method will make it prepare
itself if it needs to, as well. Using the Params[] array, you need to call
Prepare yourself before applying values to each Params[] member.

>If the SQL is equal at the new request you would thus gain
>the "prepare time", but maybe this is very small anyway ?

Understand the difference between "a whole new SQL assignment" and the
assignment of values to parameters. And, no, "prepare time" is a
complicated activity that a lot of Delphi programmers waste a lot of
resource on by causing it to happen unnecessarily.

>Isn't the prepare time the time needed for the server to compile the
>SQL ?

No. SQL isn't compiled. Prepare causes the client (gds32.dll) to ask for
validation of the statement. The server parses the statement and returns
either an error, or a structure containing metadata information. Then the
data access engine (in this case, IBO) takes over and builds a whole bunch
of structures based on that metadata. So, you see, if you make it do that
every time you query, you are thrashing the server unnecessarily.

>But perhaps the compiled SQL is cached by Interbase/Firebird ?

There's no "compiled SQL". The server caches record versions (read about
the multi-generational architecture if you want to know about this). Its
the structures in the client app that does things to preserve information
about the statement.

>And therefore the time difference is only noticable the first time
>that you prepare a SQL statement ?

Yes: provided your application isn't making it prepare every time a query
is called. If you are doing MyQuery.SQL.Add('a bit of SQL') then you are
causing an Unprepare and a Prepare.

Also, if you have 100 queries and you open them all at once (instead of
opening each one just when you need it) then this will cause a mega-lag
when the app is opened.

>On the other hand, letting many TIB_Cursors stay open would spoil a
>lot of memory and IB server recources ?

IB_Queries stay open until you close them. IB_Cursors stay open until you
reach EOF. So, if you call First on an IB_Cursor and then just let it sit
there doing nothing, it's eating something - but nothing like as much as an
ib_query, which is holding cursors open to feed a scrolling buffer.

If all you are doing with your data is walking through them and converting
them to HTML, you can just do what I call a "hit-and-run" job on them -
open a transaction, grab the data you want and then end the
transaction. Keeping the prepared statement in memory is a lot less costly
than causing an unprepare and prepare every time. You have a server
application that's competing with the server for resources so, provided you
avoid the buffered datasets, the stuff preserved in the prepared structures
in tiny compared to the noise of going through the unprepare/prepare cycle
unnecessarily.

On the other hand, if you have a set that you only have to get once in the
session, then by all means unprepare it.

This is starting to turn into a manual! Have you ever thought about
working your way through the TI sheets on the web site?

Helen