Windows service running and CPU load

I have a situation with my software where I need to run every investor through watch list to see if the investor shows up on any of the watch lists. The watch lists are provide by a third party company whose software sits on my production server. The problem is the searching is rather slow. It takes about 6 seconds to search 500 names and it's not uncommon for one page of the web application to have a list of 500 investors.

My idea is to create windows service that runs once a week during off hours and runs all of the investors through the watch lists and stores the results in my software's database. I am banking on the fact that our software will never have over 2 million investors and if we were to reach 2 million investors it would take approximately 9 hours for this service to complete. My question is, is this a feasible solution or will it consume too many resources for too long? I'm not too familiar with windows service programming so I'm not sure if writing such a program will cause the server's cpu to seriously lag.

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Here are a few thoughts and questions.

1. You say the third-party software resides on your production server, but not where the lists reside. If the lists are remote; i.e. they are performing multiple web requests (one per list they search), that could explain the search time. If the lists are kept locally in files or a database, do you have direct access to them? If so, it should not be difficult to improve on that search time per investor.

2. Services are not inherently large CPU consumers. For example, if it is requesting information from the internet or a database on a different server, it will be idle much of the time, even though the process is running.

3. If I were required to do this task, I would use a service, but not concentrate the entire search into one session. I would:
A. Create and maintain a DB table of investors. It would contain investor identification, whether the investor is clear (not on a watch list), and a timestamp of the last search for this investor.
B. When a new investor is added, add that investor to the table with a timestamp beyond the search threshold (see below).
C. Create a service that runs periodically, say every 30 minutes or an hour triggered by a timer. When the timer fires, select the investors from your new table where the last search time is beyond some threshold. That threshold is set according to your business rules for max search age; i.e. how long you are willing to risk having any investor transition from unlisted to listed without detection. Since the first few runs may include lots of investors, you might want to limit the count for any given search using TOP (for SQL Server) or ROWNUM (for Oracle). That would tend to spread them out and even the load for any given search session.


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
puterartAuthor Commented:
Thanks Jim!

To answer your question about the watch lists, they are located on the server in a series of proprietary files. I don't believe I have any way of opening up direct access to them as this was the first thing I thought of. I have to search them using a programming interface provided by the third party software.

You solution in step A is exactly the solution that I was thinking, to a T, so it's nice to see I wasn't off base. I also really like the idea for the solution in step C. It makes sense and would probably not need to search through many investors after the first few runs. I believe this will be the approach I will take for the most part.

Now my only issue is new investors. In my software users can import a list of investors and these lists need to be run through the watch lists immediately, it doesn't matter if they don't get run through the watch lists again for another two weeks, but they need to be up to date upon entrance into the system. I have worked out and actually already programmed a solution to this today, but I'd like your opinion of it.

What I've done is when a user imports a list of investors I create a thread which starts running all of the imported investors through the watch list. When the page loads I freeze the screen with a modal and show a real time progress bar. The bar gets updated via a javascript function that gets called every second using window.setTimeOut and hits a web service that returns the progress number. When it's fully loaded I hide the modal and the user is free to do whatever.

Also, if the user navigates away from the page and does another import the old thread, if it's still running, is aborted so I can't have tons of these threads per user running. I can't really think of any issues this could cause, except if tons of users were doing imports with tons of investors at the same time then maybe performance would suffer. The execution is really sleek and cool I'm just not sure how practical it is.

Thanks in advance!
Sorry - I did not know new ones needed to be tested immediately. I like your solution. I would add only one bit; i.e. after each is passed (or failed), I would add that row to your new table along with the current datetime. It would be darn near free if you can do the insert while waiting for the next one in the search list.

puterartAuthor Commented:
Yep, that's exactly what I intend to do! Thanks for all the advice Jim!
My pleasure. Good luck.

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
.NET Programming

From novice to tech pro — start learning today.