Re: MySQL Connector 6.9.6.0 thru 6.5.7.0 is slow.

[Timothy Bell]

IDE: Visual Studio 2013
VB.NET
Connector that is fast and responsive: 6.4.6.0
Connector that is slow: 6.9.6.0 thru 6.5.7

Hard to provide code samples because our form Loads have a lot of queries retreiving data.



Here is code for our Walkin Order Screen (On Load)

Private Sub WalkIn_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

'New Way to Load Data.
connect = New MySqlClient.MySqlConnection(oDatabaseConnection)
connect2 = New MySqlClient.MySqlConnection(oDatabaseConnection)
connect3 = New MySqlClient.MySqlConnection(oDatabaseConnection)
connect5 = New MySqlClient.MySqlConnection(oDatabaseConnection)

LineCounter = 1
l = 0
q = 1

'Setting Payment Click to No.
PaymentButtonClick = "N"


'This will populat the employees drop down list
Populate_Employees()



'This gets the current user
Dim index As Integer
index = ComboBox7.FindString(Current_LoggedIn_User())
ComboBox7.SelectedIndex = index




'FromBrowser
q = 0


'This will load inventory into the datagridview.
Load_Inventory() 'Added v1.1.29

'This will populate the order sources drop down list.
Populate_Sources()


'Added v2.0.11, Order Date
DateTimePicker4.Enabled = False

'Loading Discount, this needs to be set in the Master Config Screen
'**********************************************************************************
cmd.CommandText = "SELECT `ddiscount` FROM `custdefaults`;"
cmd.Connection = connect

Try
connect.Open()
returnValue = cmd.ExecuteScalar

Catch ex As Exception
If ex.Message = "The connection is already open." Then
connect.Close()
Else
MsgBox(ex.Message)
End If
End Try

connect.Close()
cmd.Dispose()

TextBox6.Text = returnValue & "%"

TextBox3.Text = 0.0
TextBox9.Text = 0.0
TextBox4.Text = 0.0
TextBox7.Text = 0.0
TextBox5.Text = 0.0


'Sales Tax Population
'*************************************************************************************************************

'This gets all the information regarding the system tray
Dim mySelectQuery As String = "SELECT * FROM `salestax`;"
Dim myConnection As New MySqlClient.MySqlConnection(oDatabaseConnection)
Dim myCommand As New MySqlClient.MySqlCommand(mySelectQuery, myConnection)
myConnection.Open()
Dim myReader As MySqlClient.MySqlDataReader
myReader = myCommand.ExecuteReader()


Dim oTaxNo, oCounty, oPercent, oEnabled, oDefault
Dim oHasRows
oHasRows = myReader.HasRows

'If there are no entries in the database then do nothing
If oHasRows = "False" Then

'Do Nothing.

Else

While myReader.Read()

oTaxNo = myReader.GetString(0)
oCounty = myReader.GetString(1)
oPercent = myReader.GetString(2)
oEnabled = myReader.GetString(3)
oDefault = myReader.GetString(4)

If oEnabled = "Y" Then

DataGridView5.Rows.Add(New Object() {ComboBox2.Items.Count, oCounty, oPercent, oTaxNo})
ComboBox2.Items.Add(oPercent & "%")

End If

If oDefault = "Y" Then
ComboBox2.Text = oPercent & "%"
End If

End While

End If



'MySQL Clean up
myConnection.Close()
myReader.Close()
myCommand.Dispose()


'End of Sales Tax Population
'*****************************************************************************************************************


'Tooltips for a description of each button.
ToolTip1.SetToolTip(Me.Button5, "Search for Item")
ToolTip1.SetToolTip(Me.Button10, "Clear Search")
ToolTip1.SetToolTip(Me.Button4, "Add Item on the Fly!")
ToolTip1.SetToolTip(Me.Button7, "Inventory Admin")
ToolTip1.SetToolTip(Me.Button8, "Take Payment")
ToolTip1.SetToolTip(Me.Button6, "Save Order")
ToolTip1.SetToolTip(Me.Button28, "Delete Order")
ToolTip1.SetToolTip(Me.Button30, "Print")
ToolTip1.SetToolTip(Me.Button31, "Print Preview")
ToolTip1.SetToolTip(Me.Button46, "Print Shopping Cart")


ToolTip1.SetToolTip(Me.Button29, "(F1) Acronym")
ToolTip1.SetToolTip(Me.Button33, "(F2) Proper Case, First Letter Capitalized.")
ToolTip1.SetToolTip(Me.Button34, "(F3) Upper Case, All Letters Capitalized.")
ToolTip1.SetToolTip(Me.Button11, "(F4) Spell Check")


