Latency in .net app using DB in .net

Please post the code which you use to get the data from the Database.

Not much, but here it is:

    Public Function Connect() As Integer
        'On Error GoTo ErrH

        dConn = New OleDbConnection(sConStr)

        dConn.Open()

        Connect = -1
        Exit Function
ErrH:
        Beep()
        Connect = 0
    End Function

Select allOpen in new window

That looks like code you use to get a connection. I am looking for the code you use to bring down the data from the database.

    Public Function FileExist02(ByVal HostName As String, ByVal Direction As String, ByVal Filename As String) As Integer

        Dim sSqlCmd As String = "Select Count(Name_) From tblOnPlanFiles " & _
            " Where Name_ = '" & Filename & "' "

        Dim cmd As New OleDbCommand(sSqlCmd, dConn)
        Dim iRet As Integer = CInt(cmd.ExecuteScalar)

        FileExist02 = iRet

    End Function

Select allOpen in new window

I suspect that this is not the function which loads the table because it only returns and integer value of the number of records/count.  Bear in mind that the execution of the SQL command to the server is a blocking command which means that it does not continue execution in your program until he had received the results of that query.  Please fine and post the code that you are having the latency issues with.

What type of database are you using?
What is the version number of the database?

I am using the DB tool that comes with Visual Studio.  And this is the way that I use the table.

I simply need to compare two lists and then retrieve the records that marked as done.

there is not any display of the table to grid or form. This query comes back w/no records when I know there are records on the table that meet the criteria ?

To your statement, "I am using the DB tool that comes with Visual Studio.  And this is the way that I use the table.", well there are many DB Tools that come with Visual Studio for instance Entity Framework, Linq to SQL, Straight ADO.Net, DataSet DbTools and some of these tools can connect to different implementation of a database such as MS SQL Server, localDb, MySql. So which ones?

To your statement, "there is not any display of the table to grid or form. This query comes back w/no records when I know there are records on the table that meet the criteria ?", well if this is the case then the issue is in the database / or query. In the database does the column Name_ have an index on it?

Fernando, its pretty busy here, sorry for not following up.

I will get you some examples / screenshots this week.

Any updates?

Sorry to be away from this question for so long.

You ask about the internal DB that I added to my V.S. application, I just simple clicked add and selected a new internal DB.
see the attached PICs. This has not been an issue lately because the application has been behaving correctly.
I think that it seems we only have issues when the virtual server farm that the system runs on get a heavy load, but my expectation is that the DB should still work correctly ?

The view shows a X over my DB, but that only started doing that after I moved the DB from its default location.
I don't think that is part of the latency issue.
You can do this and the app still works. It has been for the last few months.

Thanks
CensusFileDB02.PNG
CensusFileDB01.PNG

To your question, "I think that it seems we only have issues when the virtual server farm that the system runs on get a heavy load, but my expectation is that the DB should still work correctly ?", Well that all depends on how long it is taking to respond to a request because if it takes to long you will get a Time Out exception and will fail.

 By the way you are using SQL Server Compact v3.5.

You state in the original question, "The app worked great for the first few months running and adding 6-10 records per day.", but you still have not posted the code that adds the 6-10 records per day, can you please post.

Ok, here is the code.  I don't have issues with the inserts, its the selects that seem to Timeout.  Why would I receive that behavior ?

    Public Function AddFile(ByVal HostName As String, ByVal Direction As String, ByVal FileName As String, _
        ByVal Size As Integer, ByVal ModDate As Date, ByVal MoveDate As Date, ByVal RunNbr As Integer) As Integer

        Dim sSqlCmd As String = _
            "Insert Into tblOnPlanFiles " & _
            "(HostName, Direction,  Name_, Size_, ModDate, MoveDate, RunNbr) " & _
            "Values('" & HostName & "','" & Direction & "','" & FileName & "', " & Size.ToString & ", '" & _
            ModDate.ToString & "', '" & MoveDate.ToString & "', " & RunNbr.ToString("0") & ")"

        Dim cmd As New SqlCeCommand(sSqlCmd, dConn)

        'Dim dReader As OleDbDataReader = cmd.ExecuteReader
        Dim iRet As Integer = cmd.ExecuteNonQuery()
        AddFile = iRet

    End Function

    Public Function FileExist(ByVal HostName As String, ByVal Direction As String, ByVal Filename As String) As Integer

        Dim sSqlCmd As String = "Select Count(Name_) From tblOnPlanFiles " & _
            " Where Name_ = '" & Filename & "' and MoveDate is null"

        Dim cmd As New SqlCeCommand(sSqlCmd, dConn)

        Dim iRet As Integer = CInt(cmd.ExecuteScalar)

        'MsgBox("( " & iRet.ToString & " ) Rows affected ...")
        'FileExist = dReader.Item(0).ToString
        'MsgBox(dReader.Item(1).ToString)
        FileExist = iRet

    End Function

