I'm trying to speed up a query in a 3rd party app. I cannot change the SQL, so Indexes is all I have available.
SELECT min(MyID), max(MyID)
FROM SomeTable
WHERE
(
T_ALIAS = N'MIME'
AND VERSION = 1
AND (
TYPE = N'D'
OR TYPE = N'0'
OR TYPE = N'1'
OR TYPE = N'2'
OR TYPE = N'3'
OR TYPE = N'4'
OR TYPE = N'5'
OR TYPE = N'6'
OR TYPE = N'7'
OR TYPE = N'8'
)
AND
(
ENTRYWHEN >= '20150801 00:00:00'
AND ENTRYWHEN <= '20150831 23:59:59'
)
)
There are basically two flavours of this query, one is for a single day, the other (this one) for a whole month.
The single-day one I have successfully created an index for (based on a Suggestion made by SSMS).
|--Stream Aggregate(
DEFINE:(
[Expr1002]=MIN([MyDB].[MySch].[MyTable].[MyID] as [MyTable].[MyID]),
[Expr1003]=MAX([MyDB].[MySch].[MyTable].[MyID] as [MyTable].[MyID])
))
|--Index Seek(OBJECT:([MyDB].[MySch].[MyTable].[VersionAliasEntrywhen] AS [MyTable]),
SEEK:([MyTable].[VERSION]=(1)
AND [MyTable].[T_ALIAS]=N'MIME'
AND [MyTable].[ENTRYWHEN] >= '2015-08-31 00:00:00.000'
AND [MyTable].[ENTRYWHEN] <= '2015-08-31 23:59:59.000'),
WHERE:(CONVERT_IMPLICIT(nchar(1),[MyDB].[MySch].[MyTable].[TYPE] as [MyTable].[TYPE],0)=N'D'
OR CONVERT_IMPLICIT(nchar(1),[MyDB].[MySch].[MyTable].[TYPE] as [MyTable].[TYPE],0)=N'0'
OR CONVERT_IMPLICIT(nchar(1),[MyDB].[MySch].[MyTable].[TYPE] as [MyTable].[TYPE],0)=N'1'
OR CONVERT_IMPLICIT(nchar(1),[MyDB].[MySch].[MyTable].[TYPE] as [MyTable].[TYPE],0)=N'2'
OR CONVERT_IMPLICIT(nchar(1),[MyDB].[MySch].[MyTable].[TYPE] as [MyTable].[TYPE],0)=N'3'
OR CONVERT_IMPLICIT(nchar(1),[MyDB].[MySch].[MyTable].[TYPE] as [MyTable].[TYPE],0)=N'4'
OR CONVERT_IMPLICIT(nchar(1),[MyDB].[MySch].[MyTable].[TYPE] as [MyTable].[TYPE],0)=N'5'
OR CONVERT_IMPLICIT(nchar(1),[MyDB].[MySch].[MyTable].[TYPE] as [MyTable].[TYPE],0)=N'6'
OR CONVERT_IMPLICIT(nchar(1),[MyDB].[MySch].[MyTable].[TYPE] as [MyTable].[TYPE],0)=N'7'
OR CONVERT_IMPLICIT(nchar(1),[MyDB].[MySch].[MyTable].[TYPE] as [MyTable].[TYPE],0)=N'8'
) ORDERED FORWARD)
The VersionAliasEntrywhen index is:
CREATE NONCLUSTERED INDEX [VersionAliasEntrywhen]
ON [MySch].[MyTable]
(
[VERSION],[T_ALIAS],[ENTRYWHEN]
)
INCLUDE
(
[MyID],[AUTHOR],[OPERATOR],[DEFAULT_SECURITY],[IS_SECURED],[TYPE]
)
For the query spanning a whole month I haven't been able to find an index that it will use, it just does a Clustered Index Scan. The Clustered Index is:
ClustIDX : clustered, unique located on PRIMARY, Columns : MyID, VERSION
|--Nested Loops(Inner Join)
|--Stream Aggregate(DEFINE:([Expr1002]=MIN([MyDB].[MySch].[MyTable].[MyID] as [MyTable].[MyID])))
| |--Top(TOP EXPRESSION:((1)))
| |--Clustered Index Scan(OBJECT:([MyDB].[MySch].[MyTable].[ClustIDX] AS [MyTable]),
WHERE:([MyDB].[MySch].[MyTable].[VERSION] as [MyTable].[VERSION]=(1)
AND [MyDB].[MySch].[MyTable].[ENTRYWHEN] as [MyTable].[ENTRYWHEN]>='2015-08-01 00:00:00.000'
AND [MyDB].[MySch].[MyTable].[ENTRYWHEN] as [MyTable].[ENTRYWHEN]<='2015-08-31 23:59:59.000'
AND [MyDB].[MySch].[MyTable].[T_ALIAS] as [MyTable].[T_ALIAS]=N'MIME'
AND (
CONVERT_IMPLICIT(nchar(1),[MyDB].[MySch].[MyTable].[TYPE] as [MyTable].[TYPE],0)=N'D'
OR CONVERT_IMPLICIT(nchar(1),[MyDB].[MySch].[MyTable].[TYPE] as [MyTable].[TYPE],0)=N'0'
OR CONVERT_IMPLICIT(nchar(1),[MyDB].[MySch].[MyTable].[TYPE] as [MyTable].[TYPE],0)=N'1'
OR CONVERT_IMPLICIT(nchar(1),[MyDB].[MySch].[MyTable].[TYPE] as [MyTable].[TYPE],0)=N'2'
OR CONVERT_IMPLICIT(nchar(1),[MyDB].[MySch].[MyTable].[TYPE] as [MyTable].[TYPE],0)=N'3'
OR CONVERT_IMPLICIT(nchar(1),[MyDB].[MySch].[MyTable].[TYPE] as [MyTable].[TYPE],0)=N'4'
OR CONVERT_IMPLICIT(nchar(1),[MyDB].[MySch].[MyTable].[TYPE] as [MyTable].[TYPE],0)=N'5'
OR CONVERT_IMPLICIT(nchar(1),[MyDB].[MySch].[MyTable].[TYPE] as [MyTable].[TYPE],0)=N'6'
OR CONVERT_IMPLICIT(nchar(1),[MyDB].[MySch].[MyTable].[TYPE] as [MyTable].[TYPE],0)=N'7'
OR CONVERT_IMPLICIT(nchar(1),[MyDB].[MySch].[MyTable].[TYPE] as [MyTable].[TYPE],0)=N'8'
)) ORDERED FORWARD)
|--Stream Aggregate(DEFINE:([Expr1003]=MAX([MyDB].[MySch].[MyTable].[MyID] as [MyTable].[MyID])))
|--Top(TOP EXPRESSION:((1)))
|--Clustered Index Scan(OBJECT:([MyDB].[MySch].[MyTable].[ClustIDX] AS [MyTable]),
WHERE:([MyDB].[MySch].[MyTable].[VERSION] as [MyTable].[VERSION]=(1)
AND [MyDB].[MySch].[MyTable].[ENTRYWHEN] as [MyTable].[ENTRYWHEN]>='2015-08-01 00:00:00.000'
AND [MyDB].[MySch].[MyTable].[ENTRYWHEN] as [MyTable].[ENTRYWHEN]<='2015-08-31 23:59:59.000'
AND [MyDB].[MySch].[MyTable].[T_ALIAS] as [MyTable].[T_ALIAS]=N'MIME'
AND (
CONVERT_IMPLICIT(nchar(1),[MyDB].[MySch].[MyTable].[TYPE] as [MyTable].[TYPE],0)=N'D'
OR CONVERT_IMPLICIT(nchar(1),[MyDB].[MySch].[MyTable].[TYPE] as [MyTable].[TYPE],0)=N'0'
OR CONVERT_IMPLICIT(nchar(1),[MyDB].[MySch].[MyTable].[TYPE] as [MyTable].[TYPE],0)=N'1'
OR CONVERT_IMPLICIT(nchar(1),[MyDB].[MySch].[MyTable].[TYPE] as [MyTable].[TYPE],0)=N'2'
OR CONVERT_IMPLICIT(nchar(1),[MyDB].[MySch].[MyTable].[TYPE] as [MyTable].[TYPE],0)=N'3'
OR CONVERT_IMPLICIT(nchar(1),[MyDB].[MySch].[MyTable].[TYPE] as [MyTable].[TYPE],0)=N'4'
OR CONVERT_IMPLICIT(nchar(1),[MyDB].[MySch].[MyTable].[TYPE] as [MyTable].[TYPE],0)=N'5'
OR CONVERT_IMPLICIT(nchar(1),[MyDB].[MySch].[MyTable].[TYPE] as [MyTable].[TYPE],0)=N'6'
OR CONVERT_IMPLICIT(nchar(1),[MyDB].[MySch].[MyTable].[TYPE] as [MyTable].[TYPE],0)=N'7'
OR CONVERT_IMPLICIT(nchar(1),[MyDB].[MySch].[MyTable].[TYPE] as [MyTable].[TYPE],0)=N'8'
)) ORDERED BACKWARD)
Here are some Stats to indicate how selective, or not!, the various columns are:
669,025 COUNT(*)
37 DISTINCT T_ALIAS
428,093 T_ALIAS = 'MIME'
71 DISTINCT VERSION
668,282 VERSION = 1
3 DISTINCT TYPE
646,632 TYPE = 'D' or '0' - '8'
546,108 DISTINCT ENTRYWHEN
8,030 (1.2%) ENTRYWHEN within range
Column datatypes:
MyID float
ENTRYWHEN datetime
VERSION int
ALIAS nvarchar(64)
T_ALIAS nvarchar(64)
TYPE char(1)
SSMS has no suggestions for indexes. I've tried an index with ENTRYWHEN column first, but that was not used.