Printing reports (1 Viewer)

[Ashley Sutton]

The following code fragment is used to open a report based on one record from a recordset of around 2500 entries.

DoCmd.OpenReport "AVPS_Quote-DD_version", acPreview, , "[Quote Number]=Forms![AVPS Quotes issued]![Quote Number]"

It now takes around 40 seconds for the report to open and with the subsequent processing of various controls on the report, printed output can take several minutes to appear.

Previewing the report from the database window so that all 2500 records are included in the report also takes around 40 seconds to appear. This suggests that the above code is also including all records although only the requested one is actually produced.

Any suggestions on how to get around this problem?

 

[Pat Hartman]

1. Are you using domain functions such as DLookup or DSum in the report or query? They are extremely slow and can usually be gotten rid of by using a left join in the recordsource query.

2. Do you have the backend database stored on a network drive? Access is a file server not a database server. Whenever you request data from an Access table in the backend database, the entire database is returned to you over the network and the Where clause is applied locally. So even though you are requesting only one row, you are probably transferring 2500. Move both the front and backend databases to your C: drive and run the report there to determine if this is the problem.

 

[Ashley Sutton]

Thank you for your suggestions. It set me testing and thinking again. You were correct in that the back-end resides on a server. However performance was no better when transferred to the local machine and also when most of the records were deleted.

The problem turned out to be an OLE object that generates the page heading for the report. Although all the OLE properties looked correct I deleted the object and reinserted it. Bingo! The report now opens in approx 1 second.

 

[BarkerD]

I have also been experiencing performance problems with my database. Pat's comment about the whole database being returned from the WHERE clause has got me thinking.

I am using a Front-End/Back-End configuration.

I have quite a few functions in my application which return a recordset, perform calculations and then close the recordset object. Every time I request a recordset, is Access actually retreiving every record, and then performing the SQL where clause in my OpenRecordset method?

If this is true should I also clean up my SQL a little and stop using SELECT * ?

Should I create a whole bunch of queries on my Back-End and use them as the recordsource for my recordset objects? (Seems this may eliminate the needless traffic across the network)

[This message has been edited by BarkerD (edited 02-01-2000).]

 

[Pat Hartman]

Access doesn't return the entire database for each query but it does return every row in every table referenced by the query. There really isn't anything you can do to alter this behaviour. However, you can keep this in mind when you are writing your app. The following is an example of why DLookups are so bad.
Assume you have a table of 1000 rows and you want to print a mailing list report. Your table contains the two digit state code but you want to print the state name so you use a DLookup in the report detail section. Access will retrieve the entire mailing list table (1000 rows) once and the state name table (54 rows - including the special state codes) 1000 times for a total of 1000 + 54,000 rows = 55,000 rows. If instead of using DLookup you change your query to join the mailing table with the state name table, Access will return 1000 rows + 54 rows = 1054 rows. Quite a difference if you have a slow LAN.

Select * should be avoided even though it will not affect performance in this case. It would affect performance if you converted your back end database to SQL server, or Oracle.