[AccessD] Update Query - Need to Speed It Up

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