[AccessD] Update Query - Need to Speed It Up

Charles:

That's what I did and it reduced the time from 8+ minutes to less than 1 1/2
minutes.

Rocky

----- Original Message ----- 
From: "Wortz, Charles" <CWortz at tea.state.tx.us>
To: <accessd at databaseadvisors.com>
Sent: Tuesday, June 03, 2003 5:34 AM
Subject: RE: [AccessD] Update Query - Need to Speed It Up


> Rocky,
>
> Since it doesn't make sense to me to try to change the PPV and
> Variance's of all InvoiceDetail records, why don't you have a Where
> clause to select only those that need changing?  Presumably some Invoice
> record changed, so only its InvoiceDetail records now need updating.
>
>
> Charles Wortz
> Software Development Division
> Texas Education Agency
> 1701 N. Congress Ave
> Austin, TX 78701-1494
> 512-463-9493
> CWortz at tea.state.tx.us
> -----Original Message-----
> From: Rocky Smolin - Beach Access Software [mailto:bchacc at san.rr.com]
> Sent: Monday 2003 Jun 02 23:05
> To: accessd at databaseadvisors.com
> Subject: Re: [AccessD] Update Query - Need to Speed It Up
>
>
> Not sure I understand 'different data sets'.  The test I ran was run on
> the same data - same conditions, etc.  of the 2.5 million records, there
> were only a handful anyway in which the fields were not "" and 0.  So
> it's mostly the filtered retrieval not the update that's being measured.
> But in both cases, I'm updating the same records.
>
> Rocky
>
> ----- Original Message ----- 
> From: John Colby
> To: accessd at databaseadvisors.com
> Sent: Monday, June 02, 2003 8:43 PM
> Subject: RE: [AccessD] Update Query - Need to Speed It Up
>
>
> Yes, but are you updating different data sets?  With indexes are you
> only updating those needing updating, without indexes updating all?
> John W. Colby
> www.colbyconsulting.com
> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com
> [mailto:accessd-bounces at databaseadvisors.com]On Behalf Of Rocky Smolin -
> Beach Access Software
> Sent: Monday, June 02, 2003 11:25 PM
> To: accessd at databaseadvisors.com
> Subject: Re: [AccessD] Update Query - Need to Speed It Up
>
>
> John:
>
> 1:44 without indexes; 1:17 with indexes.  Go figure
>
> Rocky
>
> ----- Original Message ----- 
> From: Rocky Smolin - Beach Access Software
> To: accessd at databaseadvisors.com
> Sent: Monday, June 02, 2003 5:06 PM
> Subject: Re: [AccessD] Update Query - Need to Speed It Up
>
>
> Well I'll try a benchmark with no indexes - see if it's faster.
>
> Rocky
>
> ----- Original Message ----- 
> From: John Colby
> To: accessd at databaseadvisors.com
> Sent: Monday, June 02, 2003 4:03 PM
> Subject: RE: [AccessD] Update Query - Need to Speed It Up
>
>
> Rocky,
>
> Part of the problem is that the fields are indexed.  Using the indexes
> to select only the fields that need updating is appropriate of course if
> it's not all the records that need updating.  The other option is to
> delete the index, update, then rebuild the index.  Apparently after each
> update (each record) Access foes in and adjusts the index.  Removing the
> index entirely, doing the updates, then rebuilding the index from
> scratch is SUPPOSED to be faster.  never actually tried it tho.
> John W. Colby
> www.colbyconsulting.com
> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com
> [mailto:accessd-bounces at databaseadvisors.com]On Behalf Of Rocky Smolin -
> Beach Access Software
> Sent: Monday, June 02, 2003 5:59 PM
> To: accessd at databaseadvisors.com
> Subject: Re: [AccessD] Update Query - Need to Speed It Up
>
>
> 8:10.  But with Erwin's filter 1:28.  Next stop, I guess is MSDE.
>
> Rocky
>
> ----- Original Message ----- 
> From: Susan Harkins
> To: accessd at databaseadvisors.com
> Sent: Monday, June 02, 2003 2:08 PM
> Subject: Re: [AccessD] Update Query - Need to Speed It Up
>
>
> How long is too long? With 2 million records, and Jet, just how fast do
> you want it to be??? :)
>
> And about that blank -- do you really mean a zero-length string or did
> you really want Null?
>
> Susan H.
>
>
> Dear List:
>
> 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?
>
>
> MTIA,
>
> Rocky Smolin
> Beach Access Software
> _______________________________________________
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com