Performance V 14 is significantly slower compared to v 11

I had opened a series of support tickets with IBM and we had been battling this issue for months. We are migrating from IDS 11.7 to 14_10 FC7 and had set identical configuration, on both. We changed a little on v14 on the following moved to 3 temp dbspaces from 2, and from 40Gb each to 50Gb each v11 we had all user dbspaces on 8K page size, index dbspaces (for detached indexes), temp dbspaces... System dbspaces (root, physlogs) are on 2K and blob spaces on 16K v14 we left data on 8K pages and index dbspaces moved to 16K. Temp dbspaces on 8K (tested on 4K 8K and 16K and 8K perform better), sys dbspaces on default 2K and blobs on 16K. So the only difference from old to new is Index dbspaces on 16K, We added a bufferpool to account for 16K pages we have close to same bufferpool from v11 to v14 except the additional 16K pages on v14. v11 on rhel6 v14 on rhel8 Huge pages are set the same, as well as semaphores and file descriptors for both systems. Capacity is exactly the same 24Gb ram, 4 cores, and using raw disk for every chunk. Our data pool size is about 700Gb worth of data. Both systems are VMs we have several data pools (3) on v11 as system had grown through time. On v14 we have only 1 huge pool where we all chunks are created for those dbspaces and tried to set 1 chunk per dbspaces to keep contiguous pages. Migrated data over with HPLs, and done LOW stats drop dist, MED dist only and HIGH on specific indexes Dist only. Our reports and data processing loads run almost double the time. Some reports sorting thousands of records using temp areas for sorting run 80% slower, from 3 mins to 5.8 mins. Processes using cursors to load data running from 20 mins to 50 mins. Newest 4GL.ESQL installed on v14 and all 4GL code had been recompiled with no errors. We had tried numerous variables to twitch to get to v11 performance with no luck. I came across this post and followed it, but they are comparing apples to oranges. Here I have apples to apples (the closest I can get) and v14 performs bad compared to OLD v11 Performance of 14.10.FC5 is worse compared to 12.10 is that possible? https://community.ibm.com/community/user/hybriddatamanagement/communities/community-home/digestviewer/viewthread?GroupId=4147&MessageKey=3a5517e6-a8c8-4d11-81b5-b098a4d0fff7&CommunityKey=cf5a1f39-c21f-4bc4-9ec2-7ca108f0a365&tab=digestviewer I can attach any file require for analysis. Thanks ------------------------------ Tereso Garcia ------------------------------ #Informix

Hi Tereso, Your reports run slower. Are you using ORDER BYs or GROUP BYs in your reports that are slower. If that is the case, you can try changing the DS_NONPDQ_QUERY_MEMto a higher value and run the reports again. Do noy make this too big since it applies to all of the sessions. You can modify this parameter without bouncing the engine: onmode -wm DS_NONPDQ_QUERY_MEM=<new value>or onmode -wf DS_NONPDQ_QUERY_MEM=<new value> (modification in memory and in the onconfig file).Hope that this helps -- Cordialement, Regards, Khaled Bentebal Directeur G??n??ral - ConsultiX T??l: 33 (0) 1 39 12 18 00 Mobile: 33 (0) 6 07 78 41 97 Email: khaled.bentebal@consult-ix.fr Site Web: www.consult-ix.fr

I'd say the general answer to your rather general question should and definitely is: no, 14.10 is not slower than 11.x, in many respects it even is considerably faster. And there the hard work of spotting any differences in actual behavior and their - possibly common - root cause(s) begins... but probably not on this forum. If the same load against an identical setup, with only the db server version differing, behaves differently then it should be possible to determine the cause. It only is two computer systems, after all. It is two, right? If so, how identical are they? ------------------------------ Andreas Legner ------------------------------

