Long load time on preview

rbuser

RB7.02 D6 End User reporting with dictionary

I have a large report with 3 dataviews. The main dataview has an
autosearch. There are 209 fields selected with another 1494 available
(after all the joins in the dataview).

It takes <5 seconds to load the report in the designer and a preview
starts right away from the tab.

If you preview from the explorer, after the autosearch dialog has been
closed it takes 22 SECONDS for the preview form to come up.

In the designer it takes about 22-24 Seconds for the query designer to
open this dataview (same issue I guess).

While it is annoying in the designer it is horrible in the preview
since this is what all the users use.

Tracking it down it seems to be from
TdaQueryDataView.ReportGetAutoSearchValuesEvent;

Specifically rippling through it seems to be TdaSQL.SyncFields;

What can be done to fix this? A twenty two second wait for the client
with their processor pegged at 100% is just not acceptable.

Thanks,
Rick Matthews
Dartek Systems Inc.

nicocizik

Rick,

When you load the report directly in the designer, are you loading it using
the DataDictionary or not? Simply using the report explorer should not
create a delay of 20+ seconds even if you are loading the template from a
BLOB field and perhaps converting the report format. In the past we have
encountered some overhead with respect to accessing a large amount of fields
with the DataDictionary, but never with respect to the autosearch feature.
If you would like, you can send an example to support@digital-metaphors.com
and we can investigate the issue further.

--

rbuser

Nico,
Yes the dictionary is in use. Loading in the designer is fine. In
the Data tab though it takes 20 seconds to bring up the query
designer. It appears to be the loading/validating of the fields. The
dictionary has a large number of fields as does the dataview. An
example is difficult since it needs the big database etc.

Have you tried a dictionary application with a few thousand fields and
a query linking say 10 tables giving 1400 potential fields?

Thanks,

nicocizik

Rick,

When using the DataDictionary, depending on how fast your data access is,
there can be a delay the first time you load. In my testing with over 1000
fields defined in the DataDictionary, using SQL Server with ADO, there was a
delay of about 5 seconds.

--

rbuser

Thanks Nico,

My dictionary has 10,000 fields. The dataview itself has 9 tables
with 209 fields selected with another 1494 available. That is a large
system but not unreasonable in my experience.

My data access is fast using ADO SQL Server or Sybase.

If the delay was once only I might be able to live with it but opening
the dataview to the table tab is fast. Switching to the column tab
takes 20 seconds.

Close the dataview, open to table and switch and it is AGAIN 20
seconds. At this point there is NO data access going on (at least not
back to the server).

Where is the time being spent and what can be done?

Thanks,

nicocizik

Hi Rick,

As a test, if you remove all your autosearch criteria, does this speed
anything up?

--

rbuser

Nico,

Yes it does.

Same report, 1) with one autosearch, 2) with no autosearch from the
report explorer clicking preview:

1) About 1.3 seconds for the autosearch dialog. On close of
autosearch dialog then about 23 seconds before the preview screen
shows.

2) Only about 1.6 seconds for the preview screen.

Note that they both take over 20 seconds in the data tab of the
designer to open the query designer to the columns tab.

Thanks,

nicocizik

Hi Rick,

Sorry for the delay, I've been busy trying to find the problem here. After
tracing through most of the DADE code that is fired when using the
autosearch feature I was unable to find the bottle neck (in code) that is
slowing you down. Autosearch does however change your query when it is
enabled. As another test, try taking the SQL created when AutoSearch is
enabled and try to execute it on your database outside of ReportBuilder.
Perhaps with an ADOQuery connected to a DBGrid component. Let me know the
results.

--

rbuser

Nico,
It is not the SQL itself. I can run with ASTA and see that there is
no SQL issued during the 20 second pause.

Tracing through it a bit it seems to be in:
procedure TdaSQL.CreateAvailableFieldList;

Specifically: CreateFieldsForTable(SelectTables[liIndex], lFields);

Inside that the gMetaDataManager.GetFields(FSession, ... call is
fast

But the loop below it bogs down on large tables. We have some (too)
wide tables and additionally our dictionary has some non physical
columns (that get treated by RB as physical) so in the worst case we
might have 600 columns.

In this report we are joining many tables. I think if you updated
your dictionary such that a table had 1000 column entries you would
easily spot the slowdown.

Why does it have to do all this after autosearch? Additionally, even
if it has to parse 600 meta fields it should be faster shouldn't it
than this? There is after all no database accessing going on.

Thanks,

nicocizik

Rick,

Sorry this is taking so long. After creating a table of my own with over
1000 fields, I've found that this may just be a size limitation of
ReportBuilder. In all my testing, the SyncFields method does get called for
each field, but the CreateAvailableFieldList method is only called once for
each table Still it needs to create a TppField for each field in your table
and performing this over 1000 times was typically taking 10 to 15 seconds.
I will mark this down as something for us to possibly optimize in a future
major release but as for now you will need to change the structure of your
queries so only the absolutely necessary fields are available to create your
user reports.

--

rbuser

"absolutely necessary fields" are the fields defined in the user
database and hence put into the report builder dictionary!

As a temp work around for the end user is there a way I can avoid
CreateAvailableFieldList on a preview? Why when previewing a report
from the report explorer does it need a list of fields - not on the
report - but that were available to the designer?

CreateAvailableFieldList is not called if there are no autosearch
criteria.

Thanks

nicocizik

It is not necessary to call CreateAvailableFieldList for each field unless
your fields are out of sync. Otherwise CreateAvailableFieldList should only
be called once. Perhaps you can change the RB source to work the way you
need. Be sure not to change any code in the Interface sections as that will
prevent RAP from working correctly.

--

rbuser

Thanks Nico.

Could you point me in the right direction? It appears that on a
preview from the report explorer if you have autosearch criteria then
procedure TdaQueryDataView.ReportGetAutoSearchValuesEvent is called
which results in FQueryOutOfSync := True which causes
CreateAvailableFieldList to be called.

Can I safely stop this from happening on a preview? If so how/where?
Even once is 20 seconds too long. Is there a need for available
fields for autosearch in preview?

Thanks,

nicocizik

Rick,

I believe it sets the FQueryOutOfSync to True because the AutoSearch is
actually changing the SQL being executed then regenerating the page info.
You may simply be able to take the CreateAvailableFieldList call out in this
case as you are probably not changing the fields accessed when you run your
autosearch.

--