Any way to speed this process up?

[notDave]

What we have: one report, printed to pdf, 1000 pages long, one page for each
of our 1000 employees.

What we need: 1000 single page pdfs, each one with the date and emp_id in
the filename.

What we do now:
1. create a record set of the 1000 employees from their emp_id,
2. use a loop and the record set to open the report, filter it for their
emp_id, print it, close the report, move to the next record.
3. when we “print†we’re actually sending the report output to Adobe’s
PDFWriter, and we update the report’s “caption†to include the date and their
emp_id. The PDFWriter uses that “caption†as the filename when it creates
the pdf.
4. we end up with 1000 pdfs, one for each employee, dated, with their emp_id
in the filename (20070505_emp_id_ReportName.pdf)

The problem is that this takes almost two hours! Any suggestions as to how
we could speed this up?

The real lag appears to be the report, which is relatively complex. The
report has two sub reports. The main report, and the two sub reports, all
use the same table as a record source. The table contains all data for all
employees. (The report used to have 6 sub reports, each sub report based on
its own query, tied to the main query of the employee info, which the main
report used. It took nearly 3 hours just to print a single pdf of this
report. I have since reduced the sub reports to two, and based everything on
one table that I created from the multiple queries. This has cut the time
about in half.

~notDave

 

[Scott McDaniel]

What we have: one report, printed to pdf, 1000 pages long, one page for each
of our 1000 employees.

What we need: 1000 single page pdfs, each one with the date and emp_id in
the filename.

What we do now:
1. create a record set of the 1000 employees from their emp_id,
2. use a loop and the record set to open the report, filter it for their
emp_id, print it, close the report, move to the next record.
3. when we “print” we’re actually sending the report output to Adobe’s
PDFWriter, and we update the report’s “caption” to include the date and their
emp_id. The PDFWriter uses that “caption” as the filename when it creates
the pdf.
4. we end up with 1000 pdfs, one for each employee, dated, with their emp_id
in the filename (20070505_emp_id_ReportName.pdf)

The problem is that this takes almost two hours! Any suggestions as to how
we could speed this up?

Is this a split system (i.e. frontend/backend)? If so, perhaps you could localize the data for the report by dumping it
into a local table, if you're not doing that already. It appears you're flattening the data anyway, so instead of
flattening it to a networked backend, flatten it to a local table.

Look at the indexes of your table; generally reporting runs faster with more indexes, but there are no guarantees.
You'll have to try differnt index schemes before you come up with one that works best.

Subforms/subreports are real time killers, but often you can't do without them.

Scott McDaniel
scott@takemeout_infotrakker.com
www.infotrakker.com

 

[Tom Wickerath]

In addition to Scott's input, check out the sections of my document that are
sub-titled "Use indexes" and "Use JETSHOWPLAN". You may be able to optimize
your queries to work faster. Remove any sort orders from the queries, since a
report won't use them. Reports use Sorting and Grouping, but don't care about
sort orders applied at the query level.

Implementing a Successful Multiuser Access/JET Application
http://www.access.qbuilt.com/html/multiuser_applications.html


You might want to check out Win2PDF. Access MVP Arvin Meyer recently stated
this:

"The best PDF creator I've found
is Win2PDF which costs up to $35 depending upon volume. It does work in
Vista and is very fast, so fast that it will have made a 40 page PDF before
Adobe even opens."

Reference:
http://groups.google.com/group/micr...read/thread/e0e0615137ef1261/e17c43d254eacdfb


Have you removed a page count X of Y from your report? Reports are actually
formatted twice to get the Y value, as in page 2 of 8. However, if you know
that your reports will always be a single page, you can avoid this perf. hit.


Tom Wickerath
Microsoft Access MVP
https://mvp.support.microsoft.com/profile/Tom
http://www.access.qbuilt.com/html/expert_contributors.html

 

[David W. Fenton]

The problem is that this takes almost two hours! Any suggestions
as to how we could speed this up?

Well, my bet is that there is no way to speed it up. I doubt that
with a mere 1000 records the slowdown is in the report formatting,
so it's probably the Adobe PDF writer that is taking up all the
time, and there's no way that I know to speed that up. You might
check to see if there's some interface that would take queue the
print jobs so Access wouldn't have to wait for each one to complete,
but I strongly doubt there is such a utility that will work with
Access.

 

[Cyber-guy via AccessMonster.com]

Well, my bet is that there is no way to speed it up. I doubt that
with a mere 1000 records the slowdown is in the report formatting,
so it's probably the Adobe PDF writer that is taking up all the
time, and there's no way that I know to speed that up. You might
check to see if there's some interface that would take queue the
print jobs so Access wouldn't have to wait for each one to complete,
but I strongly doubt there is such a utility that will work with
Access.

Use PDFedit995 in burst mode to generate single page pdf's

http://www.pdfedit995.com/

 

[David W. Fenton]

Use PDFedit995 in burst mode to generate single page pdf's

http://www.pdfedit995.com/

How do you get the appropriate file names?

Or did you not read the whole thread to understand the complete
problem?

 

[notDave]

I apologize for two things:
1. I got pulled onto a different project and didn't reply sooner to these
wonderful suggestions.
2. I didn't index my table at all.

After indexing a few columns I got the entire process, including
transferring the completed output across the network to it final home, to
complete in 39 minutes. that's even faster than spooling the output to our
big Xerox printer. Thanks for the ideas everyone.