Execution Plans &<&> Proportionate Execution Times

Hi

I am slowly getting to grips with SQL Server. As a part of this, I have been attempting to work on producing more efficient queries. This post is regarding what appears to be a discrepancy between the SQL Server execution plan and the actual time taken by a query to run.

My brief is to produce an attendance system for an education establishment (I presume you know I'm not an A-Level student completing a project :p ). Circa 1.5m rows per annum, testing with ~3m rows currently. College_Year could strictly be inferred from the AttDateTime however it is included as a field because it a part of just about every PK this table is ever likely to be linked to. Indexes are not fully optimised yet.

Table:

CREATE TABLE [dbo].[AttendanceDets] (
[College_Year] [smallint] NOT NULL ,
[Group_Code] [char] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Student_ID] [char] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Session_Date] [datetime] NOT NULL ,
[Start_Time] [datetime] NOT NULL ,
[Att_Code] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO

CREATE CLUSTERED INDEX [IX_AltPK_Clust_AttendanceDets] ON [dbo].[AttendanceDets]([College_Year], [Group_Code], [Student_ID], [Session_Date], [Att_Code]) ON [PRIMARY]
GO

CREATE INDEX [All] ON [dbo].[AttendanceDets]([College_Year], [Group_Code], [Student_ID], [Session_Date], [Start_Time], [Att_Code]) ON [PRIMARY]
GO

CREATE INDEX [IX_AttendanceDets] ON [dbo].[AttendanceDets]([Att_Code]) ON [PRIMARY]
GO
ALL inserts are via an overnight sproc - data comes from a third party system. Group_Code is 12 chars (no more no less), student_ID 8 chars (no more no less).

I have created a simple sproc. I am using this as a benchmark against which I am testing my options. I appreciate that this sproc is an inefficient jack of all trades - it has been designed as such so I can compare its performance to more specific sprocs and possibly some dynamic SQL.

Sproc:

CREATE PROCEDURE [dbo].[CAMsp_Att]

@College_Year AS SmallInt,
@Student_ID AS VarChar(8) = '________',
@Group_Code AS VarChar(12) = '____________',
@Start_Date AS DateTime = '1950/01/01',
@End_Date as DateTime = '2020/01/01',
@Att_Code AS VarChar(1) = '_'

AS

IF @Start_Date = '1950/01/01'
SET @Start_Date = CAST(CAST(@College_Year AS Char(4)) + '/08/31' AS DateTime)

IF @End_Date = '2020/01/01'
SET @End_Date = CAST(CAST(@College_Year +1 AS Char(4)) + '/07/31' AS DateTime)

SELECT College_Year, Group_Code, Student_ID, Session_Date, Start_Time, Att_Code

FROM dbo.AttendanceDets

WHERE College_Year = @College_Year
AND Group_Code LIKE @Group_Code
AND Student_ID LIKE @Student_ID
AND Session_Date <= @End_Date
AND Session_Date >=@Start_Date
AND Att_Code LIKE @Att_Code
GO

My confusion lies with running the below script with Show Execution Plan:

--SET SHOWPLAN_TEXT ON
--Go

DECLARE @Time as DateTime

Set @Time = GetDate()

select College_Year, group_code, Student_ID, Session_Date, Start_Time, Att_Code
from attendanceDets
where College_Year = 2005 AND group_code LIKE '____________' AND Student_ID LIKE '________'
AND Session_Date <= '2005-11-16' AND Session_Date >= '2005-11-16' AND Att_Code LIKE '_'

Print 'First query took: ' + CAST(DATEDIFF(ms, @Time, GETDATE()) AS VarCHar(5)) + ' milli-Seconds'

Set @Time = GetDate()

EXEC CAMsp_Att @College_Year = 2005, @Start_Date = '2005-11-16',
@End_Date = '2005-11-16'

Print 'Second query took: ' + CAST(DATEDIFF(ms, @Time, GETDATE()) AS VarCHar(5)) + ' milli-Seconds'
GO

--SET SHOWPLAN_TEXT OFF
--GO

The execution plan for the first query appears miles more costly than the sproc yet it is effectively the same query with no parameters. However, my understanding is the cached plan substitutes literals for parameters anyway. In any case - the first query cost is listed as 99.52% of the batch, the sproc 0.48% (comparing the IO, cpu costs etc support this). BUT the text output is:

(10639 row(s) affected)

First query took: 596 milli-Seconds

(10639 row(s) affected)

Second query took: 2856 milli-Seconds

I appreciate that logical and physical performance are not one and the same but can why is there such a huge discrepancy between the two? They are tested on a dedicated test server, and repeated running and switching the order of the queries elicits the same results.

Sample data can be provided if requested but I assumed it would not shed much light.

BTW - I know that additional indexes can bring the plans and execution time closer together - my question is more about the concept.

If you've made it this far - many thanks.
If you can enlighten me - infinite thanks.