Power

 View Only
Back to discussions
Expand all | Collapse all

Making faster ODBC resquests from .NET to Power 8

  • 1.  Making faster ODBC resquests from .NET to Power 8

    Posted Thu April 13, 2023 09:20 AM

    Hi everyone, I found this community today and I like it.  My issue is related to the duration of the execution of some stored procedures from .NET to DB2.  I think that we can improve the setup of the ODBC or modify the connection string properties to make it faster.  
    Do you have any ideas to help? I will thank forever.


    Pablo



    ------------------------------
    Pablo Guzmán Barberán
    ------------------------------


  • 2.  RE: Making faster ODBC resquests from .NET to Power 8

    Posted Thu April 13, 2023 07:39 PM

    Hi Pablo,

    you can tune your Db2 for i driver and improve response time, but SP execution time is related to the database.
    If you are using the Access Client Solution for i ODBC driver you can change a couple of values to get faster response time:

    ExtendedDynamic=1
    DefaultPkgLibrary=<Library Name>
    BlockSizeKB=256 (default) - This can improve data retrieval when fetching large datasets
    LazyClose=1
    QueryOptimizeGoal=*ALLIO or *FIRSTIO , depending your needs
    ForceTranslation=1

    Check you have tracing disabled (parameter Trace)

    Beyond ODBC:
    You can also adjust your QAQQINI on your system
    Changing default memory pool for QZDASOINIT jobs to a Shared Pool could improve performance
    Use prepared statements on your code.

    Good luck!




    ------------------------------
    Diego KESSELMAN BARRIONUEVO
    General Manager
    ESSELWARE Soluciones, SA de CV
    CDMX DIF
    ------------------------------

    Original Message


  • 3.  RE: Making faster ODBC resquests from .NET to Power 8

    Posted Fri April 14, 2023 06:46 AM
    Edited by Satid Singkorapoom Fri April 14, 2023 08:23 AM

    Dear Pablo

    What do your stored procedures do ?   Does the procedure contain SELECT and/or UPDATE and/or DELETE statements with WHERE clause ?  Do they do some ORDER and/or GROUP BY and/or join operations?  Are the tables being accessed by your procedures large (large = larger than a few hundred MB)? 

    If all or many of the above, you can improve your procedure's run-time performance by creating indexes on tables larger than 100MB with columns used in WHERE clause, followed by columns used by ORDER and/or GROUP BY and then columns being joined.   This should be easy if there are a few SQL statements involved.

    If there are too many SQL statements for you to handle manually, this can be done in an effective and convenient way if you know how to use DB2 SQL Plan Cache (from IBM ACS) to identify SQL statements used by your stored procedures and display their access plans with Visual Explain and look for Index Advise in each long running statement. 

    Start from IBM i ACS Database section --> SQL Performance Center. Then select the button Show Statements... in the new window that appears. 

    Then use the filter in the left pane to get to statements from your stored procedures using the user name that you log on to the ODBC link to DB2i.  You can also specify table names for better filtering.  Use as many filters as you can and then click Show button and close the left pane.


    Then you sort in descend order all the entries by Average Processing Time and focus on entries of the job named QZDASOINIT that serves your ODBC connection (in Job Name column).  Then right click each long running statement (longer than 1 sec.) and select Visual Explain from the pop-up menu that appears. 


    In the new Visual Explain window, click on EACH TABLE SCAN icon and look in the right pane for Index Advised Information as shown in a sample below. 


    Or you can click the menu bar icon with "footprints" for Index Advisor window.  



    ------------------------------
    Education is not the learning of facts but the training of the mind to think. -- Albert Einstein.
    ------------------------------
    Satid S.
    ------------------------------

    Original Message


  • 4.  RE: Making faster ODBC resquests from .NET to Power 8

    Posted Fri April 14, 2023 09:42 AM

    Hi everyone, thanks Diego and Satid for your suggestions. I will try them and i will inform you the results. I'll hope that all together will achieve the best performance.
    Maybe i forgot to tell you that we have 2 types of sp: SQL statements and others than excute a rpg program.  
     



    ------------------------------
    Pablo Guzmán Barberán
    ------------------------------

    Original Message


  • 5.  RE: Making faster ODBC resquests from .NET to Power 8

    Posted Fri April 14, 2023 04:18 PM

    I forgot also to say a particular scenario that occurs in our enviroment. We sent a stress test to our .net web service with 500 requests in 1 minute.  We realize that the application "stops" for 3 seconds at the 100th iteration and then works again but with a long average time of response. What can be?
    By the way, this is our current ODBC Configuration:


    And the connection string is:
    "DSN=" & Trim(Fila("txDSN")) & ";User Id=" & Trim(txUsuario) & ";Password=" & Trim(txClave) & ";Pooling=false;DataCompression=True"



    ------------------------------
    Pablo Guzmán Barberán
    ------------------------------

    Original Message


  • 6.  RE: Making faster ODBC resquests from .NET to Power 8

    Posted Fri April 14, 2023 04:54 PM
    Edited by Diego KESSELMAN BARRIONUEVO Fri April 14, 2023 04:58 PM

    Pablo,

    a couple of things:

    * Can you activate Lazy Close and deactivate "Use blocking with a fetch of 1 row" ?
    * Are you creating a session on each request or just connect one time and keep this session opened? The login process can create delays and you are using a job entry on the job table each time you log in.
    * Are you using a connection pool? 
    * Are you sure this is the solution you need? When you have many request the usual solution is to use a Message Queue software (MQ) or something similar to manage queued tasks. The system can manage 500 requests per minute using QZDASOINIT, but you need to make some tuning or even increase resources to get the response time you need, and this will scale with your requests. 



    ------------------------------
    Diego KESSELMAN BARRIONUEVO
    General Manager
    ESSELWARE Soluciones, SA de CV
    CDMX DIF
    ------------------------------

    Original Message


  • 7.  RE: Making faster ODBC resquests from .NET to Power 8

    Posted Fri April 14, 2023 09:04 PM
    Edited by Satid Singkorapoom Fri April 14, 2023 09:48 PM

    Dear Pablo

    >>>> Maybe i forgot to tell you that we have 2 types of sp: SQL statements and others than excute a rpg program.  <<<<

    Do your RPG stored procedures contain embedded SQL?  If so, what I described in my first post applies.  If no, it means RPG "native I/O" is used and this means that RPG programmer needs to identify and create keyed logical files (based on the same principle I described earlier for SQL) and modify RPG code to use those keyed LFs for data access with good performance. Ask your RPG programmers whether or not they implement the use of keyed LF (based on the principle I described earlier for SQL) in their RPG codes.   

    Using RPG native IO for data access is like driving a car with "manual gear", the programmer needs to implement every proper step of data access for good performance which usually involves the use of keyed LFs (still use the same principle I described to identify and create keyed LFs). Using SQL is like driving an "automatic gear" car, you identify and create useful indexes using DB2i tools and DB2 SQL engine will use them automatically. 

    You should encourage your programmer to use embedded SQL or even create pure SQL stored procedures when possible, unless your RPG programmer is already an RPG expert.

    I noticed from your screen shots that you still use ODBC driver from the unsupported IBM i Access for Windows.   If possible, you should use ODBC driver from IBM i ACS instead.


    >>>> We sent a stress test to our .net web service with 500 requests in 1 minute.  We realize that the application "stops" for 3 seconds at the 100th iteration and then works again but with a long average time of response. What can be?  <<<<

    First of all, set a proper size for TCP/IP send and receive buffer with CHGTCPA. I suggest a size of 204800 (200KB). Do the same in Windows web server which should be done from the LAN card device driver (Windows Device Manager?).

    If the problem still persists, do you use a tool to generates the transactions of the stress test? If so, that 3-second freeze and transaction response time degrade may be from the tool. If you are sure the stress test tool is not the cause of this problem, then you may need to do a communication trace during such a stress test and analyze it with WireShark tool to possibly get the answer.    

    How many max concurrent users do you expect to use this transactions in actual workload?  If a lot, you should consider using a connection pool.  The use of message queue has been very rare in my part of the world mainly because it requires programmers to write different style of codes from using SQL with connection pool.   

    In my experience, at 500 requests per minute, you may not see much performance improvement with connection pool. But if you are looking at 1,000 requests per minute or more, connection pool may likely deliver some overall performance improvement.  This also depends on the performance of front-end and back-end servers' hardware.   What is your IBM i server model and with how many active CPU cores and how much memory and what type of disk are being used?  What about Windows web server HW that makes ODBC requests to IBM i? 

    Please read my "case 3" article to get an overall idea about your case (read the 2nd case about half way down the entire article below figure 5 - the first case is irrelevant to you now) : Guru: IBM i Experience Sharing, Case 3 – When Performance Issues Come From Without at  https://www.itjungle.com/author/satid-singkorapoom/

    Feel free to ask any further questions. 

    BTW, in the future, please ask IBM i-related questions in a group named IBM i Global at https://community.ibm.com/community/user/power/communities/community-home?CommunityKey=f0246bc4-08f3-43c5-a7f8-b6a64d387894.

    ------------------------------
    Education is not the learning of facts but the training of the mind to think. -- Albert Einstein.
    ------------------------------
    Satid S.
    ------------------------------

    Original Message


  • 8.  RE: Making faster ODBC resquests from .NET to Power 8

    Posted Mon April 17, 2023 10:08 AM

    Hi Satid, a couple of questions for you:
    "I noticed from your screen shots that you still use ODBC driver from the unsupported IBM i Access for Windows.   If possible, you should use ODBC driver from IBM i ACS instead."
    Which of these i have to use?


    When youu say that We have to use a pool connection, where i have to locate that property?



    ------------------------------
    Pablo Guzmán Barberán
    ------------------------------

    Original Message


  • 9.  RE: Making faster ODBC resquests from .NET to Power 8

    Posted Mon April 17, 2023 08:18 PM
    Edited by Satid Singkorapoom Mon April 17, 2023 11:19 PM

    Dear Pablo

    I did a Google search with "ibm i acs odbc" and found the answer here : https://www.ibm.com/docs/en/i/7.4?topic=driver-specify-odbc-data-source     I would select the 2nd one IBM i Access ODBC Driver. 
      


    As for connection pool, it is a feature of the web/app serving engine product you run in Windows. Which product are you using?  Your web serving engine may use different wording for this feature, such as persistent connection.   If you use Microsoft product, use a hot link provided in this web page to get more information on MSDN Driver Manager Connection Pooling : https://www.ibm.com/docs/en/i/7.3?topic=details-connection-pooling

    Are you aiming to handle more than 1,000 requests per minutes?  Connection pool is not mandatory and you do not have to use it if the clients' request workload to IBM i is not a lot.



    ------------------------------
    Education is not the learning of facts but the training of the mind to think. -- Albert Einstein.
    ------------------------------
    Satid S.
    ------------------------------

    Original Message


Related Content