'Out of Memory' 1

[nmair]

I receive this notification message everytime I run a report that extracts data from only 1 table but has possibly over 100 million records.

Anyway to work around this? Does increasing Virtual Memory on the PC help? Would reducing the records returned (through filtering in select expert) work around this problem?

Thanks in advance,

Neill

 

[kray4660]

I believe (and I have had this issue also), that CR usings local memory (and virtual memory) to process the records. So I would think that increasing the Virtual Memory and/or reducting the number of records returned.

Are you using a command or bringing in the table. If you are bringing in a table. Try using a command with a where clause. That would keep the records from ever getting to your computer.

I hope this helps.

 

[nmair]

Thanks for the help so far.

I did try increasing my VM from 2GB to 20GB, but no-go.

I'm simply just using the records from this table to do 4 Grand Totals(inside Report Footer). And a filter is placed on 2 fields (in Select Expert)

Other suggestions maybe?

Regards,

Neill

 

[kray4660]

Sounds like either you need to have a stored procedure created on the server or use a command that filters the data and does the grand totals. Wish I had a better answer.

 

[Turkbear]

Hi,
Revise your selection criteria so that it is all passed to the server for processing thus avoiding much of CR's memory usage..

see
faq766-3826

or

faq767-3825

Whayt database are you using?



To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[nmair]

Hi Turkbear,

how do I revise my select criteria? Do I do it by writing an SQL query and then adding it as a command where I select my datasource when creating a report? I know sql. Unfortunately I don't know which type of Database I connect to.

Thanks for your help.

Neill

 

[Turkbear]

Hi,
Use the Database..Show Sql option to see what SQL CR is producing with the criteria you currently have in the record selection formula. Use the ideas in those 2 FAQs to see if things can be optimised.

What datasource did you choose when creating the report?

It may be that using a command object and not selecting from a table may be the best way to go.



To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[nmair]

I chose 1 table from my datasource.

When using the command object would that be considered to be revising your selection criteria so that it is all gets passed to the server for processing consequently avoiding much of CR's memory usage?

Please advise.

Much appreciated.

Neill

 

[lbass]

What exactly are you trying to do? Why would you need to evaluate 100 million records? What criteria are using? What is the purpose of the report?

-LB

 

[nmair]

I'm just doing summaries, with no grouping, on the whole table. The SQL query placed in the command object is:

Select count("table"."field1"), sum("table"."field2"), avg("table"."field2")
From "table"
Where "table"."field4" = "some string1"
"table"."field4" = "some string2"
"table"."field4" = "some string3"
.......
.......
.......
"table"."field4" = "some string 50"

The reason for such a large where clause is because this criteria is entered by the user from a parameter list. I was originally not using the command the command object but instead just placing summaries in the report footer.

The report is for financial analysis. Thanks.

Regards,

Neill

 

[lbass]

What version of CR are you using? Logically wouldn't there be other criteria you could use to limit the records, e.g., a date range? You haven't hinted at what Field4 might be--is it an account or something?

-LB

 

[nmair]

We have version 11 here.

the records are card transactions and field4 is a specific merchant type.

Neill

 

[lbass]

So wouldn't you want to limit the records by date?

-LB

 

[nmair]

the table stores 3 months of transactions only, which is what is required.