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!
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))
insert table1(code) values('*')-- add some rows(40000 works not bad)
select count(*) from table1
update table1 set code='3'
where id in(select top 10000 id from table1)
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).
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.