In addition to this I have found that if you are using a query's where-all
for the update mode, it can generate a query that has:

update...... where a = :a and b = :b ...... z = :z

On certain tables this generates the most horrendous plan that takes a
second or so to execute the query, basically instead of just using the PK,
the update set of a massive index merge. Can you do what Martijn says and I
bet it will either be a fetchall problem or a poor plan.

You can test this by changing the where clauses to update ... where pk = :pk

HIH

JAC.


""Martijn Tonies"" <m.tonies@...> wrote in message
news:03ee01c2bbe5$bb813ff0$0a02a8c0@seal...

> Hi Pete,
>
> > >> My post commands are very slow.
> > >
> > >Do you mean <somedelphicomponent>.Post or do you mean an
> > >actual INSERT INTO statement?
> >
> > Yes, Delphi TIBQuery.post. I stepped through the code until I found the
> > delay. It's an UPDATE rather than an INSERT.
>
> Could you turn ON some kind of SQL Monitor and see what happens
> _after_ you do the Post? Lots of fetches, for example? How many
> rows are returned in your resultset?
>
> > >> I am selecting records "where status=1", dealing with them, then
> > >> changing Status to 2. Status is not an indexed field. The SELECT
> > >
> >
> > >Does the table have a primary key?
> >
> > Yes, an autoinc, not directly relevant to this SELECT.
> >
> > >> command is more complex, there are other "wheres", but no other

fields

> > >> are being changed.
> > >>
> > >> Since status has low selectivity - there are about 6 choices, over
> about
> > >> 5 million records - then I can guess why there might be a problem
> > >> (index? record moving out of "where" condition?). But I have no

idea

> > >> what to do about it...
> > >
> > >You shouldn't index that field - it's no use.
> >
> > I was wondering if the SELECT might create it's own index, because of
>
> It's own index? Indices are a server-side thingy... So that ain't

possible.

>
>
>
> With regards,
>
> Martijn Tonies
> InterBase Workbench - the developer tool for InterBase & Firebird
> Firebird Workbench - the developer tool for Firebird
> Upscene Productions
> http://www.upscene.com
>
> "This is an object-oriented system.
> If we change anything, the users object."
>
>
>
> To unsubscribe from this group, send an email to:
> ib-support-unsubscribe@egroups.com
>
>
>
> Your use of Yahoo! Groups is subject to http://docs.yahoo.com/info/terms/
>
>
>