What are the performance issues related with Macros as compared to BTEQ( for table to table loads ). we have all logic built into BTEQ however now certain users want to have them same built in macros so that they can be easily modified from SQL Assistant only .
I know following things where macro will fall short
1: Collect stats would be possible only at end of the script.
2:Temp tables cannot be created inside Macros
3:Conditional logic cannot be implemented.
It would be helpful if someone can provide more pointers
I personally prefer BTEQ usually.
Your points are all valid. Some to add:
"so that they can be easily modified from SQL Assistant only" is indicating not best souce controll management and dev process...
Job Log info is less meaningfull as changes in code are not reflected in the log - always the same log entry...
DBQL is less meaningfull as you see only exec macro statement - which statement need how many resources.
Much more difficult to debug problems as macro is rolled back completly.
DBQL give no indication what is really going on - see ablove. Complexity is hidden.
Very often not needed too big transaction as everything in one macro is one transaction.
The answer is Stored Procedures :-)
You get the best of both worlds:
- stored in the DBMS instead of file system
- temp tables
- DBQL for each statement
- transaction/rollback control
- advanced error handling
- complex logic using if/case/repeat/while/etc.
- dynamic SQL
(- nasty cursors)
Would agree if this option exists but SP would also need some solid ground work for proper exception handling and good standards to follow, espacially if you use them for more complex processing and logic.
And storing in DBMS is not overcoming the source control issue :-). There are some dangers associated if you change code just on the fly in the DB because it is so easy to do. But this need to be adressed mainly by the SW development deployment processes and is not so much a technical issue.
I just re-read the orignal post and now I understand your concern:
Jigar wrote "certain users" & "easily modified", so this is definitely *not* a use case for SPs.
I think we will agree that SPs should be written by experienced developers who actually *know* what and how to do :-)
If you use the SP to control the execution of a sequence of SQL I would not expect any performance overhead. And should also not depend on the frequency the SP is called.
You should NOT use the SP to process huge volumes of data via cursors! SP run on the PE.