I am currently designing Perl DBI code to extract data from tables from Sybase Database under UNIX.
There are a dozen of tables I need to extract information from.
The biggest tables are ACCOUNTS and SUBSCRIBERS.
ACCOUNT has 10 million rows and SUBSCRIBERS has 20 million rows.
SUBSCRIBERS is related to the ACCOUNTS table as every account has subscribers.
I am questioning my strategy for this design.
At the end of the extraction process, I need to end up with 1 or 2 flat files that shows rows of SUBSCRIBER data and rows of ACCOUNT data associated to those subscribers.
Which is the better option in terms of performance and reliability:
Access the sybase tables with SELECT+JOIN sql statements, order and write the results to the overall flat file file immediately ?
BCP out the results into multiple files and manipulate/rearrange/order them into a single file under Unix.
I would be most grateful if you could help me out.
Not sure; I'd think ASE would be faster simply because you can (and should) have indexes on the joined columns which basic perl / shell scripts wouldn't be able to easily duplicate. Go ahead and performance benchmark each method and that'll be your answer!