<!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><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></BODY></HTML>