Hi, Tereso, first - it's very unlikely for the Informix 14.10 to be two times slower compared to 11.70. Therefore you'll need to figure out the cause and address it. I'd start with the basics. Assuming you still have access to both environments (rhel6+11.7 and rhel8+11.4) 1. get lscpu output from each and compare (if you don't have / cannot add lscpu package - get output from "cat /proc/cpuinfo" but that would be somewhat more difficult to compare) 2. find repeatable workload which you can run on both, and which consistently shows 2x difference. In each environment, when there is no other activity , do "onstat -z ; /usr/bin/time -p (workload script) ; onstat -p" Compare items in "onstat -p" outputs. (more latching, more cpu or system tim, etc) In parallel, if you feel like it, you can run "onstat -g ath -r 5" during the test (save to file) and do something simple like: grep sql (ath-file) |cut -c50-64 | sort | uniq -c | sort -nr the "c50-64" part may need to be adjusted for 11.70 These steps, hopefully, will give you some hint where to look next. Alternatively, you can put 11.70 on rhel8 VM (I doubt that it can be installed, you can try, though, but "rsync" or tar with proper switches will likely work) and confirm that it's as fast there as on rhel6 VM ------------------------------ Vladimir Kolobrodov ------------------------------

Tereso: In addition to Vladimir's suggestions, I would also look closely at the two VMs. Is it possible that the new VM was configured with shared virtual cores while the original one that is running v11.70 is configured with physical dedicated cores? Most virtualized sites run multiple VMs on a massive frame running many VMs concurrently. Are the two VMs running on the same frame or different frames (if they are on cloud, they are almost guaranteed to be running on different frames)? You don't know what other load is running on that same frame! If performance is much different depending on the day or time of day, that would point to this problem - that v14.10 is running on a busier frame. Storage: Are the drives configured for the two environments identical in configuration? All RAID10 on both for example versus one on RAID10 and the v14 host connected to RAID5? You don't know what other IO traffic you are competing with for drive bandwidth or pipeline throughput. Were the two VMs configured with the same IO throughput guarantees? I have seen that if server is faster it can push up against the IO guarantee at cloud sites causing the vendor to throttle IO slowing the server down! I'll assume this is an Intel x86-64 environment. Could you have hyper-threading disabled on the v11.70 server (recommended) but enabled on the v14.10 server (not recommended)? These are factors that will make enough difference to explain what you are seeing. Art ------------------------------ Art S. Kagel, President and Principal Consultant ASK Database Management Corp. www.askdbmgt.com ------------------------------

Hi Tereso. If you have slow sorts and joins, it may be faster to have them use file systems - preferably in memory - instead of temp spaces by setting PSORT_DBTEMP in the "oninit" environment. See: https://www.oninitgroup.com/faq-items/ram-disk-and-informix ------------------------------ Doug Lawry Oninit Consulting ------------------------------

Hi Tereso, try to set the onconfig parameter: DIRECT_IO 0x5to speed up the temp dbspace work with direct IO . Best Regards Henri ------------------------------ Henri Cujass leolo IT, CTO Germany IBM Champion 2021 and 2022 ------------------------------

Hello, just adding an important topic here that might give you some help. The default disk IO scheduller on RHEL was changed on version 8. And the new one is not the best oerformatic for Databases. Alexandre Marini

I'd say the task at hand is restoring confidence in v14.10, i.e. understanding and eliminating causes for (negative) differences to older versions under *seemingly* similar or identical conditions and configuration. Once that's accomplished, nothing should keep anyone from exploring all the ways v14.10 can shine even brighter then previous versions ;-) As I said, it can be tedious, but also rewarding. So far we know far too little, regarding type of load as well as resource consumption and limiting factors. If the two systems still can be compared side by side, it should be possible to spot behavioral differences. If not, it had to be determined what are the top limiting factors: first of all, and for every work load separately, is the system cpu, disk or maybe network bound? Then what could be reasons for this within the database server, or outside/underneath. ------------------------------ Andreas Legner ------------------------------

On the specific point of 14.10.xC5 being slower than 12.10, I didn't contribute to that thread as I had nothing to offer at the time. However in some situations it definitely was and we had the load test results to show it. All the issues I am aware of are fixed in 14.10.xC6+, so it probably isn't relevant to your issue. Ben. ------------------------------ Benjamin Thompson ------------------------------

Alexandre: So, how does one change the IO scheduler in RHEL 8 to behave better for Informix? Art ------------------------------ Art S. Kagel, President and Principal Consultant ASK Database Management Corp. www.askdbmgt.com ------------------------------

