Re: SQL or Java?

AmoroSSo doodled thusly:

>We have a Java program (EJB) that does the file and record integrity checks
>and loads the data into three Oracle tables.

this should use SQL*Loader, not Java. Or C/C++. Java is not the best way to handle very large data volumes from a flat file into a RDBMS, regardless of the maker or nature of the processing.

>
>Then we run a set of SQL stored procedures to check the integrity and
>validity of the individual fields and store the results in yet another
>table.

couldn't this be done while data is being loaded? if it is only integrity and validity of individual fields, then it shouldn't be affected by data in the other tables? therefore, it should be done pre-load or during load.

>We believe this process could not be done with the original files,
>since they are just plain text files.
>

why? if all you're doing is checking validity of the "individual" fields, where data is stored means nothing. you have to read the data in both cases and Oracle reading or flat file reading is not particularly different in efficiency for data being processed sequentially.

however, if as I suspect you need cross-table or cross-file checking of values, then you are correct: load them in the RDBMS and do the work there.

>
>The DBA is telling us that we shouldn't use Oracle to do this checking (nor
>the moving to the final database), but an external application like Java or
>C++.

weird. it's not Oracle that should make the biggest difference, but Java or C/C++.

>Our opinion is that it would be faster, and less intensive for the
>network and the database, to do it in Oracle using PL/SQL.

assuming that your data files are in the same node as the Oracle RDBMS, yes, that is indeed the case.
Otherwise it makes no difference, assuming the constraints mentioned above are valid.

>
>So the question is ... who is right? or how can I find information or
>benchmarks on this topic.
>

I suspect we don't have all the info here to suggest correctness or otherwise. I'd like to see more details before claiming any righteousness.

Cheers
Nuno Souto
nsouto_at_optushome.com.au.nospam Received on Thu Jan 17 2002 - 03:16:31 CST