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
add comment

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.