[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
>