Why query is taking time when I select one column before *?-CodePudding

Why is this query taking time? It took more than 3 minutes, then I had to cancel the query execution.

SELECT TOP 1 EventNAme, * 
FROM DDLAudit WITH (NOLOCK) 
WHERE  EventType = 'ALTER_PROCEDURE'
ORDER BY 1 DESC

But this query is returning results in one second.

SELECT TOP 1 * 
FROM DDLAudit WITH (NOLOCK) 
WHERE  EventType = 'ALTER_PROCEDURE'
ORDER BY 1 DESC

As far as I know, ordering the columns in the select query doesn't affect performance.

CodePudding user response：

As I mention in the comment, the queries are actually quite different, because of your ORDER BY and the use of using ordinal positions (Bad Habits to Kick : ORDER BY ordinal).

As such, your 2 queries should really be written like this:

SELECT TOP (1)
       EventName,
       * --This should also be all your columns in the table, including EventName again(?)
FROM dbo.DDLAudit --WITH (NOLOCK) --Do you have a "good" reason for using NOLOCK?
WHERE  EventType = 'ALTER_PROCEDURE'
ORDER BY EventName DESC;

SELECT TOP (1)
       * --This should also be all your columns in the table
FROM dbo.DDLAudit --WITH (NOLOCK) --Do you have a "good" reason for using NOLOCK?
WHERE  EventType = 'ALTER_PROCEDURE'
ORDER BY {What Ever the first column in the table dbo.DDLAudit is} DESC;

Ordering can be an expensive operation. This is especially so if the data engine has no indexes to help it order the data. You state in the comments that the first column in your table DDLAudit is the PRIMARY KEY. You don't state it this is a CLUSTERED PRIMARY KEY but if it is, then the RDBMS has the ideal index to sort your data by. If isn't your CLUSTERED INDEX then even still, SQL Server has something it can use to sort the data, likely with a key lookup.

If we assume that the PK is CLUSTERED, then the data engine's task is simply backward scan that index until it finds a row where EventType = 'ALTER_PROCEDURE' and then it can "short circuit" the query; stopping there.

For your other column, EventName, I suspect you don't have an index to help the RDBMS. As a result it needs to find every single row that fits the where EventType = 'ALTER_PROCEDURE', then sort all that data on EventName in descending order, and finally return the first row.

If you want the first query to be faster, you'll need to ensure that it is adequately indexed (perhaps on EventName and EventType) and INCLUDE all the other columns from your table in the query. Though I would also suggest rewriting the query to not use * and actually define the columns you want. You don't need EventName returned twice in your SELECT.

CodePudding user response：

Taking a wild guess here: You select the topmost column ordered by a specific field. If that field is indexed, that is easy. If it is not, it is a full table scan. EventName is probably not indexed, while when you select all fields, the first field is most likely your primary key, that obviously is indexed.

So if my guess is right, the difference comes from having to order by EventName instead of your primary key.