Decrease data retrieval for development

[ddnh]

I have a report that takes about 15 minutes to return over 700,000 records. This obviously stinks for development whenever I add an additional field. The developers are working on the query to speed things up, but until then, is there a setting for Top N records or something that I can do so all of the records aren't read each time the report is refreshed? I'd obviously change/remove this setting once the report is ready for production. I already have date parameters and select only one day...but there is still too much data.

I'm using CR 10. My datasource is a command pulling data from SQL Server.

 

[synapsevampire]

Just alter the Report->Edit Selection Formula->Record to limit the rows returned, or modify the Command to pull only a days worth of data or some such.

Using the Add Command may be part of the issue as well depending upon what further querying is required.

Try contructing the report using conventional tables (I suggest using Views rather than tables to improve reusability and ease maintenance).

Then use the Record Selection Formula to filter the data. I have a FAQ on optimizng SQL pass through (written for CR 8.5, but still applicable):

faq767-3825

-k

 

[lbass]

You can also go to file->print->preview sample and set the number of records you want to run.

-LB

 

[Madawc]

Or you could add an extra test in record selection, restricting it to a narrow range of accounts while you are still developing it.

Where I work, we have a miniture version of the database, with the same links but far fewer records. Depending on how much development you are doing, this mgiht be a useful option.

Madawc Williams (East Anglia, UK)

 

[synapsevampire]

LB: Unfortunately if it takes 15 minutes for the database to return data, which is usually the problem, that setting won't help.

I like Madawc's solution as well, we term it the Golden Database. No changes allowed, all known values, great for testing and QA.

-k

 

[ddnh]

SV: You're right about the preview sample. I changed it to 100 records...but stopped waiting after 5 minutes.

I'll have to see if we can setup a development database.

Thanks for all of the tips.

 

[synapsevampire]

But did you try what I'd originally suggested, it's faster and easier...

Go to Report->Edit Selection Formula->Record and place some criteria which limits the data significantly.

Since we don't know which database you're using, or anything technical, I can't advise you well. It would end up being something like:

...
and
({table.datefield}) = currentdate

or if the data isn't that fresh, try:

...
and
(
({table.datefield}) = currentdate-30
)

or some such...

-k
-k

 

[ddnh]

Yes I did. I wrote in my original post that I had already limited it to one day. After that I tried also limiting to one company. Neither seems to speed it up much. I think there must be something really wrong with the query forit to be taking this long. I may hold off a while until that can be fixed.

 

[kskid]

Are any of the mentioned fields a primary key or is indexed in the database? You'd be surprised at the turnaround if you can use the primary key or have an index created on other heavily selected field such as a date field. Check with your DBA