How to Improve Query Performance When Retrieving Data from Notes & Invoice History

Release Date: 12/12/17

Q - I am trying to get all SHIP notes with related invoice information for a specific customer for a specific day. The speed of my attempts has been okay, but it feels like it could perform much faster. How can the below be improved? Thanks!

SELECT NOTE_FILE_NAME,NOTE_CREATE_DATE,NOTE_TYPE,NOTE_TOPIC,NOTE_CONTENT_1,
NOTE_CONTENT_2,NOTE_CONTENT_3,NOTE_CONTENT_4,NOTE_CONTENT_5,NOTE_CONTENT_6,
NOTE_CONTENT_7,NOTE_CONTENT_8,NOTE_CONTENT_9,NOTE_CONTENT_10,INV_NO,
INV_PURCHASE_ORD_NO,INV_CUSTOMER_NO,INV_BILL_TO_NAME,INV_SHIP_TO_NAME,
INV_SHIP_VIA_CODE,INV_DATE
FROM NOTE_INV_VIEW WHERE
INV_CUSTOMER_NO = '651675' AND
NOTE_TYPE = 'SHIP' AND
NOTE_FILE_NAME = 'CPINVHDR' AND
NOTE_CREATE_DATE = 20171211


A - Try the following the following SQL statement instead:

SELECT NOTE_FILE_NAME, NOTE_CREATE_DATE,NOTE_TYPE,NOTE_TOPIC,NOTE_CONTENT_1,
NOTE_CONTENT_2,NOTE_CONTENT_3,NOTE_CONTENT_4,NOTE_CONTENT_5,NOTE_CONTENT_6,
NOTE_CONTENT_7,NOTE_CONTENT_8,NOTE_CONTENT_9,NOTE_CONTENT_10,INV_NO,
INV_PURCHASE_ORD_NO,INV_CUSTOMER_NO,INV_BILL_TO_NAME,INV_SHIP_TO_NAME,
INV_SHIP_VIA_CODE,INV_DATE
FROM CPINVHDR, NOTES
WHERE INV_CUSTOMER_NO = '651675'
AND NOTE_FILE_NAME = 'CPINVHDR'
AND NOTE_FILE_REF_NO = RIGHT(CONCAT('000000',CONVERT(INV_NO,SQL_CHAR)),6)
AND NOTE_TYPE = 'SHIP'
AND NOTE_CREATE_DATE = 20171211;

I changed the SELECT statement in the following way:

  1. I don't use NOTE_INV_VIEW in this case because In that view we join NOTES (on the left) to CPINVHDR (on the right).  But in this case, the best strategy is to filter on CPINVHDR first before it joins with NOTES.  Therefore, we do not use NOTE_INV_VIEW in this case.
  2. I manually joined CPINVHDR and NOTES in this case by putting CPINVHDR on the left, NOTES on the right. This causes PSQL to process the CPINVHDR table first.
  3. In the where clause, I started with filtering on INV_CUSTOMER_NO, which is part of the alternate index for CPINVHDR, so this will cause PSQL to go through CPINVHDR to find a small subset of records where INV_CUSTOMER_NO = ‘651675’ first before it joins with the NOTES table.

So the lessons from this example are: (A) Sometimes you need to perform your own join instead of using the existing view to get the best performance, and; (B) you need to form a strategy on how to use index to speed up your join.

Note that you can also use the following SQL SELECT statement, but the performance will not be good:

SELECT NOTE_FILE_NAME, NOTE_CREATE_DATE,NOTE_TYPE,NOTE_TOPIC,NOTE_CONTENT_1,
NOTE_CONTENT_2,NOTE_CONTENT_3,NOTE_CONTENT_4,NOTE_CONTENT_5,NOTE_CONTENT_6,
NOTE_CONTENT_7,NOTE_CONTENT_8,NOTE_CONTENT_9,NOTE_CONTENT_10,INV_NO,
INV_PURCHASE_ORD_NO,INV_CUSTOMER_NO,INV_BILL_TO_NAME,INV_SHIP_TO_NAME,
INV_SHIP_VIA_CODE,INV_DATE
FROM CPINVHDR, NOTES
WHERE INV_CUSTOMER_NO = '651675'
AND NOTE_FILE_NAME = 'CPINVHDR'
AND CONVERT(NOTE_FILE_REF_NO,SQL_NUMERIC) = INV_NO
AND NOTE_TYPE = 'SHIP'
AND NOTE_CREATE_DATE = 20171211;

The reason this SELECT statement will not perform as well is because when the system joins from CPINVHDR to NOTES, it will not able to use the NOTE_FILE_REF_NO index due to the "CONVERT" function.  As a result, it has to read through all the CPINVHDR notes and that is much more data to process and thus slows down the procedure.  So the most important takeaway is that you need to understand how to help influence the PSQL relational engine to use the index so it can complete the operation quickly by reading through the smallest number of records.

EMK

Pervasive PSQL

  1. Btrieve Error Codes 001 - 199
  2. Btrieve Error Codes 3000 - 3099
  3. Btrieve Error Codes 3100 - 3199
  4. PSQL Version Required by Each Elliott Version
  5. Do I Need to Change PSQL Server Engine Default Parameters After Installing It?
  6. New Elliott PSQL Server Processor and RAM Suggestions
  7. Can I Dynamically Adjust Elliott / PSQL 11 Server Memory?
  8. Received "Your Computer Does Not Have PSQL 10 or 11 Client " Even though PSQL Client Is Just Installed
  9. Btrieve Error 161 on Password File When Starting Up Elliott
  10. Problems with Using Pervasive Rebuild Utility on APOPNFIL and AROPNFIL Tables
  11. Security Issue with Installing PSQL Client Remotely on User's Workstation
  12. PSQL and Distributed File System (DFS)
  13. How Do I Turn on PSQL Relational Engine Security?
  14. An Example of Debugging NOTE_ORD_VIEW PSQL Expression Evaluation Error
  15. Btrieve Error 025 on COP Open Order by Salesman Report
  16. What Is the *.^01 File for My PSQL Btrieve Table?
  17. Suggested Files to be Monitored by Audit Master
  18. Pervasive Backup Agent Is Not Compatible with Creating Work Files
  19. Hardware Recommendations for Your PSQL Database Server
  20. How to Optimize SQL SELECT Statement When Retrieving Data from Invoice History
  21. New User-Defined Functions in Elliott DDF
  22. How to Improve Query Performance When Retrieving Data from Notes & Invoice History
  23. How to Retrieve Tracking Number for an Order from Notes
  24. Actian PSQL Not Started Automatically After Server Reboot
  25. Create a New Database in the PCC for Relational Engine Access
  26. Slow PSQL Relational Engine Performance
  27. IPV6 May Cause Problem for PSQL 11 Relational Query
  28. DDF Files in DATA Folder May Confuse PSQL
  29. What to Do When PSQL 11 License Is Disabled
  30. Quick Installation Guide for Audit Master
  31. Quick User Guide for Audit Master

Feedback and Knowledge Base