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

Fixing the client's 401(k) reporting application to make it run better

The company didn't have a process in place to make sure that this one client receives their 401(k) information on a regular basis. I had developed an application last fall for this specific client to produce the information, but there was no one set up specifically to run the application on a regular basis.

I was contacted earlier today to set up this application on several people's computers. Interestingly enough, everyone could get to the application, since it exists on a networked drive, but, to make things as easily accessible as possible, I prefer to put a shortcut to the application on users' desktops.

After I finished that, I sent an email to everyone affected, letting them know about the application, and letting them know how to get the report.

About 15 minutes later, I was summoned to a user's computer because there was a problem with the report details. She was running the report for last week, but the Last Pay Date for several employees was for this week.

As I looked into the program, I realized that all the other details were correct except for this Last Pay Date, which gets updated whenever a payroll is run. I began brainstorming other ways to get the correct date.

One option was to look at the last pay check date for the date range specified, which is from the first of the month to the date the user selects on a form. That would make sure that date for employees who are getting paid this week is the date the user selected.

As I thought about that, I realized I was not finished. What if we run this report a month from now, and an employee had been terminated this month? The client would still want that employee to show up on the list.

I continued brainstorming, and wrote some mock VBA code on my marker board. Satisfied with that, I created a function, tested it out with various scenarios, and eventually came to a solution that will either use the last pay date for the reporting range OR the Last Pay Date field from the other data table.

I generated the report and sent it off to the client service representative, who will then forward it on to the client.

Client pleased by changes made to payroll journal

You might remember that I mentioned recently working with a client to answer questions about their payroll journal.

Last Friday I contacted them again to clarify some of the changes they had requested and provide some ideas to make it more useful to them. I had already provided an instruction worksheet when I initially designed the payroll journal. These instructions included how to add and delete employees, among other things.

The client wanted me to add a Training payroll item on the journal, stating that they would be paying their employees minimum wage during training sessions. Since this change would affect some of the calculated totals, I contacted them and asked if they still needed them. They replied that they didn't really need them, and, since we didn't really need them either, she said we could remove them.

I changed the layout on the spreadsheet for each payroll journal they use and sent them off to the client. I contacted them later to verify they received the new versions, and they really liked how they looked. The changes in effect made the entire journal look more legible, in their opinion.

In a separate post I'll share about how I've managed changes to the related bridge application we use to process this client's payroll.

Client relationship strengthened with assistance on payroll journal

One of our clients called me to ask for help with a payroll journal we had set up for her. I enjoyed talking with her, since it had been several months since our last phone conversation. I asked her to email me the payroll journal she was working on so that I could see the problem as she saw it, since we're at different locations. She tends to be wary of making any changes herself, and I've often had the tremendous opportunity to help her out.

After looking at the payroll journal, I was able to help the client fix the problem she was having over the phone. We also talked about some additional changes her company wanted to make and what would be involved in getting them set up, including changes to the respective bridge application we use to automate the data entry.

After we ended our conversation, I went upstairs to talk with the payroll specialist about this new change - specifically, a new payroll item - will be implemented. It turns out that implementing this additional item will be relatively easy to do. She told me that we will wait until they payroll for the client is run this week to implement the changes to the payroll journal.