My impression is that raw disk is faster than coocked disk and direct I/O. Since v11 had been on raw disk, we left the same on v14. Originally we have those 3 temp chunks from same log vol disk on the san, then we split those on individuals log vol, but it made no difference. ------------------------------ Tereso Garcia ------------------------------

I have the impression that raw disk is faster that cooked disk + direct IO. We decided to use raw to have similar results as from v11. Originally the 3 temp partitions were sharing disk on same log vol as the root and physlogs. Then we decided to test splitting them each on its own log vol, but still made not much difference. PErf came slightly faster but not enough to compete with old system ------------------------------ Tereso Garcia ------------------------------

Thanks Art for you input. We have disk setup the same on both systems. In terms of the virtualizer, yes we tested running both VMs on the same virtualizer node to discard any variation on the node or frame if that is what you are referring. For some reason as we move the VMs among nodes we noticed a faster response, but then it goes back to the usual slow situation. We also looked at the VM tuneD profiles to be the same on both. Yesterday we started to test boosting IO by switching the IO scheduler still testing that today and I will report results the next day as we have some developers to test loads that some test takes hrs to complete. So we do have regular loads we have been testing on both systems and timing out. On v14 we have almost no worload, and on v11 we have some and still perform faster. Some loads take 20 mins, other take 40 mins on the old system, as part of regular weekly data loads and processing we do. I will also try to isolate v11 workload and capture those results to compare as suggested to check. ------------------------------ Tereso Garcia ------------------------------

this is the scheduler on one raw data disk on v 11 This is on the new system data partition (also raw) IDS v 11 7 is on rhel 6 IDS v14 is on rhel 8 What is best setting for the IO scheduler on rhel 8 ? ------------------------------ Tereso Garcia ------------------------------

On any VM installation you should be using the "noop" scheduler! Just start out configuring the scheduler the same as you have it on the RHEL7 system. Then see how that goes. Art ------------------------------ Art S. Kagel, President and Principal Consultant ASK Database Management Corp. www.askdbmgt.com ------------------------------

On rhel8 there is noop anymore. It seems that is right on that the scheduler changed on rhel8 like below So to what we have currently on our systems OLD NEW It seems we have the equivalent, just not in the same order. Should those be on specific order ? this is the documentation found on that matter for that reference https://access.redhat.com/documentation/en-us/red_hat_enterprise_linux/8/html/monitoring_and_managing_system_status_and_performance/setting-the-disk-scheduler_monitoring-and-managing-system-status-and-performance I am doing a script to capture onstat -p and onstat -g ath and try to figure out where we have resources workload differences. We had noticed with one query we been using to benchmark and compare uses nested loop joints on v11 while on v14 it decides to use Dynamic hash joints. We had tested with SQL_DEF_CTRL variable from 080 to 040 on the v11 and that forces the optimizer to use Dynamic hash joints from Nested loops that best performs. But we cant determine what is the best option to try on that variable on v14 to force the optimizer to use nested loops joints. WE changes OPT_GOAL = 0 from default = -1 and that executes faster on this specific report that only returns about 8 records but joints and sorts millions for that. The only issue with that is that variable forces the optimizer to return the "first Rows" that satisfies the joints and predicates but wont return all the results. We later tested on other workloads that had been taking 20 more mins compared to old system and still performing bad. These workloads uses 4GL cursors and massive inserts on temp tables and on new tables to re-process data We also switched this undocumented variable SQL_FEAT_CTRL from default to x9008 and improves so little. We are looking inside informix and at the OS level and not getting any lucky. ------------------------------ Tereso Garcia ------------------------------

