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.
>Solution :
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,
*
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)
*
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 with 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 adequently indexed (perhaps on EventName and EventType) and INCLUDE all the other columns from your table in the query. Though I would also suggest rewritting the query to not use * and actually define the columns you want. You don’t need EventName returned twice in your SELECT.