Re: Search by index vs. sequential scan

I had a simular problem, "A empty table filled with jobs and some backgroud processes reading and deleting the rows" You will have to force your program to use a INDEX don't care if there are 2 or 200 rows in the table. Let F_cntstr = "Select {+Avoid_Full(jpqwrk)}", #V2.21C " jpqwrk.jpqwrksn, jpqwrk.jpqlv ", " From jpqwrk", " Where jpqwrk.jpqnr = ", P_jpqnr, " And ", F_selstr Clipped, " Order By jpqwrksn, jpqlv" rgds /Arthur ----- Original Message ----- From: Robin Munn <rmunn@pobox.com> To: <informix-list@iiug.org> Sent: Wednesday, May 21, 2003 4:16 PM Subject: 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 >