Changing query and report speed.

[Taavi_Remmelg_(Guest)]

Hi,__ got a strange problem here. We are changing report queries at runtime, for this we read filter expression with getFilter(), modify it and pass new query to runtime engine.__We get correct reports but overwriting query will sometimes result in very slow report execution. Report execution displays "Sorting..." screen and seems to count records forever, seems like it is creating composite records for all records in database.____This happens also when we do not modify query, just read it with getfilter() and pass to runtime.____Query() function in report returns correct filter expression, the only difference I noticed is that when we do not modify query saved in report, function returns query in mnemonic form like this:______Include all records where (PYSI->ANALKOOD is greater than or equal to anal1 and PYSI->ANALKOOD is less than or equal to anal2) and (TASUD->LAHENDUS is greater than or equal to ALGUSKPV and TASUD->LAHENDUS is less than or equal to LOPPKPV) and (PYSI->TABN is greater than or equal to tootaja and PYSI->TABN is less than or equal__to tootajani) and (PYSI->tyhikuup is equal to " " or PYSI->LAHKUNUD is greater than or equal to LOPPKPV) and (PYSI->MKAT is not equal to x_mkat1 and PYSI->MKAT is not equal to x_mkat2) and (PYSI->ALLYKSUS is greater than or equal to allyks1 and PYSI->ALLYKSUS is less than or equal to allyks2) and PYSI->TABN is not equal to__"00000"____, modified query in report looks like this:____Include all records where ((PYSI->ANALKOOD>=anal1.AND.PYSI->ANALKOOD<=anal2).AND.__(TASUD->LAHENDUS>=ALGUSKPV.AND.TASUD->LAHENDUS<=LOPPKPV).AND.__(PYSI->TABN>=tootaja.AND.PYSI->TABN<=tootajani).AND.(PYSI->tyhikuup=" ".OR.PYSI->LAHKUNUD>=LOPPKPV).AND.(PYSI->MKAT#x_mkat1.AND.__PYSI->MKAT#x_mkat2).AND.(PYSI->ALLYKSUS>=allyks1.AND.PYSI->ALLYKSUS<=allyks2).AND.__PYSI->TABN#"00000")__ /This is the part we get from report description/____And we add this expression:__.AND.(pysi->tabn#^0123^.and.pysi->tabn#^0116^.and.pysi->tabn#^0075^.and.__pysi->tabn#^0026^.and.pysi->tabn#^0171^.and.pysi->tabn#^00512^)____Any ideas?____Taavi.______

[kfleming]

This is the Xbase version?____Kathleen__R&R Support

[Taavi_Remmelg_(Guest)]

Yes, latest Xbase versioon.____Taavi.______>This is the Xbase version? __>__>Kathleen __>R&R Support __

[Taavi_Remmelg_(Guest)]

Yes, latest Xbase____>This is the Xbase version? __>__>Kathleen __>R&R Support __

[kfleming]

Try removing the calc using the QUERY() function from the report and see if it helps.____Kathleen__R&R Support

[Taavi_Remmelg_(Guest)]

Hi.__>Try removing the calc using the QUERY() function from the __>report and see if it helps. ____We do not use query() function in calculated fields, I only used it (in report header) to take a look if something is wrong with modified query when customer reported and our tests confirmed very slow report execution. So, problem was present before I added this function to report.__Seems to me R&R handles the first one (without query modification) much more quickly than the second even if we do only a minor change in query. ____What else could I try? Remove part of query?____Taavi____

[Taavi_Remmelg_(Guest)]

Hello,__ after some testing with simple query expressions seems that when we change report query in report control file with RI_QUERY=O and RI_FILTER=something (even with the simplest filter expression) it totally changes report data selection process: "Records read" and "Records selected" numbers are totally different (and a lot bigger) from numbers we see with unmodified query and "Records selected" is obviously not correct: it is almost the same as "Records selected", at the same time the report we get contains only correct data.__We use relation in this report and my guess is that composite record creation process does not honor modified query, composite record is created for every possible child table record (because we get really big numbers here) and only after that records will be selected. Without query modification this process seems to be more efficient.____Also removed query()function, it did^nt make a difference here.______>Try removing the calc using the QUERY() function from the __>report and see if it helps. ____

[kfleming]

Runtime does formulate the composite database differently but this is the first time that I have seen such a negative discrepany in performance.____As a test, can you try running the report using RI_QUERY of ? so that the additional conditions in the filter are added manually via the query dialog. I wonder if this will improve the performance or if it will be equally slow to using the RI_FILTER expression.____Kathleen__R&R Support