This is a frustrating bug I am currently encountering in Paradox 5.0 database.
I have a paradox table (file size 130 MB). When I use the following query:
Select * from friskpol where fcode = 160222
it returns correct data as required.

However, when I use Order By clause to this query as follows:
Select * from friskpol where fcode = 160222 order by fpolsym

It fails to return any record. However if I try repairing the same using Pack
Table option and then retry, things start working fine. But soon, after a few
days, someone else reports the same issue.

It may be noted that when it does not return the data, it does it almost
instantaneously. As if the lazy database does not want to search the data at
all! Can someone please help? This issue is giving nightmares to me as well
as my Project Manager.

Are you using Paradox the Application, or just Table Format?

Packing doesn't fix a corrupted table, just recovers some space.

What block size is the table? Trying coping the records to a new
table with 16k or 32k block size (if not already one of those sizes),
and use that table for a while to see if the same issue crops up.



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

Tony, thanks for the reply. I am not sure if it is Paradox application or
table format only. I got it with Borland Delphi and has Database Desktop
shipped along with it. I am connecting to the tables through the BDE engine.

I know Pack Table does not do anything significant. The point is that doing
anything to restructure the table in any way solves the problem temporarily.
I had been using a block size of 2K. Maybe I can try 16K. But its result may
not be known immediately because the issue is sporadic in nature. It is only
some transactions that seem to be showing this kind of behaviour. So once I
do this change, I shall have to wait a few days to see if any users are still
posting this issue.

In the meanwhile, can you please tell me what block size really does and how
it would help increasing it? All that I know is that it is the amount of disk
space reserved for a record of a table. Also I can tell you that we had made
a recent release that added a few columns to this table and its only after
this release we started facing this problem.

I think the limit for 2k Block Size is 128MB, isn't it?

So you may be trying to stuff more in there than is possible.

And by packing the table, you are recovering enough space to
continue - for a short while.

By adding more data, you may be corrupting data enough that the BDE
encounters a corrupted record/data and just gives up - and thus the
immdediate return of no data.

I'm more or less guessing, here, but this sounds like familiar actions
and cause.


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

Tony, this sounds like a convincing explanation. I hope this works. But how
did you map 2048 to the 128MB limit? Is this limitation documented somewhere
or you are just guessing this possibility?

Thanks once again for the spontaneous help provided by you. Its almost as if
you are sitting there polling this discussion group for any new thread :-DD

Hey seems like you have hit the nail ! Its perfect.
http://www.delphi32.com/info_facts/faq/faq_11.asp
This link explains everything. 64K is the maximum blocks my table can have.
So a block size of 2k will produce 128M as mentioned by you. Thanks buddy.
This is a great relief to me as well as my PM !!!

Increasing the block size seems to have solved the problem. Haven't heard of
any fresh issues of the same kind. However, I have been getting reports from
users that the application has become dead slow. Is there an adverse effect
of the increase in block size on the performance? Any one has an idea?

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

no, block size shouldn't impact performance.. when you moved the data, did
you pack the table?

--
Steven Green - Myrtle Beach, South Carolina USA

Diamond Software Group
http://www.diamondsg.com/main.htm
Paradox Support & Sales

Diamond Sports Gems
http://www.diamondsg.com/gemsmain.htm
Sports Memorabilia and Trading Cards

Steve,
All that I did is change the block size in BDE from 2K to 4K. Also, further
investigation I did today indicates the block size of the table has always
been 16K. Its the block size on BDE that was 2K and I changed it to 4K. So
the current state is something like this:

File size of the table: 130M
Block Size of the table: 16K
Block Size of the Database in BDE: 4K

--
Message posted via http://www.dbmonster.com

Changing the block size in the BDE only effects FUTURE tables. To change
an existing table you have to create a new instance and move the data.

Denn Santoro
President
Resource Development Associates
http://www.RDAWorldWide.Com
Offices in the United States and Germany
Providing solutions to health care, business, governments and
non-profits since 1982

spurohit via DBMonster.com wrote: