Faster alternative to DCount? 4

[JasonPurdueEE]

Hello everybody.
I have a report based off a query. In my report I have many (21 to be exact) testboxes with a DCount statement as the control source. This set up works fine, but it is terribly slow (it can take in excess of 2 mins to create the report). Is there a faster alternative to DCount? Would it be any faster to use a count in the query? Any advice is greatly appreciated. Thank you.

JASON

______________________________________
Sleep is a poor substitute for coffee.

 

[jrbarnett]

It may be quicker to use a Count (*) in the underlying SQL where applicable, but using DCount is faster than using a recordset object to Select count (*).
You will also find it is quicker to count (*) rather than count a particular fieldname, as there are optimisations in place for this.

Also, if the counts are being used in report group headers/footers to display the number of records in the section, you can set a textbox controlsource to
=Count(Fieldname) which seems fairly quick in my test db, but there isn't much data in there.

John

 

[JasonPurdueEE]

Hey John,
I've found another speed related issue. Previewing or printing the report takes a little over 2 minutes, but set it to email via a snapshot file only takes a minute to create. Any idea why the report is so much slower? Can I optimize the report in anyway?

JASON

_________________________________
Sleep is a poor substitute for coffee.

 

[jrbarnett]

An idea, not sure if it is the complete reason or not. In order to view on screen at least (but not print on printer) it has to render the entire report on screen, but not if it is docmd.outputto it to a file (assuming this is done direct from code rather than from an on screen preview).

Before the report is opened try:
Application.Echo False
and after the report is opened
Application.Echo True

Not sure if this will make any improvement to your code, but worth a try.

Of course, if the output to is being run from a screen preview, then the data has already been retrieved and formatted, which would account for the performance difference.

John

 

[JasonPurdueEE]

The process take equally as long to open the report in preview mode as it does to print. Here is what I'm using code wise to preview, print, or mail.

DoCmd.OpenReport rptName, acViewPreview
DoCmd.OpenReport rptName, acNormal
DoCmd.SendObject acReport, rptName, acFormatSNP

I added the Application.Echo False bit you suggested and it took 4 seconds off the time to open the report in preview mode.

I created a copy of my report minus the 21 DCount text boxes and it took the creation time down to about 9 seconds. This is more of the speed I need. I dont want users to mistake the time needed for the report to run for Access locking up (and give it the old Ctrl+Alt+Del treatment). I dont even want to think of the corruption problems I might run into with that.

Here is an example of one of my DCount statement. "WTI_Code_Descr" is the name of the field in the query "qryReportCriteria" and I am counting the number of times I find a matching value of "Travel Time."

