There is a certain sweet irony in creating a SQL object to query, to get around a limitation of querying the actual SQL object using the framework no ?
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://www.sqldownunder.com/> | http://greglow.me<http://greglow.me/> From: ozdotnet-boun...@ozdotnet.com [mailto:ozdotnet-boun...@ozdotnet.com] On Behalf Of Corneliu I. Tusnea Sent: Tuesday, 4 October 2016 12:36 PM To: ozDotNet <ozdotnet@ozdotnet.com> Subject: Re: Entity Framework - the lay of the land 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 <step...@lythixdesigns.com<mailto: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. [😊] 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. [😉] Feedback, criticism, laughing and pointing all welcomed. cheers Stephen ________________________________ From: ozdotnet-boun...@ozdotnet.com<mailto:ozdotnet-boun...@ozdotnet.com> <ozdotnet-boun...@ozdotnet.com<mailto:ozdotnet-boun...@ozdotnet.com>> on behalf of Kirsten Greed <kirst...@jobtalk.com.au<mailto: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-boun...@ozdotnet.com> [mailto:ozdotnet-boun...@ozdotnet.com<mailto:ozdotnet-boun...@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<tel:%2B61%20419201410> mobile│ +61 3 8676 4913<tel:%2B61%203%208676%204913> fax SQL Down Under | Web: www.sqldownunder.com<http://www.sqldownunder.com/> | http://greglow.me<http://greglow.me/> From: ozdotnet-boun...@ozdotnet.com<mailto:ozdotnet-boun...@ozdotnet.com> [mailto:ozdotnet-boun...@ozdotnet.com<mailto:ozdotnet-boun...@ozdotnet.com>] On Behalf Of Kirsten Greed Sent: Saturday, 1 October 2016 6:42 AM To: 'ozDotNet' <ozdotnet@ozdotnet.com<mailto: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-boun...@ozdotnet.com> [mailto: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
