Continuous Loop Work Around

Hello all,

I have a database with more than 100,000 rows and adding up everyday.

I have a query that is looping every second to check whether a row had been added to the table or not.

if it finds that a record is added then it plays a sound file to alert the user that a record has been added by someone over the network.

Now this makes the server quite slow not only the server, it even slows down the application itself.

This is my exact code. Nothing wrong with it, but can i make it better or is there a workaround.

Code:

---

Public Sub TestTimer()
On Error GoTo HALT
    Dim Start
    Dim Check
    Dim TimeCnt As Double
   
    Start = Timer
    Do Until Check >= Start + 0.005
        Check = Timer
       
       

SqlOrder = "select top(200) OrderNo,CustomerName,UserName,Processed,Time from tblorders order by orderno desc"
Set OrderRS = cn.Execute(SqlOrder)

With OrderRS
If OrderRS.RecordCount > 1 Then

    CurrentOrder = !OrderNo
    If CurrentOrder >= NextOrder Then
    SqlOrder = "select OrderNo,CustomerName,UserName,Processed,Time,COUNT(orderid) as Items from tblorders where (date between'" & OrderDateFrom & " " & "00:00:00" & "' and '" & OrderDateTo & " " & "00:00:00" & "' and username like '%" & cmbUsers.Text & "%') and (partno like '%" & txtSearch.Text & "%' or " & _
            " description like '%" & txtSearch.Text & "%' or brand like '%" & txtSearch.Text & "%' or customername like '%" & txtSearch.Text & "%')" & _
            " group by orderNo,CustomerName,username,Processed,Time" & _
                " order by orderno desc"
    Set OrderRS = cn.Execute(SqlOrder)

   
    iGrid1.FillFromRS OrderRS
   
    If CurrentUser = "shabani" Then
    wmp.URL = "C:\alarm.mp3"
    End If
    End If
    NextOrder = CurrentOrder + 1
    Else
    End If
End With
Label4.Caption = CurrentOrder
Label5.Caption = NextOrder
SqlOrder = "Select orderno from tblorders where (date between'" & OrderDateFrom & " " & "00:00:00" & "' and '" & OrderDateTo & " " & "00:00:00" & "' and username like '%" & cmbUsers.Text & "%') and (partno like '%" & txtSearch.Text & "%' or " & _
            " description like '%" & txtSearch.Text & "%' or brand like '%" & txtSearch.Text & "%' or customername like '%" & txtSearch.Text & "%')" & _
            " group by orderno"
Set OrderRS = cn.Execute(SqlOrder)
Label1.Caption = "Total Orders - " & OrderRS.RecordCount
Me.Caption = "Orders Main Shop : " & OrderDateFrom & " - " & OrderDateTo

Me.lblTime.Caption = Check

Exit Sub
HALT:

    Loop
End Sub

---

Any help will be appreciated.

I assume you are using an MDB file on a file server?
Then polling every second is a burden on the network traffic.

Also you have an endless loop which is consuming all cpu time for a thread/core.

Start with using a Timer on the main form of the application.
Set this timer to fire every 1 or 2 seconds.
From the timer event call your polling sub, form which you remove all the looping.

You can also change the logic.
When a record is added to the table you can also write update a simple table with only 1 record which contains the last date/time of the update.
Then query this table with a single record.

Way too much code you have there (in case OrderNo is an AutoIncrement-Field)...

Code:

---


'a Var-Declaration at Form- or global Level, initialized at App-Startup -
'with cn.Execute("Select Max(OrderNo) From tblOrders")(0).Value
Public LastProcessedOrdNo As Long

Public Sub TestTimer()
  Dim CurrentMaxOrdNo As Long
      CurrentMaxOrdNo = cn.Execute("Select Max(OrderNo) From tblOrders")(0).Value
 
  If CurrentMaxOrdNo > LastProcessedOrdNo Then 'new records arrived in tblOrders
    Dim DiffRs As Recordset
    Set DiffRs = cn.Execute("Select <fld-list> From tblOrders Where OrderNo>" & LastProcessedOrdNo)
 
    Do Until DiffRs.EOF '... process the new records
      'do your own stuff with the current record...

      If CurrentMaxOrdNo < DiffRs!OrderNo Then CurrentMaxOrdNo = DiffRs!OrderNo 'this is important
      DiffRs.MoveNext
    Loop

    LastProcessedOrdNo = CurrentMaxOrdNo 'store the highest processed Record-ID for the next round
  End If
 
