Procedure execution fast but slow fetching of records in dataset

Question

0.00/5 (No votes)

See more:

I am using the following statement to fetch the results of query in dataset.

ASM

DataSet ds = new DataSet();
        ds = OdbcHelper.ExecuteDataset(Connection.ODBCConnection(), CommandType.StoredProcedure, "{ CALL Search_EQ_SP(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)}", p);

I have two stored procedures Search_EQ_SP and Search_NF_SP.Both return nearly 90000 records.Both take less than 25 seconds to execute when checked in sql server.But when it comes to fetching data in dataset in c# Search_NF_SP takes 30-35 secs which is considerable but Search_EQ_SP takes 3 mins and above.

I have also tried using datareader instead of dataset but still getting the same results.
I still cant figure out if Search_EQ_SP is taking secs in sql server to execute then why it is taking so long to load data in dataset. Any ideas?Any suggestions?

Experts..Please help me.I am stuck.

Thanks in advance.

Posted 18-Sep-15 20:34pm

PWAVEL

Updated 18-Sep-15 20:37pm

v2

Add a Solution

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Mehdi Gholam · Answer 1 · 2015-09-18T21:08:00

Solution 1

1) DataSets are heavy objects structures and generally take longer to load.
2) Executing on SQL server will be fast since there is no network overhead.
3) Executing over a network will take longer and is dependent on the amount of data and your network speed.
4) Connecting through ODBC will incur an additional overhead.

To overcome the above try to filter or page your results for faster client access.
Also use the native SQLCommands connectors instead of ODBC.

Posted 18-Sep-15 21:08pm

Mehdi Gholam

Comments

PWAVEL 19-Sep-15 3:18am

i have also tried using datareader instead of dataset.But still takes the same time to load.I ant filter or page since i need to show all the data to user at once.Will sqlcommands cause a heavy impact on performance?

Mehdi Gholam 19-Sep-15 3:26am

Native SQL in .net is generally faster than using anything else.

Wendelius · Answer 2 · 2015-09-18T21:22:00

Solution 2

I fully agree with Mehdi. In order to better see the time consumed in different operations, try using profiling on both ends:

Posted 18-Sep-15 21:22pm

Wendelius

Naveen.Sanagasetti · Answer 3 · 2015-09-20T20:32:00

Hi,

If you are thinking about performance, then first off all you should be optimize your procedure, once procedure is working as expected then optimize your code logic, instead of load total data at a time better to filter it based on some parameters which will improve the performance, coming to your point reduce unnecessary code logic in your load event, instead of load data at loading time, better to load while searching time, these and all improve the performances, these are basic steps to improve the performance.