-
Feb 5th, 2021, 03:16 AM
#1
Thread Starter
Lively Member
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.
-
Feb 5th, 2021, 04:09 AM
#2
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.
-
Feb 5th, 2021, 04:12 AM
#3
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.
-
Feb 5th, 2021, 06:34 AM
#4
Thread Starter
Lively Member
Re: Continuous Loop Work Around
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.
-
Feb 5th, 2021, 07:34 AM
#5
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.
-
Feb 5th, 2021, 10:55 PM
#6
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) ?
-
Feb 7th, 2021, 07:34 AM
#7
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
-
Feb 7th, 2021, 07:37 AM
#8
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;
-
Feb 7th, 2021, 07:39 AM
#9
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
-
Feb 7th, 2021, 08:48 AM
#10
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|