sql server - Why does this EF query take so long? -


we have ef4 query taking 10 seconds complete. query isn't complex, there lot of related tables included, it's taking long time. i'm trying speed up.

the original query looks (shortened clarity)...

var supporttickets = ctx.supporttickets   .include(s => s.system.customer.country)   .include(s => s.system.site.address.country)   // other includes omitted   .orderbydescending(s => s.id)   .tolist(); var ticketslist = supporttickets   .select(createsupportticketlistoverview)   .tolist();

createsupportticketlistoverview() method takes entity , returns dto based on it. shortened version of looks this...

private static supportticketlistoverview createsupportticketlistoverview(supportticket x)   {     return new supportticketlistoverview {       id = x.id,       systemnumber = x.system != null ? x.system.systemnumber : "",       customername = x.system != null && x.system.customer != null ? x.system.customer.name : "",       shortsummary = x.shortsummary,       sitename = x.site != null ? x.site.sitename : "",       status = x.status != null ? x.status.description : "",       // other properties omitted clarity     };   }

as said, takes 10 seconds , returns under 4000 results. sql server profiler shows query took 6.6s. if copy sql generated , run on own, takes 2 seconds, confuses me. why faster when run on own? time needed create entities not included in database query it? if were, rest of time spent doing?

i tried improve turning off tracking , pulling required data database, rather full entities. revised code looks (again shortened clarity)...

  var tickets = ((salestrackercrmentities) getcontext()).supporttickets       .asnotracking()       .include(s => s.system.customer.country)       .include(s => s.system.site.address.country)       .orderbydescending(s => s.id)       .select(t => new {         systemnumber = t.system != null ? t.system.dhrnumber : "", t.id,         customername = t.system != null && t.system.customer != null ? t.system.customer.name : "",         sitename = t.site != null ? t.site.sitename : "",         status = t.status != null ? t.status.description : "",         // other stuff omitted       })       .asenumerable();   var tickets1 =tickets       .select(t => new supportticketlistoverview {         id = t.id,         systemnumber = t.systemnumber,         customername = t.customername,         shortsummary = t.shortsummary,         sitename = t.sitename,         status = t.status,         // other stuff omitted       })       .tolist();

to surprise, took 15 seconds complete. looking in profiler, database query took around 0.7s, ie ten times faster original query, ef query overall took 50% longer.

so i'm confused. did searching, advice found things i'm doing. example, this blog post gives 7 ways improve ef performance. these include not using repository pattern (wasn't sure meant here, didn't show example of how or not it), not using paging (we don't), using projections (we are, @ least in new query), turning off lazy loading (it off already), turning off tracking (already did) , using indexes on tables (we are). final tip reduce number of queries. can't see how can here, need related data.

in summary, original database query takes 6.6s, , overall ef query takes 10s. revised query takes .7s database part, 15s overall ef query. of these far long.

is able advise how can speed query? thanks

what version of sql server using? if it's 2016 can enable query store (https://msdn.microsoft.com/en-us/library/dn817826.aspx) , find t-sql query being executed ef4 query. if it's older version can, while ef4 query running use (http://blog.sqlauthority.com/2009/01/07/sql-server-find-currently-running-query-t-sql/) find query text , see why query slow running against server , analyzing plan/missing indexes/etc.


Comments

Post a Comment

Popular posts from this blog

aws api gateway - SerializationException in posting new Records via Dynamodb Proxy Service in API -

i getting "__type": "com.amazon.coral.service#serializationexception" as reply in postman & in test console in api gateway trying post record directly dynamodb using api proxy services.. referring aws article - https://aws.amazon.com/blogs/compute/using-amazon-api-gateway-as-a-proxy-for-dynamodb/ here's mapping { "tablename": "tablenamegoeshere", "item": { "id" : "$context.requestid" "eventname" : "$input.path('$.eventname')", "timestamp" : $input.path('$.timestamp'), "answers": "$util.parsejson($input.path('$.answers'))" } } update: did asked ... , worked if try add array of json objects gives me above same error - here's trying now. please - couldnt find on google well #set($inputroot = $input.path('$')) { "tablename": "answer", "item": {
Read more

asp.net - Problems sending emails from forum -

i have been redesigning site has message board, has been written guy looked after site. it has few problems , written in .asp , uses .mdb files database. you can see whole thing here . feel free post if helps, test thing have set using original files , new database. the basic problem when posts message, supposed email sent them, have posted it. when replies, originator supposed message, replier has been posted. i’d have thought @ point in process there should script/file in postmessage.asp? the postmessage.asp page below. <% option explicit %> <!-- #include virtual="common/adovbs.inc" --> <html> <head> <meta http-equiv="content-type" content="text/html; charset=windows-1252"> <meta name="generator" content="microsoft frontpage 4.0"> <meta name="progid" content="frontpage.editor.document"> <title>new page 2</title> <base target="_self"
Read more