Is the problem table not a NEW table, as a result of the query?


--
------------------------------
Tony McGuire

I would like to reiterate that the original table already had 16K block-size,
which I believe is sufficient. But the BDE setting for block-size happened to
be originally 2K which I change to 4K. The file size of 130M and still
growing without errors indicates that the data is getting inserted without
errors. However when I used to do a Select query with an Order BY, it used to
throw an empty resultset. The same query without Order By used to give proper
results. On changing the block size in BDE to 4K, as suggested, it seemed to
have corrected this issue but on the other hand the users have now started
reporting performance issues. The application has become slow. Am not sure if
this change from 2K to 4K is the culprit

Dennis Santoro wrote:
--
Message posted via DBMonster.com
http://www.dbmonster.com/Uwe/Forums....radox/200709/1

I would be surprised if it was.

My guess is that there is something else that causes slow performance.

Anders

Coming back to the original issue:
The table is friskpol.db
Query being attempted: select * from friskpol where acode = 83818 order by
fpolsym
This query returns no record. However if I remove order by clause, it returns
one record.

The db file has the following associated with it:
1 Primary index file and 3 secondary index files.

If I remove all these files and have only the db file, the original query
returns the desired output.
Then I do a trial and error removing each one of them one by one. I notice
that if I remove either the px file or XG0 or both, the query works fine. If
I remove any of the other two secondary index files, I get no record. So the
problem has to do with either the px or xg0 or both. I rename the xg0 to .db
to view its contents. I see that it has an index on acode (the field that we
have in the where clause). The block code for acode = 83818 in this file is
the same as the one for the record I am expecting to be returned. This, I
checked by looking at the block code in another secondary key file that does
indexing on the primary key field for the table (I don't know why that
secondary index was ever created when the px file already does the indexing
on it).

Further I checked if the max(block number) in any of the secondary index
files is 32K or more in which case the short int field would try to reference
a record with a negative block number and hence return an empty recordset.
But the highest block number is just about 8000.

So this leaves me with one single question - "What is it in px or xg0 that is
preventing proper linking with appropriate data from db file and hence not
throwing my expected resultset?" And can it be corrected without an adverse
impact on performance?

--
Message posted via DBMonster.com
http://www.dbmonster.com/Uwe/Forums....radox/200709/1

spurohit,

If I remember correctly, each secondary index reduces the table's maximum
record count. So, although you might not be running into the 'theoretical'
limits on the table, you might have hit this.

If you have a secondary index that starts with the primary key, drop it since
it is useless. This might help alleviate the problem. The only downside
would be any forms/scripts/libs/data models that open the table using that
index need to be changed. You could probably scan the code for that index
name, using window's Search button.

Also, increasing the block size of a table can have a definite impact on
you system response. In my experience, using peer-to-peer networking, every
time you open a table you get 8 blocks of data sent to you, which I assume
is to speed up sequential processing. This is all wasted for random processing,
which most interactive systems will use. Since every user is now getting
'wasted' data, that can cause your network to grind to a halt. (The more
data sent, the more retries are needed, ad infinitum.)

Finally, is it just this query that is slower, or other aspects of your system?
You could put more indexes on the table to have them used by this query
- a single-field secondary index on this particular field would definitely
help this query. But, as you've seen, adding indexes can affect other parts
of your app.

HTH,
Jim Moseley

Jim Moseley wrote:
Assuming this is correct..how come the inserts to the table are not failing?
I would assume an exception would be thrown to notify the maximum record
count has been reached.
The code simply has queries that use the various fields in the where clause
and I wouldn't know when the primary key field is in the where clause,
whether the primary index or the secondary index would be used. My feeling is
that it will always use primary index and that the secondary index on the
same is redundant.
data. A slower query would at least give correct data (although after a
longer time). Yes, the rest of the application is performing slower. But at
present I am putting that issue in the back-burner. Your suggestion that I
can put more indexes on this table seems to be contradicting the thesis you
have put forth in the beginning - "The secondary indexes could be making my
data table to hit its maximum record count even before the theoretical
capacity"
--
Message posted via DBMonster.com
http://www.dbmonster.com/Uwe/Forums....radox/200709/1