SearchField.SearchExpression problem?

rbuser

Hi!

We have an end user applicattion to print invoices.
We designed the report format, in which contains one
automatic DataView, which contains one condition (DOCTO_PV_ID = 1) .
All our users know that the formats has to be designated that way.

The Query that generates the report builder looks like:

SELECT DOCTOS_PV_1.FORMA_GLOBAL_EMITIDA,
DOCTOS_PV_1.DOCTO_PV_ID,
DOCTOS_PV_1.CAJA_ID,
DOCTOS_PV_1.TIPO_DOCTO, DOCTOS_PV_1.FOLIO,
DOCTOS_PV_1.FECHA, DOCTOS_PV_1.HORA,
DOCTOS_PV_1.CAJERO_ID,
DOCTOS_PV_1.CLAVE_CLIENTE,
DOCTOS_PV_1.CLIENTE_ID,
DOCTOS_PV_1.CLAVE_CLIENTE_FAC,
DOCTOS_PV_1.CLIENTE_FAC_ID,
DOCTOS_PV_1.DIR_CLI_ID,
DOCTOS_PV_1.ALMACEN_ID,
DOCTOS_PV_1.MONEDA_ID,
DOCTOS_PV_1.IMPUESTO_INCLUIDO,
DOCTOS_PV_1.TIPO_CAMBIO,
DOCTOS_PV_1.TIPO_DSCTO,
DOCTOS_PV_1.DSCTO_PCTJE,
DOCTOS_PV_1.DSCTO_IMPORTE,
DOCTOS_PV_1.ESTATUS, DOCTOS_PV_1.APLICADO,
DOCTOS_PV_1.IMPORTE_NETO,
DOCTOS_PV_1.TOTAL_IMPUESTOS,
DOCTOS_PV_1.TICKET_EMITIDO,
DOCTOS_PV_1.FORMA_EMITIDA,
DOCTOS_PV_1.CONTABILIZADO,
DOCTOS_PV_1.SISTEMA_ORIGEN,
DOCTOS_PV_1.VENDEDOR_ID,
DOCTOS_PV_1.CARGAR_SUN,
DOCTOS_PV_1.PERSONA,
DOCTOS_PV_1.REFER_RETING,
DOCTOS_PV_1.UNID_COMPROM,
DOCTOS_PV_1.DESCRIPCION,
DOCTOS_PV_1.USUARIO_CREADOR,
DOCTOS_PV_1.FECHA_HORA_CREACION,
DOCTOS_PV_1.USUARIO_ULT_MODIF,
DOCTOS_PV_1.FECHA_HORA_ULT_MODIF,
DOCTOS_PV_1.TOTAL_FPGC
FROM DOCTOS_PV DOCTOS_PV_1
WHERE ( DOCTOS_PV_1.DOCTO_PV_ID = 1)

Then in our end user appicattion we select the document
that we want to print (Example: DOCTO_PV_ID = 1926) and
before execute Report.Print we reeplace the Search expresion by code:
DocId : = 1926;
SearchField.SearchExpression := IntToStr(DocID);
// Note: SearchField is a TppAutoSearchField.

With big Data Bases (1 giga) with a lot of records (754,756) on the table
DOCTOS_PV
the line
SearchField.SearchExpression := IntToStr(DocID);
is so slow (1 minute).

Why i so slow? looks like when the property SearchExpression expression is
Set, report builder
execute the query... why?

Is there another way to provide the parameters to the DataView in our
end user aplicattion that works fast?

Note: We have Report Bulder 7, Firebird 1.5.3

Jairzinhio Pacheco Villarreal
Microsip

nicocizik

Hi Jairzinhio,

This sounds like a database issue. Try performing the query on the large
database without ReportBuilder and see how long it takes to return a
dataset. Perhaps
indexing your datasets will help.

If you use the built-in autosearch feature, is the generation still slow.
For instance, in the Search tab of the Query Designer, set the search
expression you already have to AutoSearch, then run your report. A dialog
should appear allowing you to enter your own search values. When this is
done, are you still waiting for a minute to see the results?

--
Regards,

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

rbuser

Hi.

I just tested your built-in autosearch feature:
The dialog appers, then I give the ID=1926
When i Click "Accept" take to much time to present the prewiew or print (1
or 2 minutes)
the same time that the line
SearchField.SearchExpression := IntToStr(DocID);
consume in my application.

Important note 1:
I also test the same report with the same query, but without asikig for
the search value. (no dialog)
I give the value 1926 in the 'Search' tab sheet in the section 'Criteria'.
This present the prewiew or print almost instantaneous.

Important note 2:
I observed that the slowness also appears when the user click in the
'Search' tab sheet the first time to show
the fields & conditions. (The second time i return to the 'Search' tab
sheet it do it quickly).

Important note 3:
I also run the query outside of Report Builder, and it runs almost
instantaneous, becase the result
is only one record (The table is indexed correctly by ID, and others).

In conclusion our Data base is working fine, the table are indexed correctly
& works fast outside of Report builder;
I think that report bulider may have some problems in the assignation of the
SearchExpression.

Please work on this becase it is cost me some problems with my customers.

Thanks.

nicocizik

Hi Jairzinhio,

Have you tried this with RB 9.03? If not, I recommend downloading a trial
version and seeing if the issue still exists. What database/connectivity
are you using to access you data? I will try to emmulate your conditions
and recreate the issue on my machine.

--
Regards,

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

rbuser

We are usign:
Delphi 5
Report builder 7
Server: Firebird 1.5.3
Components: FIBPlus

The Report builder 9.03 only support Delphi 6, Delphi 7, Delphi 2005.

Remember:
To emmulate our conditions you need to have a big data base (1 giga) & the
table that you will use with a lot of records(754,756) & ask for a parameter
with your built-in autosearch feature (return only one record).

If you need our Data base, I can upload to our ftp & then you can download.

Thanks.

nicocizik

Hi Jairzinhio,

Thanks, I will try to recreate it hear using an Interbase db. One more
small request. If you set your library path to \RBuilder\Source and hit the
pause button when you are waiting for the autosearch to resolve, where in
the RB source are you located?

--
Regards,

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

rbuser

Hi Nico.

I think we have found the problem. There is a query in daFIBPlus.pas
to "construct an SQL statment that returns an empty result set,
this is used to get the field information":

FQuery.SelectSQL.Text := 'SELECT * FROM ' + aDataName +
' WHERE ''c'' <> ''c'' ';

This query gets busy in some big tables for some server performance reasons.

If this is only to get field info, constructing the query diferently you get
the same results faster:

SELECT first 1 * FROM... or what else better.

Doing this increses time display.
I know this is not digital-metaphors code but you can advice the author to
update daFIBPlus.pas

Thanks for your comments & help on this matter.

Jairzinhio Pacheco Villarreal
Microsip.

nicocizik

Hi Jairzinhio,

Thanks for pointing this out. I believe we are using similar code for some
of the DADE plugins we've created so that is probably where the author of
this plugin got the idea. I will see about making the changes you made for
the next release.

--
Regards,

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