OPTCOMPIND controls whether the optimizer favors nested loop or hash joins. For an OLTP environment it should be set to zero while the default is 2. Another setting to try is the relatively new and undocumented parameter OPT_SEEK_FACTOR. The default for 11.70.FC6 and earlier was 10 and from 11.70.FC7 and later it is set to 6. There is no documentation revealing what this parameter defaults to in v12.10 or v14.10. You can try to set OPT_SEEK_FACTOR to 0. OPT_SEEK_FACTOR was introduced in v11.70.FC1 and is the weight given by the optimizer to IO Seeks to determine the cost of different query plans. Higher values tend to overestimate the cost of using indexes and so can often cause the optimizer to favor a hash join even with OPTCOMPIND set to zero. If your storage is SSD or other low latency seek storage then OPT_SEEK_FACTOR should be set to zero. That will restore the pre-11.70 optimizer calculations. As you figured out, you should probably not be setting OPT_GOAL to anything other than the default of -1. OPT_GOAL 0 was implemented to make interactive form based applications seem more responsive so that the app could display the first rows quickly when a returning data set includes many many rows. It tends to avoid sorting and prefers using an index on the sort key if available which can make returning the entire data set a bit slower but the first N rows arrive much more quickly. Originally it forced a MERGE JOIN but I believe that the merge join code was removed again in v11.10 (it had been removed in 7.30 and reinstalled in 7.31). This feature was implemented for me when I was at Bloomberg and for Sears Holding back in the mid-90s when both customers were having problems with interactive apps that appeared slow. Without the MERGE JOIN code it's not as useful. Also from your comments, it looks like it may be broken. ------------------------------ Art S. Kagel, President and Principal Consultant ASK Database Management Corp. www.askdbmgt.com ------------------------------

Hello Art, the comments on OPT_SEEK_FACTOR sound exciting. However, it only seems to be set explicitly in onconfig and not session specifically via an environment variable or via "set environment ...". ? Best Regards Henri ------------------------------ Henri Cujass leolo IT, CTO Germany IBM Champion 2021 and 2022 ------------------------------

Henri: You are correct. I was not clear, OPT_SEEK_FACTOR is an ONCONFIG file parameter. It is available as a dynamic setting using onmode -wm or -wf however, so a restart is not needed to modify this parameter's setting. Art ------------------------------ Art S. Kagel, President and Principal Consultant ASK Database Management Corp. www.askdbmgt.com ------------------------------

We had tested on the IO scheduler for [none] and for [mq-deadline] which are the 2 profiles recommended for Databases but that on rhel6 and rhel7 based on this redhat article https://access.redhat.com/solutions/54164 Now on rhel8 those profiles only changed names from noop to none and from deadline to mq-deadline. After testing all disks on same profile, [none] tested worse than [mq-deadline] and none of those are close to the v11. Our current tuneD profile is latency-performance. Is there any better profile that this ? We had tested virtual guest as its the default and also tested slow. Not sure to test every single one if there is a preferred profile under rhel8. Also is there any relation on tuneD and IO scheduler profiles? Should both math any configuration or recommendation ? ------------------------------ Tereso Garcia ------------------------------

We had tested IO schedulers and below the results all the below for v14 [NONE] that is the equivalent of [NOOP] Results in minutes run 1 = 7.05 run 2 = 7.11 3 = 7.14 [KYBER] run 1 = 6.57 run 2 = 6.52 3 = 6.49 [mq-deadline] = default value for disk on rhel8 run 1 = 6.44 run 2 = 6.26 3 = 6.25 All the above on tuneD profile = latency-performance On old system, v11 the same query takes 3.55 avg We also tested the suggested variable OPTCOMPIND = 0 from default value= 2 All these teste on disk back to [mq-deadline] 1 = 6.31 1 = 6.23 3= 6.36 I will test the other variable OPT_SEEK_FACTOR on the weekend and post result along with query plans and if the optimizer keeps choosing Dyn hash over Nested lops. Remember that this specific query performs better on v11 using nested loop joint I will also capture workload and post results early next week ------------------------------ Tereso Garcia ------------------------------

Hi, The next thing is to compare query plans and look for sequential scans e.g. in syssesprof. "we have close to same bufferpool from v11 to v14 except the additional 16K pages on v14. " Does that mean a smaller bufferpool is beng used for the relevant indexes on v14? Can you test with the same pagesizes and bufferpools on v14? Andreas/Art, does bufferpool size affect the optimizer choices? Regards, David. ------------------------------ David Williams ------------------------------

"I believe that the merge join code was removed again in v11.10" What? Informix cannot do MERGE JOINs any more? SQL Server can! DB2 can - https://www.ibm.com/docs/en/db2/9.7?topic=optimization-joins !!! Andreas. Can you confirm if MERGE JOINS exist in V12/V14? David. ------------------------------ David Williams ------------------------------

Hi, I would try to install the old 11.x Instance with identical settings on the new machine and new OS and check the same query in order to get to know if the OS is relevant or IDS. There are obviously too many parameters changed for just blaming IDS to be slower. My experience is that 14.x is not slower, but our environment is not really comparable to yours. Just my 50 cts. Marcus Haarmann

David, Buffer pool size does do affect the optimizer. The optimizer works based on the statistics existing at the time of finding the best path and the selectivity factors of each clause in the query( =, matches, or, and, etc) and whether you have indexes or not ( the uniqueness of indexes is taken into consideration). It determines also whether it needs temporary space or not. After finding the most optimal path, it goes and runs the query using the available ressources ( memory, temp dbspaces, ...) This is just quick view of the optimizer phase of the 3 levels that the query goes thru( parser, optimizer, run phase). Khaled Bentebal de mon portable

David: I agree with Khaled. The number of buffers or the page size of a table or index does not affect the optimizer's selection of query plans. What does are the following: Data distributions properly created (such as by using dostats) Existence of indexes OPT_SEEK_FACTOR OPTCOMPIND OPT_GOALLevel of clustered-ness of clustered index tables Query details such as join conditions, filters, group by, order by Also, David: Yes, for some reason the developers do not like merge-joins and have at least twice put the code in and removed it. I honestly do not KNOW the current status, but I have not been able to cause the engine to perform a merge join since maybe V10, so I can only assume that it was removed again in v11.10. Any HCL lurkers care to comment? The nested-loop join code was reinstated in v7.31 or so in order to implement OPT_GOAL 0 (aka FIRST ROWS optimization) and the FIRST_ROWS optimizer directive to satisfy requests from my at Bloomberg and my counterpart at Sears Holding. Art ------------------------------ Art S. Kagel, President and Principal Consultant ASK Database Management Corp. www.askdbmgt.com ------------------------------

@Art Kagel I finally tested changing OPT_SEEK_FACTOR = 0 from its default = 6 All previous variables were left as OPTCOMPIND = 0 (as suggested) from default =2 OPT_SEEK_FACTOR = 0x080 as we have it on v11 and added to v14 that tested better with it Results R1 = 7.21 R2 = 7.18 R3 = 7.05 So it seems it performs even worse with those variable combinations So based on the Query plan on V11 (OLD system) that makes the difference on OLD vs NEW . The OLD system favors a nested loop on temp area but creates Indexed tables on Temp (AUTOINDEX PATH) on the new system V14, no matter what variables we change, it keeps solving using DYNAMIC HAS JOIN but I dont see autoindexing on those temp tables created to pull data for those joins and causing a sequential SCAN instead. and this specific step takes 3 + more minutes on the NEW system v14 Any suggestions ? We had tested IO scheduler, and TuneD profiles. We had moved the VM to another virtualizer node to get similar slow results We had tested numerous scenarios with several variable combinations to get similar results only seconds variation but not enough to get closer to v11 After a new variable is set, I reset stats with "onstat -z" to make the optimizer go fresh We recalculated stats doing low stats dropping distributions, followed by medium stats on distributions only and High stats on indexes on distributions only on default resolution 2.5. we had checked the specific tables to find the index distribution buckets and those look good for the several indexes on v14 that are close enough to v11. So far on all scenarios we had got nothing to make the optimizer to behave differently from the same path on v14 going same path that differs from v11. Is that a product defect ? It that a bug ? Our system is 750 Gb of data and growing. We have no confident v14 is any better than v11. We added CPU and more RAM to v14 and still makes no difference whatsoever as increasing VPCLASS on CPU and increasing bufferpools, as some of you had previously commented that bufferpools do not affect the optimizer. the biggest tables involved (size in recors) 15 515 286 records 2 699 675 records 82 737 064 records 1 750 447 records ------------------------------ Tereso Garcia ------------------------------

Same here I was thinking that I update then this will work faster but it is slower than that , I hope the issue will be resolved. ------------------------------ Hasnat Ali ------------------------------

Tereso: Comments: OK, so this was not a difference in how the optimizer was recognizing index IO or reducing OPT_SEEK_FACTOR would have helped. This is the first time you have posted the actual query plans. I see that the query, which you did not include, appears to involve joining four VIEWs each of which has to be instantiated into a temp table. In order to complete the joins the engine has to decide whether to scan the tables and do a hash join (as it decides to do in v14.10) or create indexes on all four temp tables (Auto Index) on the fly and perform a nested loop join. I think that there is obviously a difference in how the V14 and V11 are interpreting the sizes of the instantiated VIEWs because v11 is estimating that 127 billion rows will be returned while v14 is estimating over 500 billion rows while you have stated that the tables in both instances are the same sizes. To me that says some things: You probably do not have IFX_F OLDVIEW set to 1 in either engine or the engine would likely not be instantiating the VIEWs but rather would have folded the four queries together into a single SELECT on the base tables behind the view. Though it is possible that the views are so complex that folding is not possible. And: That despite your best efforts your data distributions (UPDATE STATISTICS) may not sufficiently detailed to support the optimizer making good decisions (though, again, the queries behind the four views may be so complex that no amount of data distributions on the base tables behind them will help). Have you tried using my dostats utility to generate the data distributions? Side note: You said in you latest that " After a new variable is set, I reset stats with "onstat -z" to make the optimizer go fresh". Note that onstat -z does nothing that affects how the optimizer works. It only zero's out the counters that keep track of performance statistics, not optimizer statistics. So, while you may want to run onstat -z before a test run so you can compare those performance stats reported by other onstat options after each run to each other, each run will perform the same whether you do so or not. Anyway, if you do not have IFX_FOLDVIEW set, try setting it. If that does not help I would seriously look into rewriting the query your self to use only the base tables behind the four views and not the views themselves. (Yes, I know that 11.70 is doing a find job using the views, but imagine how much better that query would run, even on v11.70, if it did not a) have to instantiate each view into a temp table and b) did not have to build auto-indexes for all four temp tables. If you can't figure out how to rewrite that query, you could hire someone like me to do it for you. I get my jollies rewriting complex queries. B^) Art Art S. Kagel, President and Principal Consultant ASK Database Management www.askdbmgt.com Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves.

Hello @Art Kagel We found that IFX_FOLDVIEW was giving us lots of performance issue. And compared to the old system v11 it also has it disabled. I just enable it again and ran a couple of tests and execution time almost doubled R1 = 13.13 mins R2 = 13.18 R3 = 13.20 That variable set to 1 along with the previous suggested: OPTCOMPIND = 0 OPT_SEEK_CTRL = 0 OPT_GOAL = -1It did in fact changed the query behavior but took 6 more mins to resolve the query below part of the query plan I wish We can rewrite all those queries, but this is a massive system and there are hundreds of queries. Our developers had tested data loads that usually takes 20 mins to finish and now those take almost 50 mins. We have processes that go up to 45 mins, or past the hr to process. Our expectation is that moving to v14 all those scripts workloads would improve. We have years, decades of written code that is coming along and rewrite would take years. We rather put the effort to move away from informix to another platform since Informix becoming more cumbersome to setup right. ------------------------------ Tereso Garcia ------------------------------

Hi Tereso, Here is an approach I might take if faced with your problem. If you haven't already raise a support case. You'll be able to share far more detail with IBM support than you perhaps feel you can in a public forum like this, such as full table schemas and data distributions, maybe even the actual data. Looking at the problem my instinct tells me the principal issue isn't slow storage or which I/O scheduler the Linux kernel uses, but the query plan chosen. I would attempt to use directives to force the v14 optimiser to avoid the dynamic hash joins and ultimately restrict it in such a way as to match the 11.70 query plan exactly, or stop when you get something faster. You can then compare like with like and this will at least tell you whether the problem is isolated to the query optimiser, even if you don't see this as an eventual solution. At the moment you have a lot of variables. The 'avoid_hash' directive is documented here: https://www.ibm.com/docs/en/informix-servers/14.10?topic=statements-join-method-directives As mentioned already I would expect 'OPTCOMPIND 0' to be beneficial because it should favour nested loop joins. Another approach is to use 'SET EXPLAIN STATISTICS;' and let the query run to completion. This will show up any discrepancy between the estimated cost and the actual cost of an operation. Maybe you could share the output here? In some limited scenarios I have seen higher resolution distributions (more than the standard 0.5, 200 buckets) help. Ben. ------------------------------ Benjamin Thompson ------------------------------

I agree with Ben. I also think that the data distributions is one of the main problems. Tereso: can you post the statistics sections from the end of the sqexplain output from all three query plans (v11, v14 default, v14 w/IFX_FOLDVIEW)? That might help us understand. Meanwhile, definitely open a support case with IBM. Art Art S. Kagel, President and Principal Consultant ASK Database Management www.askdbmgt.com Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves.

| view attached (3) Attaching query plans for v11 and for v14 ( 2 scenarios) 1st scenario is with VM I/O scheduler on mq_deadline and OPTCOMPIND = 0 and OPT_SEEL_FACTOR = 0 2nd scenario with the above plus added the ifx_foldview = 1 that doubled the execution plan ------------------------------ Tereso Garcia ------------------------------

| view attached (3) Attaching query plans for v11 and for v14 ( 2 scenarios) 1st scenario is with VM I/O scheduler on mq_deadline and OPTCOMPIND = 0 and OPT_SEEL_FACTOR = 0 2nd scenario with the above plus added the ifx_foldview = 1 that doubled the execution plan ------------------------------ Tereso Garcia ------------------------------

Teresco: I hope that you do not take this the wrong way, but that query is a horror! Not productive, I know, but I had to get that out of the way first. I also know that overly complex SQL is not the issue here, but getting v14.10 to perform for you at least as well as 11.70 does is the issue. So, even though you have previously noted that you do not want to rewrite tons of old complex SQL in many places in many applications, still, I am going to tell you what I see that could help. The first two items do not involved modifying the SQL, however, so worth checking out. Here is what I can see, in no particular order: 1 - I can see from the estimated versus actual rows produced and from the differences in the estimates between v11 and v14 that the data distribut ions on v14 are simply insufficient. I STRONGLY suggest running the latest (or at least a recent) release of my dostats utility with the "-Z 0 .33" option so that MED stats sample at least 33% of the rows in the table. Then try the query with the various configuration options. 2 - The GROUP BY clauses all require sorting. Make sure that the DS_NONPDQ_QUERY_MEM parameter in the ONCONFIG file is set large enough to accommodate these sorts completely in memory. This is a dynamic parameter. (Reach out to me privately if you don't know how to check that). 3 - There are several older style joins that do not have the JOIN keyword nor the ON clause join filters but have the join filters in the WHERE clause which forces the optimizer to join every row to every row and filter post-join. That is mixed in with some ANSI style LEFT (outer) joins which may confuse the optimizer. Simply changing all of the joins so that all are ANSI style joins and moving those join filters such that every join has an ON clause may help. I do know that all of the "LEFT" joins are in a subquery, so not strictly mixing ANSI and traditional join styles, but still I would be more comfortable if the query was consistent. 4 - There are several instances of code expansion and code translation using CASE statements. These would probably be faster if the codes and their expansions/translations were stored in a small table. That would also eliminate the need to edit the queries when/if new codes are added in the future. Just add a row to the lookup table. 5 - The inner subquery with the UNION of selects against v_vendor_fines and v_hist_vendor_fines would actually be a good candidate to be replaced by a VIEW that is simply joined to the other tables and let the optimizer fold it back into the query. It is not being folded as it stands now. 6 - The outer subquery containing the subquery in #5 could then just be a simple join of the VIEW I mention in #5 and the three LEFT OUTER tables and the three remaining inner join tables (a12, a13, a14) with the folded subquery a15 which also does not need to be a subquery since it does not include any aggregations. Art ------------------------------ Art S. Kagel, President and Principal Consultant ASK Database Management Corp. www.askdbmgt.com ------------------------------