ToolTip1.SetToolTip(Me.Button23, "Customer Favorite Messages")
ToolTip1.SetToolTip(Me.Button15, "Full / Partial Refund")

ToolTip1.SetToolTip(Me.Button9, "Convert to Walk In")
ToolTip1.SetToolTip(Me.Button1, "Convert to Delivery")
ToolTip1.SetToolTip(Me.Button2, "Convert to Pick Up")
ToolTip1.SetToolTip(Me.Button3, "Convert to Wire")

ToolTip1.IsBalloon = True


'Hiding Card Message Controls
RichTextBox1.Visible = False
Button11.Visible = False
Label13.Visible = False
Button23.Visible = False
Button29.Visible = False 'Acronym Button
Button33.Visible = False 'Proper Button
Button34.Visible = False 'Upper Button


'Sets the Default Picture holder.
DefaultItemPicture = "C:\POS\pictures\DefaultPicture.png"
DefaultItemPicture = DefaultItemPicture.Replace("\", "\\")

'Setting OrderType.
OrderTypePublic = "W" 'Walkin Order Type


'MsgBox(BrowserFlag)
'MsgBox(BrowserOrderID)


'This Handles if You are Being called from the order browser!
'*****************************************************************************************************************
If BrowserFlag = "Y" Then


Label5.Text = BrowserOrderID
OrderID = BrowserOrderID
CPAccountID = ""


'This gets the cashier
Order_User(BrowserOrderID)

FromBrowser = "FromBrowser" & q

'Loads Items, into a hidden DataGridView.
adapter.SelectCommand = New MySqlClient.MySqlCommand("Select `LineItemNo` , `ItemName`, `ItemDescription` , `Qty` , `Price` , `LineTotal` , `itemid`, `orderpadid`, `lidisc` From `orderpad` WHERE `orderid` ='" & BrowserOrderID & "' ORDER BY `LineItemNo`", connect)
adapter.Fill(DataSet, FromBrowser) 'Workers is just a made up name for the DataGrid!
MaxRows = DataSet.Tables(FromBrowser).Rows.Count()
DataGridView4.DataSource = DataSet

'---set the table in the dataset to display---
DataGridView4.DataMember = FromBrowser

q = q + 1


'Repopulating DataGridView1 in case we want to edit.
Dim t
t = 0
Dim LIDiscount = 0 & "%"
Do Until MaxRows = t



'DataGridView1.Rows.Add(New Object() {Image.FromFile("C:\TheFloralPOS\Pictures\delete16.png"), DataGridView4(0, t).Value, DataGridView4(1, t).Value, DataGridView4(2, t).Value, DataGridView4(3, t).Value, DataGridView4(8, t).Value & "%", DataGridView4(4, t).Value, DataGridView4(5, t).Value, DataGridView4(6, t).Value, DataGridView4(7, t).Value})


DataGridView1.Rows.Add(New Object() {My.Resources.delete16, DataGridView4(0, t).Value, DataGridView4(1, t).Value, DataGridView4(2, t).Value, DataGridView4(3, t).Value, DataGridView4(8, t).Value & "%", DataGridView4(4, t).Value, DataGridView4(5, t).Value, DataGridView4(6, t).Value, DataGridView4(7, t).Value})



t = t + 1

Loop


'MySQL Closing loose ends
adapter.Dispose()
connect.Close()



'Loads Order Totals, etc into a hidden DataGridView.
adapter.SelectCommand = New MySqlClient.MySqlCommand("Select `discpercent` , `disctotal`, `subtotal` , `taxpercent` , `taxtotal` , `nettotal` , `totaltax` , `cardmessage` , `ordernotes` , `datestamp` , `timestamp` From `ordertotals` WHERE `orderid` ='" & BrowserOrderID & "'", connect)
adapter.Fill(DataSet, "FromBrowser1") 'Workers is just a made up name for the DataGrid!
MaxRows = DataSet.Tables("FromBrowser1").Rows.Count()
DataGridView3.DataSource = DataSet

'---set the table in the dataset to display---
DataGridView3.DataMember = "FromBrowser1"



'MySQL Closing loose ends
adapter.Dispose()
connect.Close()



TextBox6.Text = DataGridView3(0, 0).Value & "%" 'Discount
TextBox9.Text = DataGridView3(1, 0).Value 'Discount Applied
TextBox3.Text = DataGridView3(2, 0).Value 'Subtotal
ComboBox2.Text = DataGridView3(3, 0).Value & "%" 'Sales Tax
TextBox7.Text = DataGridView3(4, 0).Value 'Sales Tax Total
TextBox4.Text = DataGridView3(5, 0).Value 'Net Total
TextBox5.Text = "$" & DataGridView3(6, 0).Value 'Total with Tax
RichTextBox1.Text = DataGridView3(7, 0).Value 'Card Message
'This checks to see if there is a card Message!
If DataGridView3(7, 0).Value = "" Then

CheckBox1.Checked = False

Else

CheckBox1.Checked = True

End If
RichTextBox2.Text = DataGridView3(8, 0).Value 'Order Notes

'RichTextBox2.Text = DataGridView3(9, 0).Value 'OrderDate
'RichTextBox2.Text = DataGridView3(10, 0).Value 'OrderTime

'Label43.Text = DataGridView3(9, 0).Value 'OrderDate
DateTimePicker4.Value = CDate(DataGridView3(9, 0).Value) 'Order Date


Dim dt As DateTime
dt = DateTime.ParseExact(DataGridView3(10, 0).Value.ToString, "HH:mm:ss", Nothing)
Label44.Text = dt.ToString("hh:mm:ss tt") 'OrderTime

'This sets up the order so that most of the components are readonly.
DataGridView1.Enabled = False
RichTextBox1.Enabled = False
RichTextBox2.Enabled = False
Button12.Visible = True
Button6.Visible = False
Button8.Visible = False
ComboBox2.Enabled = False
Button23.Visible = False
TextBox6.Enabled = False
TextBox9.Enabled = False
TextBox11.Enabled = False 'Barcoding


Button29.Visible = False 'Acronym Button
Button33.Visible = False 'Proper Button
Button34.Visible = False 'Upper Button
Button14.Visible = False

BrowserFlag = "N"



'Get Receipt Printed Totals.
'****************************************************************************************************************************************
Dim Current_Reciept_Quantity

'Get the current receipt print total.
cmd.CommandText = "SELECT `ReceiptQty` FROM `ordertotals` where `orderid` = '" & Label5.Text & "';"
cmd.Connection = connect

Try
connect.Open()
Current_Reciept_Quantity = cmd.ExecuteScalar

Catch ex As Exception
If ex.Message = "The connection is already open." Then
connect.Close()
Else
MsgBox(ex.Message)
End If
End Try

connect.Close()
cmd.Dispose()

'If the total is nothing, make it zero.
If Current_Reciept_Quantity = "" Then

Current_Reciept_Quantity = 0

End If

TextBox10.Text = Current_Reciept_Quantity

'End Receipt Printer Totals
'*****************************************************************************************************************************************


'Get WorkTicket Printed Totals.
'****************************************************************************************************************************************
Dim Current_WorkTicket_Quantity

'Get the current receipt print total.
cmd.CommandText = "SELECT `PrintedQty` FROM `ordertotals` where `orderid` = '" & Label5.Text & "';"
cmd.Connection = connect

Try
connect.Open()
Current_WorkTicket_Quantity = cmd.ExecuteScalar

Catch ex As Exception
If ex.Message = "The connection is already open." Then
connect.Close()
Else
MsgBox(ex.Message)
End If
End Try

connect.Close()
cmd.Dispose()

'If the total is nothing, make it zero.
Current_WorkTicket_Quantity = Current_WorkTicket_Quantity.replace(" ", "")
If Current_WorkTicket_Quantity = "" Then

Current_WorkTicket_Quantity = 0

End If

TextBox35.Text = Current_WorkTicket_Quantity

'End WorkTicket Printed Totals.
'*****************************************************************************************************************************************

'This will Load the Occasion if it is saved into the order
Load_Sources(Label5.Text)

'Checking Order Status
Order_Status_Check(BrowserOrderID)

'This is to make sure that the User Change is Allowed.
BrowserFlag = "N"

Else


'Generates Order ID
OrderID = Generate_Order_Number()



'This checks the status of the order.
Order_Status_Check(OrderID)




'MsgBox("Your Order ID is: " & OrderID)
Label5.Text = OrderID
BrowserOrderID = OrderID

'Set Preliminary Date / Time for order.

'Label43.Text = Format(Date.Today, "MM/dd/yyyy") 'Date
DateTimePicker4.Value = Date.Today
Label44.Text = Format(Now, "hh:mm:ss tt") 'Time


'Added v2.1.35
Order_History(OrderID, "Created", "WalkIn Order was Created")


End If


Timer1.Enabled = True
Timer1.Interval = 500

EmptyCart()


'Added v3.0.28
Count_Order_Transactions(Label5.Text)


Me.Text = "Walk In - Order: " & Label5.Text


End Sub