sry for the typo, the subject should have been BTEQ or FASTEXPORT!
I have written a query of 1500 lines which does all computation, casting etc by retrieving 20 different tables which are quite huge. I run this query from SQL Assistant and it takes about 5-10 mins to provide the results. However, when I run the same using BTEQ in a mainframe job, it takes about 1-2 mins.
Depending on the teradata system usage, the same query runs for 1-2 hours or 1-2 mins. I am using views of all 20 tables and the result from this query will be about 2000 rows only.
We have a policy wherein I can run the BTEQ using production data but fastexport has its own restrictions as to when to run and when not to. So could not get a chance to run the query using fast load.
My question is, when I go to production with the query, which of the utility is is more efficient BTEQ or FASTEXPORT? And what would be the advantage one over the other?
The query itself will run the same in BTEQ or FastExport. The only difference is that FastExport is optimized to send back large amounts of data to the host. So, if your answer set is large (millions of rows), FastExport can send the data back quicker by putting the data into physical data blocks on the AMPs before sending them back to the host. Also, it can send the data blocks back across multiple sessions in parallel, so this speeds things up as well.
There is some overhead that FastExport has to incur to do this, especially if the answer set is sorted. But, as the answer set gets larger, this overhead time is more than offset by the improved time for transferring the answer set to the host.
For a 2000 row answer set though, I think you should stick with BTEQ export unless you need some functionality that BTEQ does not provide.