Improving speed of AvailableCriteriaList

rbuser

Hi,

A general question on how to improve the speed of the first call to
AvailableCriteriaList for a report. I know there is some caching going on
which means that after the first call to AvailableCriteriaList, subsequent
calls are quicker, but the first time hit can be quite long....the report
takes 35 seconds the first time, and 5 seconds on subsequent times.

Is there a way to add search criteria in code without having to call
AvailableCriteriaList.

I've tried running it in a thread but it crashes the app...

Is there a way I can call AvailableCriteriaList somewhere in the
initialization of the app and take the hit then?

Any ideas appreciated...

Gabhan
Input Systems

digitalmetaphors

It is slower because DADE is sending a query to get all the available table
names and field names when you create a dataview. It then stores this
infomation in the meta data cache. Try using the data dictionary component
to define the table and fields that can be used in DADE by the user. This
might speed things up.

The other possible solution would be to try to populate the meta data cache
after the designer has been created. You'll probably have to create a
dataview in the datamodule of the report on the fly and then then extract
the TdaSQL object to call methods on it to get it to populate the meta data
cache. We don't have an example of this, but it should be possible to
accomplish. Here is an example of creating a dataview on the fly and
another example showing how to extract the SQL object. It looks like you
could call TdaSQL.CreateAvailableTableList and CreateAvailableFieldList to
populate the cache.

http://www.digital-metaphors.com/tips/ExtractSQLObject.zip

http://www.digital-metaphors.com/tips/CreateDataModViaCode.zip

Cheers,

Jim Bennett
Digital Metaphors

rbuser

Jim,

Will try the dictionary - but in the mean time - I upgraded our app to RB7
D5 and that slow AvailableCriteriaList went from taking 30 seconds with RB6
to taking almost 10 minutes in RB7. Is there a way to turn off the caching
of tables and fields - I mean - surely the report does not need ALL field
names for ALL tables since my report only uses 3 or 4 tables...seems a
strange way to do things - putting such an overhead the first time a DADE
data page is used..

Gabhan
Input Systems

digitalmetaphors

Use the RB data dictionary.


Cheers,

Jim Bennett
Digital Metaphors

rbuser

Jim,

I have set up the data dictionary and populated it with the relevant fields
and set the data dictionary section of Data Settings to use that dictionary,
but the speed has not improved at all. Is there a different call I need to
make besides AvailableCriteriaList? The call I have is:

MyRep.Template.FileName := 'SalesInvRep.rtm';
MyRep.Template.LoadFromFile;

lDataModule := daGetDataModule(MyRep);
lDataModule.Template.FileName := 'UnpSalesInv.dtm';

MyRep.ResolvePipelineReferences;

MainPipeSQL.ClearCriteria;
MainPipeSQL.AvailableCriteriaList(lFields);

etc...

where MainPipeSQL is a TdaSQL object.

The database contains about 120 tables and views and if I stick a timer on
either side of the AvailableCriteriaList call it takes about 10 minutes.

Is there another call somewhere in there that I need after I've loaded the
rtm and dtm to get it to use the data dictionary?

Gabhan

Input Systems

rbuser

Jim,

I left out the lDataModule.Template.LoadFromFile below the FileName line
below...

Sorry to bug you guys with this...but the app is out there and speed has
really become an issue - I've spent at least a few days trying to get
through this but I'm figuring you may be able to put your finger on it
quicker than I will....

Any suggestions at all are welcome,

Gabhan

Input Systems

digitalmetaphors

You are making calls that are meant to be internal to RB, however, they are
there and you can use them. How about using the demo end user project on
this database. Use our out-of-the-box demo to see if you can reproduce the
problem using it as a baselne. Compare the times with and without the data
dictionary using our demo. Any differences? Next we'll need a simple example
of your approach on a demo database that shows the problem. This way we can
run it here and trace through the code to see what needs to be different.
Send it to support@digital-metaphors.com

In the end user solution, the data dictionary is assigned to the
Designer.Datasettings.DataDictionary property. The
Designer.Datasettings.UseDataDictionary property should be as well. As far
as the call you are making, the AvailableCriteriaList call should only get
the fields for the tables that are selected in the dataview's sql object.
If you point your library path to RBuilder\Source and place a breakpoint in
the TdaMetaData.GetFields method in daMetaData.pas does it use the data
dictionary to populate the entire cache for all tables and fields?

Cheers,

Jim Bennett
Digital Metaphors

rbuser

Jim,

I've got it working now with the data dictionary - the loading of the
datamodule seemed to be overwriting the data dictionary settings - resaved
these and things are now working great.
The reason I took this approach is that I wanted to have control over the
criteria from within the app, while also giving the user the ability to
design the report and add extra tables/fields as necessary.
The main table that this report runs off (the sales invoice report for
reprinting of invoices) has 100000+ rows of data so I needed to enforce some
criteria while allowing the user to specify more - but not remove my ones.

Anyways - thanks for all your help - report down to under 10 seconds first
time, and a couple of seconds each time thereafter.

Regards,

Gabhan
Input Systems