patternsqlMinor
SELECT query slow even with nonclustered Index and a full-text index
Viewed 0 times
indexfullwithqueryselecttextslownonclusteredandeven
Problem
I am attempting to query between 0 and 65,000 rows from a table.
The server is using Microsoft SQL Server 2014, and I have no way to change the hardware on the server.
Schema
Query
The following query is part of a stored procedure (the rest of which is irrelevant since it has trivial impact on the stored procedure's performance). The column names have been replaced with the column type and a number:
Execution Plan
Execution Plan XML: https://gist.github.com/BlackyWolf/046856518065bfe5293cad78f73340e9
But the information it gave so far is:
I am not using a PK in this search. This query has the following durations depending on the columns removed:
All columns in select statement = 18s-27s
Without [xml1] = 8s-11s
Without [xml1] and [varchar4] = 4s-6s
The total ex
The server is using Microsoft SQL Server 2014, and I have no way to change the hardware on the server.
Schema
[Id] (PK) INT
[varchar1] VARCHAR(4) Normal Cardinality
[varchar2] VARCHAR(250) Normal Cardinality
[varchar3] VARCHAR(250) Normal Cardinality
[varchar4] VARCHAR(100) Normal Cardinality
[date1] DATETIME High Cardinality
[varchar5] VARCHAR(100) Low Cardinality
[varchar6] VARCHAR(1000) Normal Cardinality
[varchar7] VARCHAR(100) Normal Cardinality
[varchar8] VARCHAR(20) Normal Cardinality
[varchar9] VARCHAR(100) High Cardinality
[xml1] XML Low CardinalityQuery
The following query is part of a stored procedure (the rest of which is irrelevant since it has trivial impact on the stored procedure's performance). The column names have been replaced with the column type and a number:
SELECT [varchar1]
, [varchar4]
, [date1]
, [varchar5]
, [varchar6]
, [varchar7]
, [varchar8]
, [varchar9]
, [xml1]
FROM [database].[dbo].[table] WITH (NOLOCK)
WHERE [varchar1] = '0'
AND ([date1] >='2014-1-1' AND [date1] <= '2017-1-1')
AND [varchar8] = 'someText'
AND [varchar9] LIKE '%a%'
ORDER BY [varchar1] ASC, [date1] DESC
OFFSET 0 ROWS
FETCH NEXT 65000 ROWS ONLYExecution Plan
Execution Plan XML: https://gist.github.com/BlackyWolf/046856518065bfe5293cad78f73340e9
But the information it gave so far is:
Query1: Query cost (relative to the batch): 100%
Index Seek [NonClustered]
SELECT Top [Table].[i_table_index]
Cost: 0% Cost: 4% Cost: 96%I am not using a PK in this search. This query has the following durations depending on the columns removed:
All columns in select statement = 18s-27s
Without [xml1] = 8s-11s
Without [xml1] and [varchar4] = 4s-6s
The total ex
Solution
The double-wild card action will not do you any favors ([varchar9] LIKE '%a%'). Because you're searching in the middle of the string, SQL Server is going to scan every row, and no index will help matters. It's expensive, see Brent Ozar's post (https://www.brentozar.com/archive/2016/10/searching-strings-sql-server-expensive).
FETCH NEXT logic is necessary? It's an excellent way to bog down your resources, particularly with an xml column in tow. To read about the overhead see Figure 7.2 here (https://use-the-index-luke.com/sql/partial-results/fetch-next-page)
Aaron Bertrand writes about a great technique that minimizes the extra overhead, if you really need to do paging in sql server, I have used this technique to much applause and success. Basically, collect the PRIMARY KEYS and use that to collect the xml and all the other columns you'll need. you will be slinging around a lot less baggage.
https://sqlperformance.com/2015/01/t-sql-queries/pagination-with-offset-fetch
FETCH NEXT logic is necessary? It's an excellent way to bog down your resources, particularly with an xml column in tow. To read about the overhead see Figure 7.2 here (https://use-the-index-luke.com/sql/partial-results/fetch-next-page)
Aaron Bertrand writes about a great technique that minimizes the extra overhead, if you really need to do paging in sql server, I have used this technique to much applause and success. Basically, collect the PRIMARY KEYS and use that to collect the xml and all the other columns you'll need. you will be slinging around a lot less baggage.
https://sqlperformance.com/2015/01/t-sql-queries/pagination-with-offset-fetch
Context
StackExchange Database Administrators Q#162804, answer score: 2
Revisions (0)
No revisions yet.