14 Replies Latest reply on Jul 8, 2020 1:38 AM by Vlad Ponomarenko

    Pass through partition in SQ having SQL Override

    Akilan Chandrasekaran New Member

      Hello,

       

      I believe, we can't use (or no use of) pass through partition if the SQ having SQL override query. Can anyone confirm?

       

      I tired to configure couple of pass through partition points in SQ which has override but it seems to be the same SQL override query is used in both partition SQL query which I believe going to return the same result set in both the partition point unless we manually change the SQL logic in each points.

        • 1. Re: Pass through partition in SQ having SQL Override
          David Lopez Cruz Seasoned Veteran

          Hi Akilan,

          I found this KB539343:

          https://kb.informatica.com/faq/7/Pages/21/539343.aspx

           

          Where it says:

          "If SQL Override Query has a 'WHERE' condition, session cannot be enabled for dynamic partitioning. This is a limitation on the database site.

          As a workaround, use a Filter transformation after the Source qualifier to add the filter conditions if dynamic partitioning is enabled."

           

          Does it make sense in your case?

           

          Regards,

          David

          • 2. Re: Pass through partition in SQ having SQL Override
            Akilan Chandrasekaran New Member

            Thanks David. The article stress on the dynamic partitioning whereas my scenario on manual partitioning in which specifically pass through partitioning,

            • 3. Re: Pass through partition in SQ having SQL Override
              Vlad Ponomarenko Guru

              Hi Akilan,

               

              Pass throw patriition on SQ is used when you must read a lot of data.Therefore, the Reader thread with a high percentage of employment is the bottleneck in the session.

              Is it your case?

               

              Regards, Vlad

              • 4. Re: Pass through partition in SQ having SQL Override
                Akilan Chandrasekaran New Member

                Yes Vlad, that is the case. Nearly 800 Millions of records needs to be fetched from source. I just need to increase the throughput of the session without changing any SQL logic exist in SQ (Source Qualifier).

                • 5. Re: Pass through partition in SQ having SQL Override
                  Asmita Kesarwani Active Member

                  Akilan,

                   

                  Please try to refer the below document regarding the Partitioning in the Database:

                  Database Partitioning with One Source

                   

                  Does that help you?

                   

                  Thanks,

                  Asmita

                  • 6. Re: Pass through partition in SQ having SQL Override
                    Nico Heinze Guru

                    Sorry for intervening here, but are you absolutely sure that the SQ is the bottleneck in your session?

                    Did you check the thread statistics in the session log to make sure that the reader thread is 100% busy while transformation and writer threads are far less busy?

                    Only then can you safely assume that you have a so-called source bottleneck.

                    Otherwise I would suggest that we first take a look at the session log statistics before deciding which optimisation route to go.

                     

                    Regards,

                    Nico

                    • 7. Re: Pass through partition in SQ having SQL Override
                      Akilan Chandrasekaran New Member

                      I'm not sure how exactly to check for thread statistics, but the thing is source reader taking 20 hours to fetch the 400+ million records with the throughput of ~4500+ rows per second which made me to realize there is source bottle neck, also the target completes once reader completes. So, pretty sure source SQL is the culprit.

                      • 8. Re: Pass through partition in SQ having SQL Override
                        Nico Heinze Guru

                        If possible, can you please switch the Override Tracing for the session to Verbose Initialization? This will make the session print quite a bit of statistics to the session log. Afterwards you please post the session log (as long as there are no errors, no actual data will be written to the session log) here, and we'll take a look at it.

                         

                        The fact that a source thread needs 20 hours to read 400 millions records does NOT automatically mean that this is a source bottleneck. For example, if someone performs a Cartesian join between 250,000 records and 50,000 records using a Joiner transformation, then the session will needs hours (and terabyte of disk space) to create the cache files.

                        Or the source thread is busy for only 0.5% of its total runtime, then chances are that either the network between the source system and PowerCenter is the culprit or that the source DB is swapping itself to death while trying to retrieve the source data.

                         

                        Do you use a manually written source query instead of a query generated by PowerCenter? If so, could you please post the SELECT statement here? It MAY be that after a few looks at the SQL statement we can give you one or the other hint how to improve it.

                        Not to forget that manually written SELECT statements often are not at all performing really well. It depends on the person who has written it, on the DB optimiser, and many other things.

                         

                        Regards,

                        Nico

                        • 9. Re: Pass through partition in SQ having SQL Override
                          Asmita Kesarwani Active Member

                          Akilan,

                           

                          Please refer the below article which explains you Detecting Performance Bottlenecks in PowerCenter sessions. With the help of this you will be able to figure out where the problem is:

                          15156

                           

                          Also, if you see the issue is while reading , it's necessary to measure read performance outside of PowerCenter to determine if reading data from the source is the bottleneck.

                           

                          So, please try if reading the same data outside Informatica takes equal amount of time. If so, you can probably check at the database end regarding any issue.

                           

                          Thanks,

                          Asmita

                          • 10. Re: Pass through partition in SQ having SQL Override
                            Akilan Chandrasekaran New Member

                            Thanks Nico for your insights, I will check by changing session log to Verbose initialization. And it is manually written query, it is a simple join with few tables, no fancy complex logic involved.

                             

                            However, the suggestions are aimed at changing logic. What I really wanted to in this time crunch moment is without any kind of code change, is there a way of just changing the configuration will result in the performance improvement or not.

                             

                            My initial question is 'can we use pass through partition if the SQ having SQL override query'. I tired looking into the infa document but there is no clear cut information.

                            • 11. Re: Pass through partition in SQ having SQL Override
                              Vlad Ponomarenko Guru

                              Hi Akilan,

                              You did not specify the used DB. If it would be Oracle DB I can assume 2 main reasons why you have a slow download speed - 4500 rows per sec.
                              The first is your row has very big length, For example, some fields has CLOB, VARCHAR2 (4000) and so on datatypes. I agree that SQ partition will be better.

                               

                              Further, and I believe it is more realistic that your query uses indexes. This execution plan allows you to quickly get the first row, and the developer considers this plan very good. But fetching all millions of rows takes a very long time.

                              I had a project with a similar case. Select using the index took more than 20 hours. Performance was like yours - 2000-4000 lines per second. I used the hints FULLY, USE_HASH, PARALLEL, and the time was reduced to 3 hours. Yes, the first row was not returned immediately, on different servers it ranged from 5 to 60 minutes, but then the speed was 40,000 rows per second.

                              In short, fetching with join of several tables and returning a large amount of data is much faster if it reads all the tables completely without using indexes and uses a Hash join plan.

                               

                              Regards, Vlad

                              1 of 1 people found this helpful
                              • 12. Re: Pass through partition in SQ having SQL Override
                                Akilan Chandrasekaran New Member

                                Thanks Vlad, this is helpful. This is a oracle DB and I rule out the first option as there is no CLOB or VARCHAR2 of length 4k.

                                Second, as per the XPLAN there is indeed hash join was used to fetch the records. Probably, I think I should think some other HINT or mechanism.

                                • 13. Re: Pass through partition in SQ having SQL Override
                                  Vlad Ponomarenko Guru

                                  Hi Akilan,

                                  It happens that the xplan that you see on the client is different from the real execution plan in runtime.  Check how your request is actually being executed on the server and with what plan.  You may need to contact your administrator and check what delays are occurring (session waits events).

                                  Usually, the select with hash join differs little from the select from the similar table except Initial delay.

                                  I often create a test table using CREATE TABLE AS AS SELECT <YOUR QUERY> with about 1-2 million records and testing the speed of sampling from it.

                                  • 14. Re: Pass through partition in SQ having SQL Override
                                    Vlad Ponomarenko Guru

                                    Hi Akilan,

                                     

                                    There is one more point worth paying attention to.
                                    If your query is executed with Hash-join plan you can see in the session log that first record from database is returned not immediately. This pause can be very big. Monitor shows always average rate of fetching rows, so this delay is included to the time. The real rate can be much more higher.

                                    I determine the real speed as follows.

                                    I check two consecutive values of the SQ Applied Rows and divide their difference by 10. This is the time in seconds on default of counters refreshing.

                                    And still, it would be great to see your session log.

                                     

                                    Regards, Vlad