score:3

Accepted answer

you can do these things to help the generated query.

  1. return only what you need. now you are returning everything but you only ever use timestamp so just return that. you are setting statuscode below however you are already filtering this in the where clause so you know that all returned items have a statuscode of "xx" so no need to retrieve that too. this is less data coming back across the network and less cycles taken to map that data to objects and less memory that the data takes up.
  2. you should look at the generated query by ef. you can do this using a sql profiler tool (sql server has one called sql profiler). then look at the query plan and see if there is anything that could benifit you like adding a missing index. this profiling should be done on the database server, not in c#.
  3. consolidate your where clauses because it's easier to read.

code

// list of timestamps from database
var timestamps = systemdb.devicestates.where(s => s.device_id == deviceid && 
                                                  s.timestamp > startdate && 
                                                  s.timestamp < enddate &&
                                                  s.statuscode == "xx")
                                      .select(x => x.timestamp).tolist();

if you still wanted your status code because you removed the filter you could do it like this

var timestamps = systemdb.devicestates.where(s => s.device_id == deviceid &&
                                                  s.timestamp > startdate && 
                                                  s.timestamp < enddate && 
                                                  s.statuscode == "xx")
                                      .select(x => new {x.timestamp, x.statuscode})
                                      .tolist();

score:1

you can do thin one where clause

list<devicestate> liststate = systemdb.devicestates.where(
                                  l => l.device_id == deviceid 
                               && l.timestamp > startdate
                               && l.timestamp < enddate 
                               && l.statuscode == "xx" 
                               ).tolist();

then use for rather than foreach, because foreach slower in case of big amount data

for (int i = 0; i< liststate.count; i++ )
{  
   object.statuscode= liststate[i].statuscode;
   object.timestamp = liststate[i].timestamp; 
}

score:1

igor's answer already shows the most obvious improvements that you should do in any case.

now, if you handly lots of data, then you may want to do it in multiple threads parallel with system.threading.tasks.parallel.foreach. code (with other improvements):

var devicestatetime = systemdb.devicestates.where(s => s.device_id == deviceid
                        && s.timestamp > startdate)
                        && s.timestamp < enddate
                        && s.statuscode == "xx");
parallel.foreach(devicestatetime, (time) =>
                 {
                    object.statuscode = "xx";
                    object.timestamp = time;
                 });

please note, that i did not test it (i wrote it down from memory), so i may have mistyped somewhere.


Related Query