Need suggestions of storing data.

My application is meant to store all the windows that are open (their name, hwnd, parenthwnd, time opened at, and time closed at).

Now, I have an access database with a table called "WinLog" to store this data.  

Now, ever second, my application checks through all the open windows.  For each window the program checks if the window has been logged in the "WinLog" table, if it has not been logged than it is added with the ClosedAt field empty.  When the list of windows has been traversed and a record in the database has not been met that has its ClosedAt time empty is changed to the current time (to indicate that the window was just closed).

This procedure takes to much resources.  I believe, that the database connection might be slowing this down since for each window the following function is called:

function WindowNotYetLogged(byval WindName as string)
  sqlstr = "SELECT * FROM WinLog WHERE WindowName = '" & WindName & "'"
  set rs = db.openrecordset(sqlstr,dbopendynaset)
  with rs
    if .eof then
      WindowNotYetLogged = True
    end if
  end with
end function

Now, does anyone have a suggestion in which I can optimize this?  either, by a different storage method (for example text files? or something else).  Or a different algorithm to filing the data?

Also, is it professional to have a ms access database?  This product will be released as a off the shelf package and not tailor-made.  So, is there a more professional way of storing data?


Who is Participating?
mdouganConnect With a Mentor Commented:
Well, I guess a lot has to do with what you want to do with the data you store.  If your program has to read the data that it logs and do something with it, then a database is pretty much essential.  But, a database has a lot of overhead, with maintaining indexes and things.

If you just need to log the data, and some other process is going to come along and read the logs, then simply writing out to a text log file is fine, in fact, if you are on NT or 2000 you can write to the Application Event Log if you want, and there are various tools already for viewing that log either locally or remotely.

Like I said, FileMon has had to address these issues (there are a huge number of events that you're going to end up logging).  Why don't you take a look and see what options they provide.

Lastly, Access is good, but it really starts to have problems when the database gets bigger than about 20mb.  So, if your're going to be logging a lot of stuff, then this file could grow to an unmanageable size.

If this is for an application where the logs are going to be collected or processed over a network, then there is one other option.  You could write your log records out to a "Queued Component".  Basically, this writes a file to some directory on the local machine.  Then, some network "listener" program polls machines periodically, looking for files in these directories, then uploads them and processes them asynchronosly of your client program.  This is a great design if the tool you are building is to be used by network administrators in a large network.
It's not clear from the code above - is WindowNtYetLogged a field name of simply a boolean variable?

If it's a boolean variable then you could modify the line:

set rs = db.openrecordset(sqlstr,dbopendynaset) to
set rs = db.openrecordset(sqlstr,dbopensnapshot)

I don't know if it will increase the speed a great deal but if you use dbOpenDynaset then it creates a recordset that is both readable and writable - using dbOpenSnapshot will only allow you to read the data.

Alternatively you could try:

sqlstr = "SELECT COUNT(*) FROM WinLog WHERE WindowName = '" & WindName & "'"

Set rs = db.openrecordset(sqlstr,dbOpenSnapshot)
If rs.fields(0) = 0 then WindowNotYetLogged = True

This code basically gets a count of windows where windowname is equal to WindName.  It may be worth using the GetTickCount API call before and after the code and then by subtracting the start tick count from the end tick count you could get an idea as to how long the code is taking to execute.

Hope this gives you some ideas.


do not use a query (ie:select...) to search for the record but use seek, it will be magnitudes faster
Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

to elaborate
the recordset has to be opened as a global table-type recordset
ie:set rs = db.openrecordset("Winlog",dbopentable)
the table needs an index on the field you want to search say WindowName
and now your function looks something like:
function WindowNotYetLogged(byval WindName as string)
 WindowNotYetLogged=false "=",TheNameOfTheWindow
 if rs.nomatch then
 end if
end function
sorry did not pay attention the line that says: "=",TheNameOfTheWindow
has to be: "=",WindName

This continual polling of the windows is a really huge drain of your processing power, there is a much simpler approach.

Every window executes a Form_Load event when it is created, and every window executes a Form_Unload event when it is closed.

In your case, you should put code in the Form_Load event of each form that inserts a new record with the hwnd, parent hwnd, and time opened.

Then, in the Form_Unload event of each form, update the table in Access for this form with the ClosedAt time.

To make things a bit faster, if you can store the primary key of the record that you insert during form load, then you don't have to search for the database record to update, you can just format some SQL like:


CMD.Execute sSQL

As far as using Access for an off-the-shelf product, that is a pretty good use for it.  If this was for a bunch of networked users in a big company, then maybe Access wouldn't be the best choice.
JoshyyAuthor Commented:
Thanks for your feedback mdougan, however, the question however was irrelative of be it a window, a file, a program.  (and when I mentioned windows I am not talkin about my program's windows).

Thanks anyway
a seek will be at least 20 times faster then any kind of sql
for the part:
>>Also, is it professional to have a ms access database?
an access db will do the job quite good
and for what you want to do it will even beat most client-server db's(SQLServer,Oracle,etc...)hands down (and that even with dozens of simultanious users)(provided you use seek)
>>So, is there a more professional way of storing data?
for what you want to do, where speed is the most important i would suggest any of the real ISAM-type database/managers
codebase for example

My point wasn't that running a sql statement would be any faster than a seek but that to issue a seek or any other polling command every second sounded to me to be unnecessary, if instead, you could just call it when a window was opened or closed.  Making two database calls, one to insert, one to update would be much more efficient that issuing 1000 seeks.

Now, I have a little better idea what Joshyy is trying to do -- monitoring what windows and/or programs, files etc are opened up on a users computer and log them into a database, I can see that this would complicate the issue a bit.

I still think that polling every open window every second is way too much overhead.  I'd say the only hope you have Josh, is to hook the events that Open and Close windows, files or programs and do your logging based on those events, not on polling.

If you'd consider the suggestion that I made in your other question, you should go to the site and lookup two of their tools, FileMon and RegMon.  These tools, particularly FileMon does pretty much what you want to do, except that it writes to an external log file, not to a database.  They also publish the source for these tools, though I'm sure it's probably in C or C++ it might give you an idea of how to hook these system events.  Or, you might find that your program can Shell to run FileMon and have FileMon write to a log file that you would then read and update the database asynchronosly.

JoshyyAuthor Commented:
mdougan,  I know... and in fact I am using hooks to get these.  My real question is not directly linked to window logging or any other type of logging,,, I am concerned about storing information.. what are efficient methods and whether access is good enough
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.