i have two applications (server and client), that uses TQuery connected with TClientDataSet through TDCOMConnection,and in some cases clientdataset opens about 300000 records and than application throws exception "Temporary table resource limit".
The error might be from the TQuery rather than the TClientDataSet. When using a TQuery it creates a temporary table and it might be this limit that you are hitting. However in saying this, loading 3,000,000 records into a TClientDataSet is a bad idea also as it will try to load every record into memory - which maybe possible if they are only a few bytes each but it is probably still going to kill your machine (obviously at 1kb each you are going to need 3GB of RAM minimum).
temporary table resource limit bde firebird
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..) Can anyone think of something I have missed to check on the Pdox side..??? Thanks.. John..
Do you have the temporary table creation turned off (fast queries)?Otherwise it should be making some temporary tables as you know. Maybe oneof those is somehow exceeding the max for the block size setting? just aWAG.Denn SantoroPresidentResource Development Associates Offices in the United States and GermanyProviding solutions to health care, business, governments and non-profitssince 1982
Right.The temporary resource limit error comes as it tries to create the queryanswer,and when pdox reports the error condition and halts the query,you probably dont see any of the private/intermediate tables behind thescenes.
> 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..)
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.From: Shaun Daigle Subject: Temporary table resource limit - HELP !!!!!!Date: 9 June 2000 23:00I 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 TABLENAMEAt 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 ConfigBLOCK SIZE : 32768LEVEL : 7I 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 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 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.-- Bertil IsbergCTECHFAQ newsgroup: corel.wpoffice.paradox-faqParadox Buglist: _par/index.htm
Did you try a CheckPlus query? It should require less temporary resources asno grouping and ordering is required.--Bertil Isberg - CTECHFAQ newsgroup: corel.wpoffice.paradox-faqParadox buglist: _par/index.htm
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!
There are several threats prevented by this job: maladministration issues when database volumes or external tables are being created in %Firebird%\Bin folder, very big firebird.log which can exhaust all places at drive with Firebird installation, and some other problems.
Relative paths are treated as relative to the path that the running server recognizes as the root directory of the Firebird installation. For example, on Windows, if the root directory is C:\Program Files\Firebird, then the following value will tell the server to store temporary files in C:\Program Files\Firebird\userdata\sortfiles, up to a limit of 500 Mb:
Database servers love RAM. The more that is available to the server, the faster it goes. However, that 2Gb barrier kills Superserver in an overpopulated environment because each Superserver connection uses about 2Mb of RAM to instantiate its process thread and maintain its resources. So 500 Superserver connections will use a gigabyte, leaving somewhat less than a gigabyte of total addressable RAM available to the database server for processing, sorting, transaction accounting, lock tables, in-memory storage and new connections.
A way out of the 32-bit RAM limitations is to build a 64-bit Superserver. A 64-bit Superserver is possible on POSIX platforms where the OS has stable support for 64-bit hardware. An experimental 64-bit Superserver for Firebird 1.5.2 on AMD64/Linux i64 was released more than a year ago but it proved unstable. A 64-bit AMD64/Linux i64 build is included in the Firebird 2.0 beta 2 field test armoury. On Windows, it is not yet possible to build 64-bit capable Superserver because of issues with the current releases of the Microsoft Visual Studio 7 C++ compiler, the release-standard build environment for Firebird 2 on Windows. Private builds of both v.1.5.x and v.2.0 on non-Microsoft compilers are known to be working stably in production environments and the core development team has signalled its intention to keep a 64-bit Windows release in sight for Firebird 2.0.
Trying to run a database server on a system that is running competing services, such as a web server, Exchange server, domain server, etc., will risk having those other services steal resources (RAM and CPU time) from the database server, denying Superserver even the 2 Gb that it is capable of using or limiting the number of connections to Classic, and slowing down database response time. 2ff7e9595c
Comments