Solved

Need suggestions of storing data.

Posted on 2002-03-16
10
252 Views
Last Modified: 2010-05-02
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
    .close
  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?

Regards,

Joshyy
0
Comment
Question by:Joshyy
  • 4
  • 3
  • 2
  • +1
10 Comments
 
LVL 2

Expert Comment

by:peterwest
Comment Utility
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.

Cheers

Pete
0
 
LVL 6

Expert Comment

by:pierrecampe
Comment Utility
do not use a query (ie:select...) to search for the record but use seek, it will be magnitudes faster
0
 
LVL 6

Expert Comment

by:pierrecampe
Comment Utility
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
ie:rs.index="Windowname"
and now your function looks something like:
function WindowNotYetLogged(byval WindName as string)
 WindowNotYetLogged=false
 rs.seek "=",TheNameOfTheWindow
 if rs.nomatch then
    WindowNotYetLogged=true
 end if
end function
0
 
LVL 6

Expert Comment

by:pierrecampe
Comment Utility
sorry did not pay attention the line that says:
rs.seek "=",TheNameOfTheWindow
has to be:
rs.seek "=",WindName

0
 
LVL 18

Expert Comment

by:mdougan
Comment Utility
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:

sSQL = "UPDATE WINDOWS_LOG SET CLOSEDAT = #" & Now() & "# WHERE WINDOW_ID = " & gNewKey

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.
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 1

Author Comment

by:Joshyy
Comment Utility
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
0
 
LVL 6

Expert Comment

by:pierrecampe
Comment Utility
mdougan
a seek will be at least 20 times faster then any kind of sql
Joshyy
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



0
 
LVL 18

Expert Comment

by:mdougan
Comment Utility
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 www.sysinternals.com 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.

0
 
LVL 1

Author Comment

by:Joshyy
Comment Utility
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
0
 
LVL 18

Accepted Solution

by:
mdougan earned 30 total points
Comment Utility
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.
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

I’ve seen a number of people looking for examples of how to access web services from VB6.  I’ve been using a test harness I built in VB6 (using many resources I found online) that I use for small projects to work out how to communicate with web serv…
The debugging module of the VB 6 IDE can be accessed by way of the Debug menu item. That menu item can normally be found in the IDE's main menu line as shown in this picture.   There is also a companion Debug Toolbar that looks like the followin…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now