Nested Database connection extremely slow

I’m accessing a table in a database in which one of the columns is a ‘UserID’. This table is the only table that contains the information that I need for the process and I do not have write permission to the DB.

There is another table in the DB that has both the UserID and the Full Name of the user, I want to be able to: within the initial first connection, make a connection to this table and replace the UserID with the FullName of the user.

I can do this successfully and it works however it is SLOW.

Here is my code:

# Connect to the DB to get the call outcomes for the past 24hrs
# Loads from the MySQL Connector .NET
[void][System.Reflection.Assembly]::LoadFrom("C:\Program Files (x86)\MySQL\MySQL Connector Net 8.0.28\Assemblies\net6.0\MySql.Data.dll")
# Create the mysql connection object
$myConnection = New-Object MySql.Data.MySqlClient.MySqlConnection
# Create the mysql connection string
$myConnection.ConnectionString = "DataBaseConnectionString"
# Open the connection
$myConnection.Open();

# Creating the command string
$myCommand = New-Object MySqL.Data.MySqlClient.MySqlCommand
$myCommand.Connection = $myConnection
$myCommand.CommandText = "
SELECT
    start_epoch,
    end_epoch,
    status,
    user,
FROM
    table
WHERE
    string IN ('Filter')
AND user NOT IN ('Filter')
AND status NOT IN ('Filter')
AND end_epoch IS NOT NULL
ORDER BY
    start_epoch DESC"

# Create the reader
$myReader = $myCommand.ExecuteReader()

# While the reader is reading from the db

while($myReader.Read()) {
    # Epoch time starts from 01-01-1970, so we add the amount of seconds from this time to work out the current epoch time/end
    $epochStart = (Get-Date -Date "01-01-1970") + ([System.TimeSpan]::FromSeconds(($myReader.GetString(0))))
    $epochEnd = (Get-Date -Date "01-01-1970") + ([System.TimeSpan]::FromSeconds(($myReader.GetString(1))))

    # agent ID require no processing
    $agentID = $myReader.GetString(3)

    # DB CONNECTION TO GET THE USER NAME FROM THE AGENT ID
    # Get the agent name from the agent ID 
    $myConnectionUser = New-Object MySql.Data.MySqlClient.MySqlConnection
    # Create the mysql connection string
    $myConnection.ConnectionString = "DataBaseConnectionString"
    # Open the connection
    $myConnectionUser.Open();

    # Creating the command string
    $myCommandUser = New-Object MySqL.Data.MySqlClient.MySqlCommand
    $myCommandUser.Connection = $myConnectionUser
    $myCommandUser.CommandText = "SELECT full_name FROM users WHERE userID = $userID AND active = 'Y' AND user_group IN ('Filters')"

    $agentReader = $myCommandUser.ExecuteReader()
    while($agentReader.Read()) {
        $agentFullName = $agentReader.GetString(0)
    }
    # The date 24 hours ago to ensure only records less than 24 hours old are admitted
    $24hours = (Get-Date).AddDays(-1)
   
    # Converting date to ISO 8601
    $iso8601EpochStart = Get-Date ($epochStart).ToUniversalTime() -UFormat '+%Y-%m-%dT%H:%M:%SZ'
    $iso8601EpochEnd = Get-Date ($epochEnd).ToUniversalTime() -UFormat '+%Y-%m-%dT%H:%M:%SZ'

    if($24hours -le $date) {
        if(($epochEnd - $epochStart).TotalSeconds -gt 60) {
            $outputString = "Output"
            $outputStringNoSales | Add-Content -Path "C:\path\to\folder\.txt"
            $i++
        }
    } else {
        break
    }

Is this a stupid way to do this? This feels way too slow as removing the inner DB connection and reader makes my code execute within milliseconds, but adding the inner loop takes it to over 10 minutes.

The filters ‘Active = ‘Y” and user_group was an attempt to lower the amount of overall users in the users table as it was around ~3000 before, and ~200 afterwards. But this did not make a marked improvement on the performance.

The lines

$agentReader = $myCommandUser.ExecuteReader()
while($agentReader.Read()) {
   $agentFullName = $agentReader.GetString(0)
}

Is where the program screams to a halt, is there a more efficient way to do this rather than having a nested DB connection?
Thanks for your help.

>Solution :

You can just use a join

SELECT
    origin.start_epoch,
    origin.end_epoch,
    origin.status,
    origin.user,
    users.fullname
FROM
    table origin
JOIN users on users.userID=origin.user AND users.active = 'Y' 
WHERE
    origin.string IN ('Filter')
    AND origin.user NOT IN ('Filter')
    AND origin.status NOT IN ('Filter')
    AND origin.end_epoch IS NOT NULL
ORDER BY origin.start_epoch DESC

In this way, you don´t need to query again the DB to get the users name