quote:

---

Originally posted by sunitabeck

There are a number of things you can look at and may need to consider:

1. Do the tables have proper indexes, so the querying will be more efficient.

I am not proactively doing anything to index the tables. Not sure how.

2. Can you pre-compute the data required for graphs and other processes so the queries can be more efficient?

There is no computation in these queries. The client is simply requesting values from the table.

3. Can you make the queries more efficient? Presumably they have no cursors or while loops or anything of that nature that can kill performance

The data is returned this way: (when plotting, similar to when exporting to a txt file, etc)

While (sqlRdr.Read)
    For Count = 0 To numSelectedChannels - 1
        Values(Count) = sqlRdr.GetValue(Count)
        ScatterPlots(Count).PlotXYAppend(xIndex, Values(Count))
        xIndex += 1
    Next
End While

4. Are the databases maintained properly? Indexes and Statistics?

Again, I'm no DBA so I'm not sure.

5. Can you use staging tables so the insertions go into staging tables, which then are periodically inserted into your main tables. In most cases, a single insertion of 100 rows into a table is more efficient than 100 insertions of one row.

I think this is an excellent idea. I could insert data into a local (within the VB.NET client program) table and then insert the temporary table to the SQL table, dump the data, and start buffering again. Is this what you mean?

6. Can you partition the tables, for example based on dates so that the queries that don't need the most upto date data will not interfere with insertions.

Most queries will need more recent data. Not sure how to partition though. This is the danger of some Rambo coder getting access to a SQL database without the oversight of a good DBA. My DBA is in training..

7. Can you separate the data sort of like OLTP/OLAP - perhaps even as simple as dumping the data required for analysis into a temp table and doing analysis against that.

The client program will do all of the analysis.

---

quote:

---

Originally posted by sunitabeck

1. You will need an index, but I don't know what columns it should be on. Can you post the query that you are using with the command object - the proper index would depend on what the query is.

2. DataReader is probably the most efficient, so what you have seems fine.

3. You should to update statistics on a regular basis and rebuild or reorganize indexes as required. There is some guidance on how to do this at these links - there are examples at the end of each page:
http://technet.microsoft.com/en-us/library/ms187348.aspx
http://technet.microsoft.com/en-us/library/ms189858.aspx

Whatever you do, please test them in a development environment to make sure that you are not doing any harm.

---

CREATE NONCLUSTERED INDEX IDX_B142C14_RunNUmber 
ON dbo.B142C14 (RunNumber ASC);

Dim TempTable as Table

Insert INTO TempTable blah  ' 60 times

If Count = 59 Then
   INSERT INTO B142C12 (Foo, Bar, Fizz, Buzz)
   SELECT Foo, Bar, Fizz, Buzz
   FROM TempTable

   Count = 0
   Table.Clear ' Will look for the best method here
End If

            If SQLConn.State <> Data.ConnectionState.Open Then
                UseSQL = False
                PostMessageToStatusTab("Lost connection to SQL.  Attempting to reconnect.")

                Dim AttemptCount As Byte = 1

                Do
                    If ConnectToSQL() Then
                        SQLRowCount = GetSQLRowCount()
                        BuildSQLInsertString()
                        PostMessageToStatusTab("Successfully reconnected to SQL after " & AttemptCount & " attempts.")
                        UseSQL = True
                        Exit Sub
                    Else
                        System.Threading.Thread.Sleep(5000)
                    End If
                Loop Until AttemptCount = 50
                Exit Sub
            End If

            ErrorLocation = "Executing Non Query"
            SQLCmd.ExecuteNonQuery()
        Catch ex As Exception
            SQLErrorCount += 1
            swError.WriteLine(TimeString & vbTab & "FlightRecorder" & vbNewLine & ex.ToString & vbNewLine & vbNewLine)
            frmD.txtMessage.Text = "Error writing SQL data!" & vbNewLine & _
                        ErrorLocation & vbTab & _
                        ex.Message & vbNewLine & frmD.txtMessage.Text

            If SQLErrorCount > 5 Then
                Try
                    TextMessage("Error writing SQL data!")
                    SaveError = True
                Catch ex1 As Exception
                End Try
                UseSQL = False
            End If
        End Try

SQLConnString = "Server=xx\xx;Database=yy;Integrated Security=True;Connection Timeout=30;"