使用SQL Server的VB.NET中的数据检索速度太慢 [英] Data retrieving speed is too slow in VB.NET with SQL server

问题描述

Hi
我在使用SQL Server 2014的VB.NET 2015中工作

我有一个存储过程可以检索大量数据。



问题是，当我直接在sql server 2014新查询编辑器中执行该存储过程时，它会在1秒钟内加载记录，而检索到的数据包含3000多条记录。



但是当我在我的应用程序中调用相同的存储过程将数据加载到数据网格视图中时，它需要1分钟，这意味着比在sql server中直接执行慢60倍。



i不知道我的代码有什么问题



我尝试了什么：



这是我的连接字符串

Hi I am working in VB.NET 2015 with SQL Server 2014
I have a stored procedure that retrieves a huge data.

The problem is that when i execute that stored procedure directly in sql server 2014 new query editor then it loads the records in just 1 second while the retrieved data contains 3000 plus records.

but when i call the same stored procedure in my application to load the data into a data grid view then it takes 1 minute that means 60 times slower than the direct execution in sql server.

i don't know what is wrong in my code

What I have tried:

Here is my connection string

Data Source =192.168.0.203; Initial Catalog=SunTube_ERP; User ID=sa; Password=SunTube@2016; Connect Timeout=0

将数据检索到网格的代码是：

The code to retrieve the data in to the Grid is:

SQL_Connection.Open()

        MyQuery = "Execute sp_GL " & RecordNoCompany & ", " & OpeningBalance_ & ", '" & Format(DateF, "yyyy-MM-dd HH:mm:ss") & "', '" & Format(DateT, "yyyy-MM-dd HH:mm:ss") & "','" & AcCode & "', '" & Company_Name & "', '" & Company_Addres & "'"

        Dim MyDataAdapter As New SqlDataAdapter()

        MyDataAdapter.SelectCommand = New SqlCommand(MyQuery, SQL_Connection)

        MyDataSet = New System.Data.DataSet

        MyDataAdapter.Fill(MyDataSet)

        grdGeneralLedger.DataSource = MyDataSet.Tables(0)

        SQL_Connection.Close()

请告诉我哪里有错误。



谢谢



问候：

Asim

Please tell me where i have the mistake.

thanks

Regards:
Asim

推荐答案

简单：不要尝试加载3000 +行进入用户显示控件。



从用户POV考虑：他们需要多长时间才能找到他们感兴趣的信息？几分钟还是几分钟？有多少人会决定转储您的申请，因为它不值得使用？

页面搜索，搜索，过滤它：但不要只是将它全部转储到显示器上，并期望1）它快速，2）它可用或受用户欢迎 - 这是只是懒惰，我的用户认为它非常粗鲁，我会得到一些非常讨厌的电话和消息...

Simple: Don't try to load 3000+ rows into a user display control.

Think about it from a user POV: how long will it take them to find the info they are interested in? Seconds, or minutes? How many will just decide to dump your application because it isn't worth using?
Page it, search it, filter it: but don't just dump it all on the display and expect 1) it to be fast, and 2) it to be usable or popular with users - that's just lazy, and my users would think it very rude and I'd get some very nasty calls and messages...

你可以尝试限制最初为<

You can try to limit the amount of records fetched initially for the DataGridView by using the Virtual Mode, see here for an example: Walkthrough: Implementing Virtual Mode in the Windows Forms DataGridView Control[^]
Also a DataSet is not the fastest way to load records, if you only need read-only access it's better to use a DataReader, see here for an example: Retrieving Data Using a DataReader[^]

获取的记录数量code> DataGridView 使用虚拟模式，请看这里的示例：演练：在Windows窗体中实现虚拟模式DataGridView控件 [ ^ ]

另外一个 DataSet 不是加载记录的最快方法，如果你只需要只读访问权限，最好使用 DataReader ，请看这里例如：使用DataReader检索数据 [ ^ ]

这篇关于使用SQL Server的VB.NET中的数据检索速度太慢的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！