This is a standard N+1 problem that ORM's are so good at it. It often raises it's head when developers develop against small amounts of data and runs fast but in production runs like a dog. It sounds like you should be able to do it in a single query, I am not sure about EF but with NHibernate would be fairly easy. SQL would be trivial.
On Tue, Oct 4, 2016 at 12:29 PM, Stephen Price <step...@lythixdesigns.com> 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:* ozdotnet-boun...@ozdotnet.com <ozdotnet-boun...@ozdotnet.com> on > behalf of Kirsten Greed <kirst...@jobtalk.com.au> > *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:* ozdotnet-boun...@ozdotnet.com [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:* ozdotnet-boun...@ozdotnet.com [mailto:ozdotnet-bounces@ > ozdotnet.com] *On Behalf Of *Kirsten Greed > *Sent:* Saturday, 1 October 2016 6:42 AM > *To:* 'ozDotNet' <ozdotnet@ozdotnet.com> > *Subject:* RE: Entity Framework - the lay of the land > > > > Caveat: this is for winforms line of business applications. > > > > > ------------------------------ > > *From:* ozdotnet-boun...@ozdotnet.com [mailto:ozdotnet-bounces@ > ozdotnet.com <ozdotnet-boun...@ozdotnet.com>] *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 >