Select allOpen in new window

Lets try and get a handle on this issue.

• You state, "I don't have issues with the inserts, its the selects that seem to Timeout".
• But if you were getting Timeouts it would be throwing and exception. Are you getting an exception?

• What do you mean by Latency issues?

• You state, "Why would I receive that behavior ?"
•      First we need to identify the true issue.

• You state, "The view shows a X over my DB, but that only started doing that after I moved the DB from its default ",
•      If you are seeing this inside Visual Studio Server Explorer window it means VS is not connected to the database. Expanding that node in the window should connect it to the server. But you stated that you change the DB location in that case you will need to edit the connection to the new location.

•You state, "I don't have issues with the inserts, its the selects that seem to Timeout".
•But if you were getting Timeouts it would be throwing and exception. Are you getting an exception?
No, don't see exceptions

•What do you mean by Latency issues?
I am assuming that the DB select statements are timing out and I don't see any results returned, "Which would be valid in some cases"

•You state, "Why would I receive that behavior ?"
•      First we need to identify the true issue.
If the DB was throwing errors because the table was un-available, I would expect to see exceptions on the Insert as well as the select statements.

•You state, "The view shows a X over my DB, but that only started doing that after I moved the DB from its default ",
•      If you are seeing this inside Visual Studio Server Explorer window it means VS is not connected to the database. Expanding that node in the window should connect it to the server. But you stated that you change the DB location in that case you will need to edit the connection to the new location.  
I will work on this...

To my question, "Are you getting an exception?", you stated, "No, don't see exceptions", can you state maybe in another way what is the issue?

To my question, "What do you mean by Latency issues?", you stated, "I am assuming that the DB select statements are timing out and I don't see any results returned, "Which would be valid in some cases"", as I stated before if the call to the database was timing out your program would be throwing a Timeout exception which you state is not happening.

To your statement, "If the DB was throwing errors because the table was un-available, I would expect to see exceptions on the Insert as well as the select statements.", What do you mean by, "table was un-available"?

This is my suspicion: if I have latency in the performance from the DB, that the table would not load and my Select would return results that are empty when in-fact I know under that set of circumstances, at least one record should have been returned.

You state, "This is my suspicion: if I have latency in the performance from the DB", According to your definition of latency it is a timeout problem but as I stated in previous posts if you have a timeout issue it would throw a runtime exception which you state you are not getting so it could not be a timeout issue.

To the next part of your statement, "that the table would not load and my Select would return results that are empty when in-fact I know under that set of circumstances, at least one record should have been returned.", I have asked that you post the code that would return the rows from the database but you have not, you posted code to open a connection to the db, insert records into the db and return the number of rows that has a certain Filename and MoveDate but nothing that returns a collection of rows back to your program.

Please re-state the issue that you are having because without this I can not continue with this question.

"... return the number of rows that has a certain Filename and MoveDate ..."  this is the code that I use an indicator if the record is on the table.  I don't have a 'select' that returns the record(s), no reason to.  All I want to know is if an event has already taken place, I record it in the D.B.  

I am trying to figure out what is happening.  This app runs un-attended and correctly, but there were a few times when I know that records were on the table that should have been counted and were not. When I call into work and ask the operators team to run the app more than once back to back, the 2nd time it runs successfully. My first conclusion was that the D.B. was taking time to load and returning incorrect results.  I wanted to know if anyone has seen this behavior before.

That's why I ask this question.

Fernando,  thank you for staying with this question.

Don't know if we will ever have the final answer, because the system is behaving correctly now.

I will implement the error traps that you codded out, and monitor and post back if I get any errors.

Thank You.