Report very slow

[ArnaudAbadie]

Hi all,

I made a main report with about 10 subreports on it.
All use to be ok. But now all is going very slow.
It takes about 5-10s to load report and then show data :-(
It seems to start just after we update our database (Sybase SQL Anywhere 8) and CR (8.5 PS2).

What I did then is to recreate my report from A to Z. I mean that I just supress the subreports and put it again.
It just solve my problem.

Now I would like to understand

Thank you in advance

Sorry for my bad English

Arnaud

 

[Madawc]

Crystal generates SQL commands, and does so automatically. If you understand SQL (I have just an inkling) then you can see how some methods are much faster than others.

I assume that Crystal used an inefficient method when it adjusted for your update. When you made it start again from scratch, it did much better.



Madawc Williams
East Anglia, Great Britain

 

[ArnaudAbadie]

Yes I understand SQL. I will appreciate to see how some methods are much faster than others


But how do you explain that breaking it all and do the job twice made it work nice?

Regards
Arnaud

 

[synapsevampire]

Perhaps some sort of Verify Database occurred in the process.

Crystal is very strange about passing record selection values to the database, if you have the old version, check the Database->Show SQL Query vs. the new version, they should be identical.

-k

 

[ArnaudAbadie]

that's what I previously thought. I have allready looked at SQL Query and even Find Location option. They are both the same.

(

 

[Naith]

The two reports will be identical if you based one on the copy of the other, even down to the SQL generated.

The only difference between the two would be the creation time of the first compared to the second. If there was a change to the database, the latter report would have an up to date snapshot of the database, whereas the former report would have needed you to manually hit Verify Database.

Do that now and then compare the performance times.

Naith

 

[ArnaudAbadie]

I did what you suggested. Make no change

I give you a view of SQL if that can help

SELECT
Piece_Vue."iKey", Piece_Vue."iSoc", Piece_Vue."iType", Piece_Vue."iFact_Avoir", Piece_Vue."iNum", Piece_Vue."datCreat", Piece_Vue."datEcheance", Piece_Vue."iTiers", Piece_Vue."iKeyTiersPayeur", Piece_Vue."iEtab", Piece_Vue."sDescr", Piece_Vue."sModepaie", Piece_Vue."sEcheance", Piece_Vue."dbTotalTTC", Piece_Vue."dbTotalAcompte", Piece_Vue."bPrintAdrLivr", Piece_Vue."bCloseFact", Piece_Vue."sAffaire",
Document."sTitreEdition", Document."sPiedPage1", Document."sPiedPage2", Document."sPiedPage3", Document."sPiedPage4", Document."sPiedPage5",
Piece_Lig_VueSimple."iLig", Piece_Lig_VueSimple."codArt", Piece_Lig_VueSimple."sLibArt", Piece_Lig_VueSimple."sRefFour", Piece_Lig_VueSimple."sInfoStock", Piece_Lig_VueSimple."dbQte", Piece_Lig_VueSimple."sUnite", Piece_Lig_VueSimple."dbPrixBase", Piece_Lig_VueSimple."dbPrixNet", Piece_Lig_VueSimple."dbTotalLig", Piece_Lig_VueSimple."bSautPage",
Etab."sAdr1", Etab."sAdr2", Etab."sAdr3", Etab."sAdr4", Etab."sAdr5", Etab."sInfo1", Etab."sInfo2", Etab."sLibEdit"
FROM
{ oj (("AGIR"."Piece_Vue" Piece_Vue INNER JOIN "AGIR"."Piece_Lig_VueSimple" Piece_Lig_VueSimple ON
Piece_Vue."iKey" = Piece_Lig_VueSimple."iKey&quot
INNER JOIN "AGIR"."Etab" Etab ON
Piece_Vue."iEtab" = Etab."iKey&quot
INNER JOIN "AGIR"."Document" Document ON
Piece_Vue."iType" = Document."iKey"}
WHERE
Piece_Vue."iKey" = 100040
ORDER BY
Piece_Vue."iKey" ASC,
Piece_Lig_VueSimple."iLig" ASC

thank you in advance

Arnaud