Re: Search by index vs. sequential scan

John Carlson <john_carlson@whsmithusa.com> wrote: > On Tue, 20 May 2003 20:29:17 GMT, Robin Munn <rmunn@pobox.com> wrote: > > .. snip.. > >>The problem we'd been encountering was happening when two daemons ran at >>the same time. Let's call them A and B and say that A happened to start >>a few tenths of a second before B. A goes and finds its work units, >>locks one of them, and starts processing. Then B goes and finds its work >>units; so far everyone's happy. But when B tries to lock its first work >>unit, it failed, producing the following error: >> >>SQL statement error number -244. >>Could not do a physical-order read to fetch next row. >>SYSTEM error number -107. >>ISAM error: record is locked.>> >>Analysis with SET EXPLAIN ON revealed that both A and B's UPDATE >>statements were using SEQUENTIAL SCAN to find the row they were to >>update. When B's update cursor came across the row that A had locked, it >>gave up. Aha, we said, we need an index for this update! So we created >>one: >> >>CREATE INDEX worktable_idx2 ON worktable (worktableid, daemonid);>> >>But this failed to solve the problem. To our amazement, we discovered >>that the UPDATE statements were still using SEQUENTIAL SCAN (and >>therefore failing) even though they could have used INDEX PATH to go >>straight to the correct rows, bypassing the locked rows. Why was this? >>Further testing revealed that it only happened when the worktable was >>small; once worktable became fairly large, the queries were using INDEX >>PATH and everything was fine. Then we discovered an old post on >>comp.databases.informix talking about the OPTCOMPIND parameter, which >>can tell the optimizer whether to prefer SEQUENTIAL SCAN or INDEX PATH. >>Changing that value from 2 to 0 caused INDEX PATH to be used for all our >>updates, even when the table was tiny (just two rows, one for each >>daemon). Problem finally solved. >> > > How many records are in 'worktable'? It makes sense from the > optimizer point-of-view to only use an index if necessary. OPTCOMPIND > set to 2 will hint the optimizer to use a cost-based solution. If the > table is small, then why bother with an index read if a sequential > scan is 'better'. worktable initially starts out empty, but work units get added pretty fast; I'd say at a rate of maybe ten or twenty per hour. So within a day or so, worktable is large enough that an index-based solution makes sense to the optimizer. But during that first day, we were seeing problems with locking caused by the fact that sequential scans were being used. > BTW, did you 'update statistics' for the table after the index was > built? Yes. We don't run UPDATE STATISTICS after every new row is inserted into worktable, of course, but we have a nightly cronjob to run UPDATE STATISTICS. > >>So, to return to my original question: we now have an optimizer that is >>preferring indexes over sequential scans even in small tables. This is >>necessary in our case, and we can't really change it back (that >>"physical-order read" problem had been bugging us for a LONG time...). >>But I'm wondering: are we likely to sacrifice performance here? What are >>some examples of queries where INDEX PATH is a massive performance loss >>compared to SEQUENTIAL SCAN? (Minor performance losses we can live >>with). >> > > If the table isn't too big you could "SET LOCK MODE TO WAIT <seconds>" > in your code. If there are only two rows (as listed above), that > would allow the second process to wait a bit for the first process to > finish up. We kind of already do that. If a daemon finds that it can't lock its row, it gives up temporarily and waits for its next time around (after sleeping for N minutes, where N is user-configurable). That's not as efficient, but it gets the job done eventually. Actually, I do remember a discussion among the developers in which someone talked about why we're not using SET LOCK MODE TO WAIT, but I can't remember the reasons why. I think performance issues were mentioned. I don't know if that decision (not to use SET LOCK MODE TO WAIT) is set in stone or if I can get people to re-think it; I'll see. >>Hopefully some experienced Informix admins will be able to point me in >>the right direction here... Thanks for your help so far. -- Robin Munn <rmunn@pobox.com> http://www.rmunn.com/ PGP key ID: 0x6AFB6838 50FF 2478 CFFB 081A 8338 54F7 845D ACFD 6AFB 6838