Sometimes I like to cite “Remember remember the fifth of November” in case, when I trip the mine that I teach others to avoid.
This time it happened, when reviewing some database structure and related code. So, here it goes.
The database had a table, which can be scripted as shown below. As you can probably note, we are talking about a table that holds queue of jobs that have to be performed.
CREATE TABLE [dbo].[Jobs]( [Id] [int] IDENTITY(1,1) NOT NULL, [Priority] [tinyint] NOT NULL, [DateInserted] [datetime] NOT NULL, [DateAllocated] [datetime] NULL, [DateFetched] [datetime] NULL, [Status] [tinyint] NOT NULL, [SerializedData] NULL, [ParentId] [int] NULL, [ReferenceID] [bigint] NULL, CONSTRAINT [PK_Jobs] PRIMARY KEY CLUSTERED ( [Id] ASC ) ) GO ALTER TABLE [dbo].[Jobs] ADD CONSTRAINT [DF_Jobs_DateInserted] DEFAULT (getdate()) FOR [DateInserted] GO ALTER TABLE [dbo].[Jobs] ADD CONSTRAINT [DF_Jobs_Status] DEFAULT (1) FOR [Status] GO
The use case for the table was simple:
- One part of the application inserts new jobs based on customer activities and by this sets the default date and status (1)
- Then service fetches jobs, by taking Id’s of specified amount of new (status=1) jobs and setting appropriate date on DateFetched and updating status to (2)
- Later at some point XML data will be retrieved using the Id’s retrieved previously
Initially, the code was LINQ 2 SQL in the program itself and was something like this:
var q = ctx.Jobs .Where(j => j.Status == 1) .Select(j => new { j.Id, j.Priority, j.DateInserted, j.Status }) .Take(count);
SELECT TOP (100) Id, [Priority], DateInserted FROM Jobs WHERE Status=1
(100 row(s) affected)Table 'Jobs'. Scan count 1, logical reads 648
CREATE NONCLUSTERED INDEX IX_Jobs_NewJobs ON Jobs ([Status]) INCLUDE ([Priority], [DateInserted]) WHERE ([Status]=1)
(100 row(s) affected)Table 'Jobs'. Scan count 1, logical reads 2
For a moment I thought, that the rest should be fine, because query and LINQ statement were fairly straight forward with not much magic in them, but somehow, I’ve decided to see what query LINQ does produce.
SELECT TOP (100) [t0].[Id], [t0].[Priority], [t0].[DateInserted], [t0].[Status] FROM [dbo].[Jobs] AS [t0] WHERE [t0].[Status] = @p0
I’ve also checked the query actually shown by the SQL Server Profiler:
exec sp_executesql N'SELECT TOP (5) [t0].[Id], [t0].[Priority], [t0].[DateInserted], [t0].[Status] FROM [dbo].[Jobs] AS [t0] WHERE [t0].[Status] = @p0',N'@p0 int',@p0=1
UPDATE TOP (@BatchSize) Jobs SET Status = 2, DateFetched = GETUTCDATE() OUTPUT inserted.Id, inserted.[Priority], inserted.DateInserted WHERE Status = 1
Works like a charm