Sql-server – How to improve execution time for queries by multiple fields of different data types on a single table

index-tuningoptimizationperformancequery-performancesql server

Table design

On SQL Server Microsoft SQL Server Enterprise Evaluation (64-bit), version 12.0.5207.0, I have this table, called InputResult:

enter image description here

Table has 11.645.408 rows.

The columns ValueAsString, ValueAsInteger, ValueAsNumber, ValueAsDateTime, ValueAsTime, ValueAsListGuids, ValueAsGuid are mutually exclusive for one record (a record can have only one column filled in).

Indexes

Primary key – clustered index

enter image description here

Fragmentation:

enter image description here

Non-clustered

Key columns:
enter image description here

Included columns:
enter image description here

Fragmentation:
enter image description here

Full-text:
enter image description here

enter image description here

Query

    select 
       [Id]
      ,[Occurrence_id]
      ,[AtsReport_id]
from InputResult
where 
   (InputUniqueId = '5D797CE2-F8C6-4790-B9F4-1D04FD9348D5' and contains(ValueAsString,'"asd*"'))

or (InputUniqueId = '9823FC63-8169-45AD-8AFD-C1E8DBCA6A9B' and contains(ValueAsString,'LRBB'))

or (InputUniqueId = 'B4F16CEA-1B06-4F43-BD64-1016F9A69E1D' and ValueAsNumber=6575)

or (InputUniqueId = '25625057-E245-4EE1-8627-7F26D220301E' and contains(ValueAsString,'asd2223'))

or (InputUniqueId = '7BBECDB1-BE4E-45B3-AC7D-DA6482E512D0' and ValueAsGuid='076D5382-6DA5-4F62-89B3-5B7A2581025B')

or (InputUniqueId = 'E58D56A0-B7BD-4BCC-BF7B-8C381C90C176' and ValueAsGuid='456F53A5-A2BD-4ABD-A340-8C523F632483')

or (InputUniqueId = 'E58D56A0-B7BD-4BCC-BF7B-8C381C90C176' and ValueAsGuid='456F53A5-A2BD-4ABD-A340-8C523F632483')

or (InputUniqueId = '19FD3F45-633F-46FF-A2AC-C6ABE82C74DE' and contains(ValueAsListGuids,'76ffbce1-037d-4ae7-9ddc-ea6923b9823b'))

or (InputUniqueId = 'AC2D106B-B0ED-4766-9923-B5729BDA8A62' and ValueAsDateTime=convert(datetime2,'2016-07-04 00:00:00.0000000'))

or (InputUniqueId = '4E97C4A0-8363-4DB3-916C-D88A231A3743' and ValueAsTime=72000000000)

or (InputUniqueId = 'AF360B91-3B5C-408E-AED5-4450E2B3344C' and ValueAsDateTime>=convert(datetime2,'2017-07-04 00:00:00.0000000'))

or (InputUniqueId = '1A09012C-EC76-41D2-BAD8-9E6FE4040DC6' and ValueAsInteger=12)

or (InputUniqueId = 'A51081F6-B266-4032-8026-6E1D82FA3BC8' and ValueAsInteger<=1234)

or (InputUniqueId = 'D9A548F7-A917-4103-A636-AC033D7175C9' and ValueAsTime<=54000000000)

-------------------------------------------------------------------------------------------
or (InputUniqueId = '9823FC63-8169-45AD-8AFD-C1E8DBCA6A9B' and contains(ValueAsString,'"LRBB*"'))

or (InputUniqueId = 'B4F16CEA-1B06-4F43-BD64-1016F9A69E1D' and ValueAsNumber<655)

or (InputUniqueId = '25625057-E245-4EE1-8627-7F26D220301E' and contains(ValueAsString,'asd2223'))

or (InputUniqueId = '7BBECDB1-BE4E-45B3-AC7D-DA6482E512D0' and ValueAsGuid='076D5382-6DA5-4F62-89B3-5B7A2581025B')

or (InputUniqueId = 'E58D56A0-B7BD-4BCC-BF7B-8C381C90C176' and ValueAsGuid='456F53A5-A2BD-4ABD-A340-8C523F632483')

or (InputUniqueId = 'E58D56A0-B7BD-4BCC-BF7B-8C381C90C176' and ValueAsGuid='456F53A5-A2BD-4ABD-A340-8C523F632483')

or (InputUniqueId = '19FD3F45-633F-46FF-A2AC-C6ABE82C74DE' and contains(ValueAsListGuids,'76ffbce1-037d-4ae7-9ddc-ea6923b9823b'))

or (InputUniqueId = 'AC2D106B-B0ED-4766-9923-B5729BDA8A62' and ValueAsDateTime>=convert(datetime2,'2016-07-04 00:00:00.0000000'))

or (InputUniqueId = '4E97C4A0-8363-4DB3-916C-D88A231A3743' and ValueAsTime<=72000000000)

or (InputUniqueId = 'AF360B91-3B5C-408E-AED5-4450E2B3344C' and ValueAsDateTime<=convert(datetime2,'2017-07-04 00:00:00.0000000'))

or (InputUniqueId = '1A09012C-EC76-41D2-BAD8-9E6FE4040DC6' and ValueAsInteger<=12)

or (InputUniqueId = 'A51081F6-B266-4032-8026-6E1D82FA3BC8' and ValueAsInteger>1234)

