What is more efficient in queries

[Ratzie]

I am trying to understand what would be more efficient.
We have a flat file that will contain any where up to 400 + sites. ( I am taking very high estimate)(Probably unlikely, more like 150+)
Do I take each site and query the db to see if it exists in the site table of about 400+, if it does do nothing, if not enter into database. Or, pull the table (select site_id) into a perl array and do a compare, and have perl do all the manipulations then add site_id, if it does not exist.

Any stats, some hard evidence...

[Sandman!]

Either way you could "time" your options to see which one would consume less system resources.

Another option would be load the flat file into a temporary table and compare within the database itself. Intuitively, with this approach you would be comparing only 400+ records as opposed to dumping an entire table (don't know how may records are there in it but if it's >1000; then performance penalty would be there) into a flat file and then using perl to diff out the non-matching records.

cheers!

[Hein van den Heuvel · Accepted solution]

What you don't want to do is go into SQLPLUS for every site if there are more than a dozen or so lookups as suggested by your prior questions:
Topic 1009299 - Update field based on flat file
Topic 1009045 - select script returns no results

Those same question suggest to me that you want to perform relational like operations on your data: select where...
many an 'exists' and so on.
So I would lean towards making the outside file available in Oracle and have oracle do what oracle does best: join data from tables.
You could either
- write a (perl) script to generate SQL inserts in a (temp) table
- write a simple SQL-loader script for your flat file
- have Oracle use the 'new fangled' define table on external file (my favorite for this excercise)

Now if you are more of a Unix/Shell/Perl person than an SQL hacker (and I have the impression you are) AND the database data is 'manageable' outside the DB (less than 100,000 rows?) then I'd be temped to just suck all relevant data out of Oracle with a single select and then use a (perl) script to massage the data just so.

The ultimate deciding factor for me would be where the result should be. If the desired result is new or updated data in the database, then go there and stay there. If the target is a (flat)file or list to be mailed or printed, then it is tempting to stay on the Unix side.

Hope this helps deciding,
Hein.

[Joshua Scott]

In my experience, any interpreted language (perl, java, etc.) is quite a bit slower than most of the other options. You will have to read from the file and the database to compare no matter how you do it.

Personally, I would create a temporary table in the DB to dump the file into, and then do the comparisons and adds inside the DB.

Josh

[Indira Aramandla]

Hi LHradowy,

If have a flat file then write a script to read each line in the flat file and you will query the table to see if the record exists and insert into the table if does not exist.

The other way would be to extract the table site-id into a flat file and compare this flat file with the flat file data you got and output o a third file the complete list of site id's. Then use SQL Loader (sqlldr) to load the new file data into the table.

In my opinion the second option will be faster. As you will be comparing two files at the OS level unlike the first option you will read the file content and query the table for a match. And SQL LOADER is must faster than inserting each row after comparing.


Indira A

[Yogeeraj_1]

hi,

if you have oracle 9i and upper, you can also look into external tables.

hope this helps!

kind regards
yogeeraj