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
Wednesday, August 22, 2007
Server-side processing rocks the house!
Monday, August 13, 2007
How do you draw the line between effective promotion and spam?
This is a question I've asked my LinkedIn network, and I also ask here:
How do you draw the line between effective promotion and spam?Comments are open and genuinely appreciated!
When marketing a brand or doing some other type of publicity for someone or something, there are effective ways to promote, and there are not so effective ways. How do identify, especially in the digital age in which we live, where the line between effective promotion (self- or otherwise) is and where you've crossed it?
Related tags: getthatjob get+that+job job+hunt job+search career+advice networking professional+development daniel+johnson+jr self+branding
crossposted to http://getthatjob.blogspot.com
Thursday, August 9, 2007
JIMM 90 - Crazy Busy Sticky Hot
Head over to the Journey Inside My Mind Podcast to listen and read more.
Related tags: journey+inside+my+mind journeyinsidemymind jimm daniel+johnson+jr
Subscribe to:
Posts (Atom)
