Unexpected table scan for id != id

Question

One application causes a heavy load on our Sql Server 2005 database. We don't control the application that runs this query hundres of times a minute:

select id,col1,col2,col3 from table where id != id

Note the id != id, meaning a row is not equal to itself. Unsurprisingly, the result is always no rows found. However, Sql Server does a clustered index scan scan every time it runs this query!

The id column is defined as:

varchar(15) not null primary key

The query plan shows a huge number for "Estimated Number of Rows". Does anyone have an idea why Sql Server needs the table scan to figure out the obvious?

Answer 1

I don't know why id != id takes so long in this case (although "obvious" is subjective - it isn't necessarily an optimisation I'd think of... it seems an unusual query; a cartesian join is painful, though). But in general, try "where 1 = 0" - or if you just want the schema, perhaps the slightly risky SET FMTONLY ON.

Edit: just saw " We don't control the application that runs this query hundres of times a minute"... hmmm... that makes it tricky.

Answer 2

Each value compares with the rest of the n-1 values. that is why it returns a huge number for "Estimated Number of Rows". For the above problem it's better to use not in.

The article here is a good pointer for your problem. I hope it helps you. http://www.sqlservercentral.com/articles/Performance+Tuning/2924/

Answer 3

You're biggest problem isn't the table scan. Your two biggest problems are:

• You have an absolutely useless query running 100's of times per minute against your database. My guess BTW, is that the query is actually trying to get the column names from the table as Marc Gravell suggests.

and more importantly:

• You don't have control over who or what is accessing your database.

That second problem especially is going to most likely cause you endless headaches. Assuming that you are part of the data team in your organization (since you're the one trying to solve this problem), you really should be looking to make the organizational changes necessary to do your job.

Good luck!

Answer 4

I would fake this query out... abstract away with a view, and dupe the query.

Rename your existing table 'table' to 'table_org' or something else, and create a view like this:

CREATE VIEW table
AS
SELECT * FROM table_org
WHERE id='BOGUSKEY'

Now, you should get your 1 scan through the table on the primary key, and it should (like the original query) find nothing. The application knows none the wiser...

Answer 5

I have seen this type of query.

Most likely the developers are building 'WHERE' clauses based on user input, current settings or some other factors. In many instances, maybe the default instance, they would need a WHERE clause that's just a placeholder. That's when they use criteria like 'id != id', '1 <> 1', etc.

The 'hundreds of times a minute' also leads me to believe that this is some misguided default placeholder.

Sometimes they'll use a criteria that does the opposite, always evaluates to true if the default case requires all the rows.

It's a long shot, but my suggestion would be to see if you can modify the application settings and see if this query goes away. You may end up with a small resultant set, but something that is run less frequently and better handled by SQL Server.

Answer 6

You might want to let the SQL Server support team know about this query (id <> id when the column is defined as a primary key) and the full table scan it causes, and to see if maybe they might want to add in an optimization in the query engine to ensure this doesn't cause a full table scan.

Either that, or talk to the support team of the app you can't control.

EDIT: Try the TechNet forum at http://social.msdn.microsoft.com/forums/en-US/sqldatabaseengine/threads/ to report the behavior.

Answer 7

Do you have a nonclustered index on the id column? If not, the most efficient course is always going to be a CIX scan. Try adding a NCIX on the ID column - it may still perform a scan, but at least it will be a scan on a very small index. If you were on SQL Server 2008, you could create a filtered index (WHERE id <> id), and SQL Server would use the (empty) filtered index to satisfy the query.

Answer 8

Being less familiar with SQL Server, I think the solution below could also apply to SQL Server.

In Oracle I think you can outsmart the naive SQL by using a materialized view and query rewriting. The materialized view would contain no rows, and the query rewrite recognizes the SQL and reroutes the query to the empty view instead. The materialized view would never need to be updated, due to the fact that it will always be empty.

Answer 9

After reading the answers here and your edits, let me sum up your options:

1. Change MS SQL Server to handle this case (basically, talk to Microsoft support)
2. Change the application to avoid this case, or do it differently (basically, talk to support of the company that made the application)
3. Change to something other than SQL Server (if the application allows it), that handles this case
4. Change to another application

None of these are good solutions, but unfortunately, they're the only ones you have. You have to pick one, and go with it.

I would try solution 2 first, it is the one that would/should take the shortest time to execute.

If, on the other hand, that company is unwilling to change the application, then I would go with solution 4. This is a major performance bug, and if the company is unwilling or unable to fix that problem, you have to ask yourself, what else is lurking around the next corner?

Answer 10

I'm almost too ashamed to submit this answer, but in the spirit of "if nothing sane works, try the insane"...

    Create a constraint on the table where id = id?

The where clause could only return rows breaching the constraint, of which there are none by definition. This additional (albeit redundant) information may assist the optimiser in your case. It may equally achieve the square root of nothing, but in your case I'd give it a go just in case...

Answer 11

Looks to me like you have a political problem masquerading as a technical problem. You can throw lots of time and effort at the technical problem, but as long as you keep repeating "we don't control the application", you're shutting yourself of from pursuing political options.

You can't control the app, but you can probably arrange to have some influence applied. Make all stakeholders aware of how the behavior of this application is affecting everyone else who uses that database (use graphs, since you'll want to get this message across quickly to management). And be clear it's a problem for the application's author or Microsoft to fix. That might result in pressure on the app's author, or it might yield an "O.K., fine. Let's buy another database for that application" response from management.

(You'll want to have an answer available for the question of whether Microsoft has "fixed" this in SQL Server 2008.)

Answer 12

I suspect that SqlServer doesn't "know" that != (should be "<>" ?) is anti-reflexive (i.e. A != A is always false)... it just see that it isn't constant (it depends on values from a result row) and hence puts it into the result filter. So "where id <> id" is potentially very different to "where 1 <> 1".

Now, they could certainly make it detect this case; but I guess it's just not ever got onto their priority list because it's somewhere between "odd" and "silly" to be doing this.

Yeah, the application doing this sucks, I think you already know that ;)

Answer 13

there is no way to make this not scan, you are asking for everything except 1 row, that is a scan. The best you can hope for is to have the application stop sending these queries.