程序在一段时间后变慢 [英] Programm is slowing down after a while

问题描述

你好，

我开发了一个应用程序来保留一些数据.

该应用程序正在从位于局域网中的SQL Server 2005获取数据.

工作了一会后，程序突然变得很慢.

重新启动运行程序的PC时，问题消失了一段时间，所以我认为我在SQL读写和使用所有资源上做错了事.

该程序在Windows XP Pro SP3上运行，并用VB编写. Net2008.

这就是我从SQL Server读取数据的方式.也许我做错了:

Hello,

I have developed an application to keep some data.

The application is getting data from a SQL Server 2005 which is located in the lan network.

After working for a while the program is suddenly very slow.

When rebooting the PC where the program is running, the problem is gone for a while so I think that I do something wrong with the SQL read and write and so using all the resources.

The program is running on Windows XP Pro SP3 and is written in VB. Net 2008.

This is how i read my data from the SQL Server. Maybe i do something wrong :

Dim cnstandaardinstellingen As New SqlConnection(connectionstring)
        Dim cmdstandaardinstellingen As New SqlCommand


        cnstandaardinstellingen.Open()

        Dim strQuerystandaardinstellingen As String = "select * from instellingen where hotel ='" + hotel + "'"
        Dim myCommandstandaardinstellingen As New SqlCommand(strQuerystandaardinstellingen, cnstandaardinstellingen)
        Dim myReaderstandaardinstellingen As SqlDataReader = myCommandstandaardinstellingen.ExecuteReader()
        myReaderstandaardinstellingen.Read()
        'MsgBox(myReaderstandaardinstellingen.GetString(1))
        'kamernummer(kamerteller) = myReader.GetValue(1) ' volgnummer = hoogste volgnummer per hotel zit op positie 13 in database

        'backcolorform = myReaderstandaardinstellingen.GetString(10)
        'Public kamerbezet As Color = Color.Red
        'Public kamervrij As Color = Color.GreenYellow
        'Public kamergeblokkeerd As Color = Color.Orange
        'Public kamervuil As Color = Color.Indigo
        'Public kleurgeselecteerdebetaalwijze = Color.Cyan

        hotelnaam = myReaderstandaardinstellingen.GetString(2)
        startgroep = myReaderstandaardinstellingen.GetString(20)
        ticketprinterport = myReaderstandaardinstellingen.GetString(12)
        klantdisplay = myReaderstandaardinstellingen.GetString(21)
        myReaderstandaardinstellingen.Close()
        cnstandaardinstellingen.Close()

这就是我更新数据库中某些内容的方式:

This is how I update some thing in the database :

Dim sql As String = "UPDATE Kamer SET Kamerstatus='1', Tijd='" + DateTime.Now.ToString("MM/dd/yyyy HH:mm") + "' WHERE Kamernummer='" + transferdata + "' and Hotel='" + hotel + "'"

                Dim myConnectionb As SqlConnection
                Dim myCommandb As SqlCommand
                Dim ra As Integer

                myConnectionb = New SqlConnection(connectionstring)
                myConnectionb.Open()
                myCommandb = New SqlCommand(sql, myConnectionb)
                ra = myCommandb.ExecuteNonQuery()

                myConnectionb.Close()

我使用的连接字符串:

connectionstring =数据源= 192.168.0.2;初始目录=日志记录器; uid = sa;密码=密码"

因此，当我进行读取或写入操作时，在获得所有数据后，我将关闭连接.
这会导致我总是打开和关闭的问题吗?


有人可以给我建议吗?

谢谢

最好的问候


Didier

The Connection string I use :

connectionstring = "Data Source=192.168.0.2;Initial catalog=logeurs;uid=sa;password=password"

So when I read or write, after I have all the data I close the connection.
Can this be causing the problem that I always open and close .


Can anyone give me suggestions on this please.

Thanks

Best regards


Didier

推荐答案

最好的选择是使用性能分析器，例如 ^ ].该工具可以极大地帮助您解决程序中遇到的此类问题.

如果这不是一个选择，那么我要更改的第一件事就是SELECT语句.永远不要使用"SELECT *"，仅选择所需的列.

其他选项是使用秒表 [

The best option her is to use a performance profiler like ANTS Performance Profiler[^]. This tool can help you very much with such a problem the program have.

If this is not an option, the first thing I would change is the SELECT statement. Never ever use "SELECT * ", select only the column you need.

Other options is to use Stopwatch[^] in .NET and use this to find out here the problem is.

But do you real have to reboot the computer, is a stop and start of the program not sufficient?

我没有发现任何特别有问题的地方. (注意:我的VB知识几乎为零，但与C#足够接近，可以理解.)

您确定问题出在SQL部分，而不是程序其余部分中对数据的处理吗?

检查任务管理器...查看内存使用率是否在不断上升，表明完成某些操作后没有释放内存.

I don''t see anything in particular wrong with any of that. (NB: my VB knowledge is near nil, but it''s close enough to C# here to be understandable.)

Are you sure the problem is in the SQL part and not what you''re doing with the data afterward, in the rest of the program?

Check the task manager...see if the memory usage is going up and up and up, indicating something not releasing the memory when it''s done with it.

0)我会将我的sql放入存储过程并调用这些过程，而不是像这样在我的代码中执行sql.

1)我会使用较短的变量名.代码随着您使用的名称长度而丢失.

2)我会将读取器转储到DataTable中.

这就是我编写相同代码的方式(假设我将sql放入代码本身而不是使用存储过程):

0) I would put my sql into store procedures and call those instead of doing the sql in my code like that.

1) I would use shorter variable names. The code is getting lost with the name lengths you''re using.

2) I''d dump the reader into a DataTable.

This is how I''d write the same code (assuming I was putting the sql into the code itself instead of using a stored procedure):

Dim conn   As SqlConnection = Nothing
Dim cmd    As SqlCommand    = Nothing
Dim reader As SqlDataReader = Nothing
Dim data   As New DataTable()

Try
    Dim query As String = "select * from instellingen where hotel ='" + hotel + "'"
    conn1  = New SqlConnection(connectionstring)
    conn1.Open()
    cmd1   = New SqlCommand(query, conn) 
    reader = cmd1.ExecuteReader()
    If (reader IsNot Nothing) Then
        data.Clear()
        data.Load(reader)
        ' you can actually use the column names here if you want to (it's much more maintainable)
        hotelnaam         = data(2)
        startgroep        = data(20)
        ticketprinterport = data(12)
        klantdisplay      = data(21)
    End If

Catch ex As Exception
    ' show appropriate error message

Finally
    conn1.Close()
End Try

我不知道它是否会更快，因为我不知道您调用此代码的频率，但是它更容易阅读(IMHO).

I don''t know if it''s going to be any faster because I don''t know the frequency with which you call this code, but it''s MUCH easier to read (IMHO).

这篇关于程序在一段时间后变慢的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！