Margaret.Sliming.2640353

SQL Procedure DML performance vs SQL

Discussion created by Margaret.Sliming.2640353 on Sep 18, 2015
Branched from an earlier discussion

There is an outstanding issue I will need to get the DBA's help with.  It may be a IDMSBCF issue so if anyone comes across something similar, please let me know.  When I added a bunch of IF...SELECT statements, it took 88 minutes to compile the procedure and kept going into a wait state and the operator had to reply 'continue' to let the job keep running.  The DBA is going to watch it with performance monitor to see if he can tell what's going on.  He thinks it may be a buffer issue.  I will let you know when I find out.  I wrote an ADS Procedure version of the SQL procedure using DML and of course it compiled immediately and the dialog was 25% of the size of the dialog created by the SQL procedure.  I thought that was interesting.  I was always told to keep dialogs as small as possible.  Does size still matter?  Also, the DML versions of programs seem to be faster than the SQL versions.

Outcomes