Program loses response during large DB queries VB.NET

[danielgee]

Hi,
When I execute queries which have a lot of data returned in the recordset my VB app seems to hang (IE: This program is not responding)

I'm using ODBC to connect to the database and am using Postgres DB

Public db_name As String
Public db_username As String
Public db_userpassword As String
Public db_server As String

Public connStr As String
Public sqlCommand As OdbcCommand
Public sqlConn As OdbcConnection
Public Rs As OdbcDataReader

Public Sub DoQuery(ByVal tmpSQL as String)
    connStr = "Driver={PostgreSQL ANSI};SERVER=" & db_server & ";DATABASE=" & db_name & ";UID=" & db_username & ";PWD=" & db_userpassword
    
              sqlCommand = New OdbcCommand
              sqlConn = New OdbcConnection(connStr)
              If sqlConn.State = ConnectionState.Closed Then
                  sqlConn.Open()
              End If

              sqlCommand.CommandType = CommandType.Text
              sqlCommand.Connection = sqlConn
              sqlCommand.CommandText = tmpSQL

              Rs = sqlCommand.ExecuteReader

End Sub

I'm not sure that there is a way to sort this out, I hope there is?

Thanks all!

[Oxiegen]

I would try to limit the amount of data for each query.
Using the reader in order to populate some kind of repository do take a long time.
The way the reader works is that it only reads one line of data at a time.
Ie:

While Rs.Read
If Not IsDBNull(Rs.Item("some_column_name")) Then repository = Rs.Item("some_column_name")
End While

However, if you limit the amount of data to read with a WHERE clause this will go faster.

Or, you could use a dataadapter/dataset and basically get a mirror copy of the data.
Then you can search and select from there.

Public db_name As String
Public db_username As String
Public db_userpassword As String
Public db_server As String

Public connStr As String
Public sqlCommand As OdbcCommand
Public sqlConn As OdbcConnection
Public Rs As OdbcDataReader  '''Remove this
Public Da as OdbcDataAdapter '''Replace with this
Public sqlComBuilder as OdbcCommandBuilder '''Add this
Public Ds As DataSet '''Add this too

Public Sub DoQuery(ByVal tmpSQL as String)
    connStr = "Driver={PostgreSQL ANSI};SERVER=" & db_server & ";DATABASE=" & db_name & ";UID=" & db_username & ";PWD=" & db_userpassword

              sqlConn = New OdbcConnection(connStr)
              Da = New OdbcDataAdapter(tmpSQL, sqlConn)
              sqlComBuilder = New OdbcCommandBuilder(Da)
              Ds = New DataSet()

              sqlConn.Open()
              Da.Fill(Ds,"name_of_table")
              sqlConn.Close()
End Sub

The CommandBuilder is used to automatically create INSERT/DELETE/UPDATE queries when you need to update, insert or delete information in the database. Good thing to use if you don't want to create long and complicated queries manually.