Temporary table resource limit
John B. Moore
Folks,
This error has cropped up and I have checked all "normal" causes of
this error. I'm looking for some fresh ideas...
Background,
Pdox9 updated with SP4, tested on Win98, NT4, and Win2K
Win2K machine has 1 GB of memory.
This is a select query between a Paradox table and an Interbase 7
table, linking the primary key on the Pdox table with an indexed field
on IB. The Pdox table is about 418 KB with 5,303 records. The IB table
has just under 3 million records, total (2.6).
The IB table drive on the dataserver has 5.9 GB available
The local C: drive has 9.4 GB free
The local Pdox Program/Priv directory drive has 15.6 GB available
The local Data directory drive has 21.7 GB available
The BDE Paradox driver is set to Level 5 with a block size of 32768
What has been checked thus far..
There appears to be no "temporary" table in the priv directory or
any other "large" file in any system "temp" directory
No indication that Win2K memory limits an issue (still 50% free)
Tests with a smaller subset of the IB table (1 million records) does
not return this error and completes with a table of 6,970 records. It
is estimated that the total records expected is around 15K (based on
running this with different subsets..)
There appears to be no resource exhaustion on the Interbase side.
Solutions:
At this time the only solution I can see is to move the Pdox table
"data" to IB and do the entire query there. (client would rather not,
but..<G>)
Can anyone think of something I have missed to check on the Pdox
side..???
Thanks..
John..
Dennis Santoro
John,
Do you have the temporary table creation turned off (fast queries)?
Otherwise it should be making some temporary tables as you know. Maybe one
of those is somehow exceeding the max for the block size setting? just a
WAG.
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
Robert Wiltshire
John,
Interesting thread, I have hit my head on this kind of error before, quite a
few times actually.
I am not sure I can explain why it happens,but I think you have your two
workarounds.
#1Do it in smaller pieces or
#2push the pdox table in question back up to Interbase.
See more inline.
"John B. Moore"
> Folks,
>
> This error has cropped up and I have checked all "normal" causes of
> this error. I'm looking for some fresh ideas...
> Pdox9 updated with SP4, tested on Win98, NT4, and Win2K
> This is a select query between a Paradox table and an Interbase 7
> table, linking the primary key on the Pdox table with an indexed field
> on IB. The Pdox table is about 418 KB with 5,303 records. The IB table
> has just under 3 million records, total (2.6).
Ok, so you have a join between a paradox table and an interbase table.
First of all, it is my belief that when you do a query like this,
the entire interbase table is downloaded across the network to the
workstation,
and the query is evaluated using the workstation cpu.
So, depending on the size of the Interbase table, performance might be
dreadful doing a query like this.
> The IB table drive on the dataserver has 5.9 GB available
> The local C: drive has 9.4 GB free
> The local Pdox Program/Priv directory drive has 15.6 GB available
> The local Data directory drive has 21.7 GB available
>
> The BDE Paradox driver is set to Level 5 with a block size of 32768
Seems like plenty of resources.
The dreaded 4 Gig threshhold error might hit you,
but that reports a different error.
> What has been checked thus far..
> There appears to be no "temporary" table in the priv directory or
> any other "large" file in any system "temp" directory
Right.
The temporary resource limit error comes as it tries to create the query
answer,
and when pdox reports the error condition and halts the query,
you probably dont see any of the private/intermediate tables behind the
scenes.
> No indication that Win2K memory limits an issue (still 50% free)
>
> Tests with a smaller subset of the IB table (1 million records) does
> not return this error and completes with a table of 6,970 records. It
> is estimated that the total records expected is around 15K (based on
> running this with different subsets..)
I believe this.
Working with smaller subsets is one workaround.
> There appears to be no resource exhaustion on the Interbase side.
Understood and sounds correct.
> Solutions:
>
> At this time the only solution I can see is to move the Pdox table
> "data" to IB and do the entire query there. (client would rather not,
> but..<G>)
A pretty good workaround.
Especially since uploading the smaller pdox table is better than downloading
the huge interbase table.
Make sense ?
> Can anyone think of something I have missed to check on the Pdox
> side..???
> Thanks..
> John..
My guess as to why this happens,
As paradox starts to evaluate a query,
it sometimes needs to build intermediate tables
that contain matches to links and criteria for the query.
Sort of a "hit matrix". ( there is no spoon )
As it builds the intermediate tables, something bad happens.
One possibility is the the intermediate tables
have a lower table version level and smaller block size,
and as it builds them, the intermediate tables get full, and the error
happens.
But you might be thinking, that isnt it, I have the higher block size and
table levels selected in pdox / bde.
If the intermediate tables did have block size 32K and higher table levels,
then they should be able to grow to 2 Gig, right ? Seems like a logical
assumption - read on.
But sometimes the error comes back so fast,
there is no way I think pdox could have written 2 Gig to the intermediate
tables that fast,
so I assume the error dealing with the intermediate table
must be a smaller table size than the anticipated 2 Gig size.
Not sure if that makes sense, but...............
In summary,
I get this error also when I join to a really large tables.
Specifically some tables that are over several hundred meg, or over 1 Gig.
My result set should be less than 10 meg, so the size of the answer table
really isnt the issue.
And since it isnt the size of the answer table that is a problem,
I conclude ( maybe erroneously) it is an intermediate table problem.
Narrowing the result set sometimes helps.
Doing the query in two passes sometimes helps.
For example, assume I want to peform a query to return all claims for
membersAge = 40 for YearService=2002
I link my membersToGet.db table to the claims table,
but instead of getting all claims from the claims table at one shot,
I put a date restriction on the large claims table.
Specifically on the Date of service
Pass #1 >=1/1/02,<=6/30/02
and after that query runs
I run it again
Pass #2 >=7/1/02,<=12/31/02
I am not saying that is fast, but it allows the query to run to completion.
Joining pdox to an interbase table,
downloads the interbase table before it evaluates the query,
and that is inefficient.
Plus it gives you a headache.
My feeling is , the fastest way to handle this is to upload the pdox table
to interbase.
Good luck and I hope to hear how you ended up solving your issue.
Robert Wiltshire
Bertil Isberg
John
Here's a quote from my files. No solution though.
My suggestion would be to try a SELECT ALL * with no ORDER BY or a
CHECKPLUS query.
<QUOTE>
From: Shaun Daigle <shaundai_no_spam_@_no_spam_imperialgoup.ca>
Subject: Temporary table resource limit - HELP !!!!!!
Date: 9 June 2000 23:00
I wrote a program that queries certain tables from our database and when
it tries to query a big table, I get a "Temporary table resource limit"
error message. The table is about 700 Mb in Brieve format, and it
should be approx. 300 Mb in Paradox format. The table contains 750 000
records and 94 fields. I need all the records and all the fields from
this table... so I didn't specify a 'where' clause in my SQL. So
basicaly, my SQL looks like this:
SELECT *
FROM TABLENAME
At first, I ran the program on my workstation, which is a PII 400, 64 Mb
RAM, Windows 98 SE, lots of HD space, using Paradox 9 DE, SP3. Then I
tried it on the server itself... 4 processor system (PIII 550 each), 2
Gig RAM, Windows NT Server, lots of HD space, using Paradox Runtime with
the service packs applied.... I got the same error on both machines.
BDE Config
BLOCK SIZE : 32768
LEVEL : 7
I found part of the solution... but first, I need to know where the
temporary tables are stored. Are they stored in the :priv: directory??
I got a message in the event viewer of the server saying that drive
space on drive C: (where Paradox is installed) is near 0. This means
that the temp files are stored on the C: drive on the server. On my
workstation, I still have 3 Gb of free space... so I shouldn't have any
problems on my drive.
Anyway, my question is simple... how do I make Paradox store it's temp
tables on a another drive / folder... and is there a limit on how big
those table can get?? I hope not!
From: Brian Bushay Ctech <BBu...@NMPLS.com>
Most of the temporary files paradox creates are in the private directory
but there are some operations it used the Windows Temp directory for.
From: Shaun Daigle <shaundai_no_spam_@_no_spam_imperialgoup.ca>
I found a couple of interesting things that might help you guys help me!
1st. While running a QBE query, the temp file is stored in the :PRIV:
directory. So I moved the :PRIV: directory to a drive where there's
about 20Gb of free space and ran the same query. The query stopped when
the file reached 293Mb. I didn't get an error message. The file in the
:PRIV: directory was called __QB1.DB. The table was supposed to be
saved under :dataReplication:tableName.db ... the file never got
saved in that location. When I closed Paradox, the temp file was deleted.
2nd. A SQL query creates the table directly in the
:dataReplication:tableName.db and updates the table as it queries...
The table got approx 300Mb in size (probably 293 as in the QBE) and
then... it got deleted! Then I got the error message "Temporary table
ressource limit".
3rd. I tried running the same query on a bigger table (less fields, but
more records). This table should be about 500Mb in size, but after
reaching 335Mb, the same thing happened... "Temporary table resource limit".
I'm out of ideas and I really need this program to work... Is this a
Paradox problem? A BDE problem? Any help is really appreciated.
</QUOTE>
--
Bertil Isberg
CTECH
FAQ newsgroup: corel.wpoffice.paradox-faq
Paradox Buglist: http://www.swebug.se/sig/sig_par/index.htm
John B. Moore
In article <3ec9801a$1_1@cnews>, Robert Wiltshire wrote:
> Interesting thread, I have hit my head on this kind of error before, quite a
> few times actually.
>
Thanks, good thoughts all, and I agree with all.. I just wanted a reality
check (since I do little Pdox these days and the Pdox brain cells are dying
off..<G>)
I think the only real solution is to move the pdox table to Interbase..
John...
John B. Moore
In article <3EC974F9...@RDAWorldWideSPAMFILTER.com>, Dennis Santoro
wrote:
> Do you have the temporary table creation turned off (fast queries)?
> Otherwise it should be making some temporary tables as you know. Maybe one
> of those is somehow exceeding the max for the block size setting? just a
> WAG.
>
No.. below is the basic query.. as simple linked select "check" query..
block size is set as high as is possible. There are NO temp tables that I
can find.. really strange..
===================================
qbe1 = Query
ANSWER: :PRIV:DUPSLCT2.DB
:PRIV:DUPSLCT1.DB | CUST_PHONE |
| _join1 |
:AAS_IBDATA:PROSLIST | CUST_ID | COMPANY | CUST_FNAME | CUST_LNAME |
| Check | Check | Check | Check |
:AAS_IBDATA:PROSLIST | CUST_ADD1 | CUST_ADD2 | CUST_CITY | CUST_STATE |
| Check | Check | Check | Check |
:AAS_IBDATA:PROSLIST | CUST_ZIP | CUST_CONTACT | CUST_STATUS | CUST_PHONE |
| Check | Check | Check | Check _join1|
:AAS_IBDATA:PROSLIST | SIC | IMP_DATE | PRINT_DATE |
| Check | Check | Check |
EndQuery
;//query attached to db connection - no transaction active
if not executeQBE(db, qbe1,":PRIV:DUPSLCT2.DB") then
;// snip... error handling here...
db.close()
return
endif
John B. Moore
In article <3EC9EE22...@scb.se>, Bertil Isberg wrote:
> Here's a quote from my files. No solution though.
>
Thanks, Bertil.. It confirms that the solution will have to be the
moving of the table to interbase...
John..
Bertil Isberg
John
Did you try a CheckPlus query? It should require less temporary resources as
no grouping and ordering is required.
--
Bertil Isberg - CTECH
FAQ newsgroup: corel.wpoffice.paradox-faq
Paradox buglist: http://www.swebug.se/sig/sig_par/index.htm
Steven Green
"John B. Moore" wrote:
> No.. below is the basic query.. as simple linked select "check" query..
> block size is set as high as is possible. There are NO temp tables that I
> can find.. really strange..
there's no resultant temp table because the error is generated *before* the temp
table can be completely created..
--
Steve Green - Diamond Software Group, Inc - Waldorf Maryland USA
Corel CTech Paradox - http://www.diamondsg.com - Support/Downloads/Links
---------------------------------------------------------------------------------
Do you need a Sanity Check? http://www.diamondsg.com/sanity.htm
Upgrade/Downgrade versions? http://www.diamondsg.com/upgrade.htm
-------------------------------------------------------------------------
Tony McGuire
Could this have anything to do with the 'creates a table *above* the :priv:'
issue?
Never did fully understand that one, when it occurs, nor the impact.
--
--
Tony
"I woke up and was able to get myself out of bed.
Being that fortunate, what's to complain about?"
_____________
Steven Green
Tony McGuire wrote:
> Could this have anything to do with the 'creates a table *above* the :priv:'
> issue?
I don't think so.. that's not a "limit", that's a "conflict"..
> Never did fully understand that one, when it occurs, nor the impact.
nor I, nor will we until it happens to us <g>
John B. Moore
In article <3ECA69E8...@diamondsg.com>, Steven Green wrote:
> there's no resultant temp table because the error is generated *before* the temp
> table can be completely created..
>
So I figured...<G>
It might also be that since the machine has so much memory it never had to write
any swap files to disk..
No matter.. I got it working by moving both tables to IB..
Thanks...
John..
John B. Moore
In article <3eca5e9b_1@cnews>, Bertil Isberg wrote:
> Did you try a CheckPlus query? It should require less temporary resources as
> no grouping and ordering is required.
>
No, I did not.. Just the testing I have done has eaten up most of two
days.. Got to cut to the chase and deliver a solution..<VBG>
Just finished converting this to a complete IB solution and it is quite a
bit faster as well..
Thanks for the ideas and feedback...
John..
Sundial Services
John B. Moore wrote:
A query having too many joins can produce what amounts to a "cartesian
product," then throw away 99% of it to produce a very small answer. For
example if three tables of 1,000 records each are unwisely joined, a
temporary table of 1,000 * 1,000 * 1,000 records may be attempted.
I find that the best solution is to simplify the query into a /sequence/ of
smaller queries. (Our "Report Journalist" product is the result of that.)
The strategy works extremely well.
----------------------------------
Fast automatic table repair at a click of a mouse!
http://www.sundialservices.com/products/chimneysweep
Robert Wiltshire
> product," then throw away 99% of it to produce a very small answer. For
> example if three tables of 1,000 records each are unwisely joined, a
> temporary table of 1,000 * 1,000 * 1,000 records may be attempted.
It is not always an unwise join,
sometimes it is a simple join that makes it crap out.
Imagine a table called membersToGet.db with one column.
Field #1 MemberId which is a Primary Key and data type is Alpha - 11 char
wide.
Say...50 records in it.
A query linking that to a /large/ claims table, ( say 500 meg,1 million
records )
and joining on MemberId in the claims table,
frequently makes this problem rear its ugly head.
Certainly not an unwise join - it is a simple join, and the only join that
makes sense.
I assume paradox keeps its intermediate results in a table,
and I assume that the size of the intermediate table becomes an issue.
If pdox was going to try and make a "hit table" of 50 * 1,000,000,
that makes sense it will puke.
How wide would you guess that the hit table is ?
A few columns wide , or the entire width of the large table ?
Due to the speed this error happens,
it is my ( unproven ) theory that these intermediate tables
do not exceed the max table size available as set in the BDE.
> I find that the best solution is to simplify the query into a /sequence/
of
> smaller queries.
Agreed, usually this is the best.
Like John, I have some large tables, and I have a client/server database
available ,
so I usually push up the smaller table to sql server and run the query
there.
Robert Wiltshire
Robert Wiltshire
> product," then throw away 99% of it to produce a very small answer. For
> example if three tables of 1,000 records each are unwisely joined, a
> temporary table of 1,000 * 1,000 * 1,000 records may be attempted.
It is not always an unwise join,
sometimes it is a simple join that makes it crap out.
Imagine a table called membersToGet.db with one column.
Field #1 MemberId which is a Primary Key and data type is Alpha - 11 char
wide.
Say...50 records in it.
A query linking that to a /large/ claims table, ( say 500 meg,1 million
records )
and joining on MemberId in the claims table,
frequently makes this problem rear its ugly head.
Certainly not an unwise join - it is a simple join, and the only join that
makes sense.
I assume paradox keeps its intermediate results in a table,
and I assume that the size of the intermediate table becomes an issue.
If pdox was going to try and make a "hit table" of 50 * 1,000,000,
that makes sense it will puke.
How wide would you guess that the hit table is ?
A few columns wide , or the entire width of the large table ?
Due to the speed this error happens,
it is my ( unproven ) theory that these intermediate tables
do not exceed the max table size available as set in the BDE.
> I find that the best solution is to simplify the query into a /sequence/
of
> smaller queries.