Link to home
Start Free TrialLog in
Avatar of Jyozeal
JyozealFlag for United States of America

asked on

performance difference while executing from ssms and .net code

I'm executing a stored procedure that returns 15000 rows. I get that into a dataset using entity framework.
When i execute the stored procedure in ssms(sql server 2012) the execution time is 2 secs. However from .net code it is 56 secs not acceptable. This time is just for db call to fill dataset.

I tried setting isolation level to read committed for command object - no difference. in db it is read committed snapshot. I have no access to sql profiler because of policies in place.

is there any other way to solve this? Thanks for your time.
Avatar of Duy Pham
Duy Pham
Flag of Viet Nam image

I don't think there is better way as long as you still want to fetch such a large amount of rows in DataSet/DataTable. If it's possible, you might want to try doing paging.

Why do you need to return 15000 rows at once?
Avatar of Jyozeal

ASKER

i missed to tell that i am using paging and its returning only 150 records max at a time into dataset. what i meant is even when retrieving 15000 records thru SP in SSMS its 2 seconds only.
Hi  Jyozeal;

To your statement, "When i execute the stored procedure in ssms(sql server 2012) the execution time is 2 secs", you most likely executed this on the server machine and did not incur any network latency that could add more time to the results. Also the server is not building entities to store each row when displaying the information it uses the raw data unlike Entity Framework where it uses network connection to connect to the server and builds a entity for each row to hold the row data and if the entity being returned is an entity from the EDM model then it has a couple of more things to do for each entity like set it up for Change Tracking.

To your question, "Is there any other way to solve this?", You may think of trying to reduce the number of rows that you are returning, do you really need 15,000 rows returned at one time? You may try using ADO.Net without using Entity Framework just build a connection executing a command and using the SqlDataReader to return the results to see how much faster it will be.
I would suggest that you run MS Profiler or sp_whoisactive, I suspect you will find the query generated by EF is nothing like the query you are running from SSMS.
Avatar of Jyozeal

ASKER

Thanks for your comments.  Fernando soto, i did try that using connection,command and dataadapter to fill dataset and its the same amount of time. no difference.

Anthony perkins, i have the same doubt that EF is generating the query differently than SSMS. when i searched over web, i came across a similar qtn on stackoverflow and he determined the serializable isolation level with EF query and read committed in DB. When i tried using dataadapter i did try read commited on the command object but to no avail.
I am  having no hopes that i will get either get access to sql profiler or use third party tools but i will try again.
But if you are using a stored procedure in EF then the command will still be a stored procedure call.  So you really are dealing with the way it is being processed into an object on the .NET side of things.  The size of the rows will matter too.  If you did this with returning just an INT in the result set instead of a full row you would most likely see it be faster.

Network latency is the biggest killer in a remote app so the less data that is getting sent back the better.  Test the stored procedure to return 1 row (SELECT TOP 1) and see if the latency is still there.  That will at least tell you something more.
ASKER CERTIFIED SOLUTION
Avatar of Fernando Soto
Fernando Soto
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial