Miguel:
Ordinarily I would suggest that VM performance is always unpredictable and moving from one OS version to another has also impacted performance, however, if inner joins are consistently faster and outer joins are consistently slower, that's not caused by the VM or the difference in the OSes. The query plans are the same so the data distributions are not to blame. If inner queries on the same tables using the same indexes are faster then it's not fragmentation on one server versus the other. The only things left are two:
- Since outer joins may write temp tables which inner joins are less likely to, there will be more non-cached IO, so it might be slower storage. Check the onstat -g ioh output from both servers after bouncing them and running the outer join query two or three times to see the service times on the chunks holding the tables and any temp tables.
- HCL screwed something up in v14.10.
Art
------------------------------
Art S. Kagel, President and Principal Consultant
ASK Database Management Corp.
www.askdbmgt.com------------------------------
Original Message:
Sent: Fri July 23, 2021 09:04 AM
From: Miguel Carbone
Subject: Informix 14 - ALTER JOIN -
Paul, We are using VMWARE... in both enviroments....
------------------------------
Miguel Carbone
Original Message:
Sent: Fri July 23, 2021 08:45 AM
From: Paul Watson
Subject: Informix 14 - ALTER JOIN -
VMs in the mix somewhere ?
Original Message:
Sent: 7/23/2021 8:42:00 AM
From: Miguel Carbone
Subject: RE: Informix 14 - ALTER JOIN -
Jon,
Yes, we are using the same configuration, we are using DIRECT IO, 34 CPUVP´s (same before) - 3 AIO VPs (same before)... Operational System was Red Hat Linx 6.8 and Now RedHat 8.1.
------------------------------
Miguel Carbone
Original Message:
Sent: Fri July 23, 2021 08:34 AM
From: Jon Ritson
Subject: Informix 14 - ALTER JOIN -
Presumably the upgrade was on the same machine.
O/S?
DIRECT_IO in use or not? Was it before? Number of CPU VPs if in use? Number of AIO VPs if not in use?
"OUTERS" will always read more than other queries (well, sort of ... cartesian joins will do loads of reads :) )
------------------------------
Jon Ritson
Original Message:
Sent: Fri July 23, 2021 08:23 AM
From: Miguel Carbone
Subject: Informix 14 - ALTER JOIN -
yes, thats the problem... statistics and execution plan are the same.... the issues is becouse just queries with OUTER JOIN are slower.... others are faster
sorry , did a mistake writing the title... is OUTER JOIN not ALTER JOIN
------------------------------
Miguel Carbone
Original Message:
Sent: Fri July 23, 2021 08:14 AM
From: Paul Watson
Subject: Informix 14 - ALTER JOIN -
You will have checked but ... are the sqexplains identical ?
Cheers
Paul
Original Message:
Sent: 7/23/2021 7:55:00 AM
From: Miguel Carbone
Subject: Informix 14 - ALTER JOIN -
We upgrade server to Version 14.10 FC5 , we are facing a performance issues in queries who uses ALTER JOIN. Comparing in the same machine / same configuration, between verions 12.10 FC14 and 14.10 FC5, queries who uses ALTER join are 4 times slower, others not, could be faster or same. Are there any new behavior in how version 14 deal with ALTER JOIN, any new Parameter to improve this performance.....
------------------------------
Miguel Carbone
miguel@mcsoftware.com.br
SkypeID miguelcarbone
------------------------------
#Informix