- Community Home
- >
- Servers and Operating Systems
- >
- Operating Systems
- >
- Operating System - HP-UX
- >
- What is more efficient in queries
Categories
Company
Local Language
Forums
Discussions
Forums
- Data Protection and Retention
- Entry Storage Systems
- Legacy
- Midrange and Enterprise Storage
- Storage Networking
- HPE Nimble Storage
Discussions
Discussions
Discussions
Forums
Forums
Discussions
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
- BladeSystem Infrastructure and Application Solutions
- Appliance Servers
- Alpha Servers
- BackOffice Products
- Internet Products
- HPE 9000 and HPE e3000 Servers
- Networking
- Netservers
- Secure OS Software for Linux
- Server Management (Insight Manager 7)
- Windows Server 2003
- Operating System - Tru64 Unix
- ProLiant Deployment and Provisioning
- Linux-Based Community / Regional
- Microsoft System Center Integration
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Community
Resources
Forums
Blogs
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО03-16-2006 09:40 AM
тАО03-16-2006 09:40 AM
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...
Solved! Go to Solution.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО03-16-2006 10:12 AM
тАО03-16-2006 10:12 AM
Re: What is more efficient in queries
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!
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО03-16-2006 10:36 AM
тАО03-16-2006 10:36 AM
SolutionTopic 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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО03-16-2006 11:17 AM
тАО03-16-2006 11:17 AM
Re: What is more efficient in queries
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО03-16-2006 02:44 PM
тАО03-16-2006 02:44 PM
Re: What is more efficient in queries
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО03-16-2006 06:45 PM
тАО03-16-2006 06:45 PM
Re: What is more efficient in queries
if you have oracle 9i and upper, you can also look into external tables.
hope this helps!
kind regards
yogeeraj