SQL Troubleshooting "5" When a large number of data queries to consider the case of empty results

Strange things in recent days surprisingly many, the same work orders, the same product, in a line can be normal production, but in the H-line is unable to produce, the system directly prompts timeout, monitoring found that there is a SQL statement execution slow, copy out carefully review, a simple statement, as follows:

SELECT TOP 1  from WHERE station_id='fb808a1e-5758-43b3-b243-8c728acc5264' and route_step_id = ' 29f813dc-2204-4413-9fb6-0dbf1982f33f ' Order  by desc

Seemingly simple SQL statement, but executed more than 10 s has not yet come out, the table station_id and route_step_id are indexed, theoretically should not ah, so still waiting for SQL execution (at that time, to 1 minutes, dare not execute again), found that the result is empty, as shown:

We look at another SQL statement, the only difference from the above statement is the different station_id, as follows:

SELECT TOP 1  from WHERE station_id='40d83b4a-9bc3-4fe0-be53-65685402ab2e' and route_step_id = ' 29f813dc-2204-4413-9fb6-0dbf1982f33f ' Order  by desc

This statement executes very fast, as follows:

The result is that the actual data is returned, is it the reason why the data is empty? Well, I'll manually add a qualifying H-line data, and after adding the data, execute the first SQL again, as follows:

The results show that 1s of time comes out, which seems to be the problem.

Analyze the results:

There is a more important factor, that is the target table in the amount of data, there are 100000000+ records, although the query with the top 1, but in the absence of eligible data, the entire table will be scanned, that is, the 100000000+ records are scanned, That must be very slow, even if the index is the same, because there is no matching criteria for data, then keep looking until the data are found;

Conversely, as long as there is a qualifying piece of data, the top 1 finds this qualifying record and will no longer scan other data in the table, so it's faster.

SQL Troubleshooting "5" When a large number of data queries to consider the case of empty results