When do sql optimizations become overkill?_问答_开发者

I'm updating tables with millions of records and I need to be as efficient as possible. Is there a point at which adding more criteria to the where clause will actually hurt rather than help?

For example, if know I want to set a column to 3 I could use this query:

update mytable set col = 3

Or I could update the record only i开发者_如何学JAVAf it's different

update mytable set col = 3 where col <> 3

I could also filter it so it only updates records added since the last time I ran this process

update mytable set col = 3 where col <> 3 and createDate > @lastRunDate

And perhaps I could look for more things in additional columns.

I guess my question is if there is a point where the cost of looking at additional columns outweighs the cost of the update itself and if there's a principle you can use to determine where to draw the line.

Update

~~So here's the principle I'm trying to piece together based on what was said. Feel free to argue with this and I'll update it accordingly:~~

If no indexed columns to filter on, add as much criteria as possible to limit the records being updated since a full table scan is going to happen anyway.

If the difference in records between filtering on only indexed columns and filtering on all possible columns is marginal, only use the indexed columns and avoid the full table scan.

If you have a mix of indexed and non-indexed columns, definitely use the indexed columns if you can and only use non-index columns if... [[I'm still struggling with this part. What's the threshold for introducing the non-indexed columns in the where clause?]]

Update #2 Sounds like I have my answer.

If you have an index on "col", then running your first query will update millions of rows regardless; your second query would potentially only update a few and find those quickly if there's an index available. If you don't have an index on that column, the effect will be marginal since a full table or index scan must occur to check all rows in your table (you'll just have fewer actual updates, but that's it).

The whole point of restricting your queries usnig WHERE clauses is to reduce the scope of your query, e.g. the number of rows SQL Server has to look at. Less data to process is always faster than just doing it to all millions of rows......

In response to your update: the main goal of using a WHERE clause is to reduce the number of rows you need to inspect / touch. If you have a means (typically an index) to reduce that number from 100% to a few percent, then it's definitely worth it. That's the whole point of having indices (mostly for SELECTs, but applies to other operations, too, of course).

If you have a suitable index, and thus you can pluck out a few hundred rows to check against a criteria versus having to inspect millions of rows, you'll always be faster. If you have a good book index in a bookstore that guides you easily to the two shelves where the books that interest you are located, you'll find what you're looking for more quickly than when you have to criss-cross the whole bookstore since there's no index available.

There obviously is a point where yet another criteria or index doesn't help anymore. If that's the case, typically yet another WHERE clause won't really help much - or at all. But in this case, the SQL query optimizer will find those cases and filter them out (possibly even just ignoring them when deciding on what the best query execution plan is).

This really comes down to index usage and query optimization. I would suggest looking at the query plan before making any decisions.

Adding indexed fields to the where clause will often improve query time, however, adding non-indexed fields can result in table scans which will slow your query.

My suggestion is write a query that works, look at the execution time, work to reduce it to an exceptable level by looking at the query plan. Don't over optimize, go for the acceptable solution.