(RESOLVED) Performance question (recordsets and loops)

My application will lookup a database every 10 seconds to see if it has received a special command.

I am using a timer which, every 10 secs, will <<Set rstReceiveOrders = DB_Browsers.OpenRecordset("sqlstring")>> and check its content to take action. The "rstReceiveOrders" recordset is declared at the top of my form's code.

Question #1: Would it be faster to declare the recordset every time the timer is executed? That would mean declaring a recordset variable and setting it to something every 10 seconds. I thought that declaring it at the top of my form's code would make execution faster because I won't have to declare it every 10 seconds. (of course I am closing/emptying it each time the timer is done executing).

Question #2: Would it be faster, in the form_load (or somewhere else in the beginning of my application) to SET the recordset, and then only "REFRESH" the recordset on the timer and check its updated content?

Any tips greatly appreciated. Note that in that particular case, I am more looking for a less-CPU usage solution rather than a less-MEMORY usage solution. This application will run 30-40 times SIMULTANEOUSLY on one single CPU (72kb app).

thanks

you are partly on the right track. You declared your recordset in the right way since not only one control will use it.

Just some simple revisions on your code and youre up for the optimized speed.

First, you only need to open your recordset once and the best way to do it is to open it in the form load event. In this way, you only need to requery your recordset so that you can get an updated result.

VB Code:

Dim rs as New ADODB.Recordset
 
Private Sub Form_Load()
    'open your recordset here
End Sub
 
Private Sub Timer1_Timer()
    rs.Requery
End Sub

Hi d3gerald.

I've decided to use the method you suggested. I'm gonna be requerying the recordset every 10 seconds.

Quote:

---

You declared your recordset in the right way since not only one control will use it

---

...not quite. Only one procedure will be dealing with the recordset. Yes I will have 30-40 instances of that application running, but all of them will be doing the exact same thing, each of them will be using it's own recordset. Even tho the recordset is used only in one procedure (the timer), I guess it's still better to NOT declare and set it every 10 seconds. (I just hope I'm right)

thanks for your help.

If I understand you correctly, your app runs 30-40 concurrent instances...

You would want to design a n-tier app with the data layer creating a public instance of the recordset. Then all instances of your app will be using the same instance of the recordset in the data layer reducing the number of connections and requests.

Hrrrmmm... what you just said sounds interesting. I think you understood correctly...: my app (bla.exe) will be executed 30-40 times (approximatively because it's actually in rotation - some of them are closed, some re-openned and so on - the number may vary).

But if there was a way so that the recordset is declared and populated ONCE for all my already-running and newly-executed applications, it would be awesome. I didn't even think about such a method - I didn't know this could be possible. Would it? That would mean that all applications uses a recordset declared and populated in a n-tier app? (I already have a small app, actually dealing with the executions of my main app - it would suit just fine in there)

I am using 1 database for single use in every instances and a 2nd database on a 10 seconds loops (which we dealt in the current thread).

Thanks for letting us know that you have your answer. The easy way to do that is by pulling down the Thread Tools menu and clicking the Mark Thread Resolved button. Also if someone has been particularly helpful, or even particularly unhelpful, you have the ability to affect a their forum "reputation" by rating their post.

RobDog888, how would my applications read that "public instance of the recordset" since it has been declared in ANOTHER running application?

I guess I could manage something if I had a HWND for it, or something. Which I doubt can be the case here. Unless I use a control (data control - or can't remember its name) with a sql connection established.

So what would be the approach on using this outside-scope recordset?

Thanks

how about a text file or registry value instead?

Hmm. I don't see how a text file or registry value could help me here. Could you explain furthermore? It's actually a recordset I am talking about - a registry value couldn't handle such a data collection.

But still you made me think of something (unless that's exactly what you meant): instead of using a database, every application instance could be checking for its "MyAppHWND.txt" (0k = nothing to do, <>0k = read it an action must be taken).

Maybe is this less CPU-consuming than requerying a recordset on a 10 seconds loop.

Keep on sharing your thoughts! :)

RobDog888, still waiting for your 2 cents.

Why do you need an entire recordset to come back from the DATABASE.

What is changing in the DB. Who is changing it. What do you want this change to trigger.

And more importantly - what version of MS SQL are you using, and if not MS SQL - what is your backend DB?

Could be populating a grid or something as to try to show real time data changes. ;)

The database in question will have only one table, ranging approximatively from 0 to 50 records (that equals the number of application occurrences running).

Each running apps will have a record. My primary key would be the app's HWND. Each app will have to check one given field of the record to see if the <ACTION> field has been set to "CLOSE", "BUY", "WAIT" or such orders my app might need to react to.

These "orders" are set by another VB program I've made. So in clear I have:
- My main VB app, "monitoring' all opened apps (low CPU usage, low DATABASE usage)
- 40-50 apps in rotation (some opening and some closing)

Each of my apps are creating their own record in the DB, *then* my main VB app is issuing orders to the database. Each apps checks up that DB, and react accordingly, based on a not-too-CPU-hogging timer (10 seconds - requerying the recordset opened on only ONE record; ITS record). All my apps are writing to the database only ONCE (to write up the HWND) and my main VB app is also writing to the DB only once to issue an order.

In all aspects, I'd say my applications have a very-low CPU usage (even implemented a SLEEP to cut down on CPU-usage). I've been tweaking the applications in all possible ways, even declaring my string variables with a bit limit.

My main problem is that when it comes to 50-60 applications running simultaneously, some of them will become "not responding" even tho the CPU-usage is below 20% and my 4gb-RAM limit is far from overused.

I thought I might be missing some "DOEVENTS" but I've tried it on a 5seconds timer and they'll still end up dying.

I am using an MS-ACCESS database. I know... these are kinda weak. But I think it wouldn't be that part that is faulty. I know it ends up establishing many concurrence DB connections (50 simultaneously), but most of them are on SLEEP, and not acting with the DB a lot.

These 40-50 apps are browsing to a web page, filling up some fields and clicking the submit button. Then it falls in 3-4 minutes of loops, idling/waiting for an order (so I use SLEEP in these long timers, but making sure I run a doevent every 5 seconds).

I think I might have some debugging to do to find out what is causing this... Maybe I am opening up too many apps at a time, and loading 10-20 or even 30 webpages at a time is causing the problem. Or is it the GETDC that is too CPU demanding, but I don't think so (every apps will use GETDC to take a screenshot and put it into a variable).

I've explained the best I can. If you have any idea when reading this, feel free to reply. Until then I'm gonna be debugging this...

Thanks

the web page, doest this interact with the DB also?

No. The apps simply browses to a webpage and my app checks the html. Based on what written in there, it checks a local database to fill information on the web page and submit the webpage.

First you need to eliminate ACCESS for a test.

Take the 40 to 50 programs (I'm guessing they are all the same .exe - right?) and comment out the recordset getting portion. Maybe put a random CLOSE, BUY, WAIT logic in it's place.

See if it runs now - without the ACCESS database connection.

If so then you are encountering deadlock issues against that small table - which I am highly suspicious is the cause.