=DCount ("[WTI_Code_Descr]","qryReportCriteria","[WTI_Code_Descr] = 'Travel Time'&quot

There are 21 different values in that field that I am looking for with the other DCount statments on my report. Any additional thoughts on how to optimize this? I am going to try to see what I can do with counting in the query instead of the report.

JASON

_______________________________
Sleep is a poor substitute for coffee.

 

[JasonPurdueEE]

Another update: I tried placing the DCount statement in the query and that was no faster. I'm all out of ideas, can anybody think of any way to speed this up?

______________________________
Sleep is a poor substitute for coffee.

 

[dhookom]

If qryReportCriteria is the record source of your report then you might be working way too hard:
=DCount"[WTI_Code_Descr]", "qryReportCriteria", "[WTI_Code_Descr] = 'Travel Time'&quot
Could be replaced by:
=Abs( Sum([WTI_Code_Descr] = "Travel Time&quot)
This expression takes advantage of your report's recordsource. It will also work if your report is opened with a filter. The DCount() will not work if you open the report with a where clause.

 

[JasonPurdueEE]

Wonderful solution dhookom! That cut the process down to 30 seconds! Thank you both for your help.

______________________________
Sleep is a poor substitute for coffee.

 

[jrbarnett]

Jason
Something else to think about:

The domain of qryReportCriteria suggests to me that it is being taken from a query rather than the underlying table. If possible would it be possible to take it directly from the table rather than the query (which obviously has to be executed as the report opens?)

John

 

[JasonPurdueEE]

Good Morning John,
I dont think thats possible the way I have it configured. Heres what I have set up: a form with a series of comboboxes and such is used as criteria to edit the querydef on the fly and then the report is based off that query. So just for fun I ran the query by itself and it took 12 sec for it to run. I think one of the biggest speed problems here is the time to get the data from the server. I have the FE on my local machine and the BE on the server. But I'm not sure that there is anything I can do to speed that up. Are other types of databases faster over a network?

Thank you both for your optimization tips! I need to get back to fighting with my batch file. Either of you know how to get the cmd window to close after it opens my Access database? As it is now it runs in the background until the db closes. So I figure somehow Access is not returning the focus or control to the batch file after it opens (cause anything I put below the open the db line isnt run until the db is closed). Now I'm considering an on execute macro to try to kill it. Anyway, back to my batch file. Many thanks.

JASON

______________________________
Sleep is a poor substitute for coffee.

 

[jrbarnett]

If I understand you right, your access database is run from a batch file. Something like:

START "H:\Databases\Filename.mdb"

Is this correct? If not, what is the situation?

John

 

[JasonPurdueEE]

yes, a batch file is used to check for the newest copy of the FE and if need be copy the newest version. then it starts the database.

I'm using the following line to open the database.

rem Open WTI_FE
"C:\Program Files\Microsoft Office\Office\MSACCESS.EXE" "C:\WTI_db\WrenchtimeDatabase_FE.mde" /WRKGRP "S:\Public\Shared\WTI_folder\Secured.mdw"

near as I can tell, using the START method doesnt support the /WRKGRP switch that I need. the above line opens the db fine, but the cmd window remains open until access is closed. I need the cmd window to become completely or not to open at all. I've set the size of the window to 1,1 and the run position to -1, -1 so it doesnt display on the screen, but you can still see it on the task bar. any thoughts? I've been googling this part of yesterday and all this morning.

______________________________
Sleep is a poor substitute for coffee.

 

[JasonPurdueEE]

just for clarification on that line, everything from "C:\Program..." to "...\Secured.mdw" is on the same line.

______________________________
Sleep is a poor substitute for coffee.

 

[jrbarnett]

Try the following:
Create a shortcut to the executable file with your parameters, then

START path-to-shortcut.lnk
EXIT

John

 

[JasonPurdueEE]

Wow John, you've done it again! I used a series of shortcuts (the first to the batch file which in turn runs the second which opens Access). It works beautifully! Tis a shame I cant give you another star, you certainly deserve it!

______________________________
Sleep is a poor substitute for coffee.

 

[GingerR]

hi jason--possibly more time-saving tips from beginning of post:
make sure any fields you're summing or counting in the root table have been indexed (YES-DUPLICATES OK). another thing to check is that you have SUBDATASHEETS = NONE in table design, and turn off the autocorrect feature (TOOLS+OPTIONS+GENERAL TAB+uncheck TRACK NAME AUTOCORRECT INFO. this last one--if you are in DESIGN MODE you might want to leave it on. that's what changes your name of i.e. a table thru the queries and forms it's used in. or something like that. anyhow, uncheck it before sending to production.

hope this helps--g

 

[JasonPurdueEE]

Hey GingerR,
Thank you for the suggestions, I will look into them all in the morning. One question, what are the disadvantages to indexing a field? Are there any?

JASON



______________________________
Sleep is a poor substitute for coffee.

 

[dhookom]

Indexing a field causes larger files and some data entry slowdown. Since hard-drive space is cheap and you probably will never notice the decrease in speed, index all fields that might be used to Sort or Group or filter.

Duane
MS Access MVP

 

[GingerR]

i agree with duane. for me, i have a lot of heavy calculations, charts, etc....so the increase in speed throughout the db far outweighs any decrease in speed for data entry or increase in space. the managers that view my stuff are more important and i dont want them being irritated with the time it takes to crunch so much data.