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