8 Replies Latest reply on Aug 14, 2019 8:54 AM by Nico Heinze

    IICS SQL vs SQL Stored Proc

    Jonathan Kim Seasoned Veteran

      What are the pros and cos of using SQL's in IICS vs using Stored Procedures defined in a database?

        • 1. Re: IICS SQL vs SQL Stored Proc
          Jonathan Kim Seasoned Veteran

          What are the pros and cons of using SQL's in IICS vs using Stored Procedures defined in a database?

          • 2. Re: IICS SQL vs SQL Stored Proc
            Nico Heinze Guru

            Stored Procedures must be programmed and maintained in a DBMS.

            SQL statements from IICS can be invoked without any need to maintain any code within the DBMS. You just can issue them "on the fly" as you need them (assuming that the database user ID has the right DB permissions).

             

            In addition you don't have to cater for deploying any database objects e.g. from a DEV to a TEST and finally a PROD environment when deploying IICS objects through the landscape.

             

            Regards,

            Nico

            • 3. Re: IICS SQL vs SQL Stored Proc
              Jonathan Kim Seasoned Veteran

              That's the point.  If we use a proc defined in a db instead of "on the fly" in IICS, it will be more organized in a maintenance point of view.  It will be easier to keep track of all SQL statements if they are all defined in dbms.  If we use "on the fly" sql's in IICS, we will need to look through all tasks in order to locate any sql's used in IICS.  Isn't this true?  Also, it will always require modifications to IICS mappings if there are any changes to the sql's embedded unless we use Saved Queries.

               

              Also, what would be pros and cons in terms of performance?  Wouldn't executing multiple sql's "on the fly" in  IICS issue more API calls (1 API call for each sql statement or 1 API call for each row being updated)?  If we use a proc in a db, it will always be a single db call to the db server.  How many db calls or API calls need to be issued by IICS if there are 10 sql update statements for 100,000 rows?

              • 4. Re: IICS SQL vs SQL Stored Proc
                Nico Heinze Guru

                That's exactly the point, but the consequences are - unfortunately - by far more complex than we both know.

                 

                First about performance: while it is true that DBMS servers often are strong machines, many things cannot be done easily (if at all) in a DBMS. One practical example: if you have to invoke any shell script / batch file from within any mapping, a SP won't help (at least not easily) at all.

                Also recursive processes are not easy to implement in a DBMS. For example, of course you can create a list of 50,000 numbers via a recursive query. In Oracle, for example, you can use a CONNECT BY PRIOR clause. Did you ever try that? Did you ever try to create 50,000 consecutive numbers that way? I did, and after some 14,000 numbers my own Oracle installation gave up (PGA exhausted). Recursive queries are a nightmare for a DBMS, it's that simple.

                While it is not too easy to implement in PowerCenter (that's my area of expertise), it can be done in a fairly efficient manner (in contrary to a DBMS with a recursive query).

                My experience regarding performance is: you have to try it. In quite many cases you will even experience that you cannot rely on your DEV and PROD environments to behave the same way; it can well happen that a process works faster in the DBMS in DEV but faster in PowerCenter / IICS when run in PROD. Or the other way round. Or sometimes this way and sometimes the other way. You can never know in advance.

                 

                While it is true that prepared statements (such as a SP) can be executed faster by a DBMS than statements issued "on the fly", it's not at all sure that you will notice the difference. It always depends on many things, in fact on more things than we can affect. In general it makes more sense to focus on performance analysis for large jobs than on small jobs, but that need not always be true either.

                 

                Regarding maintenance. This too depends on whom you ask. If you ask your Informatica administrator whether (s)he prefers to have deployments packages to contain anything outside of Informatica software, (s)he will in many cases reply, "the less distinct systems are involved, the better". If you ask a DBA, this DBA will probably argue differently. It always depends on the point of view of that person you're asking.

                For example, tlook at me. I like programming in C, but I will never suggest to a customer to have any libraries implemented in the C language if that can be avoided with reasonable effort. It's always something to deploy outside of PowerCenter / IICS, and deployment processes become ever more complex and error-prone the more systems you involve. I've had my share of fun with such little details more than often enough. Honestly.

                 

                You see, we can always find arguments in pro and in con. And depending on the point of view we sometimes can even use the same argument for different results.

                 

                Having written all that, I am by no means experienced with IICS. I am a PowerCenter guy. So many (if not most) of my points may not hold true for IICS, I really don't know. But I fear I am right with everything (in particular that things are simply never easy in today's environments).

                 

                Regards,

                Nico

                • 5. Re: IICS SQL vs SQL Stored Proc
                  Jonathan Kim Seasoned Veteran

                  Hi Nico,

                   

                  Thanks for your points.  I do understand them.

                   

                  Right now, what I need to figure out is how many API calls will be made for 10 different sql statements in IICS mapping which update 100,000 rows in a db tale.  Will it be 10 x 100,000 which is 1 million API calls or just 10 API calls (1 for each statement regardless of # of rows to be updated)?

                  • 6. Re: IICS SQL vs SQL Stored Proc
                    Nico Heinze Guru

                    As of my understanding (again, I'm no IICS guy, I only know PowerCenter halfway well) each statement will be executed as one single SQL statement. So, if within your mapping flow you issue 10 UPDATE statements, these will be executed as 10 statements (each of which may affect 0, 1, 100 millions, or 60 zillion records) and no more.

                    The sizes of the result sets e.g. for huge SELECT statements is a completely different story...

                     

                    But I fear in order ot get an authoritative reply you will have to raise a service request at Informatica Global Customer Support (GCS).

                     

                    Regards,

                    Nico

                    • 7. Re: IICS SQL vs SQL Stored Proc
                      Jonathan Kim Seasoned Veteran

                      Hi Nico,

                       

                      Your assessment with PowerCenter makes sense.  I am hoping it would be the same with IICS, but I have a little concerned that IICS may issue more API calls.  I will rais a support case with GCS and see what they say.  This is the determining factor.

                      • 8. Re: IICS SQL vs SQL Stored Proc
                        Nico Heinze Guru

                        Please let us know what information you get from them. This is an interesting topic in general, and in case I get in intense touch with IICS soon (quite possible), this might become important for us as well.

                         

                        Thanks and regards,

                        Nico