Slow Loading of Report Template with SQL

rbuser

When I load a report template, it can take up to several minutes for it to
load. I believe the problem is that the SQL from the Data tab in the
designer is being evaluated when the template is loaded (via
Report.Template.LoadFromFile), but before the report is run.

Is there any way to disable the evaluation of the SQL from the Data tab so
that it's only evaluated when the report is actually run and not when the
template is loaded?

Thanks,
Doug

rbuser

FYI, I'm using ReportBuilder 7.03

Thanks,
Doug

nicocizik

Hi Doug,

Are you by chance loading templates created in an earlier version of
ReportBuilder? I believe when you load older templates into the latest
version of ReportBuilder, the queries will be executed during the conversion
process. Otherwise, the SQL will not be evaluated until the report is
printed. When you load the template, ReportBuilder will need to check your
connection which could take some time depending on the database you are
using. You might try simply moving the LoadFromFile call to a place just
before you print the report so you do not have to wait twice.

--

rbuser

Nico,

Thanks. The templates were created using version 7.03. I've verified that
the SQL is definitely being executed with the SQL Profiler (I'm using MS SQL
Server). I can't simply move the LoadFromFile call to a plcace just before
the report is printed because I'm using it to load the template file and
then save it as a blob to the database. It's not really feasible to incur
the overhead against a large database just to load the report template with
the end user report designer every time it needs to be edited.

Do you have any other ideas as to why this might be happening?

Much thanks in advance,
Doug

nicocizik

Hi Doug,

I created a sample application that connects to the Northwinds Access
database using ADO, but was unable to get any SQL to execute when simply
calling Report.Template.LoadFromFile. In fact the datapipeline never opens
the datasets. If possible, please send a small example that connects to the
same database and executes some SQL code as you say. You can send the
example in .zip format to support@digital-metaphors.com.

--

rbuser

Thanks Nico.

From the Microsoft SQL Profiler, I've determined that the SQL statement is
being executed when the call to Report.Template.SaveToDatabase is being made
(we load the report and save it into the datatabase as a BLOB to be
retrieved later). I was making a call to assign a data pipeline from the
form, but I commented it out. For example:

// Report.Template.DatabaseSettings.DataPipeline := plReports;

This still didn't solve the problem. If I get some time (I'm currently
under a deadline), I'll try to reproduce this scenario with the Northwind
MSSQL database, but if you could take a look at
Report.Template.SaveToDatabase when you get I chance it would be very much
appreciated.

Thanks again,
Doug

rbuser

I've narrowed things down to what is causing the problem.

Basically, it boils down to the fact that I'm performing a search and
replace on the SQL data in the report before executing it, because there are
some parameters I need to replace. Changing the SQL text causes the SQL to
be executed down the line in TdaBDEDataSet.GetFieldsForSQL, where line 868
sets lQuery.Active := True;

I've noticed that whenever I edit the SQL data in the report, it executes
the SQL statement, presumably to validate the SQL text. Is there any way to
disable this validation so the query isn't executed until I actually run the
report? Perhaps there is a better way to do this?

Thanks,
Doug

nicocizik

Hi Doug,

Yes, when you edit the SQL, ReportBuilder by definition will execute the SQL
statement to be sure it is valid. This works the same way a TDataSet
descendent works in that if you alter the SQL of a Query, it needs to be
validated before you can use the component. The only way to work around
this requirement would be to either alter the existing BDE plugin or create
a new one with this behavior dissabled because there is no built in way to
turn this behavior off.

--

rbuser

Nico,

Are you aware of an existing plugin for Core Labs SDAC (MS SQL Data Access
Components)? If not, could you please direct me to documentation that will
show me how to create one?

Thanks again for all your help,
Doug

nicocizik

Hi Doug,

There are no plugins for the Core Labs connectivity that I know of. If you
are interested in perhaps creating your own, you will want to take a look at
the existing BDE and ADO plugins (daDBBDE.pas, and daADO) and use them as a
reference. Usually the creation of these plugins is fairly simple, only a
matter of overriding and implementing the correct methods and coding them to
perform their specific task based on the specifications of the new
connectivity.

--

rbuser

Doug wrote:
send me an mail and YOu'll get free component for that-TReportExchange...