0

We have a need to grab the contents in one MySql Database and then check to see if each line is in a separate MySQL database table (Different schema). Then display results to include number of matched records.

I am building the 1st list and then using a for loop to search the 2nd table for records with the same email address. This is slow - 1 second per loop, is there a better or faster way to do this?

Private Sub ConnectButton_Click(sender As Object, e As EventArgs) Handles ConnectButton.Click

    Dim SQLStr As String = "select t.id,t.title,t.fname,t.lname,t.transaction_ref,t.status,email from membership as t where (t.status = 2) and (t2.cost > 0)"

    SQLDt.Reset()

    Dim Dta As New MySqlDataAdapter(SQLStr, SQLConn)

    Dta.Fill(SQLDt)
    DataGridView1.DataSource = SQLDt

    SQLDt.Columns.Add("Found", GetType(Integer))

    Dim row As DataRow
    Dim column As DataColumn

    TextBox2.Text = SQLDt.Rows.Count
    TextBox2.Refresh()

    For Each row In SQLDt.Rows

        Dim emailstr As String = row.Item("email")
        Dim rowint As Integer = SQLDt.Rows.IndexOf(row)

        Dim InternalCRMSQLStr As String = "select * from membership where (email = '" + emailstr + "') AND membershipid IS NOT NULL "

        InternalCRMSQLDt = New DataTable

        Dim InternalCRMDta As New MySqlDataAdapter(InternalCRMSQLStr, InternalCRMSQLConn)
        InternalCRMDta.Fill(InternalCRMSQLDt)

        If InternalCRMSQLDt.Rows.Count > 0 Then
            SQLDt.Rows(rowint)("Found") = InternalCRMSQLDt.Rows.Count
        Else
            SQLDt.Rows(rowint)("Found") = 0
        End If

        TextBox1.Text = rowint
        TextBox1.Refresh()

    Next
End Sub
Mark
  • 1
  • 2
  • I would make a single query without a loop, but your first query makes no sense at all. How can you get an email without having a FROM table in the first place – nbk May 29 '21 at 10:16
  • 1
    MySQL allows you to JOIN tables in different databases just by using the database identifier as apart of the table name. This would allow you to perform the entire process in a single query. I'm not up on VB enough to give you sample code. Related: https://stackoverflow.com/a/5698396/14853083 – Tangentially Perpendicular May 29 '21 at 10:32
  • Sorry nbk, I cut the query down to put in this and took the from out in error. I have updated! – Mark May 29 '21 at 13:01
  • MySqlDataAdapter only allows for one connection string so not sure how I would use this when select is over two database? MySqlDataAdapter(SQLStr, SQLConn) – Mark May 29 '21 at 16:10
  • What is `t2`?.. – Rick James May 29 '21 at 16:33
  • What others have said, probably quite achievable via a single query. Couple other things though, keep your queries as small as possible. For example only return the email in first query, then in second, just returned the count. You may find it useful doing some reading on ExecuteReader() and ExcuteScaler() among others which allows query dB more directly and with over head of data adapters etc – Hursey May 29 '21 at 19:37

0 Answers0