or (InputUniqueId = 'D9A548F7-A917-4103-A636-AC033D7175C9' and ValueAsTime<=54000000000)

------------------------------------

or (InputUniqueId = '9823FC63-8169-45AD-8AFD-C1E8DBCA6A9B' and contains(ValueAsString,'asd'))

or (InputUniqueId = 'B4F16CEA-1B06-4F43-BD64-1016F9A69E1D' and ValueAsNumber>6575)

or (InputUniqueId = '25625057-E245-4EE1-8627-7F26D220301E' and contains(ValueAsString,'aaa'))

or (InputUniqueId = '7BBECDB1-BE4E-45B3-AC7D-DA6482E512D0' and ValueAsGuid='076D5382-6DA5-4F62-89B3-5B7A2581025B')

or (InputUniqueId = 'E58D56A0-B7BD-4BCC-BF7B-8C381C90C176' and ValueAsGuid='456F53A5-A2BD-4ABD-A340-8C523F632483')

or (InputUniqueId = 'E58D56A0-B7BD-4BCC-BF7B-8C381C90C176' and ValueAsGuid='456F53A5-A2BD-4ABD-A340-8C523F632483')

or (InputUniqueId = '19FD3F45-633F-46FF-A2AC-C6ABE82C74DE' and contains(ValueAsListGuids,'76ffbce1-037d-4ae7-9ddc-ea6923b9823b'))

or (InputUniqueId = 'AC2D106B-B0ED-4766-9923-B5729BDA8A62' and ValueAsDateTime>=convert(datetime2,'2016-07-04 00:00:00.0000000'))

or (InputUniqueId = '4E97C4A0-8363-4DB3-916C-D88A231A3743' and ValueAsTime>=72000000000)

or (InputUniqueId = 'AF360B91-3B5C-408E-AED5-4450E2B3344C' and ValueAsDateTime=convert(datetime2,'2017-07-04 00:00:00.0000000'))

or (InputUniqueId = '1A09012C-EC76-41D2-BAD8-9E6FE4040DC6' and ValueAsInteger>=12)

or (InputUniqueId = 'A51081F6-B266-4032-8026-6E1D82FA3BC8' and ValueAsInteger<1234)

or (InputUniqueId = 'D9A548F7-A917-4103-A636-AC033D7175C9' and ValueAsTime=54000000000)

Results

First time when I run the query in SSMS it takes 02:00 minutes to complete (782.508 rows returned).

Subsequent execution times: 7 seconds

Query Execution Plan

Query execution plan as XML

I've rebuilt indexes and now the fragmentation looks like this:
enter image description here

enter image description here

The execution of the same query takes 8 / 9 seconds after the indexes were rebuilt.

My question is: how can I optimize this query?

Best Answer

If you are going to do performance testing of queries with not small result sets in SSMS then you should disable rendering of the result set. It looks like your query spent a few seconds doing that. There's nothing wrong with running queries in SSMS, but if you aren't careful you can end up trying to tune something which isn't tunable.

Rebuilding your indexes did reduce the number of pages in them. However, that isn't enough to explain a 17X performance improvement. Rebuilding the index may also help because it updates statistics and puts the data that you need into the buffer pool. With that said, you have a guid for a clustered key so you may have an index fragmentation problem. If you have such a problem then your only options are to live with it, constantly do maintenance, or to change the structure of the table.

That doesn't mean that it isn't possible to make your query run faster. Due to the above (and other reasons), I'm going to focus on CPU for your query. Looking at run time operator statistics gives me the following breakdown of CPU for different parts of the plan:

  • 9300 ms for the scan and filtering on the [index_by_values] index
  • 7500 ms of CPU time for the full text index operations, including the index spools
  • 2000 ms for other operations related to the full text part
  • 2200 ms for other parts of the query

The [index_by_values] index doesn't have the right order for key columns for your query. You want the InputUniqueId column to be first so that the engine can seek using that column. As is, you're scanning the full index and applying the filter as a separate filter operator.

The full text part of the query will be less straightforward to improve. I have almost no experience with full text indexes, but I suspect that you'll get better performance if you rewrite the query so that it only does one or two full text index operations instead of 10. At the very least, with your current query plan all of the full text operations and index spools are on the inner side of a nested loop which means that they cannot be built in parallel. If you look at the actual number of rows for those operators you will see that 7 out of 8 threads don't do any work. I'm assuming MAXDOP 8 is appropriate for your system your query may be faster if you can get proper parallelism for the full text part of the query. One way to hopefully get a parallel, single full text TVF is to use a temp table. Below is a simplified example:

SELECT id,
CAST(CASE WHEN contains(ValueAsString,'asd')) THEN 1 ELSE 0 END AS BIT) BIT_asd
CAST(CASE WHEN contains(ValueAsString,'aaa')) THEN 1 ELSE 0 END AS BIT) BIT_aaa
into #fts_inputResult
FROM InputResult
WHERE contains(ValueAsString,'asd')) OR contains(ValueAsString,'aaa'));

It may be helpful to add a clustered index on the id column depending on how many rows get inserted into the temp table. In any case, you can change your query to simply join to the temp table instead of having the contains logic as part of your select.

It should be possible to improve the performance of your query, but you haven't stated your goal so I don't know if the above will be enough. Good luck.

Related Question