Dummy Select to SQL table takes long to run.

[mok]

When you execute a select statement to access SQL table, SSMS takes a hit in performance specifically when the table contains million of record.
Example of a simple select statement. Variable CUSTMR$ is NULL
SELECT CUSTMR$ from myTable BEGIN CUSTMRFIND$ END CUSTMRFIND$+$FE$
In reviewing SQL representation of this PVX line, I get the following..

SELECT * FROM myTable WHERE (CUSTMR IS NULL AND CREDAT IS NULL AND CRETIM IS NULL) AND COMPANY='11'
SELECT * FROM myTable WHERE (CUSTMR IS NULL AND CREDAT IS NULL AND CRETIM IS NOT NULL) AND COMPANY='11' ORDER BY CUSTMR,CREDAT,CRETIM
SELECT * FROM myTable WHERE (CUSTMR IS NULL AND CREDAT IS NOT NULL) AND COMPANY='11' ORDER BY CUSTMR,CREDAT,CRETIM
SELECT * FROM myTable WHERE (CUSTMR IS NOT NULL) AND COMPANY='11' ORDER BY CUSTMR,CREDAT,CRETIM

Is there a parameter to set where the line will ensure selection is only equal to the value of my variable.
If not, is there a solution other than replacing all programs where the above condition is found.

[Devon Austen]

Try

SELECT CUSTMR$ from myTable WHERE CUSTMR$=CUSTMRFIND$

A few other notes. The BEGIN and END you are using a defining a begin key and an end key. My guess is your key for myTable is not defined in PxPlus as just CUSTMR$ but has other segments. If you want to use BEGIN and END it is best to make sure they match the key definition even if you use something like BEGIN CUSTMR$+$00000000000000000000$. Pxplus 2017 (v 14) included improved logic for generating more optimal SQL. If using an older version of PxPlus consider upgrading.

[mok]

Unfortuntely, I have an entire system where this format of select is used in many programs and most of the files contain primary key comprised of multiple fields. I was hoping there would have been a simpler solution when accessing MS-SQL tables. Similarly, I have these lines of codes which are performed from multiple programs. It is a common subroutine but execute the same way as reported in this post. I am looking for a simple solution other than replacing the subroutine to use SQL  syntax, Can you advise in this. Here are the lines.

43000 NOTES_EXIST:! ^1000 Check for existing customer notes"
43010 SETERR NOTES_EXIST_EXIT
43020 IF NOT(SUNOT1) THEN LET SUNOT1=UNT; OPEN (SUNOT1,IOL=*,REC=SUNOT1$)%C$+"sunot1"
43030 LET BTN_CUSNOT.CTL'TEXT$=MSG("notes.4"),BTN_CUSNOT.CTL'TIP$=MSG("notes.6"),BTN_CUSNOT.CTL'MSG$=MSG("notes.7")
43040 READ (SUNOT1,KEY=%CUSNOT_KEY$,DOM=*NEXT)
43050 LET SUNOT1_K$=KEY(SUNOT1,END=*RETURN)
43060 IF MID(SUNOT1_K$,1,%CUSLEN)=%CUSNOT_KEY$ THEN LET BTN_CUSNOT.CTL'TEXT$=MSG("notes.5"),BTN_CUSNOT.CTL'TIP$=MSG("notes.8"),BTN_CUSNOT.CTL'MSG$=MSG("notes.9")
43070 NOTES_EXIST_EXIT:
43080 EXIT

Thanks

[Devon Austen]

The only way to possibly improve performance without modifying any program would be to try a newer version of PxPlus which has improved optimization logic and see if that improves performance.

Other then that you would need to modify programs so that PxPlus knows the key definition of the table. Use KEY=KeyFld1,KeyFld2:D,KeyFld3 in the OPT= on the open. Also what I mentioned before about matching the key size and/or using WHERE in the select.

[mok]

Thanks.