Re: Informix and PDQ

> I assumed that since for most of our queries we see almost no speed > increase from a setting of 5 to 40, we could run 20 queries at 5. Nope, > no show. Informix recomends sending one query at a time throught the > engine at PDQ 100. It seems that multiple queries really freak the > engine out (8.1x). The reason that there is no difference in speed at times when increasing PDQ appears to be because of some calculation the optimizer does when deciding what to do with the memory allocated to it. When doing just a sequential scan, memory is not needed for that query, just scan threads. But when doing a hash join, it obviously does, and the query is given an amount of memory based on what PDQ is set at. And while it's scanning the tables, most of that memory is used. However, when doing the join, despite wishing it would use all of the memory given to it, it doesn't. Example: We had a large join query with PDQ set to 25, and I noticed that after scanning the 1st table (making the hash table), it did not scan the 2nd table in parallel with doing the join as it usually does. When it finished scanning the 2nd table, it used none of the memory (onstat -g mgm -- tells how much memory is given and how much is actually used). It used the memory when scanning the tables and making the hash table, but the time it took for that was only 7% of the total query time. We re-did the query with PDQ of 40. More memory was given, but still not used. Finally at PDQ of 70 it finally used the memory, and the time went from 9 hrs all the way down to 1hr 45mins (of course, by this time we realized that DS_TOTAL_MEMORY was a lot less that what we thought it was). Another query was at PDQ 20, and was using just over half of the allocated memory. By setting it to 25, it then used twice as much as before, since more was now allocated to it. I haven't checked to see if it always uses memory in increments of a certain size; I'm assuming this isn't the case, but is based on many variables, such as the size of the hash table. Conclusion: Raising PDQ will give the query more resources, but the query may not use the resources fully. So we can't make the rule that doubling PDQ will make the query run twice as fast; there might be hardly any change (a slight increase in speed when scanning the tables/making the hash table), or there could be way more than 2 times an increase, depending on however it determines how much memory to actually use.