c# - Entity Framework v6.1 query compilation performance -

i confused how ef linq queries compiled , executed. when run piece of program in linqpad couple of times, varied performance results (each time same query takes different amount of time). please find below test execution environment.

tools used: ef v6.1 & linqpad v5.08. ref db : contosouniversity db downloaded msdn.

for queries, using persons, courses & departments tables above db; see below.

now, have below data:

query goal: second person , associated departments. query:

var test = (     p in persons     join d in departments on p.id equals d.instructorid      select new {        person = p,        dept = d     } );  var result = (from pd in test     group pd pd.person.id grp     orderby grp.key      select new {           id = grp.key,           firstname = grp.first().person.firstname,           deps = grp.where(x => x.dept != null).select(x => x.dept).distinct().tolist()          }).skip(1).take(1).tolist();     foreach(var r in result)    {         console.writeline("person is..." + r.firstname);         console.writeline(r.firstname + "' deps are...");         foreach(var d in r.deps){            console.writeline(d.name);         }    } 

when run result , linqpad shows time taken value 3.515 sec 0.004 sec (depending how gap take between different runs).

if take generated sql query , execute it, query runs between 0.015 sec 0.001sec.

generated query:

-- region parameters declare @p0 int = 1 declare @p1 int = 1 -- endregion select [t7].[id], [t7].[value] [firstname] (    select row_number() on (order [t6].[id]) [row_number], [t6].[id],   [t6].[value]      (        select [t2].[id], (          select [t5].[firstname]           (             select top (1) [t3].[firstname]             [person] [t3]             inner join [department] [t4] on ([t3].[id]) = [t4].    [instructorid]             [t2].[id] = [t3].[id]             ) [t5]         ) [value]     (         select [t0].[id]         [person] [t0]         inner join [department] [t1] on ([t0].[id]) = [t1].[instructorid]         group [t0].[id]         ) [t2]       ) [t6]   ) [t7] [t7].[row_number] between @p0 + 1 , @p0 + @p1 order [t7].[row_number] go  -- region parameters declare @x1 int = 2 -- endregion select distinct [t1].[departmentid], [t1].[name], [t1].[budget], [t1].    [startdate], [t1].[instructorid], [t1].[rowversion] [person] [t0] inner join [department] [t1] on ([t0].[id]) = [t1].[instructorid] @x1 = [t0].[id] 

my questions: 1) linq statements correct? or can optimized? 2) time difference linq query execution normal?

another different question: have modified first query execute (called tolist before second query). time generated sql simple shown below (it doesn't there sql query first linq statement tolist() included):

 select [t0].[id], [t0].[lastname], [t0].[firstname], [t0].[hiredate], [t0].      [enrollmentdate], [t0].[discriminator], [t1].[departmentid], [t1].[name], [t1].   [budget], [t1].[startdate], [t1].[instructorid], [t1].[rowversion]  [person] [t0]  inner join [department] [t1] on ([t0].[id]) = [t1].[instructorid] 

running modified query took varied amount of time difference not big first query set run.

in application, there going lot of rows , prefer first query set second 1 confused.

please guide. (note: have little sql server knowledge so, using linqpad fine tune queries based on performance)

thanks