Netezza Performance Server

 View Only

  • 1.  3rd Party Tool Using JDBC INSERT

    Posted Mon July 25, 2022 03:17 PM
    Need some advice here

    I'm working with a 3rd party tool that will insert data using JDBC connection into Netezza using singleton INSERT statements. 
    I know that's not ideal,  but it's the only way this 3rd party tool can pass through the data.  
    For example,  5,500 rows takes about 4min to insert.  

    What are some practices for improving this throughput?
    I tried setting up an external table,  but then learned it can't handle singleton INSERT statements either as each one submitted pre-truncates the file. 
    I also added "AutoCommit=false" to the JDBC connection string,  but doesn't seem to impact the performance.  

    Appreciate any tips/tricks for handling this,  Thank You!

    ------------------------------
    Greg Brown
    ------------------------------

    #NetezzaPerformanceServer


  • 2.  RE: 3rd Party Tool Using JDBC INSERT

    Posted Tue July 26, 2022 02:22 AM

    Howdy

    In general Netezza doesn't like single row inserts/updates it was designed to work on queries for millions/bilions of rows. Many times those single row insert will take as much as inserting 1000 rows... 

    If you are running it in separate sessions it will take even longer. 

    Those single row inserts are running in parallell or sequentionally? Maybe just for this app user on scheduler rules and set priority to high (or even critical)? What is the background "noise" (how much of other processing is running)? 



    ------------------------------
    Adam Matusewicz
    ------------------------------

    Original Message


  • 3.  RE: 3rd Party Tool Using JDBC INSERT

    Posted Tue July 26, 2022 10:17 AM

    I think you're kind of stuck given that you are doing singleton inserts. Have you tried getting timings using an IDE to see if it is equivalent with using the 3rd party tool? Singleton INSERTS are the worst. If the box is completely idle, you may see better performance, but if there is utilization, you're likely waiting for other snippets to complete before it gets around to applying your insert. If there is a way you can avoid singleton inserts by using nzload or external tables, your throughput will improve remarkably.

     

     

     

    Daniel Rolls

    Solutions Architect, Analytics / BI / Information Management

     

     

    CONFIDENTIALITY NOTICE: THIS MESSAGE IS CONFIDENTIAL, INTENDED FOR THE NAMED RECIPIENT(S) AND MAY CONTAIN INFORMATION THAT IS (I) PROPRIETARY TO THE SENDER, AND/OR, (II) PRIVILEGED, CONFIDENTIAL, AND/OR OTHERWISE EXEMPT FROM DISCLOSURE UNDER APPLICABLE STATE AND FEDERAL LAW, INCLUDING, BUT NOT LIMITED TO, PRIVACY STANDARDS IMPOSED PURSUANT TO THE FEDERAL HEALTH INSURANCE PORTABILITY AND ACCOUNTABILITY ACT OF 1996 ("HIPAA"). IF YOU ARE NOT THE INTENDED RECIPIENT, OR THE EMPLOYEE OR AGENT RESPONSIBLE FOR DELIVERING THE MESSAGE TO THE INTENDED RECIPIENT, YOU ARE HEREBY NOTIFIED THAT ANY DISSEMINATION, DISTRIBUTION OR COPYING OF THIS COMMUNICATION IS STRICTLY PROHIBITED. IF YOU HAVE RECEIVED THIS TRANSMISSION IN ERROR, PLEASE (I) NOTIFY US IMMEDIATELY BY REPLY E-MAIL OR BY TELEPHONE AT (855.472.9822), (II) REMOVE IT FROM YOUR SYSTEM, AND (III) DESTROY THE ORIGINAL TRANSMISSION AND ITS ATTACHMENTS WITHOUT READING OR SAVING THEM. THANK YOU.

    -DaVita Inc-




    Original Message