Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2003
    Posts
    2

    Unanswered: What is the fastest way to do this?

    I need to do an update that looks something like this...

    update TABLE1 set FIELD1 = NULL where FIELD2 = value1 or FIELD2 = value2 or...FIELD10000 = value10000

    Notice that this can become a very large query. The problem with doing it as shown above is that I get stack overflow on the server.

    I have tried doing this update in a loop...in effect issuing 10000 update statements. This works but is unacceptably slow as expected.

    Somebody suggested the use of a temp table with the where clause values inserted into it and then an update with a join to this temporary table. I did this too and while it works, it was as slow and hence not acceptable.

    Can somebody suggest a better way to do this? Thanks!

  2. #2
    Join Date
    Jul 2002
    Location
    Village, MD
    Posts
    621
    Of course it depends on what kind of value10000s do you use but the example below works fine. You could think about to calculate somehow your values (if it is possible) or keep them in permanent table if they are the same for some time. In another case something is wrong with design of your db or application. Famous rule - divide and rule still works.

    drop table table1
    create table table1(id int identity primary key,code varchar(50))
    go
    while 1=1
    insert table1(code) values('*')-- add some rows(40000 works not bad)
    go
    select count(*) from table1
    update table1 set code='3'
    where id in(select top 10000 id from table1)

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Your temporary table solution should not be that slow. Did you try putting an index on your table before joining it in your select query? That would improve the performance, I bet.

    Another option would be to use a table variable instead of a temporary table. Table variables are stored in memory and are not written to disk as temporary tables are. This often makes them faster, though they are less flexible than temporary tables in other regards.

    Either way, a JOIN should always be faster than using a long list of equalities in a WHERE clause, (or an IN clause, which would actually be preferred over your OR list).

    blindman

  4. #4
    Join Date
    Nov 2003
    Posts
    2
    Thanks snail and blindman for your suggestions.

    I took a closer look at why the temporary table solution was slow as the loop solution. What I found out was that most of the time was being spend in string operations like concatenation and using string lists. The database operations themselves were quite fast.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •