与SSMS相比，基本的SQL Select * to C＃有什么问题 [英] What is wrong with painfully slow basic SQL Select * through C# in comparison to SSMS

问题描述

我已经安装了SQL Express 2014及其所有工具，并使用Windows身份验证创建了一个实例（SQLEXPRESS）。然后我在master数据库中创建了一个名为dbo.doc的表，其中包含18列，并在其中输入了7731个条目。

我在Visual Studio Express（社区）2015中启动了一个新的Windows窗体项目。

我在Form Designer上放置了一个DataGridView，BindingSource和BindingNavigator数据控件。然后双击表单将我发送到表单的load事件并输入以下代码来加载数据：

 SqlConnection con =  new  SqlConnection（）; 
 SqlCommand comm =  new  SqlCommand（）; 
 SqlDataAdapter da =  new  SqlDataAdapter（）; 
 con.ConnectionString =  @ 数据源= .\SQLEXPRESS;初始目录=主;集成安全性= SSPI;; 
 comm.Connection = con; 
 comm.CommandText =   SELECT * FROM doc;  //   [dbo]。[doc]  
 da.SelectCommand = comm; 
 DataTable dt =  new  DataTable（）; 
 da.Fill（dt）; 
 bSMain.DataSource = dt;  //   BindingSource  
 bNMain.BindingSource = bSMain ;  //   BindingNavigator  
 dGVMain.DataSource = bSMain;  //   DataGridView  

我启动了代码，我不得不等待20-30加载数据的秒数。我进入SQL Server Managment Studio并输入：

使用master< br /> 
 select * from doc< br /> 
 go 

结果不到半秒钟。我进入了Visual Studio并从Tools - SQL Server - New Query编写了相同的代码并且它同时加载了。

我已经尝试了从其他类似问题中找到的所有内容，包括修复和重新安装SQL Server和Visual Studio，制作新项目，从控制台加载数据，使用连接的体系结构，不同的连接字符串，不同的命令，使用odbc驱动程序进行混合模式身份验证，编译发布版本，从管理员开始，关闭任何安全选项，检查sql server和visual studio sql设置以及其他许多设置，但每次从代码加载20-30秒，从直接查询（从SSMS或VS）加载不到一秒。

解决方案

数据绑定有一个开销，并显示UI中的行。



要检查此时间 da.Fill（dt）仅在没有数据绑定到UI元素的情况下。 （由于对象结构，作为一边 DataSet s和 DataTable 也比直接查询慢。）

I've installed SQL Express 2014 with all of its tools and created a instance (SQLEXPRESS) with Windows Authentication. Then I created a table named dbo.doc in the master database with 18 columns and entered 7731 entries in it.
I started a new Windows Form Project in Visual Studio Express (Community) 2015.
I placed on the Form Designer a DataGridView, BindingSource and BindingNavigator data controls. Then double pressed the form to send me to the load event of the form and typed the following code to load the data:

SqlConnection con = new SqlConnection();
            SqlCommand comm = new SqlCommand();
            SqlDataAdapter da = new SqlDataAdapter();
            con.ConnectionString = @"Data Source=.\SQLEXPRESS;Initial Catalog=master;Integrated Security=SSPI;";
            comm.Connection = con;
            comm.CommandText = "SELECT * FROM doc";//[dbo].[doc]
            da.SelectCommand = comm;
            DataTable dt = new DataTable();
            da.Fill(dt);
            bSMain.DataSource = dt;//BindingSource
            bNMain.BindingSource = bSMain;//BindingNavigator
            dGVMain.DataSource = bSMain;//DataGridView

I started the code and I had to wait 20-30 seconds to load the data. I went in SQL Server Managment Studio and typed:

use master<br />
select * from doc<br />
go

The results came for less than a half of a second. I went in Visual Studio and from Tools - SQL Server - New Query wrote the same code and it loaded for the same time.
I've tried everything I could to find from other similar questions including repair and reinstalling SQL Server and Visual Studio, making new project, loading the data from console, with connected architecture, different connection strings, different commands, mixed mode authentication with odbc driver, compiling a release build, starting as administator, turning off any security options, checking the sql server and visual studio sql settings and many others, but it was loading 20-30 seconds every time from the code and less than a second from direct query (from SSMS or VS).

解决方案

There is an overhead to "data binding" and showing the rows in the UI.

To check this time the da.Fill(dt) only without the data binding to UI elements. (as a side DataSets and DataTables are also slower than direct queries because of the object structure).

这篇关于与SSMS相比，基本的SQL Select * to C＃有什么问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！