过程执行速度快，但数据集中记录的获取速度较慢 [英] Procedure execution fast but slow fetching of records in dataset

问题描述

我正在使用以下语句来获取数据集中的查询结果.

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

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

我有两个存储过程Search_EQ_SP和Search_NF_SP.两者都返回了近90000条记录.在sql server中检查时，它们都花费不到25秒的时间来执行.但是，当涉及到在c#中获取数据集中的数据时，Search_NF_SP需要30-35秒的时间，但这是相当可观的，但是Search_EQ_SP需要3分钟以上的时间.

我也尝试使用datareader而不是数据集，但仍然得到相同的结果.
我仍然不知道Search_EQ_SP是否要花几秒钟在sql server中执行，然后为什么要花这么长时间才能将数据加载到数据集中.有任何想法吗?有什么建议吗?

专家..请帮助我.我被卡住了.

预先感谢.

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.

推荐答案

1)DataSet是重对象结构，通常需要较长的加载时间.
2)由于没有网络开销，因此在SQL Server上执行将很快.
3)通过网络执行将花费更长的时间，并且取决于数据量和您的网络速度.
4)通过ODBC连接将产生额外的开销.

为了克服上述问题，请尝试过滤或分页结果以加快客户端访问速度.
还可以使用本机SQLCommand的连接器代替ODBC.

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.

我完全同意Mehdi的观点.为了更好地了解在不同操作中花费的时间，请尝试在两端使用性能分析:

• 初学者入门性能分析 [SQL Server Profiler [ ^ ]

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

• Beginners Guide to Performance Profiling[^]
• SQL Server Profiler[^]

如果您正在考虑性能，那么首先应该优化过程，一旦过程按预期工作，然后优化代码逻辑，而不是一次加载总数据更好地根据某些参数进行过滤，这将改善性能.性能，降低您的加载事件中不必要的代码逻辑，而不是在加载时加载数据，在搜索时更好地加载，这些都改善了性能，这些都是提高性能的基本步骤.

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.

这篇关于过程执行速度快，但数据集中记录的获取速度较慢的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！