(no subject)

rhlab!kuhn@uunet.uu.net writes: > In running the query described below on my OLD version performance was > completely acceptable. On version 4.0, ouch!!!! Queries that took seconds > were now taking lots........ of minutes like 15,20,25 minutes. > > After sending tech support a sample database with data, they also observed > the same slow query. The result is that: > 1. This is not a bug. > 2. It is a problem with the optimizer picking the wrong thing. > 3. It is only on the 3B2's. Although I haven't checked it out in as much detail, my experience strongly suggests that item #3 is false: I believe I'm seeing the same thing on a sun4 using INFORMIX-SE Version 4.00.UD3. I made a query similar in form the one in the previous message, and interrupted it when it took far longer than expected. 'set explain on' revealed that the index was being ignored, and a sequential scan performed. The thing I didn't do was to try it under 2.10.03: I wasn't confident that it was safe to let the 2.10.03 sql engine operate on a version 4 database, so I wasn't able to verify that the old optimizer worked better, but my impression is these queries used to work efficiently. My experiments indicated that the optimizer strongly disfavors criteria that use the 'matching' construct with a '*', even when there's an index on the field. If the example query said 'pname between "ANDERSON, K" and "ANDERSON, L"' instead of 'pname matches "ANDERSON, K*"', I suspect it might work much better. Also, the optimizer does seem to do the right thing if there are no other criteria: for instance, select * from pinfo where pname matches "ANDERSON, K*"would probably work fine. In theory I suppose you could do this select into a temporary table, and then do the join with the second real table, and the temp table. This might help with canned query scripts; it doesn't help with ad hoc queries produced by 4gl 'construct' statements, as in my case. I haven't pursued this further simply because the bad cases do not come up often in my application. Item 1 above ('this is not a bug') is true only in the sense that the sql engine eventually should return the right results. It most certainly is a bug in the sense that the optimizer is failing to optimize in an easy case that it (apparently) used to handle correctly. While some of the new features of 4.0 are nice, it seems to have introduced lots of bugs in things that used to work. -- Harry Bochner -- bochner@das.harvard.edu