Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

for the record, this is a rewrite of the following question (you do not have to read that because I include all important data here) Howto improve performance for queries against table without primary key without changing the query or the table directly? .

When I originally posted it there was a huge number of constraints to the attempts that could possibly solve it. Since I was able to loosen up on those constraints a bit I decided to do this rewrite, because with those new constraints it is indeed a whole new question.

Table Layout

Consider the following table setup (done by standard installation of Windows Server AppFabric):

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[ASWcfEventsTable](
    [Id] [bigint] NOT NULL,
    [EventTypeId] [int] NOT NULL,
    [EventSourceId] [int] NOT NULL,
    [ProcessId] [int] NOT NULL,
    [TraceLevelId] [tinyint] NOT NULL,
    [E2EActivityId] [nvarchar](36) NULL,
    [TimeCreated] [datetime2](7) NOT NULL,
    [Data1UniqueIdentifier] [nvarchar](36) NULL,
    [Data1Str] [nvarchar](450) NULL,
    [Data2Str] [nvarchar](450) NULL,
    [Data3Str] [nvarchar](450) NULL,
    [Data4Str] [nvarchar](450) NULL,
    [Data1MaxStr] [nvarchar](max) NULL,
    [Data1Int] [int] NULL,
    [Data2Int] [int] NULL,
    [Data3Int] [int] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

Having these indexes:

CREATE CLUSTERED INDEX [CI_ASWcfEventsTable_TimeCreated] ON [dbo].[ASWcfEventsTable]
(
    [TimeCreated] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

CREATE NONCLUSTERED INDEX [NCI_ASWcfEventsTable_E2EActivityId] ON [dbo].[ASWcfEventsTable]
(
    [E2EActivityId] ASC
)
INCLUDE (   [Id]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

GO

CREATE NONCLUSTERED INDEX [NCI_ASWcfEventsTable_EventSourceId] ON [dbo].[ASWcfEventsTable]
(
    [EventSourceId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

GO

CREATE NONCLUSTERED INDEX [NCI_ASWcfEventsTable_EventTypeId_TimeCreated] ON [dbo].[ASWcfEventsTable]
(
    [EventTypeId] ASC,
    [TimeCreated] ASC
)
INCLUDE (   [EventSourceId],
    [Data1Int]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

GO

CREATE NONCLUSTERED INDEX [NCI_ASWcfEventsTable_Id] ON [dbo].[ASWcfEventsTable]
(
    [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

GO

I have an application that reads logs from this table and presents a web-user-interface to the user, this enables the user to filter entries by:

  • TraceLevelId: 6 distinct values (numbers 0-5)
  • EventSourceId: here we have dozens of distinct values (over 70 and this will increase pretty sure as more systems will target this database for logging over time)
  • TimeCreated: as name indicates this is the column that has the most distinct values ;)
  • Data1MaxStr: this column contains the logging message itself and has the second most distinct values (two important note on this: 28% of this column’s content IS NULL and 60% of this column’s content share a special string that is filtered out if the user did not specify a message to search for.

In short, I want to optimize the speed for some queries that are executed against this table.

I shall not change the table itself (manipulate columns/indexes, creating new indexes on the table might be acceptable though, but I would rather solve this with indexed views and/or query adaptation)

One indexed view try

Before I show you the queries I want to introduce you to one try that I have done here, I set up these views with indexes (notice that I needed a second “helper” view to force NOEXPAND to happen). But these views/indexes actually made performance worse for most of the cases.

CREATE VIEW [dbo].LoggingHelpView
WITH SCHEMABINDING
AS
  SELECT
      [ASWcfEventsTable].[Id],
      [ASWcfEventsTable].[EventTypeId], 
      [ASWcfEventsTable].[EventSourceId], 
      [ASWcfEventsTable].[TraceLevelId], 
      [ASWcfEventsTable].[TimeCreated], 
      [ASWcfEventsTable].[Data1MaxStr]
  FROM [dbo].[ASWcfEventsTable]
  WHERE [dbo].[ASWcfEventsTable].[Data1MaxStr] != N'special string'
GO

CREATE UNIQUE CLUSTERED INDEX LoggingHelpView_TimeCreated_ID
    ON [dbo].LoggingHelpView (TimeCreated DESC, ID, TraceLevelId, EventSourceId)
GO

CREATE NONCLUSTERED INDEX LoggingHelpView_TraceLevelId
    ON [dbo].LoggingHelpView (TraceLevelId) INCLUDE (TimeCreated, ID, Data1MaxStr)
GO
CREATE NONCLUSTERED INDEX LoggingHelpView_EventSourceId
    ON [dbo].LoggingHelpView (EventSourceId) INCLUDE (TimeCreated, ID, Data1MaxStr)
GO

CREATE VIEW [dbo].LoggingView
WITH SCHEMABINDING
AS
  SELECT
      LoggingHelpView.[Id],
      LoggingHelpView.[EventTypeId], 
      LoggingHelpView.[EventSourceId], 
      LoggingHelpView.[TraceLevelId], 
      LoggingHelpView.[TimeCreated], 
      LoggingHelpView.[Data1MaxStr]
  FROM [dbo].LoggingHelpView (NOEXPAND)
  WHERE [dbo].LoggingHelpView.[Data1MaxStr] != N'special string'
GO

Please note that this view filters out all rows that share this specific string, I will probably introduce another view of that kind with the inverse WHERE-condition to only include those rows if we want to search for them.

For the queries I show, the order is the following:

First I show you the original query that I intercepted with my Profiler (That is generated via LinQ at this time)

  • Below that you can see the execution plan that was taken
  • And below that you can see an average of CPU used/Reads/Writes/Duration I also took from the Profiler (average over 5-10 calls)

Second I show you my rewritten try of that query using the views I described above

  • Similarly including an execution plan for that
  • And of course also some CPU/Reads/Writes/Duration averages

I am aware that these are not all possible combinations of filters (for example one could also search for message alone), but it’s a place to start.

Enough of the forewords, let’s get into some queries.

Query 1: This is the default query that runs if you call the application without using any filters:

Original:

SELECT TOP (100) 
[Project1].[EventTypeId] AS [EventTypeId], 
[Project1].[EventSourceId] AS [EventSourceId], 
[Project1].[TimeCreated] AS [TimeCreated], 
[Project1].[C1] AS [C1], 
[Project1].[C2] AS [C2], 
[Project1].[Data1MaxStr] AS [Data1MaxStr], 
[Project1].[C3] AS [C3]
FROM ( SELECT [Project1].[EventTypeId] AS [EventTypeId], [Project1].[EventSourceId] AS [EventSourceId], [Project1].[TimeCreated] AS [TimeCreated], [Project1].[Data1MaxStr] AS [Data1MaxStr], [Project1].[C1] AS [C1], [Project1].[C2] AS [C2], [Project1].[C3] AS [C3], row_number() OVER (ORDER BY [Project1].[TimeCreated] DESC) AS [row_number]
    FROM ( SELECT TOP (100) 
        [Extent1].[EventTypeId] AS [EventTypeId], 
        [Extent1].[EventSourceId] AS [EventSourceId], 
        [Extent1].[TimeCreated] AS [TimeCreated], 
        [Extent1].[Data1MaxStr] AS [Data1MaxStr], 
        N'' AS [C1], 
        N'' AS [C2], 
         CAST( [Extent1].[TraceLevelId] AS int) AS [C3]
        FROM (SELECT 
      [ASWcfEventsTable].[Id] AS [Id], 
      [ASWcfEventsTable].[EventTypeId] AS [EventTypeId], 
      [ASWcfEventsTable].[EventSourceId] AS [EventSourceId], 
      [ASWcfEventsTable].[ProcessId] AS [ProcessId], 
      [ASWcfEventsTable].[TraceLevelId] AS [TraceLevelId], 
      [ASWcfEventsTable].[E2EActivityId] AS [E2EActivityId], 
      [ASWcfEventsTable].[TimeCreated] AS [TimeCreated], 
      [ASWcfEventsTable].[Data1UniqueIdentifier] AS [Data1UniqueIdentifier], 
      [ASWcfEventsTable].[Data1Str] AS [Data1Str], 
      [ASWcfEventsTable].[Data2Str] AS [Data2Str], 
      [ASWcfEventsTable].[Data3Str] AS [Data3Str], 
      [ASWcfEventsTable].[Data4Str] AS [Data4Str], 
      [ASWcfEventsTable].[Data1MaxStr] AS [Data1MaxStr], 
      [ASWcfEventsTable].[Data1Int] AS [Data1Int], 
      [ASWcfEventsTable].[Data2Int] AS [Data2Int], 
      [ASWcfEventsTable].[Data3Int] AS [Data3Int]
      FROM [dbo].[ASWcfEventsTable] AS [ASWcfEventsTable] ) AS [Extent1]
        WHERE N'special string' <> [Extent1].[Data1MaxStr]
        ORDER BY [Extent1].[TimeCreated] DESC
    )  AS [Project1]
)  AS [Project1]
WHERE [Project1].[row_number] > 0
ORDER BY [Project1].[TimeCreated] DESC

Original execution plan: execution plan original 1 Original execution averages: CPU: 3 Reads: 26 Writes: 0 Duration: 35

Rewritten query:

SELECT TOP 100
        EventTypeId,
        EventSourceId,
        TimeCreated,
        Data1MaxStr
    FROM LoggingView
    WHERE
        Data1MaxStr != N'special string'
    ORDER BY TimeCreated DESC

Rewritten execution plan:
execution plan rewritten 1 Rewritten execution averages: CPU: 0 Reads: 11 Writes: 0 Duration: 80

Query 2: This query includes a filter by TraceLevelId:

Original:

SELECT TOP (100) 
[Project1].[EventTypeId] AS [EventTypeId], 
[Project1].[EventSourceId] AS [EventSourceId], 
[Project1].[TimeCreated] AS [TimeCreated], 
[Project1].[C1] AS [C1], 
[Project1].[C2] AS [C2], 
[Project1].[Data1MaxStr] AS [Data1MaxStr], 
[Project1].[C3] AS [C3]
FROM ( SELECT [Project1].[EventTypeId] AS [EventTypeId], [Project1].[EventSourceId] AS [EventSourceId], [Project1].[TimeCreated] AS [TimeCreated], [Project1].[Data1MaxStr] AS [Data1MaxStr], [Project1].[C1] AS [C1], [Project1].[C2] AS [C2], [Project1].[C3] AS [C3], row_number() OVER (ORDER BY [Project1].[TimeCreated] DESC) AS [row_number]
    FROM ( SELECT TOP (100) 
        [Extent1].[EventTypeId] AS [EventTypeId], 
        [Extent1].[EventSourceId] AS [EventSourceId], 
        [Extent1].[TimeCreated] AS [TimeCreated], 
        [Extent1].[Data1MaxStr] AS [Data1MaxStr], 
        N'' AS [C1], 
        N'' AS [C2], 
         CAST( [Extent1].[TraceLevelId] AS int) AS [C3]
        FROM (SELECT 
      [ASWcfEventsTable].[Id] AS [Id], 
      [ASWcfEventsTable].[EventTypeId] AS [EventTypeId], 
      [ASWcfEventsTable].[EventSourceId] AS [EventSourceId], 
      [ASWcfEventsTable].[ProcessId] AS [ProcessId], 
      [ASWcfEventsTable].[TraceLevelId] AS [TraceLevelId], 
      [ASWcfEventsTable].[E2EActivityId] AS [E2EActivityId], 
      [ASWcfEventsTable].[TimeCreated] AS [TimeCreated], 
      [ASWcfEventsTable].[Data1UniqueIdentifier] AS [Data1UniqueIdentifier], 
      [ASWcfEventsTable].[Data1Str] AS [Data1Str], 
      [ASWcfEventsTable].[Data2Str] AS [Data2Str], 
      [ASWcfEventsTable].[Data3Str] AS [Data3Str], 
      [ASWcfEventsTable].[Data4Str] AS [Data4Str], 
      [ASWcfEventsTable].[Data1MaxStr] AS [Data1MaxStr], 
      [ASWcfEventsTable].[Data1Int] AS [Data1Int], 
      [ASWcfEventsTable].[Data2Int] AS [Data2Int], 
      [ASWcfEventsTable].[Data3Int] AS [Data3Int]
      FROM [dbo].[ASWcfEventsTable] AS [ASWcfEventsTable]) AS [Extent1]
        WHERE (N'special string' <> [Extent1].[Data1MaxStr]) AND (4 =  [Extent1].[TraceLevelId])
        ORDER BY [Extent1].[TimeCreated] DESC
    )  AS [Project1] 
)  AS [Project1]
WHERE [Project1].[row_number] > 0
ORDER BY [Project1].[TimeCreated] DESC

Original execution plan:
execution plan original 2 Original execution averages: CPU: 5 Reads: 30 Writes: 0 Duration: 55

Rewritten query:

SELECT TOP 100
        EventTypeId,
        EventSourceId,
        TimeCreated,
        Data1MaxStr
    FROM LoggingView
    WHERE
        TraceLevelId = 4
        AND Data1MaxStr != N'special string'
    ORDER BY TimeCreated DESC

Rewritten execution plan:
execution plan rewritten 2 Rewritten execution averages: CPU: 2 Reads: 30 Writes: 0 Duration: 80

Query 3: This query includes a filter by TraceLevelId and by EventSourceId

Original:

SELECT TOP (100) 
[Project1].[EventTypeId] AS [EventTypeId], 
[Project1].[EventSourceId] AS [EventSourceId], 
[Project1].[TimeCreated] AS [TimeCreated], 
[Project1].[C1] AS [C1], 
[Project1].[C2] AS [C2], 
[Project1].[Data1MaxStr] AS [Data1MaxStr], 
[Project1].[C3] AS [C3]
FROM ( SELECT [Project1].[EventTypeId] AS [EventTypeId], [Project1].[EventSourceId] AS [EventSourceId], [Project1].[TimeCreated] AS [TimeCreated], [Project1].[Data1MaxStr] AS [Data1MaxStr], [Project1].[C1] AS [C1], [Project1].[C2] AS [C2], [Project1].[C3] AS [C3], row_number() OVER (ORDER BY [Project1].[TimeCreated] DESC) AS [row_number]
    FROM ( SELECT TOP (100) 
        [Extent1].[EventTypeId] AS [EventTypeId], 
        [Extent1].[EventSourceId] AS [EventSourceId], 
        [Extent1].[TimeCreated] AS [TimeCreated], 
        [Extent1].[Data1MaxStr] AS [Data1MaxStr], 
        N'' AS [C1], 
        N'' AS [C2], 
         CAST( [Extent1].[TraceLevelId] AS int) AS [C3]
        FROM (SELECT 
      [ASWcfEventsTable].[Id] AS [Id], 
      [ASWcfEventsTable].[EventTypeId] AS [EventTypeId], 
      [ASWcfEventsTable].[EventSourceId] AS [EventSourceId], 
      [ASWcfEventsTable].[ProcessId] AS [ProcessId], 
      [ASWcfEventsTable].[TraceLevelId] AS [TraceLevelId], 
      [ASWcfEventsTable].[E2EActivityId] AS [E2EActivityId], 
      [ASWcfEventsTable].[TimeCreated] AS [TimeCreated], 
      [ASWcfEventsTable].[Data1UniqueIdentifier] AS [Data1UniqueIdentifier], 
      [ASWcfEventsTable].[Data1Str] AS [Data1Str], 
      [ASWcfEventsTable].[Data2Str] AS [Data2Str], 
      [ASWcfEventsTable].[Data3Str] AS [Data3Str], 
      [ASWcfEventsTable].[Data4Str] AS [Data4Str], 
      [ASWcfEventsTable].[Data1MaxStr] AS [Data1MaxStr], 
      [ASWcfEventsTable].[Data1Int] AS [Data1Int], 
      [ASWcfEventsTable].[Data2Int] AS [Data2Int], 
      [ASWcfEventsTable].[Data3Int] AS [Data3Int]
      FROM [dbo].[ASWcfEventsTable] AS [ASWcfEventsTable]) AS [Extent1]
        WHERE (N'special string' <> [Extent1].[Data1MaxStr]) AND (4 =  [Extent1].[TraceLevelId] ) AND (68 = [Extent1].[EventSourceId])
        ORDER BY [Extent1].[TimeCreated] DESC
    )  AS [Project1]
)  AS [Project1]
WHERE [Project1].[row_number] > 0
ORDER BY [Project1].[TimeCreated] DESC

Original execution plan:
execution plan original 3 Original execution averages: CPU: 10 Reads: 80 Writes: 0 Duration: 10

Rewritten query:

SELECT TOP 100
        EventTypeId,
        EventSourceId,
        TimeCreated,
        Data1MaxStr
    FROM LoggingView
    WHERE
        TraceLevelId = 4
        AND EventSourceId = 68      
        AND Data1MaxStr != N'special string'
    ORDER BY TimeCreated DESC

Rewritten execution plan:
execution plan rewritten 3 Rewritten execution averages: CPU: 1 Reads: 308 Writes: 0 Duration: 70

Query 4: This query includes a filter by TraceLevelId and by EventSourceId and also filteres by an interval of dates:

Original:

exec sp_executesql N'SELECT TOP (100) 
[Project2].[EventTypeId] AS [EventTypeId], 
[Project2].[EventSourceId] AS [EventSourceId], 
[Project2].[TimeCreated] AS [TimeCreated], 
[Project2].[C1] AS [C1], 
[Project2].[C2] AS [C2], 
[Project2].[Data1MaxStr] AS [Data1MaxStr], 
[Project2].[C3] AS [C3]
FROM ( SELECT [Project2].[EventTypeId] AS [EventTypeId], [Project2].[EventSourceId] AS [EventSourceId], [Project2].[TimeCreated] AS [TimeCreated], [Project2].[Data1MaxStr] AS [Data1MaxStr], [Project2].[C1] AS [C1], [Project2].[C2] AS [C2], [Project2].[C3] AS [C3], row_number() OVER (ORDER BY [Project2].[TimeCreated] DESC) AS [row_number]
    FROM ( SELECT TOP (100) 
        [Project1].[EventTypeId] AS [EventTypeId], 
        [Project1].[EventSourceId] AS [EventSourceId], 
        [Project1].[TimeCreated] AS [TimeCreated], 
        [Project1].[Data1MaxStr] AS [Data1MaxStr], 
        N'''' AS [C1], 
        N'''' AS [C2], 
         CAST( [Project1].[TraceLevelId] AS int) AS [C3]
        FROM ( SELECT 
            [Extent1].[EventTypeId] AS [EventTypeId], 
            [Extent1].[EventSourceId] AS [EventSourceId], 
            [Extent1].[TraceLevelId] AS [TraceLevelId], 
            [Extent1].[TimeCreated] AS [TimeCreated], 
            [Extent1].[Data1MaxStr] AS [Data1MaxStr]
            FROM (SELECT 
      [ASWcfEventsTable].[Id] AS [Id], 
      [ASWcfEventsTable].[EventTypeId] AS [EventTypeId], 
      [ASWcfEventsTable].[EventSourceId] AS [EventSourceId], 
      [ASWcfEventsTable].[ProcessId] AS [ProcessId], 
      [ASWcfEventsTable].[TraceLevelId] AS [TraceLevelId], 
      [ASWcfEventsTable].[E2EActivityId] AS [E2EActivityId], 
      [ASWcfEventsTable].[TimeCreated] AS [TimeCreated], 
      [ASWcfEventsTable].[Data1UniqueIdentifier] AS [Data1UniqueIdentifier], 
      [ASWcfEventsTable].[Data1Str] AS [Data1Str], 
      [ASWcfEventsTable].[Data2Str] AS [Data2Str], 
      [ASWcfEventsTable].[Data3Str] AS [Data3Str], 
      [ASWcfEventsTable].[Data4Str] AS [Data4Str], 
      [ASWcfEventsTable].[Data1MaxStr] AS [Data1MaxStr], 
      [ASWcfEventsTable].[Data1Int] AS [Data1Int], 
      [ASWcfEventsTable].[Data2Int] AS [Data2Int], 
      [ASWcfEventsTable].[Data3Int] AS [Data3Int]
      FROM [dbo].[ASWcfEventsTable] AS [ASWcfEventsTable]) AS [Extent1]
            WHERE (N''special string'' <> [Extent1].[Data1MaxStr]) AND ([Extent1].[TimeCreated] >= @p__linq__0) AND ([Extent1].[TimeCreated] <= @p__linq__1) AND (4 =  [Extent1].[TraceLevelId]) AND (68 = [Extent1].[EventSourceId])
        )  AS [Project1]
        ORDER BY [Project1].[TimeCreated] DESC
    )  AS [Project2]
)  AS [Project2]
WHERE [Project2].[row_number] > 0
ORDER BY [Project2].[TimeCreated] DESC',N'@p__linq__0 datetime2(7),@p__linq__1 datetime2(7)',@p__linq__0='2013-07-10 22:00:00',@p__linq__1='2013-07-12 22:00:00'

Original execution plan:
execution plan original 4 Original execution averages: CPU: 20 Reads: 80 Writes: 0 Duration: 20

Rewritten query:

SELECT TOP 100
        EventTypeId,
        EventSourceId,
        TimeCreated,
        Data1MaxStr
    FROM LoggingView
    WHERE
        TimeCreated BETWEEN '2013-07-10 22:00:00' AND '2013-07-12 22:00:00'
        AND TraceLevelId = 4
        AND EventSourceId = 68      
        AND Data1MaxStr != N'special string'
    ORDER BY TimeCreated DESC

Rewritten execution plan (german word “Schlüsselsuche” means “key lookup”:
execution plan rewritten 4 Rewritten execution averages: CPU: 1 Reads: 308 Writes: 0 Duration: 80

Query 5: This query includes a filter by TraceLevelId, by EventSourceId, by an interval of dates and also looks for a containing message:

Original:

exec sp_executesql N'SELECT TOP (100) 
[Project2].[EventTypeId] AS [EventTypeId], 
[Project2].[EventSourceId] AS [EventSourceId], 
[Project2].[TimeCreated] AS [TimeCreated], 
[Project2].[C1] AS [C1], 
[Project2].[C2] AS [C2], 
[Project2].[Data1MaxStr] AS [Data1MaxStr], 
[Project2].[C3] AS [C3]
FROM ( SELECT [Project2].[EventTypeId] AS [EventTypeId], [Project2].[EventSourceId] AS [EventSourceId], [Project2].[TimeCreated] AS [TimeCreated], [Project2].[Data1MaxStr] AS [Data1MaxStr], [Project2].[C1] AS [C1], [Project2].[C2] AS [C2], [Project2].[C3] AS [C3], row_number() OVER (ORDER BY [Project2].[TimeCreated] DESC) AS [row_number]
    FROM ( SELECT TOP (100) 
        [Project1].[EventTypeId] AS [EventTypeId], 
        [Project1].[EventSourceId] AS [EventSourceId], 
        [Project1].[TimeCreated] AS [TimeCreated], 
        [Project1].[Data1MaxStr] AS [Data1MaxStr], 
        N'''' AS [C1], 
        N'''' AS [C2], 
          [Project1].[TraceLevelId] AS [C3]
        FROM ( SELECT 
            [Extent1].[EventTypeId] AS [EventTypeId], 
            [Extent1].[EventSourceId] AS [EventSourceId], 
            [Extent1].[TraceLevelId] AS [TraceLevelId], 
            [Extent1].[TimeCreated] AS [TimeCreated], 
            [Extent1].[Data1MaxStr] AS [Data1MaxStr]
            FROM (SELECT 
      [ASWcfEventsTable].[Id] AS [Id], 
      [ASWcfEventsTable].[EventTypeId] AS [EventTypeId], 
      [ASWcfEventsTable].[EventSourceId] AS [EventSourceId], 
      [ASWcfEventsTable].[ProcessId] AS [ProcessId], 
      [ASWcfEventsTable].[TraceLevelId] AS [TraceLevelId], 
      [ASWcfEventsTable].[E2EActivityId] AS [E2EActivityId], 
      [ASWcfEventsTable].[TimeCreated] AS [TimeCreated], 
      [ASWcfEventsTable].[Data1UniqueIdentifier] AS [Data1UniqueIdentifier], 
      [ASWcfEventsTable].[Data1Str] AS [Data1Str], 
      [ASWcfEventsTable].[Data2Str] AS [Data2Str], 
      [ASWcfEventsTable].[Data3Str] AS [Data3Str], 
      [ASWcfEventsTable].[Data4Str] AS [Data4Str], 
      [ASWcfEventsTable].[Data1MaxStr] AS [Data1MaxStr], 
      [ASWcfEventsTable].[Data1Int] AS [Data1Int], 
      [ASWcfEventsTable].[Data2Int] AS [Data2Int], 
      [ASWcfEventsTable].[Data3Int] AS [Data3Int]
      FROM [dbo].[ASWcfEventsTable] AS [ASWcfEventsTable]) AS [Extent1]
            WHERE ([Extent1].[Data1MaxStr] LIKE @p__linq__0 ESCAPE N''~'') AND ([Extent1].[TimeCreated] >= @p__linq__1) AND ([Extent1].[TimeCreated] <= @p__linq__2) AND (4 =  [Extent1].[TraceLevelId]) AND (68 = [Extent1].[EventSourceId])
        )  AS [Project1]
        ORDER BY [Project1].[TimeCreated] DESC
    )  AS [Project2]
)  AS [Project2]
WHERE [Project2].[row_number] > 0
ORDER BY [Project2].[TimeCreated] DESC',N'@p__linq__0 nvarchar(4000),@p__linq__1 datetime2(7),@p__linq__2 datetime2(7)',@p__linq__0=N'%false%',@p__linq__1='2013-07-10 22:00:00',@p__linq__2='2013-07-12 22:00:00'

Original execution plan:
execution plan original 5

Original execution averages: CPU: 500 Reads: 3935 Writes: 0 Duration: 500

Rewritten query:

SELECT TOP 100
        EventTypeId,
        EventSourceId,
        TimeCreated,
        Data1MaxStr
    FROM LoggingView
    WHERE
        TimeCreated BETWEEN '2013-07-10 22:00:00' AND '2013-07-12 22:00:00'
        AND TraceLevelId = 4
        AND EventSourceId = 68      
        AND Data1MaxStr LIKE N'%false%'
    ORDER BY TimeCreated DESC

Rewritten execution plan:
execution plan rewritten 5 Rewritten execution averages: CPU: 1 Reads: 353 Writes: 0 Duration: 85

Conclusion/Problem

So far for my tries. As you can see, I was able to greatly improve on the reads and duration front of the last query, but it seems performance for all other queries has worsened.

Any hints to how I could setup my views/indexes differently or maybe even rewrite those queries is greatly appreciated.

One last thing to add: All measures of speed and execution plans in this post have been taken from a test db that I am using locally, it contains 100,000 entries; in production however this runs against a table containing about 20,000,000 entries - execution plans however (the original ones of course - I did not yet try my rewrites out in production) were the same.

share|improve this question

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.