VB.NET 2010 Windows窗体应用程序与SQL Server 2008的连接 [英] Connectivity of VB.NET 2010 Windows Form Application with SQL Server 2008

问题描述

您好！



我正在使用SQL Server 2008作为后端数据库的VB.NET 2010 win form应用程序项目。



项目正在网络上运行。

数据库部署在服务器系统上，而应用程序安装在近15-20个客户端系统上。



问题是应用程序非常慢。每当应用程序连接服务器计算机上的数据库时，应用程序的速度都非常慢。



下面我分享我的代码，这样任何人都可以做出决定问题出在哪里？ ？



*********************连接字符串代码******* **************

Hello !

I am working on a project of VB.NET 2010 win form application with SQL Server 2008 as back end database.

The project is running on a network.
Database is deployed on a server system while the application is installed on almost 15-20 client systems.

the problem is that the application is very slow. Whenever the application connects the database on the server machine the speed of application is very slow.

Below i m sharing my code so anyone can make decision "where the problem is ??"

********************* Code for Connection String *********************

Dim SQL_Connection As New SqlConnection
SQL_Connection.ConnectionString = "Data Source =serverip; Initial Catalog=DatabaseName; User ID=DatabaseUserName; Password=DatabasePassword; Connect Timeout=0"

*********************插入数据代码************** ***** $

********************* Code for Insert Data *********************

SQL_Connection.Open()

MyCommand = New SqlCommand("Insert Into tablename values(1,'name','address')", SQL_Connection)
MyCommand.ExecuteNonQuery()

SQL_Connection.Close()

*********************检索数据代码******** *************

********************* Code for Retrieve Data *********************

SQL_Connection.Open()
Dim MyDataSet As New DataSet
Dim MyDataAdapter As New SqlDataAdapter("select * from TableName where Condition", SQL_Connection)
MyDataAdapter.TableMappings.Add(0, TableName)
MyDataSet = New System.Data.DataSet
MyDataAdapter.Fill(MyDataSet)
MyDataSet.Tables(0).TableName = TableName
DataGridViewName.DataSource = MyDataSet.Tables(0)

***** ****************用于检索数据的Code2 *********************

********************* Code2 for Retrieve Data *********************

SQL_Connection.Open()

Dim MyQuery as String = "Select * from tablename where condition"
Dim Command = New SqlCommand(MyQuery , SQL_Connection)
Dim DataReader As SqlDataReader
DataReader = Command.ExecuteReader

Dim RowNo As Long = 0

While DataReader.Read
datagridviewname.Rows.Add(1)
datagridviewname.Rows(RowNo).Cells(0).Value = DataReader!ID
datagridviewname.Rows(RowNo).Cells(1).Value = DataReader!Name
datagridviewname.Rows(RowNo).Cells(2).Value = DataReader!Address
datagridviewname.Rows(RowNo).Cells(3).Value = DataReader!PhoneNo
datagridviewname.Rows(RowNo).Cells(4).Value = DataReader!Description

RowNo = RowNo + 1

End While

DataReader.Close()
SQL_Connection.Close()

***************************



现在请告诉我，我的错误是软件运行速度很慢？



谢谢！



我会感激一个人我将解决我的问题。

***************************

Now please tell me where is my mistake that the software runs very slow ?

Thanks !

I will be grateful to one who will fix my problem.

推荐答案

如果导致速度减慢的SQL Server很可能问题出现在'条件'。我不是在开玩笑，因为你使用的标准是性能调优的关键。



如果查询速度很慢，你需要确保表上有适当的索引支持有限数量的行操作，例如基于键值等的更新。



由于您尚未发布实际条件，因此无法提供确切的建议，我建议熟悉不同类型的索引策略。

If it's the SQL Server that is causing the slowdown most likely the problem is in the part 'where condition'. I'm not joking since the criteria you use is the key to performance tuning.

If the queries are slow you need to ensure that you have proper indexes on the table to support limited amount of row operations such as updates based on key values etc.

Since you haven't posted the actual conditions, it's impossible to give exact proposals, but I recommend getting familiar with different kinds of indexing strategies.

老实说，没有人能为你修复它，因为你没有提供任何有用的信息。



似乎可能的原因是：

1）数据库文件*已损坏*，

2）错误配置sql server，

3）windows防火墙配置错误，

4）客户端ip协议配置错误

5）连接速度慢

6）等



请注意，当我说数据库文件是* corupted *时，我并不是说文件是物理上被破坏的，这意味着文件需要缩小 [ ^ ]，索引必须改变（参见Mika的回答）等等。



我建议读这个：

监视和调整性能 [ ^ ]

配置服务器以侦听特定TCP端口（SQL Server配置管理器） [ ^ ]

为数据库引擎访问配置Windows防火墙 [ ^ ]

有关更快SQL查询的7个性能提示 [ ^ ]

第14章 - 提高SQL Server性能 [ ^ ]

To be honest, no one can fix it for you, because you did not provide any useful information.

Seems that possible reasons are:
1) database file is *corrupted*,
2) wrong configuration of sql server,
3) wrong configuration of windows firewall,
4) wrong configuration of client's ip protocol
5) connection is slow
6) etc.

Note that when i say that database file is *corupted*, i do not mean that file is physically corupted, it means that file need to be shrinked[^], indexes have to be changed (see Mika's answer), etc.

I'd suggest to read this:
Monitor and Tune for Performance[^]
Configure a Server to Listen on a Specific TCP Port (SQL Server Configuration Manager)[^]
Configure a Windows Firewall for Database Engine Access[^]
7 performance tips for faster SQL queries[^]
Chapter 14 — Improving SQL Server Performance[^]

这篇关于VB.NET 2010 Windows窗体应用程序与SQL Server 2008的连接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！