Prevew..... SLOW !!! Query...FAST !!! AAAGGGHHH.

[saustin]

Hi All,
What possible reason could there be for an A97 converted to A2000 report based upon a query to now run slow (1min) when the query returns (when run by itself) data instantly ? The tables being linked to are all oracle.

Any report preview guru's out there ?

In A97 the report preview appears instantly, the
report preview in A2000 is slow.

Any thoughts or hints in the right direction greatly appreciated.

Think Access 2000 is getting confused, very bizzare.

Thanks, Steve.

Steve Austin
MIS Department
Helicopter Support, Inc.
(p): (203)795-6051 Ext. 3378
(f): (203)799-5020

 

[scriverb]

Does your report perform any sorting and grouping? This process happens after the query has provided the recordset to the report. No matter what order your query provides your records to the report if you are using Sorting and Grouping on a number of fields and your recordset is substantial the report has to perform the sorting again to satisfy the report formating needs.

Check this out. Have you changed any of these settings without thinking of the consequences. Bob Scriver

 

[saustin]

Hi Bob,
Thanks for your reply. The report is sorted by only one field. Just ran a test report. The query returned 2 records which were sorted by the primary index. 90 % of the reports will return only 1 record. Tried taking out all sorting out of the report but it returned an error.
Is there a difference in sorting between A97 and A2000 you feel is the culprit ?

Steve.

 

[evalesthy]

Is this the only performance degradation you have noticed? Many other posts have been made regarding Acc97 to Acc2K conversion with users reporting forms slow in opening, reports taking much longer to run, etc. See

http://www.granite.ab.ca/access/a2kslow.htm

for an overview. Post here if you find anything else of general interest.

 

[saustin]

Fortunately yes evalesthy, this is the only degradation noticed so far. Perhaps because we utilize the oracle odbc driver to link to oracle tables is a good reason why we dont see the degradation. The msft oracle driver is about 3x slower in performance testing. Have no problem with the msft sql odbc drivers performance. Many of our reports are based on queries that link both sql and oracle tables and response time is almost instant.
The query is responding instantly but where is the report getting hung up ? Any thoughts evalesthy ?

 

[saustin]

Hi evalesthy,
Thanks for your link. Re 1.- LDB locking which a persistent recordset connection fixes
>>> Am the only user in this access app. Many users update the Oracle back end. Used your form open tip.

2. sub datasheet Name property set to [Auto] should be [None]
>>> It is NOT possible to change this property on a linked oracle table.

3. Track name AutoCorrect should be off
>>> It was set to off.

Regret to report NO change in response time. Still SLOW !

 

[evalesthy]

Followup: I did some more research and found two more things on the web that may or may not apply to your situation. I found a reference to Acc2K not handling long file names well. As a test I took an Acc2K database "Rehab Patient Services.mdb" and renamed it "Rehab.mdb" and did see a significant improvement in report speed. No change in speed was seen in Acc97. Second, are you entirely running all front ends and the backend as converted Acc2K? In testing, a mixture of Acc97 and Acc2K frontends (8 terminals) with an Acc97 backend resulted in measureably slower performance (timed with stopwatch) for 8 different reports run in for the Acc2K frontends. If I replaced the Acc2K front end on the pc with an Acc97 frontend the speed problem disappeared.