Jim Dettman
jimdettman at earthlink.net
Tue Jun 3 08:56:34 CDT 2003
Rocky, <<BTW, I found in very ling reports that its the Page Number that slows down the first page appearing in preview, if you use the 'page xxx of yyyy' approach. If you drop the 'of yyy' part the first page comes up right away. With an 800 page report it can take minutes for the first page to appear with the 'of yyy' in the page footer.>> That's because when you add the 'yyy', you force Access to run the report twice. The first pass it does to determine the last page number. The output is sent to the Null device. When that's complete, it then starts over and prints the report. Jim Dettman President, Online Computer Services of WNY, Inc. (315) 699-3443 jimdettman at earthlink.net -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com]On Behalf Of Rocky Smolin - Beach Access Software Sent: Tuesday, June 03, 2003 9:34 AM To: accessd at databaseadvisors.com Subject: Re: [AccessD] Update Query - Need to Speed It Up Gustav: It's not really a clean-up. It's zeroing out these two fields preparatory to doing a purchase price variance analysis for a report. The underlying data is very stable but not completely static and the user can specify the level of PPV to be noted on the report. So after the fields are reset, I retrieve the data set in product number/Invoice date sequence and look for variances in the purchase price over time for each product, noting where the price of a purchase varies more than the user defined limit from the previous purchase and that gets flagged in the record (also on the previous and following records) which makes it easy to run the report. I suppose I could put these two fields in a separate table with a one-to-one relationship. But then I have a join to deal with when running the report which might take a long time. Since the UPDATE query runs acceptably fast now the pacing item is the sequential search through the 2.5 mil records for purchase price variances. BTW, I found in very ling reports that its the Page Number that slows down the first page appearing in preview, if you use the 'page xxx of yyyy' approach. If you drop the 'of yyy' part the first page comes up right away. With an 800 page report it can take minutes for the first page to appear with the 'of yyy' in the page footer. Regards, Rocky ----- Original Message ----- From: "Gustav Brock" <gustav at cactus.dk> To: <accessd at databaseadvisors.com> Sent: Monday, June 02, 2003 11:27 PM Subject: Re: [AccessD] Update Query - Need to Speed It Up > Hi Rocky > > Why are you doing this regular clean-up thing (which I guess it is)? > Wouldn't it be wiser to set these values when you append or manipulate > those records? > > /gustav > > > > I am running an update query on a table with 2,500,000 records and it's taking just a bit too long for the my and the client's liking. > > The SQL is: > > > UPDATE tblInvoiceDetail SET tblInvoiceDetail.fldInvoiceDetailPPV = """", tblInvoiceDetail.fldInvoiceDetailVariancePercent = 0; > > > It's setting one field to blank the other to 0. > > > Is there a way to make it run any faster? > > _______________________________________________ > 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