1

As far as I know, there are two ways for the SQL connection handling when executing multiple commands in parallel using the Threading.Tasks.Parallel.Invoke approach. The first method is to define a common SQL connection which is shared between all threads. The second approach is to open a new connection for each thread.

Here is an example:

Threading.Tasks.Parallel.Invoke(
Sub()
    Using con As New IfxConnection("ConStr")
        con.Open()

        Using cmd As New IfxCommand("SELECT * FROM some_table", con)
            Using reader = cmd.ExecuteReader()
                While reader.Read()
                    DoSomeStuff()
                End While
            End Using
        End Using
    End Using
End Sub,
Sub()
    Using con As New IfxConnection("ConStr")
        con.Open()

        Using cmd As New IfxCommand("SELECT * FROM another_table", con)
            Using reader = cmd.ExecuteReader()
                While reader.Read()
                    DoMoreStuff()
                End While
            End Using
        End Using
    End Using
End Sub)

The other way would be to use one shared connection:

Using con As New IfxConnection("ConStr")
    con.Open()

    Threading.Tasks.Parallel.Invoke(
    Sub()
        Using cmd As New IfxCommand("SELECT * FROM some_table", con)
            Using reader = cmd.ExecuteReader()
                While reader.Read()
                    DoSomeStuff()
                End While
            End Using
        End Using
    End Sub,
    Sub()
        Using cmd As New IfxCommand("SELECT * FROM another_table", con)
            Using reader = cmd.ExecuteReader()
                While reader.Read()
                    DoMoreStuff()
                End While
            End Using
        End Using
    End Sub)
End Using

Now, which of those two ways should be preferred? Which one would result in a better performance?

As far as I observed, when using a shared connection, like in my second example, the SQL commands are getting executed sequentially. Is that true?

André R.
  • 427
  • 7
  • 17
  • To the extent I understand the question, I think you'd do better with two separate connections to the Informix database, so the first solution should be the better. There are complicating factors in the discussion — shared memory connections are one; there might be multiplexed connections you can use, if you do the setup in the server and modify the code to use them, but I'm not sure of the details in C, let alone VB. – Jonathan Leffler Mar 30 '17 at 04:02
  • Thank you! From my observation, it runs faster when using multiple connections. As far as I know, the .net runtime bundles them in background anyways. But I'm not sure, if this really happens – especially when running many threads in parallel. Also I don't have an understanding of how much of a performance drawback it brings, to freshly configure the connection every time. And especially how strongly this .net caching/connection pooling overrides that drawback. – André R. Mar 30 '17 at 07:49

0 Answers0