Take the 2-minute tour ×
Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. It's 100% free, no registration required.

I have two huge tables on separate databases. One of them has the information of all the SMS that passed through the company's servers while the other one has the information of the actual billing of those SMS.

My job is to compare samples of both of these tables (for example, the records between 1 and 2 pm) to see if there are any differences: SMS that were sent but not charged to the user for whatever reason that may be happening.

The columns I will be using to compare are the remitent's phone number and the exact date the SMS was sent. An issue here is that dates usually are the same on both sides, but in many cases differ by 1 or 2 seconds.

I have, so far, two alternatives to do this:

  1. (PL/SQL) Create two tables where i'm going to temporarily store all the records of that 1hour sample. One for each of the main tables. Then, for each distinct phone number, select the time of every SMS sent from that phone from both my temporary tables and start comparing one by one using cursors. In this case, the procedure would be ran on the server where one of the sources is so the contents of the other one would be looked up using a dblink.

  2. (sqlplus + c++) Instead of storing the 1hour samples in new tables, output the query to a text file. I will have two text files, one for each source. Then, open the first file and load all of it's content on a hash_map (key-value) using c++, where the key will be the phone number and the value a list of times of SMS sent from that phone. Finally, open the second file, grab each line (in this format: numberX timeX), look for numberX's entry on the hash_map (wich will be a list of times) and then check if timeX is on that list. If it isn't, save it somewhere to finally store it on a "uncharged" table (this would also be the final step on case 1)

My main concern is efficiency. These samples have about 2 million records on each source, so just grabbing one record on one side and looking it up on the other would not be possible. That's the reason I wanted to use hash_maps

Which do you think is a better option?

share|improve this question
    
do you think you could add a few sample rows from the two sources, enough so that a proposed solution for the sample would solve your problem? –  Aaron Anodide Jun 1 '12 at 21:38
add comment

5 Answers

I suggest creating a database link between the two databases, it lets you access a remote table as if it where in the same database.

Once you have the database link, code the solution in a PL/SQL store procedure.

I suggest PL/SQL Developer as the IDE for writing store procedure.

share|improve this answer
add comment

Here's a very efficient way to compare two queries (generalized solution for the problem of comparing two tables efficiently):

SELECT * FROM
(
  SELECT foo1 col1, foo2 col2 FROM ...
  UNION ALL
  SELECT bar1 col1, bar2 col2 FROM ...
) t
GROUP BY col1, col2
HAVING COUNT(*) = 1

This query takes two sub-queries that are expected to be identical, and the output is the rows that are only in one of the queries. In other words, if the two sub-queries return identical rows, the output should be empty.

I needed to compare two large data sets in the past, where performance was an important concern, and this was by far the fastest method I found.

You mentioned the timestamps may have a few seconds difference. To hide that noise, I recommend to use some kind of date rounding, but I don't know the syntax for that.

share|improve this answer
    
And which of the alternatives I suggested you think is a better choice? –  Alex Jun 1 '12 at 20:43
    
You should take a moment and consider the query I proposed, it should be more efficient than the two solutions you offered. But the first method you propose is really not good at all, because processing records one by one is inefficient, and would impact your live databases. Your second solution will be faster, and it won't impact much your live databases. –  janos Jun 2 '12 at 6:04
add comment

At first blush, your second option is probably going to be superior.
Here's my line of thinking and assumptions.

0) I'm assuming you don't have a massive DB system sitting idly by waiting for a query like this to justify its purpose.

1) You're likely going to have run this multiple times. If nothing else, once discrepancies in the bill processing have been resolved, this audit process will be needed to validate the fixes.

2) Parallel operations will be key in getting reasonable run times. The second option you suggest will allow you to break things out into chunks of individual "sent" listings and "billed" mappings. Those chunks can be farmed out to distributed clients for faster processing time.

3) C++ code will give you more options in handling the time discrepancies and subsequent (re-)processing of those entries.

share|improve this answer
add comment

Two million records shouldn't be that big a deal, especially if phone number is indexed. So your time is probably more important than the query time. Heck, I would probably create a table and import all the records from one database to the other if I couldn't otherwise reference both in one query.

So, I'll assume you can reference both tables in one query.

The logic for matching within the 2 second window is somewhat tricky. For example what if there was an SMS at 12:00:01 and 12:00:05 and a bill for 12:00:03? Really, one of the two hasn't been billed.

So what you really want as a first step is to match SMS message count and messages billed count for a phone number. Later you can figure out which one wasn't billed (or possibly not have enough information to do so precisely, as in my example).

We can do that:

SELECT A.PhoneNumber,SentCount,BilledCount 
FROM 
     (SELECT SentMessages.PhoneNumber, Count(SentMessages.SentDate) as SentCount
        FROM SentMessage
    GROUP BY SentMessages.PhoneNumber) as A JOIN
      (SELECT BilledMessages.PhoneNumber, Count(BilledMessages.BilledDate) as BilledCount
        FROM BilledMessages
    GROUP BY BilledMessages.PhoneNumber) AS B
          ON A.PhoneNumber=B.PhoneNumber
       WHERE SentCount>BilledCount

And we might as well run this on the entire table as a sample - except that maybe some messages aren't actually supposed to have been billed yet, so we don't want those. That's outside the scope of the question anyway.

share|improve this answer
add comment

If the databases are on the same server, you can simply run a query against both databases.

If not, you could use Oracle Database Gateways (if you have it) to connect to both, and similarly do the comparison.

share|improve this answer
    
And what about the options I suggested? which you think would perform better? –  Alex Jun 1 '12 at 20:52
    
Probably option 2. You don't necessarily need c++ to do it, either. Just SQL to two files and then a diff utility. –  Matthew Flynn Jun 1 '12 at 22:28
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.