Flat File/DB Comparison with 2500 items

[trc]

I have a flat file with 2500 items in it with two values per item. I need to match and compare these values to the corresponding values in an Oracle database to find out which values are different. What is the best way to do this?

As I see it I have the following options:
-Load everything from the file and database into arrays for comparison.
Issue: Memory pig and network congestion.
-Load the flat file into an array and then create a record set of data from the database from comparison.
Issue: increase in network traffic
-Create temp tables in the database and fill them with the data from the file and from the database and use views and minus in SQL to find the items that are different.
Issue: Overhead?

Thank you in advance,
TRC


************
My purpose in life is to show others what not to do.
<!--Caution, dates on calendar are closer then they appear.-->

 

[BobRodes]

One question: how many of the fields in a given record do you have to match with those in the oracle db? All of them, or some subset?

Bob

 

[EBGreen]

You could use ADO to get the text file into a recordset then process it and the DB data as recordsets.

[red]"... isn't sanity really just a one trick pony anyway?! I mean, all you get is one trick, rational thinking, but when you are good and crazy, oooh, oooh, oooh, the sky is the limit!" - The Tick[/red]

 

[trc]

I need to compare two fields fromt he file to two from the database. Then I need to capture the difference if there is one, but that is another story.

I thought about the ado recorset for both. I forgot to mention it. Is this the best option? Will it be the fastest?

************
My purpose in life is to show others what not to do.
<!--Caution, dates on calendar are closer then they appear.-->

 

[EBGreen]

I don't know if it would be fastest, but if the file is large I think it would help reduce memory consumption.

If you can convert the text file to csv, you should also be able to connect to it as a DB through Jet (I think).

[red]"... isn't sanity really just a one trick pony anyway?! I mean, all you get is one trick, rational thinking, but when you are good and crazy, oooh, oooh, oooh, the sky is the limit!" - The Tick[/red]

 

[gmmastros]

To make the comparisions, you will have to send one of the datasets across the network in order to compare them.

You say there are 2500 records in the flat file. How many are in the database? If the number is similar to 2500, then it's a toss up. If, however, there is an order of magnitude or more (250,000)records in the Oracle database, then you should send the data to the database.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[BobRodes]

Well, the reason I asked my question is because if you are just comparing one value using a key, say, you might get very good throughput if you ship the value/key pair as text to the server, check the values there, and ship back only the values that match. If you're comparing the entire record, that wouldn't be a good idea, of course.

Bob

 

[trc]

Geroge
Well, there are over 250,000 records in the DB but I can select a subset of them and dump them into what ever I choose (rs, temp table, array). i think it is a toss up fro network traffic. I guess I want to know if it is better to try and kill my computer or the server? (As in, where should I do the work?)

Bob
I wish the flat file had a key filed. I only has the name field and two numeric data field attached. :|

************
My purpose in life is to show others what not to do.
<!--Caution, dates on calendar are closer then they appear.-->

 

[PCLewis]

For such a small text file I would just import it to a scratch table in your main database and do it there.

 

[gmmastros]

I agree with PCLewis. It should be faster to send 2500 records to the DB in to some sort of temp table than to pull more than 250,000 record from the DB.


-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[trc]

Great thanks kids. Stars all around.

************
My purpose in life is to show others what not to do.
<!--Caution, dates on calendar are closer then they appear.-->

 

[fredericofonseca]

As for loading the table into Oracle I advise you to look at SQL*Loader to do that.

It will load faster than anything else you can use, and you will not need to code.

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[BobRodes]

<I wish the flat file had a key filed.

I've never tried optimizing a non-key field in an ADO recordset, but it would be interesting to see how the .fields.properties("Optimize") property works on one.

< I guess I want to know if it is better to try and kill my computer or the server?

You might decide for yourself what the comparative processing power of clients and servers, relative to the comparative load of same, is. Personally, I've been of the opinion that client processing power is generally underutilized, and that a good load balancing strategy would leverage that processing power more than it typically does. So, I've been a fan of disconnected recordsets and as much client-side processing as possible.

It might be interesting to note that disconnected data sets are the norm in the ADO.Net environment, for all but simple data read operations.

HTH

Bob