[AccessD] Job Duration Puzzler - Access 2007 Report

Brad Marks BradM at blackforestltd.com
Tue Sep 17 13:19:52 CDT 2013


All,

I conducted some additional tests early this morning before anyone else
was using the system.

I ran the long-running report (20 minutes).  While this was running, I
watched the system with the Windows Task Manager.  The Access report
would often get 10-15% of the CPU.  The remainder of the CPU was going
to the System Idle Process.  

I then ran some smaller, faster running Access reports.  One such report
ran in 1 second on the smaller PC and took nearly 30 seconds to run on
the larger Server.

I also reviewed the ODBC connections.  They are set up exactly the same
on the smaller PC as on the larger server.  I wanted to be sure that
Tracing and logging were not turned on.

This situation really has me puzzled.

Thanks for the help!

Brad    

"All we are saying is give peas a chance" - International Legume Council

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
~~~~~~~~ 

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Rocky Smolin
Sent: Monday, September 16, 2013 5:33 PM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] Job Duration Puzzler - Access 2007 Report

Odd.  I'd ask about hardware differences between the two boxes but you
say the server box used to be fast and is now slow.  So I'm assuming no
hardware changes on the server. 

Next thing I'd try to determine is if this slowness is unique to this
app or if other apps are experiencing the same slowdown.

R


-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Brad Marks
Sent: Monday, September 16, 2013 1:23 PM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Job Duration Puzzler - Access 2007 Report

Rocky,

Thanks for your help.

I have watched the Task Manager a few times and not seen anything else
using a lot of the cycles.

This past weekend, I ran a number of tests, both on the Server and on
the Stand-alone PC.

I am quite sure that nothing else was running on either of these two
boxes during my tests.

The strange thing is that the stand-alone PC is able to generate the
report in less than half the time that it takes the Server to create the
report.

It almost feels like the connection between the Server and the database
is somehow different than the connection between the PC and the
database.  Both machines are using the exact same .accdr file and both
are pulling the same data from the same SQL-Server tables.

Thanks again,

Brad  

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Rocky Smolin
Sent: Monday, September 16, 2013 2:33 PM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] Job Duration Puzzler - Access 2007 Report

WAGS:

Other jobs running at the same time now that weren't running before
taking machine cycles?  Change in priority of your job? Can you monitor
the Task Manager for the server while your job is running to see what
else might be going on?

R
 

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Brad Marks
Sent: Monday, September 16, 2013 12:25 PM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Job Duration Puzzler - Access 2007 Report

Rocky,

Yes, it is running entirely on the server, pulling data from a local
SQL-Server database.

Brad  

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Rocky Smolin
Sent: Monday, September 16, 2013 2:21 PM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] Job Duration Puzzler - Access 2007 Report

Is it running entirely on the server with nothing coming across the
wire?

r 

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Brad Marks
Sent: Monday, September 16, 2013 12:15 PM
To: Access Developers discussion and problem solving
Subject: [AccessD] Job Duration Puzzler - Access 2007 Report

All,

We have a high level Access 2007 Report that is generated every night.
The data for this report is pulled from a number of SQL-Server database
tables. 

This report is automatically generated nightly on a server with these
specs
- MS Window Server 2003 R2 Enterprise Edition SP2 2.50 Ghz
12 GB Ram

About a month ago, I noticed that the "run time" for generating this
report went from 8 minutes to 20 minutes.  Everything works fine, except
that the run time has more than doubled.

I can also run this same report on a stand-alone PC with these specs -
Windows XP SP3 2.50 Ghz
3.25 GB Ram

When the same report is generated on this PC, the job runs for about 8
minutes.

Over the weekend, when nothing else was running, I conducted a number of
tests to confirm what I was seeing.

The results were consistent.  The generation of this report takes more
than twice as long on the Server as compared to the PC.

I am very puzzled and not sure where to look.

I don't know why the run times went up dramatically on the Server.

I don't understand why the run times vary between the two boxes.

Any insights or advice would be most appreciated.

Thanks,

Brad    
   



--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com

--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com

--
This message was scanned by ESVA and is believed to be clean.
Click here to report this message as spam. 
http://h0stname/cgi-bin/learn-msg.cgi?id=26A7228C9A.3C04B



--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com

--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com

--
This message was scanned by ESVA and is believed to be clean.
Click here to report this message as spam. 
http://h0stname/cgi-bin/learn-msg.cgi?id=4D40928C9A.CB309



--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com

--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com

--
This message was scanned by ESVA and is believed to be clean.
Click here to report this message as spam. 
http://h0stname/cgi-bin/learn-msg.cgi?id=C07FE28CAC.33212





More information about the AccessD mailing list