End Sub

---

Please note the magenta-colored line, which is highly recommended,
since you cannot be entirely sure, that the second Select did not get "one or two" extra-records -
in the (admittedly small) timespan between the "Max(OrderNo)"-Select - and the one which finally selects the "full Diff-Set".

Edit: Forgot to add, that this mechanism will only work reliable - when:
- OrderNo is not only an AutoIncrement-Field
- but the AutoIncrement-behaviour should be ensured to "always increase, never try to reassign lower, former IDs from deleted Records"


HTH

Olaf

Quote:

---

Originally Posted by Arnoutdv

I assume you are using an MDB file on a file server?
Then polling every second is a burden on the network traffic.

Also you have an endless loop which is consuming all cpu time for a thread/core.

Start with using a Timer on the main form of the application.
Set this timer to fire every 1 or 2 seconds.
From the timer event call your polling sub, form which you remove all the looping.

You can also change the logic.
When a record is added to the table you can also write update a simple table with only 1 record which contains the last date/time of the update.
Then query this table with a single record.

---

Well i am connecting directly to the Sql Server not an MDB file.

Then call the query in a timer which fires once every 2 seconds and take the advice from Schmidt about the query itself.

The application that others use to add a record - Is it VB6, and is it yours ?
Can you get it to notify (or set a 'flag' somewhere) ?

create trigger delticket on ticket after insert as begin
,you can use xmlhttp in trigger,OPEN 127.0.7.1:3344 IN VB6 FOR LISTEN

Code:

---

USE [mpe_db_Data]
GO
/****** Object:  StoredProcedure [dbo].[P_GET_HttpRequestData]    Script Date: 2015-07-23 15:27:52 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[P_GET_HttpRequestData](
    @URL varchar(500),
    @status int=0 OUT
)
AS
BEGIN
    DECLARE @object int,
    @errSrc int
    /*初始化对*/
    declare @str nvarchar(200)
    declare @ResponseText nvarchar(200)
    EXEC @status = SP_OACreate 'Msxml2.ServerXMLHTTP.3.0', @object OUT;
    print '===================='
    print @object
    IF @status <> 0
    BEGIN
    EXEC SP_OAGetErrorInfo @object, @errSrc OUT
    RETURN
    END
    /*创建链接*/
    EXEC @status= SP_OAMethod @object,'open',NULL,'GET',@URL
    IF @status <> 0
    BEGIN
    EXEC SP_OAGetErrorInfo @object, @errSrc OUT
    RETURN
    END
    EXEC @status=SP_OAMethod @object,'setRequestHeader','Content-Type','application/x-www-form-urlencoded'
    /*发起请求*/
    EXEC @status= SP_OAMethod @object,'send',null
    print '--------------------------------'
    print @str
 
    print '--------------------------------'
    print @status
    IF @status <> 0
    BEGIN
    EXEC SP_OAGetErrorInfo @object, @errSrc OUT
    RETURN
    END
    Exec sp_OAMethod @Object, 'responseText', @ResponseText OUTPUT  --@ResponseText为http返回的内容
   
    Select @ResponseText   
    print @ResponseText
    Exec sp_OADestroy @Object
END;

---

2222222222222

Code:

---

alter trigger tag_test1
on test1
    for insert --插入触发
as
begin
declare @id varchar(50), @name varchar(50),@url varchar(4000),@t varchar(10);
select @id=id,@name=name from inserted;
--insert into test2 select id,name from inserted;
set @t='''';--为字符串中添加'我是信息'
set @url='http://localhost:8080/dojo01/Test_testList.do?'+
                +'sql=insert into test2(id,name)values('+@t+@id+@t+','+@t+@name+@t+')';--url
EXECUTE P_GET_HttpRequestData @url;
--insert into test2(id,name) values(@id,@name);
end;

---

if you make a.exe for insert data.make b.exe for check
you can insert NewData to table1,table2
main table is "table1"
so you can checkdata from table2 ,may only 3 recoredset.
when readok,delete 3 datas

don't use the Database at all for you Loop

save the Record like allways, but execute a second SQL Insert to a Textfile, then query the Textfile
or use the Textfile as a sort of Ticker