Results 1 to 10 of 10

Thread: Continuous Loop Work Around

  1. #1

    Thread Starter
    Lively Member
    Join Date
    Feb 2008
    Posts
    102

    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.

  2. #2
    PowerPoster Arnoutdv's Avatar
    Join Date
    Oct 2013
    Posts
    5,872

    Re: Continuous Loop Work Around

    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.

  3. #3
    PowerPoster
    Join Date
    Jun 2013
    Posts
    7,219

    Re: Continuous Loop Work Around

    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
    Last edited by Schmidt; Feb 5th, 2021 at 04:20 AM.

  4. #4

    Thread Starter
    Lively Member
    Join Date
    Feb 2008
    Posts
    102

    Re: Continuous Loop Work Around

    Quote Originally Posted by Arnoutdv View Post
    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.

  5. #5
    PowerPoster Arnoutdv's Avatar
    Join Date
    Oct 2013
    Posts
    5,872

    Re: Continuous Loop Work Around

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

  6. #6
    Frenzied Member
    Join Date
    Dec 2008
    Location
    Melbourne Australia
    Posts
    1,487

    Re: Continuous Loop Work Around

    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) ?

  7. #7
    PowerPoster
    Join Date
    Jan 2020
    Posts
    3,746

    Re: Continuous Loop Work Around

    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

  8. #8
    PowerPoster
    Join Date
    Jan 2020
    Posts
    3,746

    Re: Continuous Loop Work Around

    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;

  9. #9
    PowerPoster
    Join Date
    Jan 2020
    Posts
    3,746

    Re: Continuous Loop Work Around

    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

  10. #10
    PowerPoster ChrisE's Avatar
    Join Date
    Jun 2017
    Location
    Frankfurt
    Posts
    3,046

    Re: Continuous Loop Work Around

    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
    to hunt a species to extinction is not logical !
    since 2010 the number of Tigers are rising again in 2016 - 3900 were counted. with Baby Callas it's 3901, my wife and I had 2-3 months the privilege of raising a Baby Tiger.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width