Slow T-SQL query, convert to LINQ to Object
- by yimbot
I have a T-SQL query which populates a DataSet from an MSSQL database.
string qry = "SELECT * FROM EvnLog AS E
WHERE TimeDate =
(SELECT MAX(TimeDate)
From EvnLog
WHERE Code = E.Code)
AND (Event = 8)
AND (TimeDate BETWEEN '" + Start + "' AND '" + Finish + "')"
The database is quite large and being the type of nested query it is, the Data Adapter can take a number of minutes to fill the DataSet. I have extended the DataAdapter's timeout value to 480 seconds to combat it, but the client still complains about slow performance and occassional timeouts.
To combat this, I was considering executing a simpler query (ie. just taking the date range) and then populating a Generic List which I could then execute a Linq query against.
The simple query executes very quickly which is great. However, I cannot seem to build a Linq query which generates the same results as the T-SQL query above.
Is this the best solution to this problem?
Can anyone provide tips on rewriting the above T-SQL into Linq?
I have also considered using a DataView, but cannot seem to get the results from that either.
