Click here to Skip to main content
15,867,308 members
Please Sign up or sign in to vote.
4.00/5 (2 votes)
See more:
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 :

VB
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 :

VB
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()





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
Posted
Updated 13-Jun-11 10:50am
v2
Comments
Dalek Dave 13-Jun-11 16:51pm    
Edited for Grammar and Readability.

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?
 
Share this answer
 
Comments
Didier Cauberghe 13-Jun-11 13:17pm    
Hello,

Thanks for responding to my question

I do select * because i need almost all the columns.

Maybe youre right and is stopping and startign the application also ok to solve the slownes.

But i am searching where the problem is.

Maybe it is the fiels server where the SQL is on that is giving the problem. When i restart the PC where the program is running the problem is solved.

Thanks.

br

Didier
Kim Togo 14-Jun-11 2:53am    
As other has written, I do not think the problem is with the SQL call.

Try a Profiler, there are free Profiler for C# that you can use. My experience with performance profiler, is that the problem is some here else where you think the problem is.
I still think the best option is to use a profiler.
Dalek Dave 13-Jun-11 16:51pm    
Good Call.
Kim Togo 14-Jun-11 2:43am    
Thanks
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.
 
Share this answer
 
Comments
Didier Cauberghe 13-Jun-11 13:56pm    
Thanks for the fast answer.

I was thinking on the SQL data , but i am going to check the rest of the programm.

Since you have told that you think that the SQL seems to be correct!

Best regards

Didier
GenJerDan 13-Jun-11 15:00pm    
:^) Well, it's the same basic pattern I use in all of mine, and I haven't had any noticeable problems with it.
Dalek Dave 13-Jun-11 16:52pm    
Good answer.
TRK3 13-Jun-11 17:07pm    
If it slows down after a while, then it's because you are using something up. If it's not memory, it might be some resource (system handles, threads, processes, or nonpaged kernel memory -- something used by a remote SQL access but not properly released). You should be able to see it in task manager or resource monitor.
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):

VB
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


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).
 
Share this answer
 
Comments
Didier Cauberghe 13-Jun-11 15:36pm    
Thanks for answering, i wil try this instead.

best regards

Didier
Dalek Dave 13-Jun-11 16:52pm    
Excellent Answer, John.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900