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.
JyozealAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Duy PhamFreelance IT ConsultantCommented:
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?
0
JyozealAuthor Commented:
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.
0
Fernando SotoRetiredCommented:
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.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Anthony PerkinsCommented:
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.
0
JyozealAuthor Commented:
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.
0
DBAduck - Ben MillerPrincipal ConsultantCommented:
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.
0
Fernando SotoRetiredCommented:
Hi Jyozeal;

I was thinking more like this without the use of DataAdapter and DataSet/DataTable because they bring allot of overhead themselves.
// Modified from Microsoft documentation.

string str = "Data Source=(local);Initial Catalog=Northwind; Integrated Security=SSPI";

string queryString = "SELECT OrderID, CustomerID FROM dbo.Orders;";

using (SqlConnection connection = new SqlConnection(connectionString))
{
    SqlCommand command = new SqlCommand(queryString, connection);
    connection.Open();

    SqlDataReader reader = command.ExecuteReader();

    // Call Read before accessing data. 
    while (reader.Read())
    {
        ReadSingleRow((IDataRecord)reader);
    }

    // Call Close when done reading.
    reader.Close();
}

//...

private void ReadSingleRow(IDataRecord record)
{
    Console.WriteLine(String.Format("{0}, {1}", record[0], record[1]));
}

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ASP.NET

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.