Server-side processing rocks the house!

I've spent the last couple of weeks learning how to optimize the search tool we built. The short story is that we're now using a pass-through query in Access to pass parameters to stored procedures on SQL Server. Processing time has gone from about 2 minutes to less than 2 seconds.

Now that I know how to work with stored procedures, I'm thinking of how else to implement them with other types of applications.

BACKGROUND

The company has clients spread across multiple databases for various reasons; namely specific business rules (e.g., worker's comp rate, state tax rate, unemployment insurance, etc.) apply across an entire database.

Quite often someone in the company receives paperwork from an agency or client with nothing but the client employee's name or their SSN. Searching for this employee has been trial and error, going through each database at a time. Also, in many cases the person in our company just needs to see some basic information about the employee. For example, during tax season we just needed to verify a client employee's address and phone number. Hence the reason for such a tool.

We had initially designed the tool to have individual pass-through SELECT queries, uniting and applying criteria to them in Access. It was the uniting and applying criteria that has been slowing things down.

OPTIMIZING

So, I created two stored procedures with parameters: one that searches for an SSN one one that searches on a name. Each stored procedure unites the individual SELECT queries on a database separate from the other 12 that were being searched.

In VBA code, based on which criteria the user enters, we determine which stored procedure to run. We create a pass-through query to EXECUTE the stored procedure. This pass-through query then contains the search results, which is what we had with the earlier version of the tool.

PREVENTING SQL INJECTION

While reading the literature, I came across the concept of SQL injection and the need to prevent it. Since users are passing text to a SQL statment, we need to prevent the intentional or unintentional insertion of code that would prematurely terminate a text string and append a new command.

I wrote one procedure that scans what users enter in the first and last name fields for "--", "CREATE", and other such items. My coworker gave me some code to prevent users from entering other illegal characters. This double-barreled approach will prevent any SQL injection from occurring.

COMMUNICATING WITH THE MASSES

I enjoyed sharing the news of the updated tool with the rest of the company. I knew many had not even heard of it. I constructed the email the way I would a friendly blog post, including pictures.

One user immediately replied: "Awesome! Works very fast. Good job." I was contacted by two people in Accounting and Payroll who asked that I put the tool on their machines as well.

Much joy all around, I tell you. And IT looks good.

----------------
Now playing: Tim Hawkins - KidsRock
via FoxyTunes

What people want and how I help them get it is my passion

I think the part of my job that I enjoy the most is finding out what people want and then showing them how to get it. I realize that is a broad statement, but I think it definitely applies to my primary motivation for what I do.

For example, at work we're working on this search tool, which I shared about previously. I've been talking with potential end users of the tool to further define its requirements, while my fellow developer is working to implement these requirements. I like that I can communicate to each group based on their needs; whether it's with a user who is not so savvy about technology or with my coworker about the nitty gritty details of a SQL statement.

It hasn't been just with this project either; I can see other examples with previous projects.

Deploying a much-needed search tool in the workplace

We have a limited number of licenses to use the payroll management software in our organization. As we have added more staff whose responsibilities involve using that software, those licenses get used up pretty quickly. It's not uncommon to receive 5 emails every day asking if someone would log out of the system so that someone else can get in to search something or run a payroll.

With some knowledge of the database, we've been able to develop a tool to work around the system by querying the server directly. In fact, this is exactly what I've done when developing the 401(k) reporting tool and other custom tools for the organization.

We have created a search tool that members of the organization can use to return some basic information about an employee. I may have mentioned before that we have several databases for different clients, and many times, we don't know the for whom the client works or in which database his or her records are. As a result, the user has to use trial and error to find the employee, which is time-consuming.

Our new Search Tool, which is what I'll refer to it as here, returns an employee's address and company name, along with whether he or she is marked as Active in the system. I have to give a lot of credit to my fellow developer for taking the intial idea I had, and, with some guidance on the SQL, has created the tool to execute the search.

I spent part of the day deploying the tool to different members of the organization who would most likely use it the most: One person from the Risk Management department who coordinates the paperwork related to unemployment claims; one person who is fielding calls from employees about not getting W-2 forms (probably because of an address change); and the receptionist, who helps out with overflow work, especially with respect to returned mail.

We're still building on to the tool while the application is in use, but we are developing in a separate environment. Once again, by having this application on a shared network drive, we can update the file on that one location as needed.