Performance hit editing SQL object

rbuser

Hi. I am currently using RB 7.04.

I am running a report that is executing a query that returns only 1 record
from a DBISAM database. Executing the report WITHOUT modifying the SQL
object takes 2-3 seconds. However, as soon as I modify the SQL object, even
with the EXACT same query, the report takes 25-27 seconds. The longest wait
time is experienced during the "Accessing Data..." part of the preview. The
report does have 5 subreports and multiple links on the "Data" view.
However, like I said, it only takes 2-3 seconds if I DO NOT edit the SQL
object??? The code I am using to modify the SQL object is below. I'm
getting a lot of complaints from customers. In many cases the report takes
even longer on their systems. What can I do to increase the performance?

lSQLText := TStringList.Create;
aSQL.EditSQLAsText := True;
lDataView.OutOfSync;
lSQLText.Text := strCustomSQL;
aSQL.SQLText := lSQLText;
lSQLText.Free;
Report1.DeviceType := dtScreen
Report1.AllowPrintToFile := True;
Report1.OutlineSettings.Enabled := False; //Saw this elsewhere in the
newsgroups as a performance enhancer.
Report1.Print;

Thanks in advance.

Brian Kennedy
RB 7.04

nicocizik

Hi Brian,

This is not a known issue with RB 7.04. In my testing with RB 9.02 with
code identical you yours below, I did not get a delay in report generation.
If you place a break point at the top of the code below, which line causes
the 25 second delay? Are you able to re-create this issue using a different
database?

--
Regards,

Nico Cizik
Digital Metaphors
http://www.digital-metaphors.com

rbuser

In article <430a304d$1@dm500.>, briank@mtcpro.com says...

This may be WAY off in left field, but...

A similar thing happens to me with CDS/SQLConnections where "keep
connection" is false and the last table is closed. In other words, I drop
connection, have to re-establish connection, re-authenticate and load
metadata for complete database.

Any chance something like this is occuring when you change SQL code?


EdB

rbuser

Thanks for the replys. Well, we have found that if we add the same SQL
criteria we assigned to the master data view to the linked data views, that
corrects the performance problem. The slowdown was from the sub-data views
querying all their records regardless of the criteria applied to the master
data view. I would think it would be optimized so that would not occur???

Brian Kennedy
RB 7.04

nardmoseley

When using the built-in DADE linking, the search criteria applied to the
master query are propogated to the detail query(s) to optimize them.
However, once the SQL Text is manually edited, DADE no longer generates
optimize sql (it generates no sql at all) and the autosearch and auto
linking features are essentially disabled.

----------------------------------------------
Tech Tip: Limitations of using TdaSQL.SQLText
----------------------------------------------

The TdaSQL object can contain either an object based description of the SQL
(SelectTables[], SelectFields[], etc.) that it uses to generate SQLText OR
it contain a SQLText string. If you specify the SQLText string then TdaSQL
will not generate any SQLText. Therefore the built-in AutoSearch and Linking
features can no longer be used - because these require that special SQLText
be generated.

If you want to use AutoSearch with a SQLText, you will need to handle this
in your custom dataview code. For an example see RBuilder\Demos\AutoSearch
and see the TdaQueryDataView.CreateAutoSearchFields and
TdaQueryDataView.ReportGetAutoSearchValuesEvent methods in
daQueryDataView.pas.



-------------------------------------------
Tech Tip: Linking DataViews
-------------------------------------------

Overview
---------

Linking DataViews in the ReportBuilder Data workspace (DADE), requires that
the detail data be sorted by the linking fields. When DataViews are linked,
DADE generates special linking SQL for the detail DataView. The linking SQL
(Magic SQL) can be viewed by positioning the mouse over the DataView window
and pressing Ctrl + Left Mouse button. The results of the Magic SQL query
can be viewed by pressing Shift + Left Mouse button.

ReportBuilder's DataPipeline architecture includes support for linked data
traversal. Whenver the record position of the master data changes, the
traversal logic compares the master and detail field link values and then
determines where to locate the record position for the related detail data.
The linking logic will only work properly if the Database engine's collation
of the detail query result set is in sync with ReportBuilder's internaly
field link comparison logic.

--
Nard Moseley
Digital Metaphors Corporation
http://www.digital-metaphors.com