> > The difference is ISQL is likely not preparing any
> > statement at all. It's probably just doing an execute
> > immediate command and displaying the result.
>
> Something like that is what i supose...
>
> > If you are using a statement over and over again then
> > it makes sense to have the cost of a prepare to be
> > able to repeat execution, but if you are using a
> > different statement each time then you don't need
> > to prepare it at all.
>
> That is my case... absolutely... different statements
> at the request of the user with some experts programmed
> in the app to "translate" the querys of the user to SQL.

I am guessing that you have a format that remains fairly consistent.
(Finger's crossed.) In which case, you could use SELECT stored procedure(s)
that receive the input criteria as parameters and then inside the stored
procedure you write code that branches down until you get to the actual
SELECT statement that meets the criteria entered and then it will return the
records requested. Some parameters would determine which statement to run
and other parameters would be as parameters in the statement itself.

I have done this with a web search application and there are actually select
procedures that call other select procedures and the number of select
statements are in the dozens. You could make hundreds of them. What this
does is centralize all the complexity of the record selection logic into a
simple interface. It also provides the benefit of the app having just one
simple query that has standardized input parameters and which allows
multiple applications to benefit. You could have a GUI app or a Web app
share this logic. Another benefit is the server stores all the "prepared"
data so that preparing the stored procedure is really just the server
pulling the pre-prepared BLR into memory. This should make the prepare time
go way down.

> > You can use the ExecuteImmediate function within IBO
> > to avoid preparing a statement.
>
> That would be great!!, but as i can see this function works with
> INSERT, UPDATE, DELETE statements, can i use something like that to
> work with cursors or datasets???

Unfortunately, no. You can only use statements that involve single input
and single output.

> > Perhaps you could tell me a bit more of what you are
> > doing so I can help you more.
>
> Sure!, i'm working in a kind of OLAP application, with a lot of
> querys readonly to allow select records of different relations and
> combinations of views with SP's, so is common to need to request
> different statements to the server at the time, so with every new
> statement a new (slow) prepare operation is triggered. The prepare
> statement is wath takes all the time, the data transfer is almost
> instantaneous in IBO, but the continuous re-prepare for arbitrarious
> statements is the problem.

It soulds like you are going in the right direction if you are already using
views and SP's. Your next step is to simply see if you can standardize a
stored procedure to handle all the foreseeable select statements and their
parameters into a standardized interface.

> here is a sample of one of my querys over the slow
> connection:
>
> /*---
> PREPARE STATEMENT
> TR_HANDLE = 3950112
> STMT_HANDLE = 3951160
>
> SELECT GES_ID
> , GES_NOMBRE
> , GES_TELEFONO
> , JUR_NOMBRE
> FROM AFI_GESTORES
> LEFT JOIN GEN_CIUDADES ON (GES_CIUDAD = CIU_ID)
> LEFT JOIN AFI_JURISDICCION ON (CIU_JURISDICCION = JUR_ID)
> WHERE (
> (GES_NOMBRE CONTAINING ? /* TEXTOBUSCADO */ ) or
> (JUR_NOMBRE CONTAINING ? /* TEXTOBUSCADO */ ))
>
> PLAN JOIN (JOIN (AFI_GESTORES NATURAL,GEN_CIUDADES INDEX
> (PK_GEN_CIUDADES)),AFI_JURISDICCION INDEX (PK_AFI_JURISDICCION))
>
> FIELDS = [ Version 1 SQLd 4 SQLn 30
> AFI_GESTORES.GES_ID = <NIL>
> AFI_GESTORES.GES_NOMBRE = <NIL>
> AFI_GESTORES.GES_TELEFONO = <NIL>
> AFI_JURISDICCION.JUR_NOMBRE[JUR_NOMBRE] = <NIL> ]
>
> SECONDS = 3.054
> ----*/
> /*===
> //>>> STATEMENT PREPARED <<<//
> TIB_Statement.API_Prepare()
> TIBOInternalDataset: "BGestores.IBOqrBGestores" stHandle=3951160
> ====*/
> /*---
> EXECUTE STATEMENT
> TR_HANDLE = 3950112
> STMT_HANDLE = 3951160
> PARAMS = [ Version 1 SQLd 2 SQLn 2
> [TEXTOBUSCADO] = ''
> [TEXTOBUSCADO] = '' ]
>
> SECONDS = 0.120
> ----*/
> /*---
> FETCH
> STMT_HANDLE = 3951160
> FIELDS = [ Version 1 SQLd 4 SQLn 4
> AFI_GESTORES.GES_ID = 2
> AFI_GESTORES.GES_NOMBRE = 'DRA. ANA LUISA TEJEDA'
> AFI_GESTORES.GES_TELEFONO = '044 686 569 9364'
> AFI_JURISDICCION.JUR_NOMBRE[JUR_NOMBRE] = 'MEXICALI' ]
>
> SECONDS = 0.060
> ----*/
>
>
> as you can see the prepare time is of 3.054 seconds, but the data-
> transfer time is just of a few miliseconds, if i could just avoid to
> prepare my querys with something like "OpenImmediate" would be
> great!!, i know that all the statements that are query'ed to the
> database are valid ones because i check and create the statements
> myself with my "experts".
>
> > You don't need to use any other toolset because with IBO you can go
> as low
> > as you want to work as efficiently as you want. For that matter,
> if you
> > wanted to put together your own native BLR requests with IBO you
> can do that
> > too.
>
> Thanx Jason, i don't know so much about all the great features of
> IBO, i even didn't know about this ExecuteImmediate function. You
> have think in all the situations in C/S as i can see, so i'm sure
> that must have something i'm missing in my configuration or that can
> be improved. As i say to my partners: "IBO is a Pro-Suite, so must be
> a lot more faster, and powerfull that those that you are trying to
> make usable" =:-)

I appreciate the compliment but the truth of it is the good thinking was
done by those who created InterBase in the beginning. I've just tried to do
my best to make it shine as much as possible.

> P.D. Are there some links where we could study about the BLR
> instructions that you say???

Using the IB_SQL tool you can create a stored procedure and then when you go
into a Browse window (accessed from the connection tab, glyph with
eyeglasses on it) you can look at stored procedures and there is a tab on
the stored procedures tab that allows you to look at the BLR for the stored
procedure. In this way you can teach yourself how BLR works.

Then, if you want to actually play with BLR yourself, you can look in the
Samples folder of IBO and see where I have put together a little
demonstration of assembling my own BLR statement and executing it.

If you wanted to go to the extent of producing BLR instead of a SQL
statement you could avoid the prepare time that way too. Then, you just
send BLR to the server and run with it.

Jason Wharton