Queries! Use of memory 
Author Message
 Queries! Use of memory

Hi there world ( for the first time )

I am using Paradox Vers 5, Windows 3.11 and trying to run large queries
consisting of the following linked files  1*60000 records, 1*80000,
1*350000, 1*4500000 plus 5 other files and getting the error message Out
of memory. The machine has 16mb of ram and the maxbufsize is set to
this, there is 149 mb of disk space available and a swap file of 19mb. I
am not sure why the error is occuring but I assume there is no memory
available to store the results of the query as increasing the maxbufsize
has solved a similar problem when the largest file had 200,000 records.

Remove the asterisk form the address when replying

Cheers

Barry



Sat, 16 Oct 1999 03:00:00 GMT
 Queries! Use of memory

Quote:
> I am using Paradox Vers 5, Windows 3.11 and trying to run large queries
> consisting of the following linked files  1*60000 records, 1*80000,
> 1*350000, 1*4500000 plus 5 other files and getting the error message
> Out of memory. The machine has 16mb of ram and the maxbufsize is set to
> this, there is 149 mb of disk space available and a swap file of 19mb.

You are so short of resources it isn't funny. For what you are trying to
do upgrade to 32MB RAM and you need at least 3X the available hard drive
as the size of the largest total size of all tables involved in a query.

You've probably always wanted more machine. Here's your justification to
get it. Gigabyte hard drives are only a couple of hundred dollars (US).
RAM is almost free compared to what it used to be. Splurge.

Daniel A. Morgan



Sat, 16 Oct 1999 03:00:00 GMT
 Queries! Use of memory

Quote:

> Hi there world ( for the first time )

> I am using Paradox Vers 5, Windows 3.11 and trying to run large queries
> consisting of the following linked files  1*60000 records, 1*80000,
> 1*350000, 1*4500000 plus 5 other files and getting the error message Out
> of memory. The machine has 16mb of ram and the maxbufsize is set to
> this, there is 149 mb of disk space available and a swap file of 19mb. I
> am not sure why the error is occuring but I assume there is no memory
> available to store the results of the query as increasing the maxbufsize
> has solved a similar problem when the largest file had 200,000 records.

Your shortage of disk space is probably your biggest problem, depending
of course on how complicated your queries are.  If the anticipated size
of your final answer is not that big, you may have the option of
simplifying your queries.  One big query can take forever to run and
create a horendous number of temporary files in your private directory (
probably what is happening to you).  A series of simple queries to
accomplish the same thing may prevent the overload and be finished
sooner to boot.

Al Solter



Sat, 16 Oct 1999 03:00:00 GMT
 Queries! Use of memory

This is a _single_ query joining all the tables you mentioned? If so,
try spliting that up into a series of several smaller queries.
Ideally, first do the one that most restricts the answer set that you
will subsequently use.

I'd simplify this as much as possible, see if the simpler queries will
run, then begin dealing with resource issues if necessary.

Good luck,

Bob Martin
RDM Data Solutions

Quote:

>Hi there world ( for the first time )
>I am using Paradox Vers 5, Windows 3.11 and trying to run large queries
>consisting of the following linked files  1*60000 records, 1*80000,
>1*350000, 1*4500000 plus 5 other files and getting the error message Out
>of memory. The machine has 16mb of ram and the maxbufsize is set to
>this, there is 149 mb of disk space available and a swap file of 19mb. I
>am not sure why the error is occuring but I assume there is no memory
>available to store the results of the query as increasing the maxbufsize
>has solved a similar problem when the largest file had 200,000 records.
>Remove the asterisk form the address when replying
>Cheers
>Barry



Sat, 16 Oct 1999 03:00:00 GMT
 Queries! Use of memory

Some queries generate very large intermediate join tables. This is
especially the case when links are on non-indexed fields and there are
many tables involved.

If you can break the single large query into many small ones you are
usually better off - you can make sure the queries execute efficiently
by matching the database and index structure.

