Brad Marks
BradM at blackforestltd.com
Mon Sep 16 15:34:04 CDT 2013
Arthur, Thanks for sharing your ideas/insights. Yes, the connection to SQL Server is ODBC. When I generate the report on the stand-alone PC, it is generated in less than half the time that it takes to generate that exact same report (same accdr file) on the server. I am just curious as to how the two boxes differ and why it takes much longer to generate the report on the more powerful box. Brad PS. I plan to dig deeper into Stored Procedures and PTQs down the road when I have more time available. -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Arthur Fuller Sent: Monday, September 16, 2013 3:08 PM To: Access Developers discussion and problem solving Subject: Re: [AccessD] Job Duration Puzzler - Access 2007 Report First of all, I can't explain the sudden jump in execution time, but since you run the report daily, I assume that a significant amount of data is added every day. It's possible that your tables have become fragmented. That would be one thing to check. You didn't mention the data-source, other than saying the data comes from several SQL Server tables. Is the connection to SQL Server an ODBC connection? Is the data-source an Access query using a WHERE clause? If so, that is a definite performance hit, since in that context ALL the data travels the pipe, and then Access applies the WHERE conditions. You could probably gain hugely in performance if you create either a View or a Stored procedure on the server database and then change the data-source to invoke that. A good way to handle that is to create a view on the database that mimics your Access query, and then create a Pass-Through Query (PTQ) in Access, whose content reads "CALL <your_view_or_sp>". Finally, change the data-source on the report to the name of the PTQ just created. This technique eliminates the passing of all the data to Access; instead all the work is performed on the server. Recently I did some consulting/mentoring with a guy who wrote a monster Access app, with hundreds of Access queries. We identified some bottlenecks and followed the procedure outlined above, and the results were dramatic to say the least: execution was sometimes 10% of what it was originally. As a result of our initial eexperiments, the client gradually replaced almost all his Access queries with PTQs, and overall the conversion was a huge success, eliciting kudos from many of his customers. Hope this helps. Arthur On Mon, Sep 16, 2013 at 3:32 PM, Rocky Smolin <rockysmolin at bchacc.com>wrote: > 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 > -- Arthur -- 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=91A1B28C9A.BC189