<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META http-equiv=Content-Type content="text/html; charset=iso-8859-1">
<META content="MSHTML 6.00.2800.1170" name=GENERATOR>
<STYLE></STYLE>
</HEAD>
<BODY bgColor=#ffffff>
<DIV><FONT face=Arial size=2>Updating all the records without the index would be
slow. I was originally doing it that way, then added the indexes to see if
the update would run faster. It didn't appreciably. But updating all
records without the index would probably be faster. OTOH there seems to be a bit
of time consumed by creating the indexes as well.</FONT></DIV>
<DIV><FONT face=Arial size=2></FONT> </DIV>
<DIV><FONT face=Arial size=2>Rocky</FONT></DIV>
<DIV> </DIV>
<BLOCKQUOTE
style="PADDING-RIGHT: 0px; PADDING-LEFT: 5px; MARGIN-LEFT: 5px; BORDER-LEFT: #000000 2px solid; MARGIN-RIGHT: 0px">
<DIV style="FONT: 10pt arial">----- Original Message ----- </DIV>
<DIV
style="BACKGROUND: #e4e4e4; FONT: 10pt arial; font-color: black"><B>From:</B>
<A title=jcolby@colbyconsulting.com
href="mailto:jcolby@colbyconsulting.com">John Colby</A> </DIV>
<DIV style="FONT: 10pt arial"><B>To:</B> <A title=accessd@databaseadvisors.com
href="mailto:accessd@databaseadvisors.com">accessd@databaseadvisors.com</A>
</DIV>
<DIV style="FONT: 10pt arial"><B>Sent:</B> Tuesday, June 03, 2003 4:40
AM</DIV>
<DIV style="FONT: 10pt arial"><B>Subject:</B> RE: [AccessD] Update Query -
Need to Speed It Up</DIV>
<DIV><BR></DIV>
<DIV><SPAN class=657283511-03062003><FONT face=Arial color=#0000ff size=2>What
I'm trying to say is that if you just needed to update ALL the records in the
dataset (add 5% to a cost for example), then dropping the index, updating,
then adding the index back would probably be faster. In your case, if
you have a filter that says "only those not 0" (for example) and you drop the
index, then it takes LONGER to find those records that are "not 0" and thus
dropping the index SLOWS the thing down (as you observed).</FONT></SPAN></DIV>
<DIV><SPAN class=657283511-03062003><FONT face=Arial color=#0000ff
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=657283511-03062003><FONT face=Arial color=#0000ff size=2>If
in one case (with an index) you say "update all records <> 0" and in the
other case you just drop the index on the column that might be 0 and then just
update ALL the records to be zero... then which is faster?</FONT></SPAN></DIV>
<P><FONT size=2>John W. Colby<BR>www.colbyconsulting.com</FONT> </P>
<BLOCKQUOTE dir=ltr style="MARGIN-RIGHT: 0px">
<DIV class=OutlookMessageHeader dir=ltr align=left><FONT face=Tahoma
size=2>-----Original Message-----<BR><B>From:</B>
accessd-bounces@databaseadvisors.com
[mailto:accessd-bounces@databaseadvisors.com]<B>On Behalf Of </B>Rocky
Smolin - Beach Access Software<BR><B>Sent:</B> Tuesday, June 03, 2003 12:05
AM<BR><B>To:</B> accessd@databaseadvisors.com<BR><B>Subject:</B> Re:
[AccessD] Update Query - Need to Speed It Up<BR><BR></FONT></DIV>
<DIV><FONT face=Arial size=2>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.</FONT></DIV>
<DIV><FONT face=Arial size=2></FONT> </DIV>
<DIV><FONT face=Arial size=2>Rocky</FONT></DIV>
<DIV> </DIV>
<BLOCKQUOTE
style="PADDING-RIGHT: 0px; PADDING-LEFT: 5px; MARGIN-LEFT: 5px; BORDER-LEFT: #000000 2px solid; MARGIN-RIGHT: 0px">
<DIV style="FONT: 10pt arial">----- Original Message ----- </DIV>
<DIV
style="BACKGROUND: #e4e4e4; FONT: 10pt arial; font-color: black"><B>From:</B>
<A title=jcolby@colbyconsulting.com
href="mailto:jcolby@colbyconsulting.com">John Colby</A> </DIV>
<DIV style="FONT: 10pt arial"><B>To:</B> <A
title=accessd@databaseadvisors.com
href="mailto:accessd@databaseadvisors.com">accessd@databaseadvisors.com</A>
</DIV>
<DIV style="FONT: 10pt arial"><B>Sent:</B> Monday, June 02, 2003 8:43
PM</DIV>
<DIV style="FONT: 10pt arial"><B>Subject:</B> RE: [AccessD] Update Query -
Need to Speed It Up</DIV>
<DIV><BR></DIV>
<DIV><SPAN class=306244103-03062003><FONT face=Arial color=#0000ff
size=2>Yes, but are you updating different data sets? With indexes
are you only updating those needing updating, without indexes updating
all?</FONT></SPAN></DIV>
<P><FONT size=2>John W. Colby<BR><A
href="http://www.colbyconsulting.com">www.colbyconsulting.com</A></FONT>
</P>
<BLOCKQUOTE dir=ltr style="MARGIN-RIGHT: 0px">
<DIV class=OutlookMessageHeader dir=ltr align=left><FONT face=Tahoma
size=2>-----Original Message-----<BR><B>From:</B> <A
href="mailto:accessd-bounces@databaseadvisors.com">accessd-bounces@databaseadvisors.com</A>
[mailto:accessd-bounces@databaseadvisors.com]<B>On Behalf Of </B>Rocky
Smolin - Beach Access Software<BR><B>Sent:</B> Monday, June 02, 2003
11:25 PM<BR><B>To:</B> accessd@databaseadvisors.com<BR><B>Subject:</B>
Re: [AccessD] Update Query - Need to Speed It Up<BR><BR></FONT></DIV>
<DIV><FONT face=Arial size=2>John:</FONT></DIV>
<DIV><FONT face=Arial size=2></FONT> </DIV>
<DIV><FONT face=Arial size=2>1:44 without indexes; 1:17 with
indexes. Go figure</FONT></DIV>
<DIV><FONT face=Arial size=2></FONT> </DIV>
<DIV><FONT face=Arial size=2>Rocky</FONT></DIV>
<DIV> </DIV>
<BLOCKQUOTE
style="PADDING-RIGHT: 0px; PADDING-LEFT: 5px; MARGIN-LEFT: 5px; BORDER-LEFT: #000000 2px solid; MARGIN-RIGHT: 0px">
<DIV style="FONT: 10pt arial">----- Original Message ----- </DIV>
<DIV
style="BACKGROUND: #e4e4e4; FONT: 10pt arial; font-color: black"><B>From:</B>
<A title=bchacc@san.rr.com href="mailto:bchacc@san.rr.com">Rocky
Smolin - Beach Access Software</A> </DIV>
<DIV style="FONT: 10pt arial"><B>To:</B> <A
title=accessd@databaseadvisors.com
href="mailto:accessd@databaseadvisors.com">accessd@databaseadvisors.com</A>
</DIV>
<DIV style="FONT: 10pt arial"><B>Sent:</B> Monday, June 02, 2003 5:06
PM</DIV>
<DIV style="FONT: 10pt arial"><B>Subject:</B> Re: [AccessD] Update
Query - Need to Speed It Up</DIV>
<DIV><BR></DIV>
<DIV><FONT face=Arial size=2>Well I'll try a benchmark with no indexes
- see if it's faster.</FONT></DIV>
<DIV><FONT face=Arial size=2></FONT> </DIV>
<DIV><FONT face=Arial size=2>Rocky</FONT></DIV>
<DIV> </DIV>
<BLOCKQUOTE
style="PADDING-RIGHT: 0px; PADDING-LEFT: 5px; MARGIN-LEFT: 5px; BORDER-LEFT: #000000 2px solid; MARGIN-RIGHT: 0px">
<DIV style="FONT: 10pt arial">----- Original Message ----- </DIV>
<DIV
style="BACKGROUND: #e4e4e4; FONT: 10pt arial; font-color: black"><B>From:</B>
<A title=jcolby@colbyconsulting.com
href="mailto:jcolby@colbyconsulting.com">John Colby</A> </DIV>
<DIV style="FONT: 10pt arial"><B>To:</B> <A
title=accessd@databaseadvisors.com
href="mailto:accessd@databaseadvisors.com">accessd@databaseadvisors.com</A>
</DIV>
<DIV style="FONT: 10pt arial"><B>Sent:</B> Monday, June 02, 2003
4:03 PM</DIV>
<DIV style="FONT: 10pt arial"><B>Subject:</B> RE: [AccessD] Update
Query - Need to Speed It Up</DIV>
<DIV><BR></DIV>
<DIV><SPAN class=755320023-02062003><FONT face=Arial color=#0000ff
size=2>Rocky,</FONT></SPAN></DIV>
<DIV><SPAN class=755320023-02062003><FONT face=Arial color=#0000ff
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=755320023-02062003><FONT face=Arial color=#0000ff
size=2>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.</FONT></SPAN></DIV>
<P><FONT size=2>John W. Colby<BR>www.colbyconsulting.com</FONT> </P>
<BLOCKQUOTE dir=ltr style="MARGIN-RIGHT: 0px">
<DIV class=OutlookMessageHeader dir=ltr align=left><FONT
face=Tahoma size=2>-----Original Message-----<BR><B>From:</B>
accessd-bounces@databaseadvisors.com
[mailto:accessd-bounces@databaseadvisors.com]<B>On Behalf Of
</B>Rocky Smolin - Beach Access Software<BR><B>Sent:</B> Monday,
June 02, 2003 5:59 PM<BR><B>To:</B>
accessd@databaseadvisors.com<BR><B>Subject:</B> Re: [AccessD]
Update Query - Need to Speed It Up<BR><BR></FONT></DIV>
<DIV><FONT face=Arial size=2>8:10. But with Erwin's filter
1:28. Next stop, I guess is MSDE.</FONT></DIV>
<DIV><FONT face=Arial size=2></FONT> </DIV>
<DIV><FONT face=Arial size=2>Rocky</FONT></DIV>
<DIV> </DIV>
<BLOCKQUOTE
style="PADDING-RIGHT: 0px; PADDING-LEFT: 5px; MARGIN-LEFT: 5px; BORDER-LEFT: #000000 2px solid; MARGIN-RIGHT: 0px">
<DIV style="FONT: 10pt arial">----- Original Message -----
</DIV>
<DIV
style="BACKGROUND: #e4e4e4; FONT: 10pt arial; font-color: black"><B>From:</B>
<A title=harkins@iglou.com href="mailto:harkins@iglou.com">Susan
Harkins</A> </DIV>
<DIV style="FONT: 10pt arial"><B>To:</B> <A
title=accessd@databaseadvisors.com
href="mailto:accessd@databaseadvisors.com">accessd@databaseadvisors.com</A>
</DIV>
<DIV style="FONT: 10pt arial"><B>Sent:</B> Monday, June 02, 2003
2:08 PM</DIV>
<DIV style="FONT: 10pt arial"><B>Subject:</B> Re: [AccessD]
Update Query - Need to Speed It Up</DIV>
<DIV><BR></DIV>
<DIV><FONT face=Arial size=2>How long is too long? With 2
million records, and Jet, just how fast do you want it to be???
:) </FONT></DIV>
<DIV><FONT face=Arial size=2></FONT> </DIV>
<DIV><FONT face=Arial size=2>And about that blank -- do you
really mean a zero-length string or did you really want
Null?</FONT></DIV>
<DIV><FONT face=Arial size=2></FONT> </DIV>
<DIV><FONT face=Arial size=2>Susan H. </FONT></DIV>
<BLOCKQUOTE
style="PADDING-RIGHT: 0px; PADDING-LEFT: 5px; MARGIN-LEFT: 5px; BORDER-LEFT: #000000 2px solid; MARGIN-RIGHT: 0px">
<DIV><FONT face=Arial size=2></FONT><BR></DIV>
<DIV><FONT face=Arial size=2>Dear List:</FONT></DIV>
<DIV><FONT face=Arial size=2></FONT> </DIV>
<DIV><FONT face=Arial size=2>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. </FONT></DIV>
<DIV><FONT face=Arial size=2>The SQL is:</FONT></DIV>
<DIV><FONT face=Arial size=2></FONT> </DIV>
<DIV><FONT face=Arial size=2>UPDATE tblInvoiceDetail SET
tblInvoiceDetail.fldInvoiceDetailPPV = """",
tblInvoiceDetail.fldInvoiceDetailVariancePercent =
0;</FONT></DIV>
<DIV><FONT face=Arial size=2></FONT> </DIV>
<DIV><FONT face=Arial size=2>It's setting one field to blank
the other to 0.</FONT></DIV>
<DIV><FONT face=Arial size=2></FONT> </DIV>
<DIV><FONT face=Arial size=2>Is there a way to make it run any
faster?</FONT></DIV>
<DIV><FONT face=Arial size=2></FONT> </DIV>
<DIV><FONT face=Arial size=2></FONT> </DIV>
<DIV><FONT face=Arial size=2>MTIA,</FONT></DIV>
<DIV><FONT face=Arial size=2></FONT> </DIV>
<DIV><FONT face=Arial size=2>Rocky Smolin<BR>Beach Access
Software</FONT></DIV>
<DIV><FONT face=Arial size=2></FONT> </DIV>
<DIV><FONT face=Arial size=2></FONT> </DIV>
<P>
<HR>
<P></P>_______________________________________________<BR>AccessD
mailing
list<BR>AccessD@databaseadvisors.com<BR>http://databaseadvisors.com/mailman/listinfo/accessd<BR>Website:
http://www.databaseadvisors.com<BR></BLOCKQUOTE>
<P>
<HR>
<P></P>_______________________________________________<BR>AccessD
mailing
list<BR>AccessD@databaseadvisors.com<BR>http://databaseadvisors.com/mailman/listinfo/accessd<BR>Website:
http://www.databaseadvisors.com<BR></BLOCKQUOTE></BLOCKQUOTE>
<P>
<HR>
<P></P>_______________________________________________<BR>AccessD
mailing
list<BR>AccessD@databaseadvisors.com<BR>http://databaseadvisors.com/mailman/listinfo/accessd<BR>Website:
http://www.databaseadvisors.com<BR></BLOCKQUOTE>
<P>
<HR>
<P></P>_______________________________________________<BR>AccessD
mailing
list<BR>AccessD@databaseadvisors.com<BR>http://databaseadvisors.com/mailman/listinfo/accessd<BR>Website:
http://www.databaseadvisors.com<BR></BLOCKQUOTE></BLOCKQUOTE>
<P>
<HR>
<P></P>_______________________________________________<BR>AccessD mailing
list<BR>AccessD@databaseadvisors.com<BR>http://databaseadvisors.com/mailman/listinfo/accessd<BR>Website:
http://www.databaseadvisors.com<BR></BLOCKQUOTE></BLOCKQUOTE>
<P>
<HR>
<P></P>_______________________________________________<BR>AccessD mailing
list<BR>AccessD@databaseadvisors.com<BR>http://databaseadvisors.com/mailman/listinfo/accessd<BR>Website:
http://www.databaseadvisors.com<BR></BLOCKQUOTE></BODY></HTML>