CODEWITHSUNDEEP
sqlsql-serversql-server-2008
O. V.
O. V.

Reputation: 203

SQL Server query speed is difference when filtering by different columns

I have a database with just one table Logs that contains columns:

I have created an index INDEX_SERVICENAME (Non-Unique, Non-Clustered) on the ServiceName column and that includes all columns except Id, ServiceName.

Problem:

I want to select all columns from table filtering by ServiceName and by TaskVariant or by Source with paging. My original query is for selecting last 100 items filtering by Source is:

SELECT TOP (100) 
[Filter1].[Id] AS [Id], 
[Filter1].[Date] AS [Date], 
[Filter1].[Data] AS [Data], 
[Filter1].[ServiceName] AS [ServiceName], 
[Filter1].[LogLevel] AS [LogLevel], 
[Filter1].[StackTrace] AS [StackTrace], 
[Filter1].[TaskVariant] AS [TaskVariant], 
[Filter1].[Source] AS [Source], 
[Filter1].[Message] AS [Message]
FROM ( SELECT [Extent1].[Id] AS [Id], [Extent1].[Date] AS [Date], [Extent1].[Data] AS [Data], [Extent1].[ServiceName] AS [ServiceName], [Extent1].[LogLevel] AS [LogLevel], [Extent1].[StackTrace] AS [StackTrace], [Extent1].[TaskVariant] AS [TaskVariant], [Extent1].[Source] AS [Source], [Extent1].[Message] AS [Message], row_number() OVER (ORDER BY [Extent1].[Id] DESC) AS [row_number]
    FROM [dbo].[Logs] AS [Extent1]
    WHERE (@serviceName = [Extent1].[ServiceName]) AND (@source = [Extent1].[Source])
)  AS [Filter1]
WHERE [Filter1].[row_number] > 0
ORDER BY [Filter1].[Id] DESC

This query works very fast ~ 00:00:00 time.

But when I tried to filter by TaskVariant query takes ~ 00:02:18 minutes (next query).

SELECT TOP (100) 
[Filter1].[Id] AS [Id], 
[Filter1].[Date] AS [Date], 
[Filter1].[Data] AS [Data], 
[Filter1].[ServiceName] AS [ServiceName], 
[Filter1].[LogLevel] AS [LogLevel], 
[Filter1].[StackTrace] AS [StackTrace], 
[Filter1].[TaskVariant] AS [TaskVariant], 
[Filter1].[Source] AS [Source], 
[Filter1].[Message] AS [Message]
FROM ( SELECT [Extent1].[Id] AS [Id], [Extent1].[Date] AS [Date], [Extent1].[Data] AS [Data], [Extent1].[ServiceName] AS [ServiceName], [Extent1].[LogLevel] AS [LogLevel], [Extent1].[StackTrace] AS [StackTrace], [Extent1].[TaskVariant] AS [TaskVariant], [Extent1].[Source] AS [Source], [Extent1].[Message] AS [Message], row_number() OVER (ORDER BY [Extent1].[Id] DESC) AS [row_number]
    FROM [dbo].[Logs] AS [Extent1]
    WHERE (@serviceName = [Extent1].[ServiceName]) AND (@taskVariant = [Extent1].[TaskVariant])
)  AS [Filter1]
WHERE [Filter1].[row_number] > 0
ORDER BY [Filter1].[Id] DESC

Question: why does the second query execute so much slower and how to solve this issue?

Thank you very much for your suggestions.

Execution Plans1

Upvotes: 2

Views: 3059

Answers (2)

Darren Kopp
Darren Kopp

Reputation: 77657

The difference in execution time you are seeing is primarily due to the fact that the first has an index and second doesn't. As for why it's such a large difference, it's likely the fact that since there's an index, it means that the values are sorted.

Since the values are sorted, you can use very efficient string search algorithms that can make the number of operations when filtering orders of magnitude smaller.

In addition, there's a lot of other characteristics that can affect this. It's possible that the entire index is in memory, while the table data is not, so the filtering in the first query could be done all on memory and never touch the disk, where as the other may not.

Upvotes: 0

Mark Sowul
Mark Sowul

Reputation: 10610

The index works like a hierarchy/tree, with levels corresponding to the columns in it.

So if your index is on ServiceName, TaskVariant, you can quickly filter to specific ServiceNames because that is the top level in the tree.

But if you try to filter by TaskVariant, you now have to read through the entire index: you can't just jump to a specific TaskVariant because the same TaskVariant will be under different ServiceNames.

If you want to filter on TaskVariant, you need another index that starts with TaskVariant. NB: don't just create full indices on every column: every index takes up additional space and requires more work on UPDATEs and INSERTs

Upvotes: 2

Related Questions