Skip to main content
added 138 characters in body
Source Link
David Spillett
  • 32.4k
  • 3
  • 49
  • 91

In your first query the index is used to find rows matching [InverterData].[Date] = '05.01.2016' then it needs to lookup the rest of the row data to satisfy being able to return ACPower and DCPower - if you remove these columns from the output you'll see the extra lookup go away.

You could include the extra columns in the index with either:

CREATE NONCLUSTERED INDEX [NonClusteredIndex] 
ON [InverterData] ([Date] DESC, [InverterID] ASC) INCLUDE ([ACPower], [DCPower])

orThis removes the extra lookup via the clustered index at the expense of making the non-clustered index consume more space on disk (and in memory). This query speed and used space trade-off is something you'll have to decide upon by running benchmarks on the bits of the application that use that table.

Note that you could also do:

CREATE NONCLUSTERED INDEX [NonClusteredIndex] 
ON [InverterData] ([Date] DESC, [InverterID] ASC, [ACPower], [DCPower])

(the which uses the extra columns as part of the key rather than just INCLUDING them. The former example is likely to be more efficient as the values are unlikely to be filtered/sorted upon so it saves page splits ascaused by the engine triestrying to keep the (effectively random) values stored in order)

This removes the extra lookup via the clustered index at the expense of making the non-clustered index consume more space on disk (and in memory). This query speed and used space trade-off is something you'll have to decide upon by running benchmarks on the bits of the application that use that table.

In your first query the index is used to find rows matching [InverterData].[Date] = '05.01.2016' then it needs to lookup the rest of the row data to satisfy being able to return ACPower and DCPower - if you remove these columns from the output you'll see the extra lookup go away.

You could include the extra columns in the index with either:

CREATE NONCLUSTERED INDEX [NonClusteredIndex] 
ON [InverterData] ([Date] DESC, [InverterID] ASC) INCLUDE ([ACPower], [DCPower])

or

CREATE NONCLUSTERED INDEX [NonClusteredIndex] 
ON [InverterData] ([Date] DESC, [InverterID] ASC, [ACPower], [DCPower])

(the former is likely to be more efficient as the values are unlikely to be filtered/sorted upon so it saves page splits as the engine tries to keep the values stored in order)

This removes the extra lookup via the clustered index at the expense of making the non-clustered index consume more space on disk (and in memory). This query speed and used space trade-off is something you'll have to decide upon by running benchmarks on the bits of the application that use that table.

In your first query the index is used to find rows matching [InverterData].[Date] = '05.01.2016' then it needs to lookup the rest of the row data to satisfy being able to return ACPower and DCPower - if you remove these columns from the output you'll see the extra lookup go away.

You could include the extra columns in the index with:

CREATE NONCLUSTERED INDEX [NonClusteredIndex] 
ON [InverterData] ([Date] DESC, [InverterID] ASC) INCLUDE ([ACPower], [DCPower])

This removes the extra lookup via the clustered index at the expense of making the non-clustered index consume more space on disk (and in memory). This query speed and used space trade-off is something you'll have to decide upon by running benchmarks on the bits of the application that use that table.

Note that you could also do:

CREATE NONCLUSTERED INDEX [NonClusteredIndex] 
ON [InverterData] ([Date] DESC, [InverterID] ASC, [ACPower], [DCPower])

which uses the extra columns as part of the key rather than just INCLUDING them. The former example is likely to be more efficient as the values are unlikely to be filtered/sorted upon so it saves page splits caused by the engine trying to keep the (effectively random) values stored in order.

Source Link
David Spillett
  • 32.4k
  • 3
  • 49
  • 91

In your first query the index is used to find rows matching [InverterData].[Date] = '05.01.2016' then it needs to lookup the rest of the row data to satisfy being able to return ACPower and DCPower - if you remove these columns from the output you'll see the extra lookup go away.

You could include the extra columns in the index with either:

CREATE NONCLUSTERED INDEX [NonClusteredIndex] 
ON [InverterData] ([Date] DESC, [InverterID] ASC) INCLUDE ([ACPower], [DCPower])

or

CREATE NONCLUSTERED INDEX [NonClusteredIndex] 
ON [InverterData] ([Date] DESC, [InverterID] ASC, [ACPower], [DCPower])

(the former is likely to be more efficient as the values are unlikely to be filtered/sorted upon so it saves page splits as the engine tries to keep the values stored in order)

This removes the extra lookup via the clustered index at the expense of making the non-clustered index consume more space on disk (and in memory). This query speed and used space trade-off is something you'll have to decide upon by running benchmarks on the bits of the application that use that table.