Peter Wachtel
Databars Australia.

Quote:

> Hi there world ( for the first time )

> I am using Paradox Vers 5, Windows 3.11 and trying to run large queries
> consisting of the following linked files  1*60000 records, 1*80000,
> 1*350000, 1*4500000 plus 5 other files and getting the error message Out
> of memory. The machine has 16mb of ram and the maxbufsize is set to
> this, there is 149 mb of disk space available and a swap file of 19mb. I
> am not sure why the error is occuring but I assume there is no memory
> available to store the results of the query as increasing the maxbufsize
> has solved a similar problem when the largest file had 200,000 records.

> Remove the asterisk form the address when replying

> Cheers

> Barry



Sun, 17 Oct 1999 03:00:00 GMT
 Queries! Use of memory

Quote:

> I am using Paradox Vers 5, Windows 3.11 and trying to run large queries
> consisting of the following linked files  1*60000 records, 1*80000,
> 1*350000, 1*4500000 plus 5 other files and getting the error message Out
> of memory.

I had a similar problem once.
In my case the query was creating extra answer records due to some joins that
select all records from one of the tables for every record in the other.
(Just try to imagine how much extra work poor PC has to do in this case,
 all those bits flying back and forth, it may even get blisters on the hard
drive heads... :-)

My advise - before rushing to order HD upgrade and extra memory (spending a
couple of hundred pounds(UK) and then getting the same error message in a month
- as the database grows), spend a bit of time to check the query.

Estimate, how many records should the query produce for a certain selection
criteria, if > 1,000,000 then goto Client/Server endif.

If You are > 100% sure that the query is OK, split Your large query into
several simple ones (hard to imagine a 4,500,000 records database with the data
model that does not allow reducing the number of tables in a query to get an
intermediate result)...

Hope that will help You and your computer.



Sun, 17 Oct 1999 03:00:00 GMT
 Queries! Use of memory

also the special queries, (Changeto, Delete, Set), can generate humungous
temporary tables that can run you out of memory in a big hurry...

Brad Miller



Sun, 17 Oct 1999 03:00:00 GMT
 Queries! Use of memory


Quote:
>> I am using Paradox Vers 5, Windows 3.11 and trying to run large queries
>> consisting of the following linked files  1*60000 records, 1*80000,
>> 1*350000, 1*4500000 plus 5 other files and getting the error message
>> Out of memory. The machine has 16mb of ram and the maxbufsize is set to
>> this, there is 149 mb of disk space available and a swap file of 19mb.
>You are so short of resources it isn't funny. For what you are trying to
>do upgrade to 32MB RAM and you need at least 3X the available hard drive
>as the size of the largest total size of all tables involved in a query.
>You've probably always wanted more machine. Here's your justification to
>get it. Gigabyte hard drives are only a couple of hundred dollars (US).
>RAM is almost free compared to what it used to be. Splurge.

Another thought to consider is... can you break down that one query into a
sequence of smaller ones that are run one at a time?  If you introduce too
many tables into the query at one time then, depending on the version, the
Query Optimizer may or may not "guess right" as to what's the fastest way to
get the job done.


Wed, 20 Oct 1999 03:00:00 GMT
 
 [ 8 post ] 

 Relevant Pages 

1. query analyzer using too much memory

2. Query Slows after memory has been used...SQL 7

3. Query Analyzer using too much memory.

4. Memory Allocation for SQLDA using (Embedded) Dynamic SQL Queries

5. Not enough memory/Virtual memory error from large query

6. UK query - Delphi3 - NT4-Memory leak on SQL Query to SQL/server 6.5

7. Query using UDF joined to a sub query vs Temp Table to sub Query

8. How would I get the list of word used in the query using inflection

9. Spooling output of query to test file using query analyzer

10. Using Query Results as Queries

11. Why would the same query on 2 servers uses different query plans

12. Using query resluts in the same query


 
Powered by phpBB® Forum Software