Helen,

I wasn't on the net for some days, and just now I saw these emails (How to
improve performance of IBO.) What do you think about my approach: I have (in
my BDE application) DataModule with 1 TDatabase and 5 TQueries (Query1,
query2,...), etc which have empty sql statements. I have 3 views in my
database which have same structure, but fields in them are calculated
diferent. So, my query depends on that what checkbox user will check, and I
have to in run time to make SQL which get data from adequate view. I think
table(view) can't be used as parameter, so I can not use parametrisied
queries. Also, some clausules will be included in query if user chek some of
the chekboxes, and sometimes they will not be used if user does not check
these checkboxes. This means that where clause of sql statement sometimes
can be only with parameter, but sometimes there are 5-6 values passed to
where clausule, for instance:

var
sql, s, view: string;
begin
s:='';
If checkbox1.checked then
Begin
view:='viewformyboss';
End
else
Begin
view:='viewforcustomer';
End;
If checkbox2.checked then
Begin
S:=S+' and (v1.date>='01.01.2002')';
End;
If checkbox3.checked then
Begin
S:=S+' and (v1.customer_type='Retailer')';
End;
SqlText:='select somefields from '+view+' v1 where some_conditions
'+s+'group by etc..';
datamodule.Query1.Active:=False;
datamodule.Query1.SQL.clear;
datamodule.Query1.SQL.Add(SqlText);
datamodule.Query1.Active:=True;
etc.

In my opinion, this is fine way to get a lot diferent results from one form.

Looking forward to get answer from you.

Best Regards

Zoran Zivkovic

-----Original Message-----
From: Helen Borrie (TeamIBO) [mailto:helebor@...]
Sent: Friday, February 08, 2002 01:54
To: IBObjects@yahoogroups.com
Subject: Re: [IBO] How to improve performance of IBO.


At 03:11 PM 07-02-02 -0800, Allen wrote:

>Mirko,
>
>You might try dropping TIBOQuery components on a form or data module and
>creating persistant fields. I would guess that your performance hit is due
>to the creation of the dynamic field every time you reopen the Query with
>new SQL.

I agree: it's possible that you have old persistent fields there, from your
old BDE application. If you are using this (not recommended) technique of
swapping SQL statements in and out of a single TIBOQuery, then those old
TFields will get created and won't ever get used. Each time you change the
SQL, the TDataset will create its own set of persistent fields internally.

So, even in the VCL, it is bad design strategy to use a single TQuery as a
generic container for multiple queries. To avoid the "double-dipping" you
can create and destroy the TQuery at run-time (you can write a generic
procedure to do this) OR you should use design-time objects with
parameterised SQL which does not change.

You should realise, too, that the SQL property does not need to be (and
should not be) changed if the only thing that changes is the WHERE clause.
If you haven't used parameterised queries in the past, now is definitely the
time to start!

Now, coming to the TIBO* components: with the overlay of the TIB_Dataset
wrapper, you have not only the overhead of TDataset creating internal field
objects but ALSO the IB_InternalDataset object creating its TIB_Column
objects. If you are using your query objects correctly, this overhead is
counter-balanced by the increased efficiency of the InternalDataset
behaviour when you actually come to do things with the dataset. The way you
are using the TIBOQuery, you lose all of the benefits of the native IBO
wrapper while "gaining" the extra overhead.

To address your question, the broad answer is that you must review your
application architecture and make some changes in order to capture the
benefits of the native IBO connectivity layer. Getting rid of the BDE is
only one of the benefits of moving to IBO!! Once the conversion is done,
the warts in your application design tend to show up rather quickly...

As an absolute precursor, make sure that your app uses a connection object.
The TDataset architecture actually allows you to connect directly to a
dataset and, internally, will create a TDatabase object. This "feature" was
never intended to be used with databases - it is there to support
connections to spreadsheets.

I mention this because of the frequency with which I've encountered Delphi
database apps that have no TDatabase! So, add a TIBODatabase if you have no
connection object.

Next, the "Band-Aid" solution is to visit all of those TIBOQueries that you
are (ab-)using for the purpose of swapping SQL statements in and out. Go to
the Fields Editor and delete all of the persistent field objects.

Next, look at the reason you need to swap SQL. If it turns out that the
change of SQL involves only changes in the WHERE clause, then simply alter
the SQL so that the WHERE clause is parameterised for each of the WHERE
criteria that will change during run-time. For example:

SELECT <FIELDLIST> FROM ATABLE
WHERE FIELD1='teddybear'
AND FIELD2=' dolarkey'

would change to

SELECT <FIELDLIST> FROM ATABLE
WHERE FIELD1= :FIELD1
AND FIELD2= :FIELD2

Then, at run-time, you pass user-selections into the query using
ParamByName() or Params[n]. The SQL never changes, the query doesn't even
have to be re-prepared, and it goes like a rocket.

With IBOQueries that MUST support completely ad hoc SQL, you have two
solutions:

--- if they are ExecSQL queries (DML queries) then don't use TIBOQuery, use
TIB_DSQL. Connect its IB_Connection property to your TIBODatabase.

--- if they are SELECT queries, create and destroy the TIBOQuery at run-time
and avoid the performance penalty of "double-dipping" the persistent field
objects. You can hold a TDatasource persistently but you must be meticulous
in setting its Dataset property to nil BEFORE the TIBOQuery gets destroyed.
Do the whole thing inside a try...finally block.

Alternatively, you could keep a "work-horse" IBOQuery in your app and use it
as you do now - but just make certain that your application never explicitly
creates or refers to the persistent field objects.

But I reiterate that, if at all possible, you should avoid using a query
object for ad hoc SQL. However you do it, you are going to have a lot of
coding to do to set the properties of the controls in which ad hoc SQL is to
be displayed, since, with SQL, you don't know the types, sizes, names or
column-orders of the dataset columns.

If you counter-argue that by saying "But I always do know those things, it's
only the values that change" then that is a clear sign that your query is
NOT ad hoc at all, but is simply a candidate for parameters.

--- Another significant area for review is to examine places where you are
bringing datasets across to your app for the purpose of processing data
non-interactively. Typically, these are places where your code puts the
dataset into Edit mode, loops through the dataset and changes one or more
columns in every row. This type processing has NO PLACE in a client/server
application. Take a fresh look at those datasets and the code you wrote to
operate on them and think [DYNAMIC DML]. Typically, this would be

UPDATE ATABLE SET AFIELD = :PARAM1,
SET BFIELD = :PARAM2
WHERE XFIELD = :PARAM3
AND YFIELD = :PARAM4
Such a statement would go into a IB_DSQL component at design-time and would
receive parameters from the user at run-time. No dataset ever comes across
the wire and all that passes across from the API to the server is a
statement (once) + (at each iteration) a set of parameter values supplied by
the user or the application.

I'm sure there is more to review, but I hope that these comments will help
to get you started on optimising your architecture to take advantage of the
server-centric capabilities of IBO. Once your architecture is made
receptive to a data access layer that is focused intensely on using the
features of the server, many techniques become available for tuning
performance.


regards,
Helen Borrie (TeamIBO Support)

** Please don't email your support questions privately **
Ask on the list and everyone benefits
Don't forget the IB Objects online FAQ - link from any page at
www.ibobjects.com



___________________________________________________________________________
IB Objects - direct, complete, custom connectivity to Firebird or InterBase
without the need for BDE, ODBC or any other layer.
___________________________________________________________________________
http://www.ibobjects.com - your IBO community resource for Tech Info papers,
keyword-searchable FAQ, community code contributions and more !

Your use of Yahoo! Groups is subject to http://docs.yahoo.com/info/terms/