Stephen, My 2 cents without seeing the query. 1. Try to make a view that groups your main table with the detail table to calculate that extra status field. I'd expect that to be quick and easy to do. 2. Change your EF to not query the table + 100 queries for the status but query the view.
On Tue, Oct 4, 2016 at 12:29 PM, Stephen Price <[email protected]> wrote: > Hey all, > > > Am looking at optimising an EF query right now, so thought it would be ok > to hijack this thread. Even if it leads to bagging of EF, I'm ok with that. > [image: > 😊] > > > So I have a single table being queried, and I grabbed the query being run > via SQL Server profiler. > > 4.5million records in the table. Have an Id field, a year field and an > EventId field. The rest of the fields are data, so not searching those. > > The query being produced is showing as an sp_execsql and does a where > against the year field. > > The actual query itself takes 1699ms, but the screen takes longer to > return the result as it then loads the detail of each item so it can show > the current status of each row. (ie the highest version status is the > current, in a related status table). > > So each query is fast but by the time it loads 100 of them, its made 100 > little calls which all add up to a long delay to the user. > > > Options I'm thinking here (looking for validation of my thinking, or new > ideas outside my database knowledge) > > 1. Reduce the number of items. Say 20 instead of 100. > > 2. Get the Status asyncronously. Would need to work out how to do that > client side but seems viable. Initial list would load in 2 seconds, then > statuses at the top would load almost right away. Items out of sight > (scroll to view them) would load later. > > 3. Single query. Server side query is doing a take(100) to reduce the > number of results if the search is too broad... which means its possibly > prematurely resolving the linq query and sending the status lookups > individually rather than single query. > > 4. something else. Get rid of EF and hand write SQL. Look for new job > because didn't deliver on time. [image: 😉] > > > Feedback, criticism, laughing and pointing all welcomed. > > cheers > > Stephen > ------------------------------ > *From:* [email protected] <[email protected]> on > behalf of Kirsten Greed <[email protected]> > *Sent:* Saturday, 1 October 2016 5:26:33 PM > > *To:* 'ozDotNet' > *Subject:* RE: Entity Framework - the lay of the land > > That makes sense > > It would be good to have some guidelines about where the cut over point is. > > Also whether solutions like NService Bus could mitigate the use of EF ? > > > ------------------------------ > *From:* [email protected] [mailto:ozdotnet-bounces@ > ozdotnet.com] *On Behalf Of *Greg Low (??????) > *Sent:* Saturday, 1 October 2016 12:40 PM > *To:* ozDotNet > *Subject:* RE: Entity Framework - the lay of the land > > Agreed but not websites with thousands of concurrent users. The problem is > that people don’t realise that the same logic doesn’t apply in both areas. > > > > Regards, > > > > Greg > > > > Dr Greg Low > > > > 1300SQLSQL (1300 775 775) office | +61 419201410 mobile│ +61 3 8676 4913 > fax > > SQL Down Under | Web: www.sqldownunder.com | http://greglow.me > > > > *From:* [email protected] [mailto:ozdotnet-bounces@ > ozdotnet.com] *On Behalf Of *Kirsten Greed > *Sent:* Saturday, 1 October 2016 6:42 AM > *To:* 'ozDotNet' <[email protected]> > *Subject:* RE: Entity Framework - the lay of the land > > > > Caveat: this is for winforms line of business applications. > > > > > ------------------------------ > > *From:* [email protected] [mailto:ozdotnet-bounces@ > ozdotnet.com <[email protected]>] *On Behalf Of *Kirsten Greed > *Sent:* Saturday, 1 October 2016 6:35 AM > *To:* 'ozDotNet' > *Subject:* Entity Framework - the lay of the land > > My 2c > > > > Horses for courses > > > > I am using EF Code first and loving it. > > > > Most of the posts on this thread are about *building the thing right*. > > > > Yet I am finding that EF Code first helps me a lot with *building the > right thing.* > > > > I find changing the database design is much easier now that I use EF > Migrations, this helps me stay in a "play" headset, lowering my fear of > changing the database structure. > > > > There are places where I choose to break into transact-sql, but most of my > CRUD is done via DevExpress XAF with EF Code first. > > > > My 2c :-) > > Kirsten > > > > > > > > > > > > > > > > > > > > > > > > __________ Information from ESET NOD32 Antivirus, version of virus > signature database 14206 (20160930) __________ > > The message was checked by ESET NOD32 Antivirus. > > http://www.eset.com > > > __________ Information from ESET NOD32 Antivirus, version of virus > signature database 14208 (20161001) __________ > > The message was checked by ESET NOD32 Antivirus. > > http://www.eset.com >
