Show Posts
This section allows you to view all posts made by this member. Note that you can only see posts made in areas you currently have access to.
Pages: [1]
1
Programming / Re: Dummy Select to SQL table takes long to run.
« on: August 11, 2020, 04:20:14 PM »
Thanks.
2
Programming / Re: Performance difference between READ and WRITE RECORD to ODBC channel
« on: August 11, 2020, 04:18:11 PM »
So, is it correct to imply that if I want to see performance gain, I should change all my READ (IBIS_DB'ODB, KEY="!" to WRITE RECORD (IBIS_DB'ODB)
3
Programming / Performance difference between READ and WRITE RECORD to ODBC channel
« on: August 11, 2020, 10:21:25 AM »
I have these two lines of code accessing records from SQL table via ODB channel. Channel opened in this case is the same. Why is the READ slower to respond than the WRITE RECORD. Using READ sometimes result in error 0 because of the length of time result comes back to the current program but WRITE RECORD has not yet resulted in error 0.
READ (IBIS_DB'ODB,KEY="!"+SQL$)
WRITE RECORD(IBIS_DB'ODB)SQL$
Thanks
READ (IBIS_DB'ODB,KEY="!"+SQL$)
WRITE RECORD(IBIS_DB'ODB)SQL$
Thanks
4
Programming / Re: Dummy Select to SQL table takes long to run.
« on: August 11, 2020, 09:57:00 AM »
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
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
5
Programming / Dummy Select to SQL table takes long to run.
« on: August 06, 2020, 03:44:21 PM »
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.
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.
Pages: [1]