Why The Difference In Speed?

From: chris (anonymous_at_discussions.microsoft.com)
Date: 04/04/04 

Date: Sat, 3 Apr 2004 17:18:11 -0800

Hello.

If I perform this query:

UPDATE table1 SET field1='Y'
 WHERE field1 IN (SELECT field1 FROM table2
                   WHERE id > 0 AND id < 100);

...it takes a second at most

If I perform this query:
UPDATE table1 SET field1='Y'
 WHERE field1 IN (SELECT field1 FROM table2
                   WHERE id > @counter AND id <
@counter+100);

...it takes an eternity.

Basically, I want to do a loop and update a table from
another table. I've found that if I do the updates using
smaller select chunks from the second table it is much
faster.

Any help appreciated.

Chris.

Relevant Pages

  • Splitting file
    ... UPDATE TABLE1 SET ... ... No row was found for FETCH, UPDATE or DELETE; ... query is an empty table. ...
    (comp.unix.shell)
  • Re: Splitting file
    ... UPDATE TABLE1 SET ... ... No row was found for FETCH, UPDATE or DELETE; ... query is an empty table. ...
    (comp.unix.shell)
  • Re: Updating the first row which is null
    ... I am trying to update table1 and set a new value with a condition ... The query executes but it doesn t not update the informations. ... UPDATE table1 SET table1.Name = newName ... Updating the first row which is null ...
    (comp.databases.ms-access)
  • Re: Updating the first row which is null
    ... In your query, your WHERE clause is limiting your results to ... I am trying to update table1 and set a new value with a condition ... The query executes but it doesn t not update the informations. ... UPDATE table1 SET table1.Name = newName ...
    (comp.databases.ms-access)