Board index » delphi » TQuery Problem (doesn't show inserted recs)

TQuery Problem (doesn't show inserted recs)

Hi all,

I've got a problem with TQuery on Interbase:

I select the records from a table Tasks that looks like this:
    Company : Integer not null
    Version:Integer not null
    No: Integer not null
    txt: Varchar(200) not null
    Cd1: Integer not null
    Cd2: Integer not null
    ....
    Cd15: Integer not null
Indexes are Company, Version,Cd1 to CD 15, Primary Keys are Company,
Version,No

The select retrieves all records for a certain Company and Version ordered
by CD1 to 15.
There is a calculated field, that shows the information from Cd1 to 15 like
"1.14.2.3". RequestLive is TRUE.

The problem is: When I insert a record at the appropriate position (see sort
order) it either walks up to the first position or "disappears" completely
(apparently depending on the information given in CD1 to 15) from the
associated DBGrid, as soon as I press Post on the navigator.

Only if I close and re-open. the TQuery the new record is shown correctly,
but this is not an option due to performace reasons.

Any ideas?

Thanks in advance!

Guido

 

Re:TQuery Problem (doesn't show inserted recs)


Hi all,

I've got a problem with TQuery on Interbase:

I select the records from a table Tasks that looks like this:
    Company : Integer not null
    Version:Integer not null
    No: Integer not null
    txt: Varchar(200) not null
    Cd1: Integer not null
    Cd2: Integer not null
    ....
    Cd15: Integer not null
Indexes are Company, Version,Cd1 to CD 15, Primary Keys are Company,
Version,No

The select retrieves all records for a certain Company and Version ordered
by CD1 to 15.
There is a calculated field, that shows the information from Cd1 to 15 like
"1.14.2.3". RequestLive is TRUE.

The problem is: When I insert a record at the appropriate position (see sort
order) it either walks up to the first position or "disappears" completely
(apparently depending on the information given in CD1 to 15) from the
associated DBGrid, as soon as I press Post on the navigator.

Only if I close and re-open. the TQuery the new record is shown correctly,
but this is not an option due to performace reasons.

Any ideas?

Thanks in advance!

Guido

Re:TQuery Problem (doesn't show inserted recs)


Quote
"Guido Hillebrand" <guido.hillebr...@inode.at> wrote in message

news:3c0b7837$1_1@dnews...

Quote

> The problem is: When I insert a record at the appropriate position (see
sort
> order) it either walks up to the first position or "disappears" completely
> (apparently depending on the information given in CD1 to 15) from the
> associated DBGrid, as soon as I press Post on the navigator.

> Only if I close and re-open. the TQuery the new record is shown correctly,
> but this is not an option due to performace reasons.

This is the way queries work. A query is like a snapshot, it shows you the
data as it exists at the moment the query is executed. It is the way all SQL
servers work in general, not just Interbase.

If you are having a performance problem then this is what needs to be looked
at, not the fact that the query needs to be re-executed. The reasons for the
performance problems may include a query that needs to be optimzed, and/or
indexes that need to be looked at.

You indicate you have indexes on every field in this table except txt. This
by itself sets off alarm bells. Further, it sounds like you have created
indexes on Company and Version *in addition* to them being part of the
primary key (assuming the primary key starts with Company, then a separate
index on Version may be called for but definitely not on Company).

Creating too many indexes (or more properly, creating the wrong indexes) can
slow performance rather than enhance it. For Interbase, when given two or
more indexes that start with the same column, the optimizer can often choose
to use a less optimal one.

Finally, how many records are in this table and, of that, how many are you
selecting when you open the query? If the answers are a large number and all
of them then this also needs to be reconsidered. For large volume tables,
there is never a good reason why all records need to be selected for display
in an interactive application. You need to provide means of selecting only a
subset.

--
Wayne Niddery (Logic Fundamentals, Inc.)
RADBooks: http://www.logicfundamentals.com/RADBooks/delphibooks.html
Try to make original mistakes, rather than needlessly repeating [others]. -
Donald Rumsfeld, U.S. Secretary of Defense

Other Threads