Program slows down after a period of time

[Smee]

Hi all,

A user has been testing my software on a scanner. He reports all is well until a lot of sales have been made and then instead of a delay of about .5 second the delay is 5 or 6 seconds. Could someone have a look at my code and see if there is anything in it that would cause this type of delay

thanks

Sub WriteSale
Dim ProdNo,Qty,Timex,ProdPrice

'Get the Next Trx Number
WaitCursor(True)
SQLConn.BeginTransaction
command.CommandText="Drop Table If Exists TemporaryTable"
Command.ExecuteNonQuery
StblOrders.RemoveCol("Product")
StblOrders.RemoveCol("Price")
SQLConn.CreateSQLTable("StblOrders","TemporaryTable")
StblOrders.AddCol(cString, "Product",195)
StblOrders.AddCol(cNumber, "Price",15)
Command.CommandText = "INSERT INTO Sales SELECT * from TemporaryTable"
Command.ExecuteNonQuery

If trxTypeCash.Checked=True Then TrxType= 0
If trxTypeCard.Checked=True Then TrxType= 1
If trxTypeWaste.Checked=True Then TrxType= 2
If trxTypeStaff.Checked=True Then TrxType= 3
If trxTypeEFT.Checked=True Then TrxType= 4

command.CommandText="UPDATE Sales SET TrxType=" & TrxType & " WHERE TrxType=5"
Command.ExecuteNonQuery

Reader.New1
Command.CommandText = "SELECT DISTINCT TrxNo FROM Sales ORDER BY TrxNo DESC"
Reader.Value = Command.ExecuteReader
Reader.ReadNextRow
TrxNo=Reader.GetValue(0)
TrxNo=TrxNo+1
Reader.Close

If TrxType= 1 Then
'Credit has been approved
Command.CommandText="UPDATE Sales SET CCNo='" & CustCard & "' WHERE TrxType=1 AND CCNo=0"
Command.ExecuteNonQuery
Command.CommandText="UPDATE CreditHolders SET CardValue=" & CreditValue-Total & " WHERE CustCard='" & CustCard & "'"
Command.ExecuteNonQuery
End If

SQLConn.EndTransaction

InitVars
End Sub


Sub InitVars
sTblOrders.Clear
ItemNo=0
SubTot=0
Total=0
lblSub.Text=0
lblTotal.Text=0
SlblDescription.Text=""
StxtProdCode.Focus
WaitCursor(False)
End Sub



Joe

 

[Erel]

First thing you can try it to remove the SQLConn.BeginTransaction / EndTransaction.
You are not doing many updates so they are not really important here, and they may cause problems in some cases.