From: "JeremyWoertink@..." <JeremyWoertink@...>
Date: 2007-11-03T09:53:53+09:00
Subject: Re: Database speed issues

Cool. Ok I will explain the best I can the whole entire situation from
the beginning.

I work for a company that produces plastic cards (i.e. credit cards,
debit cards, gift cards, id cards etc..)

I receive files from customers containing anywhere from 1..100000000
records. These files get formatted and put into a network drive in
their respective job number folders. One customer is Starbucks,
andother CVS pharmacy, Mcdonalds, Jack N the Box, Mexico government
Drivers license.

About a year ago there was an idiot programmer who formatted the data
incorrectly causing 150,000 duplicate cards. So because of this, the
company wants to put in place a system of checking for duplicates. The
way it has to work (because I don't have a say in this even though i'm
the developer >.<) is that the past 18 months of jobs will be loaded
into a database (or something useful) and when we (the programmers)
get a new file, we will generate our data from these files, then run
our application to see if there are any duplicate records in what we
created compared to what we have created in the past 18 months
according to their respective companies. (i.e. we get a starbucks job,
program it and then test it against last 18 months of starbucks
records.)

If there are no duplicate files, then we will load that job into the
database (or whatever we need) and continue on from there. If there
are duplicates, then we have to re-program that job, and generate a
report stating how many duplicates, and what exactly it is that was
duplicated (i.e. account number, mag stripe, pin, customer name). The
report is then automatically e-mailed to our boss who then walks next
door to our office to yell at us and tell us to re-program the job.

So for a figure we did 100 million starbucks cards in the past 12
months. We need to check the last 18 months. Starbucks jobs come in
from a company called Valuelink, who generates the data for us. CVS
pharmacy and Disney and McDonalds roughly equal another 100 million
records combined. These also come from Valuelink.
This is important, because if we program it correctly, but Valulink
sends over a duplicate card number, then we need to be able to report
this. So when they send over another job of 5 million cards, now we
are checking that against 200 million + cards to see if anything is
duplicate.

We program roughly 20-40 jobs in a day, so we need a quick way to go
through all this and not lose time or else we will end up working
longer days, and not be compensated for it :(


I hope that explains it a little better. Thanks again for all the
advice.


~Jeremy